You know it’s true: Spreadsheets have errors like dogs have fleas. It is generally accepted that nine out of every ten spreadsheets suffer some error, and consequences can be severe:
- A cut-and-paste error cost TransAlta $24 million when it underbid an electricity-supply contract.
- A missing minus sign caused Fidelity’s Magellan Fund to overstate projected earnings by $2.6 billion (yes, billion) and miss a promised dividend.
- Falsely-linked spreadsheets permitted fraud totalling $700 million at the Allied Irish Bank.
- Voting officials reported spreadsheet irregularities in New Mexico and South Africa.
- A new drug introduction was delayed several months by an untested macro, costing the pharmaceutical company profits and its patients misery.
9 Quick Ways to Idiot-Proof your Spreadsheets
1.Use Freeze Panes So Your Audience Always Understands What They’re Looking At
This one is a must have.
Honestly, it’s painful to open up “completed” spreadsheets which don’t have Freeze Panes enabled.
Freeze Panes allows you to always have headings fixed at the top or left of the Excel window, so headings are always visible when you scroll around the page.
Without Freeze Panes, your audience will have to scroll back up to the top of the page to remember what each column of data represents. If you’re in a meeting or presentation, this can waste precious time and cause your audience to lose focus.
To freeze the top row, use the shortcut Alt-W-F-R. To freeze the first column, use Alt-W-F-C.
2.Hide unnecessary sheets
If you have sheets in your workbook that your audience shouldn’t alter, then hide them.
Good examples of this are any sheets that contain reference data, lists, named ranges, or anything else that only exists to complete a formula or is an intermediate step in your work.
You can hide sheets by right-clicking on the sheet name in the bottom left corner of the screen, and selecting the Hide option:
3.Hide rows/columns that aren’t in use or unnecessary
If you have some columns of data that don’t really need to be visible, then hide them!
Use Ctrl + 0 to hide the current column, or Ctrl + 9 to hide the current row.
4.Use consistent formatting to advise which fields should be changed (if any)
Some Excel workbooks are designed so that the user should edit a small number of values, to get the results they want.
They might want to select a department number, a specific date or period, or enter their own name.
And you have probably set up the file so that those values are included in formulas somewhere else in the sheet.
The potential issue here, is that your audience should only be playing around with the cells you specify.
Here’s a basic measurement calculator I created in Excel, without any formatting.
This file is helpful because you enter in your imperial measurements on the left, and the relevant value in the metric system is calculated in column E.
But there’s one issue.
It’s not obvious to see which cells should be changed, and which ones have formulas, right?
The file looks really bland and boring, too.
Now let’s look at the same file with some basic and consistent formatting, to aid the user in only modifying the relevant cells.
5.Use Data Validation messages to give hints to your audience
If your sheet is using drop down menus to select specific values, then you might want to give some hints relating to what values should be entered.
When you open the Data Validation options (shortcut: Alt-A-V-V) in Microsoft Excel, there are two tabs that can give hints to your users.
In the Input Message tab, you can cause a note to appear whenever the relevant cell is selected.
Here’s an example:
6.Go to the first sheet and/or top left cell before saving
Not too many people are aware of this, but Excel remembers which sheet and cell(s) are selected when you save your workbook.
And as a result, the next time you open the file, you’ll pick up exactly where you left off!
7.Remove any formulas references to other files
Referring to data in other Excel workbooks is sometimes necessary, but if you’re sending a file to your boss or your teacher, then those file references probably won’t work when they open it up.
It’s not very professional and could potentially lead to privacy issues if your potential customer sees a formula containing a filename reference like “G:\SecretManagementDrive\AnnoyingClients\CompanyABC\testfile.xlsx”.
The best option here is to override the data by copying the data and pasting it in the same location but using the Paste Special option and selecting “Values”.
8.Name all sheets, tables and graphs
Nobody wants to see Sheet14. Rename it to what it actually represents!
Don’t worry, any formula references will automatically update to refer to the new name.
9.Keep the formulas so your calculations can be understood
As long as you have the original data in your Excel workbook, it’s always a good idea to retain the formulas so people can understand your work.
The main reason for this is that people will be able to retrace your work and understand your file much easier. And if someone wants to make a small adjustment, all your subsequent calculations will still apply.