Guide to Excel Modeling Best Practices
CFI’s list of top Excel modeling best practices. Microsoft Excel is an extremely robust tool. Learning to become an Excel power user is almost mandatory for those in the fields of investment banking, corporate finance, and private equity. With that being said, the sheer strength of Excel also presents certain difficulties. When misused, an Excel model can become very unwieldy and not user-friendly. The following are some guidelines to make the modeling experience in Excel as seamless as possible.
Excel Modeling Best Practices: Toolbar Tools
#1 Conditional Formatting
Under the format menu, this tool allows you to set rules for certain cells that, when triggered, change the formatting of the cell. There are numerous types of formatting that can be used, but the most common is to use green, red, and sometimes yellow to denote certain statuses of your cells.
For example, you may conditionally set the background of the net income cell of an income statement to color red when it’s in the negative, and green otherwise. These visual cues enable the user to more quickly skim the model for the information he or she needs.
#2 Data Validation
Under the data menu or ribbon, data validation allows you to set rules about what values a cell can or cannot take. For example, cells in a debt schedule should almost always be a number cell and not text. If this is the case, data validation allows that cell to be locked as a number and will display an error message if the user mistakenly tries to add a text value, date value, or other non-numerical value. Another helpful use of data validation is the ability to make drop boxes or drop lists. This function easily makes the list of Excel modeling best practices.
Under the tools menu or review ribbon for newer Excel versions, protection allows the user to define certain cells or worksheets that cannot be changed without the proper authorization. Normally, this means that the user will set a password that locks those cells or worksheets. When an unauthorized user tries to make changes to said cells or sheets, Excel will display an error message.
#4 Tracing Precedents or Dependents
Under the Formula ribbon, Excel allows you to trace Precedents or Dependents on your active cell. Pressing this displays arrows representing other cells that are linked to the current cell. Precedents are cells that the current cell links from, whereas Dependents are cells that rely on and use the contents of the current cell. These arrows remain on the screen until you press the nearby remove button.
This tool is not only useful for analyzing where circular references are created, but also for analyzing symmetry in your formulas and making sure that adjacent cells that are supposed to be calculating the same piece of data or using the same references and formulas.
This is one of the most effective Excel modeling best practices out there!
Excel Best Practices: Formulas
Instead of using nested if statements, which can become quite hard to follow after three or four “nests”, try to master the art of the vlookup. The VLOOKUP function works in conjunction with a vlookup table that the user will create. Think of this table as a dictionary. The first column will have the word and the subsequent columns will have possible definitions the word can take. The VLOOKUP function will define which of the subsequent columns to look under for the correct definition. However, instead of just like a words in the dictionary example, the vlookup table can take on any number or text value.
In certain calculations, you may occasionally run into the #DIV error, which means an error where the formula is trying to divide by an undividable, such as zero. This may appear in calculations of gross margin or the interest coverage ratio if the denominator is zero. As such, it is safer to use the =IFERROR() formula, before entering the real calculation. This allows you to set a value or phrase that appears, in the event of an error, to avoid the unseemly #DIV error in your models.
Excel Modeling Best Practices: Shortcuts
#1 Go To Special
Go To Special: Pressing F5 allows you to bring up the “Go To” menu. At the bottom left-hand corner of the pop-up box, there is a “Special…” button. Pressing this brings up options about certain types of data – for example, comments, constants, formulas, and blanks. Selecting any of these will command Excel to highlight all the cells that contain any other instances of the same.
For example, selecting “Constants” and pressing OK will highlight all the non-blank cells in your model that contain hard-coded, constant data. This is useful if you want to format or color all the cells in your model that are not formulas.
This is one of the top Excel modeling best practices out there!
#2 Select Multiple Sheets
If you have several worksheets that have the exact same format, you can select and make changes to several of them at a time. To do this, hold down the control and shift buttons, and press the page up or down keys in the direction of the adjacent worksheets.
For example, if you have three worksheets that are identically formatted right after one another, you can select the last sheet in the group, hold control and shift, and press the page up key twice.
Alternatively, you can hold the shift button and click the last member of the group. In the same example, you can select the first sheet in the group, hold shift, and click the third and last worksheet to select all. From here, you can make changes to formulas and cell contents directly across all three sheets.
Note: It’s very important that the format and positioning of cell rows and columns line up across all three sheets. All the changes you make in Cell D39, for example, will happen to cell D39 across all the sheets. This means that if Cell D39 contains a different piece of data or formula in one of the sheets, your model may run into errors.
Thank you for reading CFI’s guide to Excel modeling best practices. To keep learning and progressing your career as a financial analyst, these additional CFI resources will be helpful on your journey: