Traci Williams guides us through the best ways to set up a spreadsheet
I am often asked, “What is the best way to set up a spreadsheet?” Sadly, the answer is… there is no best way. It is completely dependent on the data you have and what it needs to do.
One Sheet or Lots of Sheets?
Some spreadsheets can be created with a separate (identical) sheet for each month / product / customer / supplier, etc., as this provides a functional sheet for each area, and serves as its own report too. This is a typical setup, so it’s likely that a user may inherit spreadsheets that have historically been created in this way; therefore, it feels familiar, and this setup continues to be used.
However, having multiple sheets also requires layout, formulas and formatting to be maintained and amended individually on each sheet. This can increase the risk of error and becomes time-consuming (and tedious) to manage, especially if the layout needs to change at any point.
When data is on separate sheets, it also makes it difficult to consolidate and can require the use (and knowledge) of formulas, or the consolidate function, which again increases risk of error, and the potential for reconciliation issues too.
Pivot tables (i.e., THE best way to consolidate data) can only be used with data from one source, so having multiple sheets removes the possibility of utilising this fantastic function.
Most of the time, I would recommend keeping data on ONE sheet, with ONE layout and ONE set of formulas, and then using functions such as Filters, Find, Sort and Pivot Tables to consolidate or analyse the data.
Of course, there are always scenarios to which this rule won’t apply.
Totals – Top or Bottom?
95% of the client spreadsheets I look at include total formulas at the bottom of the data, and I always ask why this is.
The usual answer is, “That’s just the way we’ve always done it.”
I think this also stems back to school days, when we started maths and we were taught to write down numbers in a list, then draw a line underneath and put the total underneath the line. Then we just carried on doing the same thing when we started using spreadsheets, and sadly, most of us are so busy that we don’t have the time to question (or think about) something so trivial. So, let’s spend a minute to do this…
When totals are at the bottom of the sheet, it means we must scroll all the way to the bottom to see them, and when we print, we need to find the last page to find the total.
It also means that the totals could (potentially) be on a different page on every sheet, and so we must spend our time scrolling and searching for the totals. And if we had any formulas linked to totals, they would also be looking at a different row for each sheet and it would not be easy to identify if they were incorrect… not to mention that each formula would need to be created (or amended) individually too.
If we wanted to insert any rows to the bottom of the data, we’d have to be careful where they were inserted to ensure that the total formula still included those new rows, and if not, this would cause reconciliation issues (i.e., MORE work for us).
On most of my spreadsheets, I include some blank rows at the top of the data (Row 1 or 2), and I include my totals there. This means they are always at the top of the page (on every page) and easy to find. They are also going to be on Page 1 of every print, so again, no searching required. I also extend the totals formula so that it includes rows outside of the range, so I can add data to the bottom of the sheet, knowing I needn’t worry about it not being included in the totals.
Headings
For me, a spreadsheet should always throw out the most obvious information (so that I don’t have to work too hard!), so I always start a spreadsheet by creating a row of clearly marked headings for each column.
To ensure they stand out, I use functions such as:
- Bold text
- Wrap text
- Text alignment (centre)
- Borders
- Colour
- Font
This makes it obvious which row contains headings and which rows are part of the data set.
Colour
I tend to work with spreadsheets by colour as much as I can, just to make my life a bit easier. So, if I have a sheet containing sales data, I may apply a blue fill colour to the heading row, whereas for the next sheet containing expenses, I may use a green fill colour. This means that when I look at the sheet, I can see the colour and instantly know that it’s sales… without needing to read any of the data.
I also include colours within the sheet too, to make specific elements stand out. For cells containing formulas, I apply a pale yellow colour and then remember ‘DO NOT OVERWRITE YELLOW CELLS’. It can also make the spreadsheet less daunting when you open it, as you can visually see which cells need to be edited and which contain formulas.
If I was creating the spreadsheet for someone else to complete, I’d apply a pale green colour to the cells to be edited. That way, in my instructions I can simply say ‘edit the green cells’, as opposed to referencing individual cells / columns or rows.
I’d also recommend using conditional formatting (in the Data ribbon) to highlight specific data (which meets given criteria) within the spreadsheet too. This could be anything, such as:
- Values within a specified range
- Specific customers / suppliers
- Area / postcodes
- Age ranges
- Blank cells
- Errors
This helps data to visually stand out, and as the formatting is applied automatically, it is more reliable too.
Freeze Panes
Freeze panes (in the View ribbon) is a clever little trick that will always keep a header row visible, even when you scroll down the page. It can also keep columns visible too, and I’d highly recommend using this tool if you have lots of rows and columns, as it makes it so much easier to view.
Filters
Filters are the most versatile, useful and simple function, and it’s rare that I have a sheet that doesn’t use them. They enable the user to temporarily hide rows so that they can view a specific element (e.g., month / customer / supplier) based on the columns of data included.
Filters can be applied to more than one column and are completely temporary, so they can just be ‘unapplied’ in order to revert back to the original data.
You can also filter by cell / fill colour or conditional formatting icon too.
Named Range
Your sheet can be referenced based on the ‘absolute cell references’ (e.g., $A$3:$F$1000), and this will work perfectly.
However, you can also give a name to that range of cells too (e.g., ‘data’), so that you can use the name instead of the absolute cell reference.
The named range can then be used within pivot tables, formulas or print setting, and can also be used to simply select the data, which is particularly useful when you need to use the sort function.
Using named ranges becomes really powerful when you create multiple pivot tables linked to the named range. When the data range changes, you can update the named range ONCE and ALL pivot tables will automatically update. However, when pivot tables are linked to absolute cell references, the user will need to manually update the ranges in EACH pivot table… this is not only time-consuming, but also prone to the risk of missing one out.
Protect Sheet
Each sheet can be protected so that data cannot be edited. This is a perfect tool to protect a sheet from being edited accidentally, or by someone without permission.
The great thing about protection is that you can also select specific cells that CAN be edited while the sheet is protected. I use this all the time to save myself from over-typing formulas by accident (I’m sure we’ve all done it at least once). Using this function also improves the accuracy of the data, as you have confidence that users will not have access to amend any of the formulas.
The protection can include a password too, and this can be as long or short as desired and can include upper- and lower-case letters, numbers or characters.
Please be warned that Microsoft DOES NOT provide a password recovery option if it is forgotten.
Adopting some (or all) of the suggestions above will make your spreadsheet both easier and more efficient to work with, and will allow you to work perfectly with formulas, macros, pivot tables, pivot charts and dashboards.
