Table of Contents
Data can be categorized based on values using Excel by utilizing its sorting and filtering functions. This allows for the organization and grouping of data based on specific criteria, such as numerical values or alphabetical order. By selecting the data range and choosing the desired sorting or filtering options, Excel can automatically rearrange the data to meet the specified criteria, making it easier to analyze and interpret the information. This feature in Excel is beneficial for data management and presentation purposes, as it helps to identify patterns and trends within the data.
Excel: 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:
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: