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 classify your 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 […]

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 2016* 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 2016* onwards. In Excel 2016 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 function. The IFS function removes the […]

This tutorial is applicable to Excel 2016* onwards. New in Excel 2016 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 IF’s, 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 […]

You’ve probably seen a running total formula like this before: = SUM($E$3:E4) You know the one, where the first cell reference is absolute and the second isn’t so that when you copy the formula down the column the range increases by one row at a time. Clever huh. However when you put a formula like […]