When developing a solution with advanced sheets in Excel, you make decisions and change functions and formulas that at that time make perfect sense and have a logical flow. After you return to agree, or improve or fix the problem with your distribution list, what made sense at the time of development may not be so obvious at a later stage.
So here are my top 5 things to avoid when developing a spreadsheet solution so you can fix any problems and make your distribution list as effective as possible.
1. Combined cells.
They may look good, but they can damage the functionality of your spreadsheet, including the loss of the ability to properly sort the data or run VBA, because it does not cope with the merged cells very well. You also lose the functionality of a regular data table in Excel; these are great features such as pivot tables, SUMIF, COUNTIF, etc. The list goes on !. If you want to use the autocomplete function without merging cells, and do not expect that you can copy and paste it.
2. Hidden rows and columns.
If you really don't need to hide any rows or columns, just don’t. Why, well, because they are hidden! They can confuse users who are trying to follow the logic of a spreadsheet, users cannot see them, but Excel will still use these cells in calculations, which can then appear to users to cause erroneous data. They can also cause problems with importing new data into a workbook and problems with VBA code.
3. Avoid empty cells, rows, and columns.
You may not always have a meaning for each individual cell in your workbook, but leaving the cells completely empty has a huge impact on some functions in Excel. For example, -UTOSUM or the use of filtering with empty cells will affect Excel calculations. If you have spaces, always consider using zeros or an appropriate descriptive value, such as NA.
4. Avoid many volatile functions.
The volatile function is a function that is recalculated every time a sheet of a sheet changes. These include the functions NOW (), TODAY (), OFFSET. If you use a little or a lot of this data in a workbook, it will eventually begin to slow it down.
5. Avoid unnecessary complicated formulas.
One thing I learned while developing common sheet solutions for users is that they (ever) want to see all the formulas. For example, a typical sales formula
= (Price * SalesQuantity) - (Price * SalesQuantity) * Discount + (Price * SalesQuantity) * Tax
Users will want to know the sales totals, the discounted amounts, and the sales tax display value — these are separate columns. Here we can break down the components of a formula into smaller formulas in our own columns or what we call helper columns. If you can increase the transparency of the calculations, it will simplify the logic of the spreadsheet for users, which will lead to fewer questions.
These are my 5 best outs when creating a table.
