How To Make Categories In Google Sheets

Last Updated on November 1, 2023 by Jake Sheridan

In this tutorial, you will learn how to make categories in google sheets.

When you’re working in Google Sheets, you may find you need a way to categorize your data for filtering or color-coding purposes. Categories can be used to show the status of orders, leads, inventory items, etc, to designate the priority of tasks, or to label purchases with the locations to calculate sales tax, to name just a few potential purposes. Here’s how to make categories in Google Sheets

Category List

The first step is creating a list of categories to use and entering this list into a specific column in the spreadsheet for easy reference. Here’s how:

Step 1

Choose a location in the spreadsheet for the list where it will be out of the way. For this example, we’ll make a tab called Categories

Step 2

Enter the category items. Don’t worry if you don’t have the full list yet, you can edit this easily later

Step 3

Take note of the range the categories are stored in. In this example, they’re in column A of the Categories sheet, so the reference to them is Categories!A:A. We’ll need this in the next section

Category Dropdowns

Once you’ve created the list of categories, it’s time to add in-cell dropdowns to easily categorize your data. Here’s how:

Step 1

Return to the sheet where you want to add the categories, and designate a column for them. Add a header to label the column.

Step 2

Select the data cells in the column (excluding the header) and open the Data menu, then choose Data Validation

Step 3

In the window that appears, ensure that the List From Range option is selected for criteria, then enter the range reference from the previous section in the text field beside it. Be sure to include the equals sign before the range reference, just as you would when adding a cell reference to a spreadsheet cell

Step 4

Make sure the box is checked for Show Dropdown List in Cell to make it easier to identify the cell as a dropdown menu. It’s a good idea to also choose the Reject Input option for how to handle invalid data, unless you want to allow users to enter categories by typing them instead of choosing them from the list

Step 5

Click Save and small dropdown arrows will appear in the selected cells. You can now click on these cells and a dropdown menu of categories will appear for you to select

Step 6

Any changes to the list of categories will be reflected the next time you open the dropdown list, but note that values selected from the list will not update if the corresponding category in the list is updated

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to make categories in google sheets. Want more? Check out all the Google Sheets Tutorials.