Spreadsheet design – 10 top tips

I have been creating and implementing spreadsheets, databases systems for over 20 years. Hence it seemed appropriate to come up with my definitive list of Top 10 tips for good spreadsheet design:-

1. Is a spreadsheet really the ideal solution ?

It actually amazes me how many individuals start using spreadsheets when a proper functioning database is required. If you have multiple spreadsheets interacting with each other, then it is a strong indication that a database is required.

However it can be a good idea to start with a spreadsheet first to understand what you are trying to acheive. When you have grasped this you can think about having a proper database system. In essence the spreadsheet system is a prototype for the real system. This was the approach that we adopted at the Department of Health for one of their systems.

2. Thinking time

Regardless of whether or not it is a spreadsheet or database solution you require, don’t start underatake any real spreadsheet design work. Instead have some thinking time – preferably away from the desk. Use it to really understand what you are trying to acheive.

I remember one of my earliest bosses being gobsmacked that I was literally apparently doing nothing. Even when I explained my approach he remained sceptical. Only later on when he saw what I came up with (which was I have to admit an ingenious solution and led to us winning a £ 10 m order) did he apologise, I saw him years later and after working with me, he changed the way he viewed people.

3. Inputs, Calculations, Data and Outputs

One of the best practices for effective spreadsheet design is to have seperate tabs for inputs, calculations, outputs and data. Further, the best practice is to have inputs in the front tabs, then calculations, and finally the output tabs. Any data tabs should go towards the nack of the workbook. It is actually good practice to colour these types of tab in distinct colours.

4. Colour Code Cells

There are in essence five types of cell in a spreadsheet:-

a) Those that require an input value

b) They need a value from a drop down

c) It contains a calculation

d) it is a total row / column

e) It is a label

So for each of these types of cell, have a different colour. Further, put the colour scheme at the top of each sheet so that it can be easily followed. Some people suggest that values that contain calculation cells should be protected – but this can be cumbersome to manage in a large spreadsheets.

I recommend that the row and total columns are made bold so that they stand out.

5. Work from left to right It may sound obvious but start at the left hand side of the worksheet and move towards the right. If your spreadsheet is something that shows time related values (i.e Jan, Feb, Mar etc) then use the same row and column for a particular month on eacvh sheet. So if on one sheet Jan-15 is in cell C2, then it should be in the same cell on every sheet.

6. Use consistent formulae in any block of cells I have to say that of all the potential pitfalls / errors in a spreadsheet design, this one is the most dangerous. In each block of cells the formulae used need to be consistent. If not, it will be very hard to locate an error. If it is necessary to have different formulae break the block down into seperate blocks, each with consistent formulae. Then create a total block that sums across each of the individual blocks.

If there are only a couple of individidual blocks then one possible workaround is to use nested IF statements. However do not go over the top with this approach, as these can be very memory consuming and slow down your workbook.

7. Use Monthly, Quarterly and Yearly Totals for reconcilations If your data is time dependent then create monthly, quarterly and yearly blocks. After this have totals for each of these ranges, and cross check the totals. Although it will significantly increase the size of your workbook, it will acts as an invaluable audit for your spreadsheet design. Indeed part of the initial thinking process for the workbook, should be devoted to working out what cross checks are needed in the model.

Further, having quarterly and yearly information acts a useful summary for management.

8. Use Graphs That old cliche that a picture is worth a thousand words, could be modified for a spreadsheet – a graph is worth a thousand cells. A good graph will be labelled and use the correct format (i.e line . bar , pie) for the data being represented. Further any anomalies in the data ought to be easily identified.

9. Have a summary sheet Almost all the spreadsheet models I develop are extremely complicated – some with over 50 worksheets. Hence a single summary sheet with a couple of graphs can be very useful.

10 Save your work – often I have been forever haunted when I lost a days work through my spreadsheet being too big and I was under presure to deliver. So save your work, and use version control in case you need to go back to an earlier version.

About the Author

Kapil Kapur is the Managing Director of Fingertips Intelligence who helps companies by managing their data more effectively. If you would like more information on how we can help your organisation  then get in touch on 0845 163 0149.

Leave a Reply