Five more tips for better spreadsheets
/Last week I wrote a post giving you five tips for improving your spreadsheets, and in that post I promised to share five more tips in the future. the first five tips were:
Inputs, calculations & outputs go on different worksheets
Use an input in only one place (so it is easy to change)
Do not hard code numbers into calculations
Break up long formulas into component calculations in separate rows/columns
Use the exact same formula across all the cells in a row
Read the post to get an explanation of these tips.
Below are five more tips, some of which build on the first five.
6 Do not mix up time periods in a work sheet
In tip 5 last week I recommended that you use a common formula across the whole of a row. One reason that people don’t use the same formula across all rows is because they insert sub-totals. In a spreadsheet with monthly columns they might have annual totals inserted every thirteenth column. There is, I think, a better way by using a consistent time period in a spreadsheet and then creating other spreadsheets that summarise the data in alternative time periods.
So, if you need monthly and yearly figures, first create your spreadsheet as monthly columns. These columns can have several rows of headings as needed, to show the count of the months, the financial year, calendar year, etc. What you then need to do is create a new worksheet for the annual totals. In this worksheet you can use a ‘SUMIF’ formula to sum all the amounts that are in year 1, year 2, etc.
This is even more flexible because, for example, if your financial year is not a calendar year you can easily create separate worksheets for each way of analysing the data, something you cannot easily do any other way.
7 Do not use named ranges
If you put all your input data into an inputs worksheet it can be tempting to use named ranges to define them, to make it easier to write formulas in the calculation worksheet(s). This would be easier for you as the creator of the spreadsheet but it makes things harder for reviewers, auditors and people trying to update it. This is because it is quite difficult in Excel to work out what range the name defines. It can be done, but the dialogue box will list all the named ranges and the reviewer will have to scroll to find the one they want and probably will have to keep notes of them to avoid repeatedly having to make the check.
If you don’t use named ranges the reviewer can see exactly which cells and ranges are being used in calculations. This is much better.
8 Do not hide rows or columns
When creating a spreadsheet you might want to simplify what is seen by hiding some of rows and/or columns using the Hide function. If you do this it can be difficult for a reviewer to understand what is going on. Essentially they have to look at all the row and column numbers and letters and spot where they are not consecutive. That can be surprisingly difficult to do.
A better way of tidying up rows and columns is to use the outlining function instead. This function can be done automatically (it finds the subtotals and totals for you and assumes that is where you want the outline to be) or you can do it manually. The advantage of this method is that small boxes with + or – signs appears in the top and left margins making it obvious to the review that there are rows/columns to open out or collapse as the case may be.
9 Do not use macros
Macros are computer programs (scripts) embedded within the Excel file
Macros are an advanced technique and perhaps a relatively small proportion of Excel users could create them. Even if you know how to create them, my recommendation is that you do not use them. This is for several reasons:
like most of my tips, the use of macros make reviewing and updating your spreadsheet difficult (this would be less the case if you created full documentation for the macros but, let’s face it, would you really do that)
macros are not supported in all versions of Excel so you cannot be sure other users could even use the macros
if you are emailing the spreadsheet it may be seen as spam because it is an executable file (a programme) and not a data file.
10 Hide the gridlines
My reason for recommending this is because the faint grey cell borders add a lot of visual clutter to spreadsheets which is not needed. I think the final spreadsheets are much cleaner and easier to understand without the cell borders. Apart from anything else, it makes any other formatting that you use (colours, shading, underlining, etc) much clearer.
Gridlines are the default option for all new spreadsheets. To turn them off, on a worksheet by worksheet basis, the easiest way is to select the View tab in the menu bar and uncheck the Gridlines box. This works for Windows and Mac versions.
This last tip causes the most discussion when I teach this subject face to face. I don’t quite know why that is. I know very few people who would change the default printer settings to add back the cell borders when printing their spreadsheets.
So there we go: ten tips for better spreadsheets. For more
