Tips for Excellent Spreadsheets

Some really useful tips copied from here: https://www.perfectxl.com/about-spre...-spreadsheets/

1. Create Worksheets with the Future in Mind
o 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.The first of our Excel tips 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. Think about the Order of Worksheets
o 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.
o 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.

3. Choose Clarity over Looks
o 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.

4. Keep your Timeline Consistent
o 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.

5. Organize the Information Flow
o 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.

6. Label Columns and Rows
o 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.

7. Keep Formulas Readable
o The 7th of Excel tips 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.
o Many spreadsheets are hard to use by anyone other than its creator.

8. Avoid Repetitive Formulas
o 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.

9. Avoid Fixed Numbers in Formulas
o 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.

10. Do not Merge Cells
o 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.

11. Avoid Hiding Data
o 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.
o Hiding data increases the risk for errors.


*]Build in Data Verification
o 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.

12. Save Styling for the End
o 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.

13. Keep Styling Consistent
o 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.

14. Keep Conditional Formatting Simple
o 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).

15. Use Positive Numbers
o 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.

16. Be Clear Which Units Are Used
o 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.

17. Clarify your Sources
o 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.

18. Avoid the Use of Macros
o 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.

19. Use Simple Ranges
o 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.

20. Clean Your Input Data
o Garbage in, garbage out? Not if you pay serious attention to cleaning your input data first. And if possible, in such a way that it becomes an automated process for the next time you use the same spreadsheet.

21. Delete What You Don’t Need
o Compare transferring a spreadsheet to renting your house via Airbnb. It remains more neat when you clean it first, right? Messy stimulates messy, neat stimulates neat. Remember an average spreadsheet will have 12 users after you! What would happen when the chain starts with a messy file?

22. Prefer INDEX and MATCH over VLOOKUP
o There are several ways to search in Excel. The VLOOKUP and the combination of INDEX and MATCH are well known. To our great frustration, VLOOKUP is used a lot more often than the INDEX and MATCH, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.

23. Every Spreadsheet Needs a Second Opinion
o You’ve created an amazing spreadsheet. You feel satisfied and proud and fully confident to send it to all relevant coworkers. But within one minute after hitting ‘send’, the first colleague is standing beside your desk, pointing out a fatal mistake. A mistake that could have been avoided.

24. Plan before you start

Excellent Post! May I be as bold as to add some of my own. My apologies, but I don't know how to make the list start at 25.

1. Use Excel Tables when possible. Excel tables have many benefits. Some of them are:
   • Excel Tables know how big they are and expand/contract automatically, so formulas, pivot tables, charts and VB code that reference them use exactly the right amount of data. There is no guessing as to how many rows to include.
   • You can reference Excel tables by column heading name. This makes it easeir to understand formulas and VB code.
   • Tables "remember" formulas and copy them down automatically. This also goes for formats, data validations and other attributes of the cell.
2. Use consistent naming conventions. For example all my tables are prefixed with "Table_" such as "Table_Sales" or "Table_Defects". This makes it easier to understand and also program with things like INDIRECT() or concatenations within VB code.
3. If you must use VB, let Excel do as much of the "heavy lifting" as possible. For example, the easiest way to get a list of unique names in a table is to put the name column header as the row header in a pivot table. This also does the sorting for you. Then if you need to loop through a unique list of names in the code, refer to the results of the pivot table. Use Excel to do what it does best and sew and bolt the pieces together with VBA.