Introduction to creating and maintaining good spreadsheets
Because of its flexibility, Excel is considered an easy general-purpose tool for spreadsheets. However, being easy to use comes with its own drawback. It is very easy to make mistakes in complex spreadsheets. Errors that are commonly found are incomprehensible calculations, complex relations, hidden information or just plain mistakes.
We want to help you create and maintain good spreadsheets. That’s why we’ve set up a list of expert Excel best practices, that address commonly made mistakes when using Excel. Use these tips as a tool to create clear and concise spreadsheets, right from the start.
21 Expert Excel Tips
1. Preparation of a good Spreadsheet
The first of our absolute Excel best practices is to choose an organization standard before developing your spreadsheet. Stick with it for as long as you’re using the spreadsheet. An organization standard sets the stage for all future users who end up working with the spreadsheet. A shared standard improves communication and saves up in development time. Standardized organization may include cell formatting, general layout, color scheme, ordering, etc.
2. Create Worksheets with the Future in Mind
It is important to be prepared. You might come across situations in the future that weren’t present when you started setting up your spreadsheet. For instance, there may be additions that come into play later. Or perhaps you need room for values that depend on events that are bound to change over time. Try to consider as much future factors as possible that might possibly force you to change a worksheet. By preparing for any future developments, you increase the lifespan of the worksheet. A longer lifespan means you waste less time on creating a replacement worksheet. A good preparation is therefore one of the most important Excel tips we can give you.
3. Think about the Order of Worksheets
Put different kinds of data on different worksheets. For example, use the first few worksheets for input information, the following worksheets for calculations and the last sheet as a presentation worksheet for graphs and results. Limit the amount of tables per worksheet to just one. Multiple tables per worksheet cause problems when attempting to sort, insert or format cells.
Explanatory worksheets show other users how the spreadsheet should be used.
When creating a large number of worksheets, add an explanatory worksheet or a worksheet dedicated to a table of contents. This makes it easier for someone to understand how the spreadsheet is set up. Explanatory worksheets also show other users how the spreadsheet should be used.
4. Choose Clarity over Looks
If your worksheet is user oriented, use an attractive “Results Worksheet”. If not, don’t! Most worksheets work best when they are designed to provide clarity of all present calculations. You can try splitting up long formulas, but do not hide them to provide more clarity for users.
5. Keep your Timeline Consistent
Keep the timeline consistent across all worksheets, even if this leads to empty rows. Consistent timelines vastly improve the clarity of the spreadsheet and reduce the risk of incorrect formulas. For example, shortening four months into quarterly figures might give a more clear design in terms of presentation, but it also raises the risk of incorrect totals. An easy way to avoid this is to insert a single timeline per worksheet, ideally at the top, in a frozen header row.
6. Organize the Information Flow
Try to organize worksheets in such a way that information always flows from top left to bottom right. This makes it considerably easier for a user to understand how the sheet works. Exceptions to the rule are an “Input Worksheet” and/or a “Results Worksheet”. Putting these at the start increases clarity for users, which is especially useful for a user oriented worksheet. When managing the flow of information, avoid criss-cross dependencies as they greatly detract from understandability. At all times avoid circle chain relations, if a link to any previous data is needed.
7. Label Columns and Rows
Columns without clear and consistent names might not be problematic for the creator of the worksheet, but to other users it might appear confusing. That is why our next Excel tip is to always label columns of tables with simple names that consistently follow previously used naming conventions. The same applies to horizontal tables. In these cases, only use row labels.
8. Keep Formulas Readable
The 8th best practice in Excel is to split up calculations into multiple smaller calculations. This is a great way to increase readability, just like the correct use of spacing. Too many different operators or too many different references in a formula can make the formula unreadable. This causes the spreadsheet to be hard to use by anyone other than its creator. The original author ends up always being the one having to explain it to others, or even ends up being the only user actually able to work with the spreadsheet.
Many spreadsheets are hard to use by anyone other than its creator.
9. Avoid Repetitive Formulas
Avoid repetitive calculations. An exact duplicate of a formula doubles the risk for errors. In addition, changes in one formula are not automatically replicated in duplicates. Sometimes, these duplicates are overlooked, causing inconsistencies within the spreadsheet. When the use of the exact same formula is required, refer back to the first instance of the formula. In extreme situations it may be necessary to break a formula down to maintain clarity.
10. Avoid Fixed Numbers in Formulas
Use a separate input cell for fixed values and employ references to this cell in order to use it in calculations. A formula that contains fixed numbers is a major risk. If the value ever changes, it needs to be changed in every instance it appears. This is, at the very least, cumbersome when dealing with large worksheets. At worst, it is one of the easiest ways to create mistakes.
11. Do not Merge Cells
Merging cells is seldom good practice. It is usually done to improve aesthetics, but it eventually leads to an increased risk of problems with calculations and references. The biggest risk is making references to merged cells. In a referenced merged cell, all cells can be part of the calculations, but only one of those cells is going to be the correct part. This ambiguity gives way to an increasing amount of errors the longer the spreadsheet is being used.
12. Avoid Hiding Data
Hiding columns, rows, or even entire worksheets from view is almost never a good idea. It only increases the chance that a user overlooks something important when working with the spreadsheet, increasing the risk for errors. The only exception to this rule would be when it is absolutely necessary to hide information that somehow can not be put into a separate worksheet. Since it’s almost always possible to separate sensitive data, these cases are rare.
Hiding data increases the risk for errors.
13. Build in Data Verification
Building in data verification, such as audit tests, alerts and automated checks, is a good way to avoid making any damaging changes or additions to existing work. It is important to build these in when the worksheet is created. Not doing so often leads to forgetting about them later on. Data verification is an excellent way to build a strong foundation for worksheet maintainability.
14. Save Styling for the End
Excel’s formatting options are quite extensive. They allow for the fine-tuning of cell appearance, values, and plenty of options for colors, borders, and features alike. While styling can help keep a spreadsheet understandable, the process of doing so includes abstracting information for the viewer. This is good for the final product, but makes development burdensome and increases the risk of errors. The best way to go is saving the style of a worksheet for last.
15. Keep Styling Consistent
Your choice of styling should remain consistent throughout the entire spreadsheet. A simple and consistent style for formatting is critical to help viewers understand your spreadsheet. Always include a legend. Abbreviations and colored cell definitions can be listed on a separate worksheet if needed.
16. Keep Conditional Formatting Simple
Conditional formatting helps the viewer understand how the spreadsheet works. Complex formatting rules defeat that purpose by obscuring how the spreadsheet is set up. It also causes confusion for the user. Keep the rules for conditional formatting simple (e.g. changing colors based on cell content).
17. Use Positive Numbers
Ever accidentally subtracted what should have been added? Chances are this was the result of an input cell that was entered as a negative number. Always try to build a spreadsheet that promotes the use of positive numbers.
18. Be Clear Which Units Are Used
Make sure viewers can always trace the units that are used in the spreadsheet. You can do this by either setting the units in the cell properties within Excel, or you can mention them in a column label. But beware: never type a currency symbol (€, $, £ etc.) directly after a value. This causes problems when using the value in a formulas.
19. Clarify your Sources
When complicated calculations in Worksheet A are made using cells from Worksheet B, show the viewer those cells from Worksheet B in Worksheet A in your spreadsheet. Users that trying to understand how the calculation works can stay on the same worksheet, instead of having to switch worksheets. Make your references clear and understandable. Without proper formatting, references can cause a worksheet to look cluttered, especially if there are a lot of them.
20. Avoid the Use of Macros
Always use Excel’s solutions before resorting to VBA macros. VBA macros make the spreadsheet less transparent, as they abstract away logic. For some tasks, VBA macros even perform worse than Excel’s default solutions.
21. Use Simple Ranges
For many calculations in your spreadsheet, it is good practice to incorporate additional empty rows or columns. This prevents future edits of the spreadsheet from messing up your formulas. The less you use separate small ranges, the smaller the chance of erroneous calculations in the future.