Quick Tips 4 - Structure



The structure of your spreadsheet plays an important role in minimizing errors and ensuring the spreadsheet is a useful tool for the user. Here are some things you can do to improve the structure of your spreadsheet.


Give your worksheets meaningful names that are easy to read.


Don't use spaces in worksheet names as it can cause errors in formulas if you use the wrong syntax. Instead either (a) Capitalize the first letter of each word or (b) use the underscore to separate names. So, instead of "Financial Forecast" use "FinancialForecast" or "Financial_Forecast".


Have separate tabs for data input, data storage, calculations, reports, and charts. Arrange the order of your spreadsheet tabs so they follow a natural data flow. I discuss this in my blog post Pro Forma Financials 2 - Auditing the Spreadsheet.


Avoid hiding columns or rows. If you perform an operation in an area that contains a hidden column or row, then you will affect the hidden data. The result can be errors in your calculations - errors that are difficult for the user to detect because the rows and columns are hidden. It is better to put the data you want to hide in another section of the worksheet or on a different tab altogether.


Put all of your assumptions on one worksheet - do not scatter them throughout the spreadsheet tabs. This makes it easier for the user to review the data. It also minimizes the chances that you will duplicate data, or have two different values for the same variable item.


On the assumptions page, if a variable or constant refers to a specific cell or range, be sure to add a note stating this so the user can better understand how the spreadsheet works and flows.






3 views