Excel: How to Categorize Data Based on Values


Often you may want to categorize data based on values in Excel.

For example, suppose we have the following dataset about basketball players and we’d like to place each player into a category based on their points:

The following step-by-step example shows how to do so.

Step 1: Enter the Data

First, enter the data values into Excel:

Step 2: Define the Limits for the Categories

Next, we need to define the limits for the categories.

For example, suppose we would like to define the following limits:

  • 0-9 points = Bad
  • 10-19 points = OK
  • 20-29 points = Good
  • 30-39 points = Great
  • 40+ points = Excellent

We can create the following table in columns E and F to define these limits:

Step 3: Categorize the Data

Next, we can type the following formula into cell C2 to categorize the first player based on the number of points in cell B2:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

We can then click and drag this formula down to each remaining cell in column C:

Excel categorize data based on values

From the output we can see:

  • Andy scored 22 points, so he is categorized as Good.
  • Bob scored 14 points, so he is categorized as OK.
  • Chad scored 19 points, so he is categorized as OK.
  • Doug scored 35 points, so he is categorized as Great.

And so on.

Note: You can find the complete documentation for the VLOOKUP function in Excel here.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Sum Values by Category in Excel
How to Group by Name in Excel
How to Rank Values by Group in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *