Excel Formulas

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 CTRL+SHIFT+ENTER on massive array formulas …

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 …

If you import data to Excel from another program chances are the dates will come in formatted as text, which means they’re not much use to you in formulas or PivotTables. There are many ways to fix dates formatted as text in Excel and the method you choose will depend partly on the format they’re …

A while back Vernon asked me how he could search one cell and compare it to a list of words. If any of the words in the list existed then return the matching word. Ugh, I’ve written that 3 times and I’m not sure it’s any clearer…let’s look at an example. I’ve highlighted the matching …

For many years there has been something kooky going on in my Excel worksheets. It was this strange phenomenon that from time to time would rear its head. I never had a name for it, I just knew it existed. Then one day a 3 minute video from MrExcel ended years of wonder and finally …

This is a guest tutorial written by Ruthie Ward of Idaho, USA. I wanted to find a partial match to a text string in a long list of words without having to scroll down and look at each one. I also wanted to know the number of possible matches before I filtered the list. Using the …