Data Categorization in Excel: Comprehensive Guide and Techniques

Ever feel like you're drowning in data? Like there's just too much of it, and you can't understand it all?

If so, you're not alone. Not knowing how to categorize data can lead to missed opportunities and skewed decisions.

Let's make it easier for you!

In this guide, we'll discuss everything you need to know about categorizing data in Excel.

We'll cover the basics, like creating categories and assigning values, and more advanced techniques, like conditional formatting and pivot tables. Let’s get started!

How to categorize data in Excel

In this article, we’ll learn basic and advanced techniques for data categorization in Excel. Without further ado, let’s dive straight into these categorization techniques:

Basic techniques for data categorization in Excel:

There are three basic techniques for data categorization in Excel—

1. Sorting
2. Filtering
3. Grouping

Let’s learn more about them one by one.

1. Data categorizing using sorting:

Sorting data can help you find specific data points in a large dataset.

When sorting data, it is essential to specify the order in which you want it sorted. For example, you can sort data in ascending order (A to Z, smallest to largest), descending order (Z to A, or largest to smallest), and by multiple columns.

Or, you could sort a list of names by first name and then by last name.

Here’s how to use the sort feature in MS Excel—

• Take any sheet with your data. We’ve opened a hypothetical marketing sample sheet.

• Select the range of cells you want to sort.

• Click the Sort button to the Data tab in the Excel ribbon.

• Choose the column by which you want to sort the data, and select the sort order (ascending or descending). Here, we’ve chosen to sort the Conversion column in ascending order.

Now, our data is sorted in ascending order. You can repeat this process to sort lists in descending order, too.

Pro-tip: there’s a simpler way to do it - use Gigasheet, a big data spreadsheet platform that makes data categorization feel like a piece of cake.

Gigasheet allows you to sort numbers, alphabets, and dates in any desired order. Here’s how:

