Use an Excel Nested If Function to Create Categories

You’ve probably got a lot of population data that you’ve collected through assessments and surveys. And most likely, you use Excel (sometimes or all the time) to analyze all that data. The IF function in Excel is a logical expression which consists of a “logical test” and a true/false result. And you can use the Excel Nested If function to apply categories to population data.

When would you use an Excel Nested If Function?

Here are three examples of when you might want to use an Excel Nested If function:

Create age categories for individuals when you’ve only collected age data
Use a formula (like a vulnerability calculation) only for certain survey respondents
Apply income ranges to individual household results when you’ve only collected income data

Syntax of the basic IF function

=IF(logical_test, [value_if_true], [value_if_false])

The logical_test is most often some comparison between two values. Value_if_true is the value that will be returned as a result of the formula if the logical_test is TRUE. Value_if_false is the value that will be returned if the logical_test is FALSE.

These two values can be in any format: cell, text, number, date, time, or even another function, etc.

To create an Excel Nested IF function, if the logical_test is FALSE, we want to apply a second logical_test. In this case we will put a new IF function in the place of the [value_if_false] parameter.

Syntax is Boring – Let’s Look at a Real Example – Age Categories

In this example survey data, we have collected age data (for example, we’ve asked “How old is the head of household?”) Here’s a small snapshot of the age data:

If we were to show this data in a bar chart, here’s what it would look like (not super useful to anyone) – just a lot of bars:

Usually, you want to group the heads of households in certain age categories based on your organisation’s indicators, or based on your donor indicators. For example, let’s use these age categories:

Less than 18 years (<18)
18-39 years
40-59 years
60 years or older (>60)

If you apply the Excel nested if function to the age data using these categories, you’ll end up with this chart instead (much nicer to put into a report):

Step-by-Step: Excel Nested If Function

1. Write your first If Function

First, add a new column to the spreadsheet and call it “Age Categories”. Start out by putting a single “If” function into the top cell in this column. Make this function check: “if the age is less than 18 years old, then apply age category ‘<18 years'”. You write the function like this:

First, write an “equals” sign, then write “if” and an open bracket.
Next, click on the cell where your age data is held (in my example, my age data is in cell E2).
Then, put in a “less than” sign, put in the age 18 and a comma to mark the end of the logical test.
Then, put double quotations and the text that you want to apply and double quotations again.
Last, put another bracket to end the function:

=if(E2<18,"less than 18 years")

And hit enter = if your age data is greater than or equal to 18, the cell will now saw “FALSE”

2. Add a second “if function”

Right now, we’re only checking one of our age categories (we’re checking to see if the data in cell E2 is less than 18). So, you’re going to add a second IF function to the formula to check if it’s in the next age category.

Take the formula you’ve already written. After the second set of double quotation marks, add a comma.
If you hit “Alt+Enter” on your keyboard – this puts the next part of the formula on the next line and makes it easier to keep track of all the if statements in your formula.
Then, write “if” again, and another open bracket, and check if the age is less than 40 years by writing E2<40. Then put another comma to end the new logical test.
Next, type in “18-39 years old”.
And then put in two closed brackets to close both if statements:

=if(E2<18,"less than 18 years", if(E2<40,"18-39 years old"))

Press enter when you’re done.

3. Add a third “if function”

You’re now checking if the age meets one of two age categories. First, you’re checking to see if the data in cell E2 is less than 18. Then, if the data isn’t less than 18, you’re using a nested if function to check if it’s ). Now you’re going to add a third IF function to the formula to check if it’s in the next age category (40-59 years old).

Take the formula you’ve already written. After the “18-39 yrs” put a comma, and hit “Alt+Enter” on your keyboard – this puts the next part of the formula on the next line.
Then, write “if” again, and another open bracket, and check if the age is less than 60 years by writing E2<60. Then put another comma to end the new logical test.
Next, type in “40-59 years old”.
And then put in three closed brackets to close all three if statements:

=if(E2<18,"less than 18 years", if(E2<40,"18-39 years old", if(E2<60,"40-59 years old")))

Press enter when you’re done.

4. Add the last age category

For your last age category, you don’t need to put another nested if statement. You just want to make a statement that “if the logical test is still FALSE – then just give it this leftover age category”. We want the final category to just say “60+ years old”. It doesn’t matter if the age is 60 or 600, it will still apply this final age category.

Take the formula you’ve already written. After the “40-59 years” put a comma, and hit “Alt+Enter” on your keyboard – this puts the next part of the formula on the next line.
Then, write “60+ years” and leave the three closed brackets to close all three if statements:

=if(E2<18,"less than 18 years", if(E2<40,"18-39 years old", if(E2<60,"40-59 years old", "60+ years")))

Press enter when you’re done.

5. Copy the formula down

When you hit enter after you put the final formula in the cell, you’ll see the result immediately in that top cell.

Copy and paste (or “fill down”) the formula into the whole column of Age Categories. Here’s a partial screenshot:

6. Make a pivot table of the “Age Category” column

Highlight the age category column
Click Insert -> Pivot Table
Press “OK” to insert the pivot table into a new spreadsheet
Drag “Age Categories” into the Pivot Table “Rows”
Also drag “Age Categories” into the Pivot Table “Values” so that it shows “Count of Age Categories”

7. Make a bar chart of the final age categories

Highlight the data from the pivot table
Copy and “paste as values” so the data is no longer linked to the pivot table
Turn “absolute” numbers into “percentages”
Click on Insert -> Bar Chart
Format the bar chart to your liking

By Janna

Janna is an aid worker, an engineer, a mom, a wife, and a self-declared data-lover! Her mission is to connect with every field worker in the world to help the humanitarian sector use information management and technology to make aid faster and more accountable.