Keep your spreadsheet simple

Spreadsheets can range from very straightforward – simple lists – to very complicated, with all sorts of macros and shiny user interfaces (you can see one of these coming in advance if you hear it described as a “tool”).

I deal with the ones in the middle – fairly complex data sets that don’t justify or aren’t suitable for creating a fancy “tool”, but where you still want to do some analysis and create some outputs.  If you are creating one of these middling spreadsheets, you can make things much easier on yourself if you think about the following:

• Don’t leave blank rows or columns.  Row 1 should be for your column headings, and there should be no blank rows beneath this or in the middle of your data set.  Avoiding blank rows and columns makes sorting and filtering the data so much easier.
• Don’t merge cells.  It may seem a neat trick but at some point it will mess up your sorting, filtering, formatting, copying and pasting, because it creates a “kink” in the layout of the cells.
• If you must have formatting, make it conditional formatting, so that it automatically changes if the value in the cell changes.  If you don’t do this you will have two things to worry about in each cell (the format and the cell entry) rather than just one (the cell entry).
• It is easier to combine values from different cells than split them.  If you’re not sure if you need a separate column for first name and surname, keep them separate for now.  You can easily join them later, but splitting them out can be tough.
• If it is humanly possible, have all of your data set in one table.  Don’t be tempted to start another table half way down a page or on a different worksheet.  If you split your data into three tables, any changes you make to the layout you will have to do three times, which wastes time and increase the chance of error, and you can’t analyse the whole lot in one go.  If you often only want to see a small subset of your data you can use filters.
• Don’t EVER calculate values manually.  You are using a spreadsheet.  Use it.
• If you create a graph that you might want to use in a report or presentation, have Excel create it in a new worksheet, rather than as an object in the worksheet with the data.  It looks better and will copy across to your document more reliably.
• Don’t try and create big tables of numbers in a spreadsheet for presentation or inclusion in a report.  Spreadsheets are for analysing.  If you want to present a data table that you can’t do in a Word table, it is too big.
• Don’t hide rows.  If they are rows with data, use filters; if they are blank rows, delete them.
• Try not to hide columns either.

Here endeth the lesson.