How to Categorize Data in Google Sheets

Staring at a messy spreadsheet is overwhelming, but categorizing data in Google Sheets is much easier than you think. It's the essential first step to transforming raw numbers and text into clear, actionable insights. In this tutorial, we'll walk you through several practical methods to organize your data, from simple formulas to more advanced, scalable techniques.

Why Categorize Data in Google Sheets?

Categorizing data is about adding context. Instead of a long list of individual transactions, campaigns, or leads, you get organized groups that tell a story. Properly categorized data is the foundation of effective analysis and reporting.

• For Analysis: It allows you to use tools like Pivot Tables and charts to summarize information. You can't chart your "top expense categories" if you haven't defined any categories.
• For Clarity: It makes large datasets understandable at a glance. Seeing "Social Media," "PPC," and "Email" is more insightful than seeing hundreds of individual campaign names.
• For Decision-Making: It helps you spot trends. Are you spending too much on "Supplies"? Is the "Social Media" category driving the most leads? You can't answer these questions without grouping your data first.

Method 1: The Manual Approach (And Why to Avoid It)

The most basic way to categorize anything is to do it by hand. This usually involves sorting your data by a specific column and then manually typing a category name in an adjacent column for each row. You might create a new column called "Category," sort your data by "Description," and then type "Software" next to every entry for Zoom, Slack, and Adobe.

While this might seem quick for a dozen rows, it's a terrible strategy for anything more extensive.

• It's slow: Manually typing or copying and pasting is incredibly time-consuming.
• It's error-prone: Typos are inevitable. "Marketing," "Marekting," and "marketing" will be treated as three unique categories by Google Sheets, throwing off your totals.
• It's not scalable: The moment you add new data, you have to repeat the entire manual process. There's a better way.

Method 2: Using the IF and IFS Functions for Simple Logic

Formulas are your best friends for automating categorization. The IF function family is the perfect starting point for rule-based sorting. It checks if a condition is true and then gives you a specific output.

The Basic IF Function

The IF function is a simple workhorse. It follows this logic: IF something is true, THEN do this, ELSE do that. The syntax is:

=IF(logical_expression, value_if_true, value_if_false)

Let's say you have a list of sales transactions in column B and you want to categorize them. You could classify any sale over $500 as a "Large Order." In cell C2, you'd write:

=IF(B2>500, "Large Order", "Standard Order")

Google Sheets will check the value in B2. If it's greater than 500, it will return "Large Order." If not, it will return "Standard Order." You can then click the small blue square on the bottom right of cell C2 and drag the formula down to apply it to all your rows.

Handling More Categories with Nested IFs

What if you have more than two categories? For years, the answer was "nested IFs," where a second IF function is placed inside the first one.

Imagine you want three sales categories: "Large" (over $500), "Medium" (over $100), and "Small" (anything else). You could write a nested IF formula like this:

=IF(B2>500, "Large Order", IF(B2>100, "Medium Order", "Small Order"))

This works, but as you can see, stacking more than two or three IF statements becomes messy and hard to troubleshoot. That's why Google introduced a cleaner alternative.

A Cleaner Approach: The IFS Function

The IFS function lets you check multiple conditions in a single, easy-to-read command. It's perfect for replacing clunky nested IFs. The syntax is:

=IFS(condition1, value1, condition2, value2, ...)

Let's recreate the same sales categorization using IFS:

=IFS(B2>500, "Large Order", B2>100, "Medium Order", B2<=100, "Small Order")

This is much easier to read and modify. You can add more conditions just by adding another comma-separated pair without adding more nested parentheses. It's the modern, preferred way to handle multiple rule-based categories.

Method 3: Using Keyword Matching with SEARCH and IFS

Categorization often involves looking for specific words inside of text, like a transaction description or a marketing campaign name. A simple IF statement won't work because IF(A2="Google") will only find cells that contain only the word "Google," not "Google Ads Campaign US."

