If formula

Here we’ve got some detail regarding what’s covered on our course on Excel formulas. Our aim is to cover all functions people find the most helpful in their financial modelling.

We start with some basics

Some of what we send you you may well be familiar with. But as the lessons progress we get into advanced modelling territory and we think there’s a good chance you’ll find something a bit new and novel somewhere (did you know that the Sumproduct formula has a superpower – that it straps on its tights at night and is practically powerful enough to go out fighting crime? But more of that later).

“Shift” “F3” for Excel’s insert function dialog box

To get started we want to make sure you’ve got a few critical features of Excel firmly in your modelling survival toolkit. We’ll have you opening a spreadsheet and pressing the “Shift” “F3” Excel shortcut. That shortcut brings up Excel’s insert function dialog box.

Excel's insert formula function

If you’ve seen a function being used in someone else’s model and are not sure exactly how to use it, you can start to type the function’s name at the top of that dialog box. Click on “Go”. Once you’ve found the function you’re interested in click on it and hit “OK” at the bottom. Straight away what you’ve got there is a few boxes showing the inputs to the function. That all helps you start building your brand new formula step by step in a structured fashion.

The insert function dialog box

We think that “Shift” “F3” and that insert formula dialog box is really handy when you’re tackling functions you’re not completely familiar with. We want to make sure you’ve got that one firmly in your Excel modelling survival toolkit. As you get more familiar with a function, you can start entering it directly into a cell but you’ll notice you’re still getting a bit of guidance about the formula’s structure.

Enter a formula in Excel

When you’re interrogating someone else’s model, at any time you can hit “Shift” “F3” on any formula you see to get a bit more detail about what’s going on at each stage.

Our first lesson: embedded If formulas

But you need to make sure you’ve got a few other things apart from “Shift” “F3” in your modelling survival toolkit! The good news is, amongst all the rich functionality that Excel has to offer, there are only a few critical functions that you’ll use to do most of your modelling. You absolutely need to make sure you’ve mastered those ones. In your Excel survival toolkit, as well as “Shift” “F3” we want to make sure you’ve at least got in there “F1” (the shortcut for Excel help if you need a bit more information than you get from “Shift” “F3”), If formulas, Vlookup functions and goalseek. It’s those basics that we’ll cover in the first three lessons.

In our first lesson we concentrate on those basic/ essential If formulas, thinking about how we could create a balanced scorecard that uses an embedded/ nested If function to code results “red”, “green” or “amber” for a warning.

Get an Excel financial modelling tuneup

We’re happy to send you a brief email covering territory like this regularly each day over the next month. By flicking through each lesson we think you can give yourself a tiny little Excel work out probably in as little as 10 minutes a day – making sure you’re toned up and fighting fit for your modelling. As the month progresses you’ll start to see more advanced functions that are likely to prove helpful but that you’ll use less frequently. To start with, we want to make sure you’re totally on top of a few essential Excel formulas, starting with If functions.