Excel financial modelling formulas

Learn about all the best Excel formulas for financial modelling: all for free.

10 minutes a day on Excel modelling formulas

This program covers all the formulas attendees on our financial modelling course ask about and like best.

The material is extracted from our regular financial modelling course and is well proven with delegates.

Excel has zillions of formulas on offer. That can present a bit of a challenge: you need to become fluent in the Excel formulas that are going to be best for your work.

We think that, by sending you one email a day for a month, in just ten minutes a day we can check that you’re on top of critical Excel modelling formulas.

What we cover on the course: building your Excel survival toolkit

To start with we build you an Excel modelling survival toolkit (note, at this stage we’re assuming you can do a very few things like breathe, strap on a back pack, apply your own camouflage paint, build a Sum function and press “F1” for Excel help). Beyond that we want to make sure you’re totally on top of some of the Excel functionality we would regard as absolutely essential in financial modelling:

    1. If formulas. In the first day’s lesson we look at the If formula. We construct an embedded/ nested If formula that could be used in a ‘balanced scorecard’ management report: coding results “Red”, “Amber” or “Green”. We also cover the importance of the insert formula shortcut because that’s your pathway to any Excel formula you might be a bit unsure about.

    2. Vlookup. So often in Excel modelling you need to pick out data from a table, so we make sure you’ve got at least one data picking solution in your survival toolkit. To start we make sure you’re completely comfortable with Excel’s Vlookup formula – one of the trickier functions to put together.

    3. Goalseek. Next we look at iterating with a model. In modelling you’ll want to vary an input to see the impact on an output. Excel’s goal seek will help you with that. We think it’s an essential tool in Excel financial modelling so we just make sure you know about it.

Could I really do all my modelling with just that?

Well, maybe not, but you’d be pretty close to surviving! With “Shift” “F3” for insert function, “F1” beyond that for Excel help, summing up (“Alt” “=” is the shortcut), If formulas, a data picking solution and some judicious use of goalseek you’d be getting most of the way there most of the time and you’d have almost enough theoretically to start carving out a career in corporate finance. But of course there’s more to modelling than that! To start we just want to make sure you’re totally on top of some of the basics that you’ll use most regularly.

As our course program progresses we gradually start to get into more advanced modelling territory. Not all of it you’ll use every day but it’s interesting to take a quick look at other formulas you’ll also find useful. Just so you know about them and have some clear reference examples for the future.

Slowly moving into more advanced territory

