What do you need to know about 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 modelling experience in Excel as seamless as possible.
Best Practices: Toolbar Tools
- 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 allow the user to more quickly skim the model for the information he or she needs.
- 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-number value. Another useful tool with data validation is the ability to make drop boxes or drop lists.
- Protection: 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.
- 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 link from, whereas Dependents are cells that rely 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, making sure that adjacent cells that are supposed to be calculating the same piece of data, use the same references and formulas.
Best Practices: Formulas
- Vlookups: 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 words in the dictionary example, the vlookup table can take on any number or text value.
- Iferror: 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 calculation gross margin or the interest coverage ratio, if the denominator is zero. As such, it is safer to use an “=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.
Best Practices: Shortcuts
- 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, blanks. Selecting any of these will command Excel to highlight all the cells that contain any of these. 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 wanted to format or color all the cells in your model that are not formulas.
- 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 button, 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.