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!
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:
There are three basic techniques for data categorization in Excel—
Let’s learn more about them one by one.
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—
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:
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.
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—
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—
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.
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—
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—
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—
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.
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.
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—
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)—
Just click on it to open the Column section.
This is what your Pivot Table will look like. Easy peasy lemon squeezy.
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—
This is how you identify similarities, patterns, and trends in Excel.
To do the same in Gigasheet—
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.
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?
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—
This formula checks the value in cell B2 (Impressions) and assigns a category ("High," "Medium," or "Low") based on the conditions provided.
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—
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.
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—
a. In D2: 5000
b. In D3: 90000
c. In D4: 100000
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.
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—
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?
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.