• Sign up for Gigasheet (it's free) and upload any file.
• Click on Sort at the top of the page, and a dialog box will come up like this–

• When you click on the Column box, you’ll get a dropdown of all the columns in your sheet. Click on any or multiple of them and select the order you want them sorted. You’ve two options 1 to 9 in ascending order and 9 to 1 in descending order.

We’ve selected the four columns in ascending order.

Now, just click apply and wait for the magic to happen! It’s that easy. Now, onto the next part.

2. Data categorizing using filters:

Data filtering is selecting a subset from a larger data set, making it more manageable by focusing on a specific aspect. There are many different ways to filter data, but the most common methods are based on criteria. For example, you could filter a list of customers by their age, their location, or their purchase history.

Here’s how to use Excel's Filter and Advanced Filter features—

• Select the range of cells you want to filter.

• Go to the Data tab, select the Filter button, and use the filter dropdowns in the header row to select specific criteria. Here, we’ve filtered the three rows based on ascending to descending order, and here you go!

You can use Advanced Filter to apply highly specific filters by defining your criteria.

Now, let’s show you how to do the same with Gigasheet. Gigasheet lets you filter data quickly and easily, giving you more control over what data you want to see. This is how you can do it—

• Click on the Filter button, and this dialog box will appear.

• Decide if you want to filter by Rows or Groups, then select where to filter them. You can also add conditions.

We want to filter the Impressions columns, which are equal to 90,000.

Now, just click apply.

All the impressions column that has data equal to 90000 will be filtered. Next.

3. Data categorizing using groups:

Grouping data is used to organize and analyze any patterns and trends. It helps you compare and contrast data groups using tables, columns, and graphs.

Here, we want to identify the relationship between Campaign Name and Conversion Rate. To calculate the average conversion rate for each campaign, we would—

• Go to the Data tab, choose the columns to group the data, and click the Group button.

The data will look like this—

Level 1 represents the Campaign Name, and Level 2 represents the Conversion Rate. Now, to calculate the average conversion rate—

• Click on Subtotal and ensure the following functions–

1. At "At each change in," select "Campaign Name."
2. At "Add subtotal to," select "Conversion Rate."
3. Ensure "Replace current subtotals" is selected.
4. Check "Average” and click "OK." You’ll get your average.

You can try out this entire method step by step with Excel or just do it with a few clicks on Gigasheet. Here’s how—

1. Click on the Group button to see this dialog box–

2. Now, just add the columns you want to group together, single or multiple. We want to group the Campaign Name and Conversion Rate (%) together here.

Exit the dialog box, and you’ll see your data grouped together like this—

Now, if you want to calculate the average of the conversion rate column.

3. Click on the drop-down button present in every column, and select any function you want. We want to average the Conversion Rate, so I’ll click on average, and boom! You’ve got it.

Way better than the complexities of Excel, don’t you think?

Now, we move on to the more advanced data categorization techniques in Excel and Gigasheet.

Advanced techniques for data categorization

Features like Pivot Tables, and conditional formatting have a steep learning curve, but it doesn’t have to be.

Let’s counter all three advanced methods of data categorization using Excel and how to make the process easier with Gigasheet.

4. Data categorizing using pivot tables:

A Pivot Table allows you to summarize and analyze large amounts of data. It’s a dynamic table that can be easily re-sorted and re-filtered to view the data differently. For example, you could use a Pivot Table to combine sales data for a particular product or customer data for a specific region.

Here’s how to create Pivot Tables in Excel—

• Select your data range, and go to the Insert tab. Here, we selected Clicks and Conversions.
• Click Pivot Table and choose where to place it (use a new worksheet).
• Drag fields into the Rows or Columns area to categorize data. I’ve dragged Campaigns Name into Rows and Clicks and Conversions into Values.

• Use the Values area to summarize data (e.g., sum, count, average). By default, Excel uses Sum. And there you go! Your Pivot Table is ready for dynamic categorization.
  

The problem with creating Pivot Tables in Excel is you need to set them up repeatedly.

You won't face the same problem with Gigasheet. Why? Because everything is set up like a Pivot Table, you can add multiple layers to Filters, group data, and analyze a billion rows in a single sheet with Gigasheet.

Let’s show you how to create Pivot Tables in Gigasheet (using the same spreadsheet we’re using)—

• At the end of your sheet, on the right side, you’ll see an arrow sign (that we’ve pointed with an arrow).

Just click on it to open the Column section.

• Now, you can add the columns to their respective area. We added Campaign Name to Row Groups and Clicks and Conversions to Values.

• Just switch on the Pivot Mode, and you’re done.

This is what your Pivot Table will look like. Easy peasy lemon squeezy.

5. Data categorizing using conditional formatting:

Conditional formatting allows you to apply specific formatting to cells in a spreadsheet based on criteria.

For example, you could use conditional formatting to highlight cells containing specific values or apply a different color to each data category. This can make seeing patterns and trends in your data much more effortless.

Follow this process to do Conditional Formatting in Excel—

• First, select your data column headers. We’ve used Campaign Name, Impressions, Clicks, and Conversions.
• Go to the Home tab and click Conditional Formatting in the Styles group.
• From the dropdown menu, you can choose any rules—

• If we choose the rule highlight cells value and then select greater than (say 15000), we’ll see all the cells with values greater than 15000.

This is how you identify similarities, patterns, and trends in Excel.

To do the same in Gigasheet—

1. Go to the Filter Tab and apply your conditions like this—
   

2. Now, just click apply, and your data that matches the conditions will appear.

Note: Gigasheet will not color or highlight the cells but will filter the cells that don’t match your criteria. It’s more efficient that way.

Data categorization by text, values, and custom orders

This is done with the help of “=IF” and “VLOOKUP.” Both are complex functions in Excel, but Gigasheet makes them both a cuppa tea.

Want to see how?

6. Data categorizing using the IF formula:

The IF formula is used to categorize data based on certain criteria. For example, you could use it to categorize sales data by region, product, or customer type to see how sales are performing in different areas and identify which products are generating the most revenue.

Here’s how you can do it in Excel—

• Along with the same three columns, open another one, Category.
• In cell D2, use the IF formula—

=IF(B2>100000,"High",IF(B2>50000,"Medium","Low"))

This formula checks the value in cell B2 (Impressions) and assigns a category ("High," "Medium," or "Low") based on the conditions provided.

• After entering the formula in cell D2, drag the fill handle (a small square at the bottom right corner) of cell D2 down to copy the formula for all rows with data.

There you’ve it!

If you’re not a fan of formulas (most of us aren’t) and prefer a visual IF builder, then Gigasheet’s IF function will help you—

• Click on the Functions Tab, and then click on the IF function. A dialog box will open, and then you just add in your conditions–

• When you click Insert, a new column will appear, and you’ll see the results of your IF conditions.
  

As you can see here for all the values, the Impression Column has a value less than 100,000, then the value in the category column is 150,000, else 90,000.

It doesn’t matter how large your data is, Gigasheet, with its data handling capabilities, makes it easy for you to take data-driven decisions.

7. Data categorizing using VLOOKUP:

This method is beneficial if you want to find information based on the value of another data point. For instance, you want to find what similar marketing campaigns you’ve run the previous year and this year. You can use VLOOKUP to simply find the similarities you’re looking for.

Follow this process to categorize data using custom order—

• Using the same three columns, create a separate column, say column D, and list the custom categories in order: 5000, 90000, 100000.

a. In D2: 5000

b. In D3: 90000

c. In D4: 100000

• To use VLOOKUP for Categorization:

a. In cell E1, enter the header Category.

b. In cell E2, enter the following formula:

=VLOOKUP(B2, $D$2:$D$4, 1, TRUE)

This formula uses VLOOKUP to find the appropriate category in the custom order list (D2:D4) based on the value in cell B2 (Impressions). The TRUE parameter indicates an approximate match.

• After entering the formula in cell E2, drag the fill handle (E3 to E20).
• Column E now contains the custom category for each campaign based on the Impressions using the VLOOKUP function.

Just like this.

Gigasheet’s Cross File VLOOKUP function makes it easier without remembering any syntax or formulas. You just need to follow the prompts, and you will have the information you’re looking for.

Let’s see how—

• Open the Functions Tab, and then click on Cross File VLOOKUP.
• A dialog box will appear asking for specific conditions. Here, we’re trying to match how many campaigns are similar in the Marketing Sample Sheet1 file, which contains a list of marketing campaigns we conducted in the past three years.

Once we’ve filled in all the data, click apply, and let Gigasheet do its job. In a few seconds, a new column will appear with the LookUp result.

Just like that. Isn’t that easy?

Data Categorization in Excel — Final Thoughts!

Categorizing data in Excel is important for smart decision-making.

We explored different categorization techniques, like IF functions to sort based on conditions, VLOOKUP for custom orders, and PivotTables to group data. Each method will help you extract insights, like spotting trends in different campaigns.

However, doing so manually will require you to be an Excel Ninja. But that’s not the case with Gigasheet!

You’ve already seen how Gigasheet is much easier, time-saving, and more efficient than Excel. And the best part? You don’t need to be an Excel guru to use it.

But don’t take our word for it. Take Gigasheet out for a test spin today. Sign up for free.