My name is Antoine Bishara, and I love Excel. Why do I love Excel so much? I think it’s because for us accountants and finance professionals, life is somewhat rigid (i.e. perform X to solve for Y). Excel isn’t like that. When you hit that glorious Excel icon, a new unchartered world opens in front of you. You effectively become an artist, and Excel is your canvas. Put more simply, it is color, in an otherwise a black and white work-life, at least for me.
I am an Excel Power User, and I now spend a lot of time mentoring and teaching individuals on how to use Excel more effectively in their daily lives, and by doing so, how they can become better analysts, managers or executives.
Here, I would like to share my top-5 favorite Excel tips (Excel version 1807, Office 365. If you are using a different version of Excel, the concepts are still valid, but some of the location references may be different):
1) Formula: Index-Match
In the courses I teach, I find most people have heard of the “index-match” formula, but they often revert to the VLOOKUP (and HLOOKUP) formula, as they feel they are more intuitive and easier to understand. However, there are inherent problems with the VLOOKUP function, namely:
1) Speed – the VLOOKUP function requires more processing power because Excel needs to evaluate the entire table when looking for the selection. This may not seem important, but as your models increase in size – efficiency of your model becomes imperative.
2) Column insertions and hardcoding – any insertions of columns in your data set will distort the lookup results. As such, you need to manually change the “column number” in the formula:
(The 4 is hardcoded and must change every time the table is expanded)
The formula therefore breaks down when you insert more columns within your data set, unless you remember to manually adjust the formula each time (i.e. hardcode). This leads to potential errors, as these adjustments are often overlooked. Those who know me know how I feel about hardcoding (I hate it). In summary, it’s bad. Don’t do it.
Once you master the INDEX-MATCH formula, you will appreciate its adaptability and speed. If you want to take the plunge, you can learn how to use “INDEX-MATCH” here, at our partner firm’s site, Corporate Finance Institute - Advanced Excel Training Course.
2) Setting: Edit Directly in Cell
In my career I have audited multiple models from different parties. One thing I became very good at is identifying errors and auditing a model quickly. How did I get so fast? One of the tricks I employed was “unclicking” the option to “edit directly in cell”. What this allows you to do, is by simply “double clicking” you will be taken to the cell (in any tab) that is at the start of the formula. This allows you to navigate through any Excel worksheet quickly. Here’s how to set this up. Go to:
File -> Options -> Advanced -> “uncheck” Allow editing directly in cells
However, remember that once this setting is “unchecked”, you can no longer edit a cell by double clicking it. You will have to use the F2 button to do that.
3) Formulas: Live text
Nothing can be more irritating than updating your financial model, footing and cross-footing all your numbers, saving it as a PDF, and then realizing that some of the notes that you had created do not reflect your updated numbers. Thankfully fixing this is quite easy, all we would need to do is convert the “numbers” into “text”, and then use them in our formulas. How would you do that? Do the following:
Let us assume that cell A15 = 1235132.
We convert that number into text using the following function in cell A14: =text(A15,”$#,##0”). What this formula is doing is telling Excel that cell A15 is now text, and it will be currency with a ‘$’ and ‘commas’. Note: when specifying the format of the text, you must put quotation marks around the format (see the formula).
A14 now equals $1,235,132, and is text, which means we cannot add or multiple that number. Fantastic!
However, this is just half the battle. We now want to embed that “text” into a text string such as “I know I will be wealthy when my savings total $1,234,132”. To do this, we will change cell A14 to the following (note the quotation marks):
=”I know I will be wealthy when my savings total “&text(a15,”$#,##0”)&”.”
What this formula should unlock for you is the ability to add multiple lines of text and formulas by inserting the “&” between each formula/text. As such, you can simply add a “&” at the end of that formula and add another string to that formula. Since this is just text, in order to create spaces between your imported numbers and other sentences, you need to add an explicit space using quotation marks “ “. As you can see above, the space after ‘total’ is not by mistake, it is to create space between the word and the imported number.
Now, I’ve revised my estimate of what it takes to be wealthy. $1,234,132 isn’t going to cut it anymore, I need more. A lot more. So, I change cell A15 = $10,100,000, and A14 automatically updates to: I know I will be wealthy when my savings total $10,100,000. Very slick.
4) Consolidation: Using a Formula and Bookends
Excel has long had a “consolidation” button that can be found on the “Data” tab, however I tend to shy away from some built-in-features of Excel. Why? Because the functions operate as a “black box”, not allowing the user to understand what exactly is happening. A great example of this is the “Consolidation” button. Not only do you have to select what you are “consolidating”, but the result is a number. You have no idea what it refers to, unless you open the “Consolidate” function to see what the cell is referring to. You must be thinking “there must be a better way!” Thankfully, there is.
This is my personal favorite way of consolidating information – you simply insert “Bookends” as tabs in your model. For example, let us assume we want to consolidate three tabs that are currently named 2015, 2016 and 2017. What you would do, is insert two tabs – one at the beginning and one at the end. You would label these tabs as “Beg” and “End” (or whatever names you prefer). In addition, you must create a “consolidation” tab, which will house all the information that you intend to consolidate.
Note: To have this function work, you must ensure that all the tabs that you are attempting to consolidate have the exact same structure. This will ensure that you are “adding/consolidating” the right cells. Take a look at the following screen shots:
In the “Consolidated Final” tab, you create a table with a similar format. However, in this tab you enter the following formula in C5 (the cell representing revenue): =SUM(Beg:End!C5). You then copy that formula down to populate the rest of your consolidated result. You can see the results of this in the screenshot below:
This formulaic way of consolidating allows you to audit your formulas easily. Also, if you just add a tab (for example 2018) in the midst, with the exact same format, the result in “Consolidated Final” will include this newly added tab. This is very helpful when you are trying to consolidate something like a cash flow forecast. Back when I was consolidating these, I would simply send a “template” to each division. Once the divisions completed their cash flow forecast, I would drop each one between my “bookends”. The entire process used to take approximately 10 person-days per month to complete – we worked it out with the teams and by using this method we were able to reduce the cash flow forecast processing time down to 2 person-days per month. It works!
5) Formula: xNPV, xIRR
I have spent many years analyzing different projects using two common approaches: Net Present Value (NPV) and Internal Rate of Return (IRR). However, I always struggled with applying the “=NPV” and “=IRR” formulas, as they do not account for the “lumpiness” of cash flows. The IRR and NPV functions assume each distinct cash flow occurs in equally spaced periods. Imagine you have 12 separate cash flows in one year. The IRR and NPV functions consider these cash flows as equally spread throughout time, thus happening over 12 equally spaced years in the future. In reality the 12 cash flows are all happening in the same year, and the result is a much lower discounted value than what is intended.
Thankfully, the problem of lumpy cash flows can be solved easily by putting an “x” before the NPV and IRR functions. What the “x” does is allow you to introduce dates to your sample, which allows Excel to perform more accurate discounting. This means you can now accurately calculate the present value of cash flows happening 12 times a month, and then once a year after that.
The xNPV and xIRR functions are both quite intuitive, as all the user needs to do is input the discount rate (if applicable), the cash flows, and the dates. Excel will do the rest!
Excel is a powerful tool that allows us to be prepared, allows us to analyze, and helps us conclude on a subject matter. However, we must never forget that Excel is just that – a tool. And like any tool, proficiency comes with practice. Never lose sight of that.
If you would like to hold an Excel workshop for your team, or would like to learn more about the learning solutions we provide, please contact us at firstname.lastname@example.org.