Hands-On Excel

Hands-On Excel Challenges Nested IF

Nested IF practice: grade bands

A nested IF puts one IF inside another, so a formula can choose between more than two answers: =IF(B2>=80, "A", IF(B2>=65, "B", IF(B2>=50, "C", "F"))). Start with the highest band.

CoreAbout 3 minutesFree, no sign-upBy Updated

Try it yourself

11 · Nested IF · Core

Grade bands with nested IF

Give each student a grade in column C: 80 or more is an A, 65 or more a B, 50 or more a C, and anything lower an F.

Put one IF inside another: if the first test is false, the next IF gets its turn. IF stops at the first test that is true, so start with the highest band.

grades.xlsx
Ready

Write in the green cells, then press Check. Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

The guide · Nested IF

How it works

An IF has a test, an answer for TRUE and an answer for FALSE. In a nested IF, the FALSE answer is another IF. Excel goes through the tests from left to right and stops at the first one that is true.

=IF(B2>=80, "A",
   IF(B2>=65, "B",
      IF(B2>=50, "C", "F")))

For a score of 72: is it 80 or more? No. Is it 65 or more? Yes, so the answer is B, and the last test is never checked.

Why the order matters

Write the same tests from small to large and every passing student gets a C:

=IF(B2>=50, "C", IF(B2>=65, "B", IF(B2>=80, "A", "F")))

A score of 91 is "50 or more", so the first test is already true and IF stops there. With >=, go from the highest band down. (With <, go from the lowest up: =IF(B2<50, "F", IF(B2<65, "C", …)).)

Count the brackets

Every IF opens a bracket, and they all close at the end: three IFs need ))). Excel colors matching brackets while you type, which helps.

When there are many bands

Past three or four levels, nested IFs get hard to read. Two other ways do the same job:

Common mistakes

FAQ

How do I write a nested IF in Excel?
Put the next IF where the FALSE answer goes: =IF(B2>=80,"A",IF(B2>=65,"B",IF(B2>=50,"C","F"))). Close all the brackets at the end.
Why does my nested IF give everyone the same grade?
The first test is true for almost everyone, usually because the lowest band is checked first. IF stops at the first true test, so with >= start with the highest band.
How many IFs can you nest in Excel?
Up to 64, but a formula with more than three or four is hard to read and check. Use IFS or a lookup table instead.
Why does my nested IF show FALSE?
The last IF has no answer for when its test is false. Add a third argument to it, such as "F".

All challenges

  1. 01 Add it upSUM Starter · 3 min
  2. 02 Find the averageAVERAGE Starter · 3 min
  3. 03 Write once, fill downRelative references Starter · 3 min
  4. 04 Lock it with $Absolute references Core · 3 min
  5. 05 Lock just the row or the columnMixed references Advanced · 3 min
  6. 06 Percent of a totalPercentages Starter · 3 min
  7. 07 Up or down by how much?Percent change Core · 3 min
  8. 08 Select to the end in one moveCtrl + Shift + arrow Core · 3 min
  9. 09 Pass or fail?IF Core · 3 min
  10. 10 Both or either?AND, OR Core · 3 min
  11. 11 Grade bands with nested IFNested IF Core · 3 min
  12. 12 Count the ones that matchCOUNTIF Core · 3 min
  13. 13 Add up the ones that matchSUMIF Core · 3 min
  14. 14 Use a cell as the conditionCriteria in a cell Core · 3 min
  15. 15 Count with two conditionsCOUNTIFS Core · 3 min
  16. 16 Add up with two conditionsSUMIFS Core · 3 min
  17. 17 Look it upVLOOKUP Core · 3 min
  18. 18 Find the right bracketVLOOKUP approximate match Core · 3 min
  19. 19 XLOOKUP, the modern wayXLOOKUP Core · 3 min
  20. 20 Where is it in the list?MATCH Core · 3 min
  21. 21 INDEX and MATCH togetherINDEX + MATCH Advanced · 3 min
  22. 22 Catch the error, not the bugIFERROR Core · 3 min
  23. 23 Split first and last namesLEFT, MID + FIND Core · 3 min
  24. 24 Mistake hunt: #N/A everywhereFixing #N/A Core · 3 min
  25. 25 Mistake hunt: hard-coded numbersReferences instead of typed numbers Core · 3 min
  26. 26 Mistake hunt: numbers stored as textNumbers stored as text Core · 3 min

Pro · coming soon

From first formula to job-ready.

100+ challenges on lookups, pivot tables, dates and text cleanup, an interview track modeled on the Excel tests employers use, and your progress saved across devices.

$29 one-time · early-bird price

You won't be charged today.

You're early!

Pro isn't open yet. We're building it right now. Leave your email and we'll hold the $29 early-bird price for you and tell you the day it launches.

We'll email you a link to confirm, then one email when Pro launches. Privacy