Professional looking worksheets

I start with basic layout below, and there are more pages here....
Navigation
Working with Controls
Rangenames
Formatting
Lookups

The basics 

Let's start with a simple example.

Given a basic model like that alongside, there are quite a few things you can do to improve it. I would

  • include a column/row at top and left to give the model room to breathe
  • remove the gridlines (Tools, Options, View tab) - but some users can't live without them, so check first
  • possibly also remove the row/column headers, if the users don't need them
  • colour code the inputs so they stand out clearly, and align them all to the left
  • format all the numbers so only the important digits show. There's no point showing 5 decimals if they don't matter. I'm an actuary, and I was taught to round numbers so that the reader can see clearly how accurate they are - so if your estimates are accurate to within $1 million, round to $1 million.

    • See what you think, below

     

    Actually, I've done a bit more....

  • customised a couple of the standard colours to give me lighter colours for cell shading (how? - see here)
  • increased the row height of the input cells to 15 so as not to crowd them, and put a white border around them so they are clearly separated
  • used conditional formatting* to lightly underline every second row in the table of results - I wouldn't overdo this because too much will slow Excel down

    • *The formula I used for (say) row 11 was =(MOD(B11,2)=0) where B11 is the Year column. So the conditional formatting will trigger when the year is even.

    I wouldn't expect everyone to agree that this is the ultimate layout - there are many different ways to go. You need to experiment to find what works for you (and the people using your models).

     

    The animated GIF alongside allows you to directly compare the before and after views, and I've added a third, more conventional approach which frames the table in a box, and the individual lines are picked out with a soft line. For me, the table lines are too strong. I prefer the version above.

    The main change in both revised versions is to highlight the set of inputs and the table of outputs, with enough white space to make it easy on the eye. The data stands out. In contrast, the original table is a sea of labels and lines and meaningless decimals, all of which create clutter and make it more difficult to see the data.

    The test of success is how quickly, effortlessly and accurately your reader can understand the model. Of course, a model isn't just about colours and lines, so we'll look at design separately.

     

     

    Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.