Excel Formulas

In an ideal spreadsheet our formulas would always reference adjacent cells and columns and it would be obvious which cells contributed to a result, like this: However, in financial modelling and reporting it’s often less obvious, like the example below: Let’s look at some ways we can easily identify which cells contribute to a result. …

If your company’s fiscal year runs in line with the calendar year then it’s easy to convert dates into quarters and years for reporting. Convert dates to quarters with this formula (where cell A2 contains your date): =ROUNDUP(MONTH(A2)/3,0) And if you want to prefix it with ‘Q’, then use this formula: ="Q"&ROUNDUP(MONTH(A2)/3,0) Convert dates to …

If you share Excel files and reports with users from different countries then you might want to give them a report in their language…and I don’t just mean converting from English to American 😉 It’d be a load of work to create a separate file for each language, so instead we can automate it like …

New in Excel 2019* is the TEXTJOIN Function. It's the grown-up sibling of CONCATENATE. Both TEXTJOIN and CONCATENATE join text together, but TEXTJOIN enables you to more easily join a list or range of cells including a delimiter that you want to insert between each text value. Best of all, TEXTJOIN allows you to ignore …

Last week I received the following question from André: “I need a formula that will count how many values in cells D4:D7 are listed in B4:B13. I don’t want to count the number of times they appear in column B, I just want to know how many values from column D are in column B“. …

This tutorial is applicable to Excel 2019 onward and with a Microsoft 365 license. In Excel 2019 there are a load of new functions, one of which is the IFS Function. Given last week’s topic, ‘When to say no to Excel Nested IFs’ I thought it was a good time to introduce the Excel IFS …

This tutorial is applicable to Excel 2019 onward. New in Excel 2019 is the SWITCH function. It looks up a value in a list of values, and returns the result corresponding to the first matching value. It’s a great alternative to nested IFs, or even the new IFS function. I liken it to a VLOOKUP, …

In Excel 2007, Microsoft thought it would be a good idea to increase the number of times you can nest IF functions from 7 to 64. S I X T Y – F O U R! Are you kidding me? If you need to nest any more than 7 IFs then you should be using …

There are a few ways to approach a running total formula, but Excel Tables require something special, or you're likely to end up with errors that aren't obvious. Watch the Video Download the workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we …

I’ve written about how to create dependent data validation lists before here; Excel Data Validation with dependent lists, and here; Dynamic Dependent Data Validation. However the approach I’m going to cover in this tutorial is probably the best I’ve seen, especially if you have a lot of dependencies as it’s easily scalable. What is Dependent …

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions to get the party started. Fair enough I suppose, after all, the more the merrier. In this tutorial we’re going to use choose to toggle through different aggregation methods (AVERAGE, MAX, MIN, SUM) as seen …

Excel MROUND function is perfect for rounding numbers to the nearest multiple. For example, last week Brenda said she was making a timesheet and wanted to round the start and finish times to the nearest 15 minutes while allowing for a 7 minute grace period. The table below has some examples: Rounding these times to …

There comes a time in many Excel users’ careers where we start to write incredibly complex Excel formulas to summarise or extract data from poorly structured workbooks. I’m not talking about relatively simple nested functions like INDEX and MATCH, I’m talking about monsters like this: =IF(ISERROR(INDEX(Sheet1!$A$1:$E$162, SMALL(IF(Sheet1!$A$1:$E$162="ABC", ROW(Sheet1!$A$1:$E$162)),ROW(1:1)),4)),"", INDEX(Sheet1!$A$1:$E$162, SMALL(IF(Sheet1!$A$1:$E$162="ABC", ROW(Sheet1!$A$1:$E$162)),ROW(1:1)),4)) And as we press …

I use Dynamic Text Labels all the time, whether it’s in a Dashboard report or chart title, and even to annotate variances like you'll see in this example. Anyone can create an Excel chart or report but it doesn't mean your reader will get the message you're hoping/trying to convey. With a dynamic text label …

Excel Multi-cell array formulas are a single formula which returns multiple values and is entered into multiple cells. Hence ‘multi’ in the name. Let’s look at an example, say we want to return a list of numbers 1 through 10 in cells A1:A10. Step 1 – very important; first select cells A1 to A10 Step …

You'll typically find the ROW(S) or COLUMN(S) functions nested inside other functions because they make formulas more efficient to write. However, many users get caught out by these functions because you don’t edit the cell references in the same way you might with other formulas. Watch the Video Download Workbook Enter your email address below …

The AGGREGATE Function is the Swiss Army Knife of functions; it’s so versatile it can replace these 19 Excel functions in one fell swoop: But that’s not reason enough to use it, the function-killer reason to use AGGREGATE over any of the 19 standard functions it can replace is because it is far more powerful. …

The Excel SIGN function returns a 1 if the number is positive, a zero if it’s zero and a -1 if the number is negative. The syntax is simple: =SIGN(number) Here are some basic examples: So what’s it good for? Well, last week we had a request for help from Alston. He was comparing the …

Excel’s CELL function doesn’t get a lot of press but it can be handy to know. The CELL Function simply returns information about the formatting, contents or location of a cell. Let’s look at an example: In cell A5 I have a date and in C5 I have my CELL formula =CELL(“format”,A5). The result returned …

Dynamic formulas are the cornerstone of interactive reports like dashboards. They enable our report recipients to interact with the data, and if set up right they can make our reports quicker and easier to update each month/week etc. With just one data validation list or Combo Box we can control all of the formulas feeding …

We should all be using Go To Special to quickly remove rows containing blanks, but if those blanks are returned by a formula which uses the double quotes to denote a blank then you’ve got a problem because Go To Special thinks a blank returned by "" isn’t blank! The "" is like an invisble …

If you reformat data brought into Excel from an external source regularly then you’ve most likely come across Excel’s Text to Columns tool. You can also reformat text using formulas like MID, SEARCH, LEFT and RIGHT to name a few but this can be tedious and hurt your head 🙂 If you perform the same …

Excel Tables are one of my favourite features. They make working in Excel much more efficient in so many ways, but they have some quirky (read cool) features, one of which is the Structured References they use in formulas. And if you’ve ever tried to write a formula that references an Excel Table then you …

WARNING: this tutorial contains some advanced formula concepts which may incur the following side effects: Negative side effects: Headache, urge to bang head on desk, fidgeting in seat, urge to look for distractions like e-mail to give your brain a rest. Positive side effects: Jumping up and cheering out loud when you realise you cracked …