How to make a nested IF formula easier to read

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 a 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, in the form of line breaks, to make the second 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, first 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, and I'll show you why this is an even better approach.