9 Quick Ways to Idiot-Proof your Spreadsheets (+Free Checklist!)
When you send Excel files to other people, you are probably hoping that they don’t mess around with the layout and design too much.
Personally, I hate it when people play around with my Excel files.
But fortunately there are ways to idiot-proof your Excel files so that your audience uses them correctly, doesn’t (easily) break the whole file, and can clearly understand all of the hard work you’ve put in.
Idiot-proofing isn’t just for your boss, by the way. It’s also for you.
Idiot-proof Excel spreadsheets are easier to navigate and clearer to understand. When you inevitably re-visit your work from six months ago you’ll know exactly how the spreadsheet operates.
In this post, you’ll get 9 simple ways to make your Excel sheets clean, intuitive and less likely to be broken by someone who thinks they know better.
And, at the end of this article you can download the checklist for free so you can keep it handy on your PC, or print it out and have it ready on your desk or at home.
- 1. Use Freeze Panes So Your Audience Always Understands What They’re Looking At
- 2. Hide unnecessary sheets
- 3. Hide rows/columns that aren’t in use or unnecessary
- 4. Use consistent formatting to advise which fields should be changed (if any)
- 5. Use Data Validation messages to give hints to your audience
- 6. Go to the first sheet and/or topleft cell before saving
- 7. Remove any formulas references to other files
- 8. Name all sheets, tables and graphs
- 9. Keep the formulas so your calculations can be understood
- Get the checklist!
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.
If you want to hide a different number of columns or rows (or both!), select the first column, row or cell that you don’t want to be frozen and use the shortcut Alt-W-F-F. Alternatively, click on View > Freeze Panes > Freeze Panes in the Excel Ribbon.
For example, if you select cell C4 and enable Freeze Panes, the top three rows and two leftmost columns will be frozen.
Using Freeze Panes is just one of the tips in the FREE eBook “10 Simple Microsoft Excel Tips to Save You Time”. Get the free eBook now!
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.
Now the file is significantly easier to understand, because the use of consistent formatting gives an idea of how the file should be used.
The person who uses the file only needs to change the values in the cells highlighted in yellow.
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.
Create an input message when cell is selected
In the Input Message tab, you can cause a note to appear whenever the relevant cell is selected.
Here’s an example:
It’s a good idea to keep this note simple and easy to understand.
What’s good about this option is that you can apply it to cells that don’t have any data validation, so you don’t have to worry about enforcing a drop down menu or applying any validation requirements.
Show an error alert when an invalid value is entered
Another option is to cause a alert to appear when an incorrect value is entered. The Error Alert tab gives you the options to customise the error message however you like.
You can choose the icon style as well as the message.
So if someone enters a cell that isn’t suitable for your Excel workbook, they will receive this message:
Want more tips on setting up a drop down menu in Excel? Read this to find out how.
6. Go to the first sheet and/or topleft 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!
This is helpful if you’re working on a specific portion of a workbook, but not if you’re giving it to your boss after all your work is completed. It doesn’t make sense to open up an Excel workbook in the middle of your raw reference data, for example.
When you save your file for the last time, go to the first sheet (since that’s where most people will want to start from) and go to cell A1 in the top-left corner.
You can use the Ctrl + Home shortcut to always get to the first cell in a sheet, or keep pressing Ctrl + PgUp to move to the previous sheet.
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”.
You can Paste Values using the keyboard only by:
- Selecting the relevant cells
- Copying the cells (Ctrl + C)
- Using the shortcut Alt-E-S-V.
If you aren’t sure if you have any references to other files, you can search your Excel workbook for things like “[” or “:/” which will appear in the formula. Use Ctrl + F to open up the Find dialog box, and make sure you select “Workbook” in the Within: option.
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.
Get the checklist!
Now that you know 9 fantastic ways to make your Excel spreadsheets smarter and idiot-proof, go download the checklist so that you can apply them at a moments notice!
Do you have any other hot tips on cleaning up your Excel workbooks before you present them? Let us know in the comments!