Excel – Standard Conventions
Adhering to standard conventions in Excel makes your spreadsheets simpler to use and significantly reduces the effort required for others to decipher the flow of your models.
Below, I discuss three of the most basic conventions that all Excel models should employ:
- Text Color
- Friendly Save
- File Naming
The standard convention for financial models in Excel is to use the following color scheme.
Blue Text – Input Cells
Input cells are any cells with hard-coded data. These are often the assumptions that drive your model. In many cases, you manually typed in these numbers. Each input assumption should only occur once in your model, so a change to a single assumption flows through the entire spreadsheet.
Black Text – Calculated Cells
Calculated cells are all the cells with formulas. They are derived from the input cells (and other calculated cells). Making the text color black reminds you not to hardcode any values into these cells when you are experimenting with the model. These are calculated values, not assumptions.
Green Text – Mix of Input Assumptions & Calculations
It’s best to avoid this if possible, but sometimes you may need both hard numbers and formulas within a single cell. If possible, pull the hard-coded number out and make it a blue input cell. If not, use the green text color so at least you and others will know to watch out for these cells because there are hard-coded values buried within the formulas.
If you adhere to this 3 color scheme convention, your entire model should be completely driven by the blue text cells and it will be significantly easier to navigate.
Also, if you choose the correct colors: Blue (51, 51, 255) and Green (0, 128, 0), they will print as black (or near black) when the spreadsheet is printed in black & white.
Unlike Word and PowerPoint, which always open at the top of the document, Excel opens with the active cell exactly where it was left when the file was last saved. In fact, Excel remembers the active cell locations for each tab. This is great feature, if you remember to place the active cell where you want it before saving. This is called a “friendly save” because it’s much easier on the recipient to open the file and immediately start where it makes the most sense. Usually, this is cell A1, but not always. For a friendly save, go through each tab in your spreadsheet and place the active cell where it best orients the recipient, then save. Be sure to leave the active cell on the correct tab as well.
It’s always a good idea to save your work frequently and to version your files so you can go back a version or two if needed. Some people use a file naming format that looks something like this:
Filename_v1 | Filename_v2 | Filename_v3
Don’t. It’s better than nothing, but conveys very little information. For all your files, not just Excel, use this file naming convention instead:
Filename_YYYY_MM_DD_HHMM (using 24-hour time)
For example, the file name for this document might be: “Standard Formatting_2017_10_31_1508”.
Using this convention, all your files will be in the correct, chronological order when shown alphabetically. It’s also easier to determine when you created the file or saved the last revision.
Change Log (Bonus Tip)
While this is not standard formatting, it’s a useful tip…
For Excel templates that are used company-wide (for example, financial valuation models), create a new tab called “Change Log”. Your change log tab is a record of all changes made to the Excel file template over time, when it was made and by whom. This makes it much easier to debug if something is not correct in the model. A change log might look something like this:
Oct 19, 2017 | Andy Jones | Edited the calculation of WACC on the DCF tab.
Oct 31, 2017 | Andy Jones | Updated text color scheme to conform to industry standards. Smart.