Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to make a nested IF formula easier to read

Tags 
Summary 
By their nature, nested IF formulas are hard to read. In this video, we show you how to easily make a nested IF formula more readable, by adding line breaks.
Video Transcript 

This tip was inspired by an article by Annie Cushing on her Annielytics blog: How To Find Text Within Text In Excel. Annie has a lot of great in-depth articles and videos on Excel from the world of internet data. –Dave

In this video, we're going to look at how to make a nested IF formula more readable, by adding line breaks.

Here I have a worksheet that calculates sales commissions, based on the commission structure shown in the table.

For example, we can see that King sold $124,500 and gets a commission of 5%, worth about $6,000.

On the left, I have a small test area, with three formulas to calculate commission rates. Each formula returns the same rate when we plug in different sales numbers.

So, if we look at the first formula, you can see it's created with series of nested IFs. Like all nested IFs, it's difficult to understand what the formula is doing at first glance.

The second formula is identical.

Let's use white space to make the 2nd formula easier to read.

One trick you can use to understand a formula is to use the formula tip window, which appears when you put the cursor into a formula. In this case, we can click into the first IF statement and use the formula tip window to select the logical test, the value if true, and the value if false.

If we click into the next IF statement, we can do exactly the same thing.

So, you can see that nested IF statements follow a pattern. By adding white space to the formula, we can expose this pattern visually.

To do this with a nested IF, expand the formula bar. We need to be able to see more than one line.

Then, click into the first IF statement, and use the tip window to select the value if true. Next, use the right arrow key to step one character to the right. Then add a new line. On the Mac, add a new line with Control Option return. On Windows, use Alt Enter.

Do the same thing for each IF statement.

You might also want to add a new line after the equal sign at the start, so that all statements line up perfectly.

When I press return, the formula works the same as the original.

But the new formula is easier to read and edit. It actually looks like the commission table, so it's a lot easier to find and change specific values.

So remember that Excel doesn't care about extra white space in formulas — you're free to add new lines or extra space as you like.

In the next video, we'll look at the final formula, which uses the VLOOKUP function. I'll show you why this is an even better approach.

Author 
Dave Bruns

Related shortcuts

AltEnter
Return
Your SUBSTITUTE formula for cleaning up phone numbers works perfectly, and saves a TON of time - thank you so much! - Janet
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course