Along with the ‘must haves’ we’ve got a list of lessons covering useful Excel formulas likely to help you address specific financial modelling challenges:

    4. Conditional formatting. In this lesson we look at layering multiple conditional formats into our previoulsy-created balanced scorecard. Instead of just using If formulas to display the text “Red”, “Green” etc, we use Excel’s conditional formatting actually to colour those cells. Conditional formatting seems to have become overly complicated in the later versions of Excel so today we concentrate on helping you navigate that complexity quickly.

    5. Choose. By now we’ve looked at Vlookup but Excel provides us with multiple other data picking solutions. Some will provide a shorter neater solution depending on the exact problem you’re trying to solve. On the course today we look at Excel’s Choose formula. It provides a short neat solution to data picking when you’ve got a small data set. It’s so short and neat we think you’ll grow to love it. When you have a small data set, we think the opportunity to use Choose will feel as good as getting a free lunch!

    6. Drop-down boxes. Have you seen those groovy graphical combo boxes or drop down boxes in Excel? They don’t actually do anything clever in Excel – all they do is change a very simple input. But they can make your model much more user friendly and make you look like a modelling expert pretty quickly. On today’s course lesson we look at combo boxes, including a few advanced applications you may not have seen before.

    7. Cell names. Today we look at cell names in Excel (“Ctrl” “F3” and “Ctrl” “Shift” “F3” are the keyboard shortcuts). We have a few serious warnings around names. Some people like them so we feel we have to make you aware of the pitfalls.

    8. Scenario manager. When we looked at goal seek we found we could access it from the “Data” tab, then “Data Tools”, “What If Analysis”. On today’s course lesson we take a quick look at something else you might have noticed sitting under that menu item: Excel’s scenario manager. We will tell you why we’re not great fans of it but it regularly gets questions on our financial modelling course training so we think it would be wrong to skip it here.

    9. Model switching. In today’s course lesson we give you some clues about switching on and off large swathes of your model. Today is all about model switching.

    10. Custom formats. Next we look at custom formatting in Excel (“Ctrl” “1”). We help you get that (bracket) formatting into Excel that accountants seem so fond of. (Bracket) formatting is not on the list of Excel’s standard formats so you need to be able to customise/ build one of your own. If you want to keep the accountants in your life happy.

    11. Index. Today we add Excel’s Index formula to our list of other data picking solutions (on top of the standard Vlookup and then Choose for a small data set). Some of our financial modelling course attendees prefer Index because they like the logic and reportedly it chews less Excel memory.

    12. TRUE/FALSE checks. Did you know you can shortcut a full If formula? After today’s lesson we think you may decide to promise yourself never to use a full If formula again. If you take that pledge, your models will suddenly be looking like they’ve been created by someone who really knows what they are doing. Today’s course lesson is all about Excel’s TRUE/ FALSE checks as an alternative to the standard If formula.

    13. Data validation. Today it’s data validation: forcing users to enter particular values in a spreadsheet. We’ve seen data validation used in the spreadsheet that goes around the office ahead of the Christmas dinner. Your choices are going to be “Turkey” (that has to be on there), “Nut roast” (there has to be a vegetarian option – but it’s never sounding that exciting is it?) and “Salmon” (for everyone else). Nobody’s ever going to be allowed to put “I want Beef Wellington” against their name in the spreadsheet now are they? That’s the kind of thing data validation is designed to help us with: forcing users to enter a limited range of inputs. But we’ve regularly seen people apply data validation to scenario switching in their modelling. We prefer the drop-down boxes (for good reasons that we happily explain) but, because the two look very similar, we need to prepare you for picking up someone else’s model – the one that’s using data validation to switch scenarios.

    14. Password protection. You may feel you’re surrounded by idiots: the kind that expect to be able to order Beef Wellington for their Christmas dinner. We understand – we feel your pain. Along with data validation, password protecting a spreadsheet is something you could find helpful. What that will do is stop other users making changes e.g. to formulas in the Excel model you’re circulating. They’ll only be able to change e.g. the input cells you want them to change.

    15. The Round formula. Do you have other issues with your colleagues? Are there a few around who might see 1.5+2.4=3.8 in a spreadsheet, fail to realise that Excel stores numbers to lots of decimal points out on the right hand side, and all we’re seeing is the impact of rounding within Excel? Is there a risk that a colleague or client might get themselves distracted by your apparent inability to be able to add correctly? Excel’s Round formula is going to be really helpful for dealing with the obsessive compulsives in your life. The ones who need to have all the pictures straight in the office, the mugs stacked neatly in even numbers in the kitchen, and all the key totals looking like they add up in the Excel spreadsheet.

    16. Iferror/ Iserror. In today’s lesson we look at using Excel’s Iserror and Iferror functions to identify and reduce modelling errors.

    17. Sumif, Sumifs and Sumproduct. Today we complete our list of favourite data picking solutions, tackling some that can also help you add up/ consolidate data. This course lesson covers Sumif and it’s bigger stronger beefier cousin Sumifs. We also look at the Sumproduct formula which we think is the winner amongst all of them. It picks data. It amalgamates data. It amalgamates over multiple rows and columns. It really is totally awesome – but you’ll get little guidance from Excel’s own “F1” help on its super-powers.

    18. Array formulas. Is there a chance that you could ever want to do two things at once (in an Excel model)? In the previous lesson we met a couple of two-in one-formulas. Sumifs kind-of combines an If formula with adding up. Sumproduct multiplies and adds up. If you like the idea of a two-for-one special you might want to check out Excel array formulas. They’re probably one of the weirdest formulas you’ll ever see in Excel with funny { } squiggly brackets at each end. Most people meet them first in someone else’s model when they interrogate the formula (“F2”) and then press “Enter”. The {brackets} disappear and the formula and model break. Today you’ll find that “Ctrl” “Shift” “Enter” is what you need to press to generate the brackets and make the array formulas work.

    19. Data tables. Our investment banking delegates seem to love Excel data tables – with good reason. They help you with your sensitivity analysis, automatically creating the table that shows the impact of a first variable (across the top) and a second key variable (down the side) on the output (in the middle). In today’s course lesson we also look at the potential to use a special case single variable data table to display the results of our scenario analysis.

    20. Excel date formulas. Usually the top of our model will have date headings running across the top of it. Today we focus on rolling dates forward in the model. We come out with a strong recommendation on the EOMonth formula.

    21. Escalation factors. In a large project model we might want to escalate revenues up by e.g. a % contracted growth rate and expenses e.g. by % inflation. Today’s course lesson looks at pitfalls in modelling escalation factors.

    22. 3D referencing. Excel’s 3D referencing enables you to play a cool trick when totalling data across multiple tabs. You won’t use it very often but you’ll love the occasional opportunity to use it: it’ll be a good day at the office that one.

    23. The watch window. Excel’s watch window helps you keep key results in view so that you get the earliest possible notice when your model falls over with #REF! errors. Cool.

    24. Excel macros. If you’re unfamiliar with them, today we get you started with Excel macros.

    25. Deliberate circular references. In this lesson we’re preparing you for a chance encounter with a Dark Lord of financial modelling: the one who is in the habit of leaving deliberate circular references in a model. The Dark Lord expects the model to work (even though he’s put a circular reference in it), and expects you to know what to do with it. This could be scary.

    26. Max/ Min formulas. We’ve already shown you how to use TRUE/ FALSE checks as an alternative to the standard If formula, making you look like a modelling pro. Today we add to that the alternative of the Min or Max formula.

    27. Modelling tax losses. At one level today’s just an example of how you can apply Min/ Max formulas in the place of the If formula. At another it’s our little gift to you: a record of how you can ‘bank’ tax losses without getting yourself tied in complex knots. You can save it for later.

    28. Pivot tables. Today is a quick lesson, just checking that you know how to create an Excel pivot table. We also make sure you’re aware of the alternatives that Excel’s standard formulas provide us with – that you don’t always feel obliged to jump in and create a huge table manipulating a lot of data.

    29. Edit links. Now we’re easing off with a short lesson on managing Excel’s edit links settings. You’re most likely to encounter this when you get someone else’s model and Excel warns you that the model contains broken links. We also re-iterate one of warnings on names because names can end up acting like broken links in a model. “Ctrl” “F3” will tell you whether you’ve got that kind of problem.

    30. Modelling course conclusion. Today we finish by congratulating you on making it through the program and pointing you to other online resources we’ve prepared. We have other free resources that we think you’ll find useful in your financial modelling work. We also mention that we’d love to see you on a live taught financial modelling program (of course – you’d expect that!) where you get to wire up a real model using some of these formulas. But there really is no catch. Other than being forced to tolerate a quirky take on Excel formulas (obsessive-compulsive bosses? Dark Lords? Really?!) all of the online financial modelling course material you get here carries no cost: it’s totally free.

Get started with the online financial modelling course extract

Please just use the sign up form above and straight away we’ll send you your first Excel financial modelling course extract for free.