Spreadsheet Design 101

So many people, ignore spreadsheet design but it is important if you are building a complex worksheet or one that you will be referrring back to over time.

Spreadsheet Design


Two things make a good Excel spreadsheet. Good data is obviously one of these things but the other that is often overlooked is good design. Good design helps to ensure a reduction in errors which is obviously key.

A nicely formatted spreadsheet is nice and clearly it is easier to understand; however, a nicely formatted spreadsheet and well designed spreadsheet are not synonymous. A well designed spreadsheet is concerned with the organization of data and assumptions rather than appearance although obviously appearance is a factor – it is just not the primary factor.

Good design helps to ensure:

• Navigability
• Ease of use
• Reduction of repetitive input which helps to cut down on errors
• Locate errors more easily

 Too often people dive into creating a spreadsheet without a thought as to the end product. How many of you have had to cut and paste or insert rows and columns for data that needed to be moved later or to make room for data that had not considered when you started? I would be surprised if everyone reading this failed to raise their hand!

Before touching that keyboard consider the following:

• Who will be using the spreadsheet?
• What is the purpose of the spreadsheet?
• What are the required inputs?
• What mathematical expressions are you thinking about using?
• What are the desired outputs?
• How are you going to test the model?

It is highly recommended that after considering the questions above, you sketch out mentally or on paper how the spreadsheet should be laid out. You should have a minimum of 3 separate modules or areas and usually you will have at least 4.  These days, rather than modules, think of sheets within the Excel file.

• Input Area All the user inputs and defined variables (constants) Including assumptions and documentation
• Work Area Calculations The raw data and formulas to process the inputs – nitty gritty stuff
• Output Summarization and display of data
• Reports Charts and tables -(Presentation)

  

Clearly you could have a number of modules in the work area depending upon the complexity of the problem you are examining. You should plan on a number of modules in your work area if you anticipate a complex spreadsheet.

 Design Points

As you begin to design a spreadsheet you need to consider the following design points

Documentation

Yes- that old thing. Even if you hate to document what is going on with the spreadsheet, at least put the date, your name and the title of the worksheet or project. No one ever thinks they have the time to sit down and document but at least if you set a standard design with an assumptions section you will have a starting point later.

For everyone who says they have no time to document consider how much time you have spent staring a spreadsheet that you created 7 or 8 months before wondering what the heck that number is or what the purpose of a cell’s formula is. Admit it – we all have. I have one individual who admits every time budgets roll around he cringes because he knows that he will have to spend time figuring out what he did the previous year because he didn’t bother to document it in the worksheet and he can never find those little scraps of paper with the notes he made at the time.

Natural Flow

Ensure there is a natural flow of information down each worksheet instead of having to jump forwards, backwards and sidewards.

Headings

Excel in many cases assumes that there is a heading above the raw data. It also makes things much easier to read. Bolding the headings increases readability too as opposed to italicizing which is often difficult to read.

  

Blank Rows and Columns

  

• Blank rows and columns seem to be a favorite among accountants for a cleaner look; however, be aware that a lot of Excels built-in features assume a blank row or column means no more data. In other words do not break up data that you may need to sort or filter with blank rows.

•  For design purposes some people recommend leaving at least 4 blank rows above your headings and data as these can be used for viewing Totals instead of having to scroll down the bottom of a large spreadsheet. Also blank rows at the top of a spreadsheet are useful for criteria for database functions and the advanced filter feature.

  

Absolute Cell References

• Use absolute cell references wherever possible. It is much easier to change a single cell and have the impact ripple through the spreadsheet than to have to find the cell, change it and then copy it across or down and then view the impact. I think that you can also see how changing just one number is more efficient and would help to reduce potential errors.

•   If you do use absolute cell references set them up in a separate section so that they are easier to locate.

Cell References

• Always use cell references instead of typing in numbers. It increases readability and helps reduce errors and repetitive data entry.

•  It is also helpful to have input cells in the same column – reduces errors in referencing them in formulas.

Sorting

If possible, sort your data as many Excel functions such as Vlookup assume that your data is sorted.

Dates

Ozgrid.com recommends using real dates for headings and to format them appropriately. By this I mean if you want the names of the months as headings type them in as 1/1/2001, 1/2/2001, 1/3/2001 etc then format them as "mmmm". This is a very simple procedure that is all too often overlooked by many. If you have real dates as headings life will be much easier further down the road if you need to use them in formulas. Iff you don't need them -nothing is lost.

Use Single Cells

Keep formulas simple.
Don't put in one cell what could go in more than one cell. Say you have the names of 100 people to put into your spreadsheet; don’t put their full name in one cell. Instead, put the First name in one cell and their surname in the next cell to the right. If you need to place them into one single cell at a later stage, this can be done very easily by concatenating them; however, the same cannot always be said for the reverse.

Break Model Down

If the spreadsheet is complex, large or has a lot of linked cells break it down into manageable components. Errors increase exponentially as the complexity of the model increases. The frequency of use also impacts the potential error rate.

Test your spreadsheet

Test your model before you begin using it! 

• Test the formulas 
•   Use error checking formulas in adjacent cells – IF functions can be helpful
•  Test range names if you have used them
•  Test formulas and ensure they were copied correctly
•  Check the effects of rounding and number formatting
•  If using unusual functions check to make sure they were correctly applied.
• Test the charts and reports
• Compare results of data to previous versions or prior periods

 And test it again, after you make changes to it. Don't use the same spreadsheet year after year without testing it periodically after your have made revisions.
This last is an important point, that many people forget about - to their regret!