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:
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
