Most people who want to produce an Excel spreadsheet make their first mistake when they fire up Excel.
So why is that a mistake and what should we be doing instead?
What we all do when creating a spreadsheet is to start up Excel, enter some row headings, enter some column headings, put in some data - real or sample - and then analyse the results with some calculations. This inevitably means the results or 'answers' will be far over to the right hand side of the sheet or way down at the bottom.
How many of your spreadsheets have the answers way down the sheet at some arbitrary row. And that always needs moving as soon as you add or remove data.
The best place for the answers is always at the top left hand corner of the sheet because that's always the easiest place to get to. And you don't need to keep on moving it.
I think creating a spreadsheet is a four step process.
Step 2 is switching on Excel and actually building the spreadsheet.
Step 1 is grabbing a pen and paper and sketching out a design for your spreadsheet. Take the time to decide what your output needs to look like and you're far more likely to have a working and workable spreadsheet.
What questions are you answering? How are people going to use the information you're collecting and analysing. Which bits of the data need to be the most visible? Always think about the output first.
And don't feel you have to think (or act) conventionally because many of the reasons we do things the 'usual' way have more to do with pen and ink than modern day electronics.
Totals, and other statistics, often end up at the bottom of columns of numbers because that's where they always appear, but the original reason for putting the total at the bottom has far more to do with adding up the way you did when you first went to school than any sort of conscious decision.
If you put the totals (and other summary statistics) at the top of the sheet, above the data, you can lock the first few rows on the screen using Freeze Panes so any changes to the data are immediately reflected in the summary statistics. You also get the added benefit that the answers are easy to find and you don't have to keep on moving them every time you add some data.
If you do spend some time designing your spreadsheet before you start building it, you will definitely save yourself time, effort and aggravation in the long run.
And the two missing steps - that's for another post!