To solve this, you need to combine the SEARCH and ISNUMBER functions with IFS.

• SEARCH("find_text", text_to_search) looks for a piece of text inside a cell and returns the starting position number if found. If not found, it returns an error.
• ISNUMBER() checks if its content is a number. We can wrap the SEARCH function in it, so ISNUMBER(SEARCH(…)) returns TRUE if the keyword is found and FALSE if it's not.

Imagine you have marketing campaign data in column A that you want to group by channel. Your categories are "Search," "Social," and "Email."

You can build an IFS formula to check for keywords associated with each channel:

=IFS( ISNUMBER(SEARCH("google", A2)), "Search", ISNUMBER(SEARCH("bing", A2)), "Search", ISNUMBER(SEARCH("facebook", A2)), "Social", ISNUMBER(SEARCH("instagram", A2)), "Social", ISNUMBER(SEARCH("klaviyo", A2)), "Email", TRUE, "Other" )

Let's break that down:

• It checks for "google" or "bing" and assigns "Search."
• Then it checks for "facebook" or "instagram" and assigns "Social."
• The final part, TRUE, "Other", is a catch-all. If none of the previous conditions are met, this one will be, assigning the "Other" category.

Method 4: Using VLOOKUP for a Scalable Lookup Table

While IFS is great, it falls apart when you have dozens or hundreds of items to map to categories. Editing the formula every time you add a new keyword is inefficient. For these situations, the most robust and professional method is using a VLOOKUP with a lookup table.

A VLOOKUP (Vertical Lookup) scans down the first column of a table to find a specific value, then returns a corresponding value from a different column in the same row.

Step 1: Create a Lookup Table

First, you need a reference table. The best practice is to create this in a separate tab within your Google Sheet. Let's call the new tab "Categories." In this tab, create two columns:

• Column A (Lookup Value): A unique list of all the items you want to categorize (e.g., source names like "google," "facebook," "linkedin").
• Column B (Category): The corresponding category for each item (e.g., "PPC," "Paid Social," "Paid Social").

Your "Categories" tab might look like this:

Step 2: Write the VLOOKUP Formula

Now, go back to your main data sheet. The VLOOKUP syntax is:

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key: The cell in your main data sheet you want to look up (e.g., A2, which contains 'google').
• range: Your entire lookup table. You'll reference the other tab and use dollar signs ($) to lock the range so it doesn't shift when you copy the formula down (e.g., Categories!$A$2:$B$7).
• index: The column number in your lookup table that contains the category name. In our case, it's the second column, so the index is 2.
• is_sorted: This should almost always be FALSE, which tells VLOOKUP to look for an exact match.

Your final formula would be:

=VLOOKUP(A2, Categories!$A$2:$B$7, 2, FALSE)

Handling Errors with IFERROR

If VLOOKUP can't find the search_key in your list, it will return an #N/A error. You can wrap your formula in the IFERROR function to return a fallback value instead, like "Uncategorized."

=IFERROR(VLOOKUP(A2, Categories!$A$2:$B$7, 2, FALSE), "Uncategorized")

This is the gold standard for categorization because it's scalable. Need to add a new source or change a category? You only update the central "Categories" tab - you never have to touch the formula again.

Final Thoughts

Categorizing data in Google Sheets turns a cluttered list into a powerful analysis tool. Whether you start with simple IF statements for quick labeling, use keyword searches for descriptive text, or build a scalable VLOOKUP table for consistency, organizing your data is the first step toward uncovering valuable insights that can move your business forward.

While mastering these formulas is a great skill, we know the real goal is to get answers quickly without getting stuck in spreadsheets. That's why we built Graphed. Instead of writing formulas, you can connect your Google Sheet and simply ask, "Show me last month's spending by category," and get an instant chart. We handle connecting your data, cleaning it up, and preparing it for analysis, allowing you to focus on the insights, not the setup.