Create & maintain good spreadsheets

In order to create and maintain consistently good Excel files, there are a number of best practices to keep in mind. In this article we’ll provide a list of Excel pro tips to improve your spreadsheets and the way you work with them.

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.

21 Expert Excel tips & best practices

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.

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.

When creating a large number of worksheets, add an explanatory documentation 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 it’s not, then 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 spreadsheet 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 spreadsheet. When managing the flow of information, avoid criss-cross dependencies as they greatly detract from comprehensibility. At all times avoid circular references, 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. Read more about the use of consistent labels in Excel (best practices).

8. Keep formulas readable

Our 8th Excel expert tip is to split up long formulas 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 illegible. This causes the spreadsheet to be hard to use by anyone other than its creator. The original author ends up always having to explain the spreadsheet to others, or even ends up being the only user actually able to work with the spreadsheet.

9. Avoid repetitive formulas

Avoid repetitive calculations and calculate formulas only once! 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 hard coded numbers in formulas

Never use fixed numbers in Excel 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. This is, at the very least, cumbersome when dealing with large worksheets. At worst, it is one of the easiest ways to create fatal mistakes.

11. Do not merge cells

A very important topic on our list of Excel Best Practices, is not to merge cells! Merging cells in Excel is rarely a good idea. 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 information, such as columns, rows, or even entire worksheets from view is hardly ever a smart idea. It only increases the chance that a user overlooks something important when working with the spreadsheet, increasing the risk of 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.

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 include this immediately 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 spreadsheet maintainability.

14. Save styling for the end

Excel’s formatting and styling 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 design can help keep a spreadsheet comprehensible, 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 to keep the styling of a spreadsheet 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

Did you ever accidentally subtract something that 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 about units and number formats

Make sure viewers can always trace the units and number formats 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 who are trying to understand how the calculation works can stay on the same worksheet, instead of having to switch to the other. 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 in Excel, best practices prove that it is smart 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.