How To Filter By Color In Google Sheets

Have you ever wanted to organize your data in Google Sheets better? Well, here’s a handy technique that can make your data management much easier: “Filter by Color” in Google Sheets. Don’t worry if you’re new to this – it’s a straightforward feature that can have a big impact.

Imagine you have a spreadsheet with lots of information, and you’ve used different colors to highlight certain cells or rows, like marking important customers in one color. With Google Sheets’ “Filter by Color” feature, you can do two main things: sort and filter.

1. Sorting by Color: Let’s say you’ve highlighted some crucial data with colors. With the sorting option, you can bring all those highlighted rows to the top of your list. This way, the most important information is easy to find and focus on.

2. Filtering by Color: But what if you only want to see the rows that you’ve highlighted? That’s where the filtering option shines. You can make the other rows temporarily disappear and only display the ones you’ve coloured. This can be super handy when you’re dealing with a large amount of information and want to concentrate on specific details.

Here’s a little extra tip: “Filter by Color” can also be a great tool for eliminating duplicates in your spreadsheet. By filtering out the duplicates you’ve marked with color, you can ensure your data is clean and accurate.
The best part? Using “Filter by Color” in Google Sheets is a breeze. It’s designed to be user-friendly, especially for beginners.

Scenario: Making Sense of Sales Data

Let’s dive into a real-life scenario to see how handy “Filter by Color” can be in Google Sheets. Imagine you’re in charge of tracking sales for a small business that sells various products. Your sales data spreadsheet is starting to look like a sea of numbers and information.

To make things easier, you decide to use colors to highlight the products that are selling exceptionally well and those that need a boost in sales. You choose green for high-performing products and red for products that require more attention.

Filter By Color In Google Sheets: A Practical Example

Now that we have set the tone for today’s guide on how to filter by color in Google Sheets, let’s give you hands-on experience on how to do it.

To demonstrate just how easy it is to filter by color in Google Sheets, we will be using the following sample data.

Now that we have compiled the sample data we want to use to teach you how to filter by color in Google Sheets, let’s jump into the practical aspect, shall we?

Step 1: Select The Data

Let’s begin by highlighting the data we want to filter by color in Google Sheets. For this example, we will highlight the entire data in our spreadsheet.

Step 2: Data > Filter Views > Create New Filter View

Now that we’ve highlighted the data we want to work with in Google Sheets, let’s take the next step. Go to the “Format” menu at the top, and look for “Filter Views” among the choices.

When you click “Filter Views,” a few more options appear. From these options, pick “Create New Filter View.” This will help us set up the specific way we want to filter our highlighted data.

If you did the steps as we explained, you’ll see something new in your Google Sheets. Each title at the top of your columns now has a little filter icon next to it. This is exactly what we aimed for with the steps we talked about earlier.

Step 3: Filter For Best-Performing products

Now that we have those little filter icons up top, let’s narrow our focus. We’re zooming in on a particular part. We will work with the “Performance color” column in our spreadsheet. This is where we will learn about using color filters in Google Sheets. What we’re aiming to do is to sort out this column by the color green. This way, we can see the products that have been doing really well.

Here’s what you need to do: see that filter icon? It’s right there next to the “Performance color” title. Give it a click.

A bunch of options will pop up. Choose the one that says “Filter by color.” Once you do that, another set of choices will appear. Look for the words “Fill color” and pick the color green.

By following these steps, you’ll be able to filter out and see only the products that are shining bright with green color. It’s a neat trick to spot the top performers quickly.

Here is what our spreadsheet looks like after filtering this column by color green.

Looking at the image provided, you can clearly observe that we’ve successfully filtered the spreadsheet to show only the best-selling products, using color as our guide.

And that’s all there is to it when it comes to filtering by color in Google Sheets.

Step 4: Filter For Poor Performing Products

Looking at the picture above, you’ll notice that we’ve filtered the sheet by green to show the top-performing products. But guess what? We can do the same thing with red to see the not-so-great ones.

Here’s how you do it:

Look at the “Performance” column and find that filter icon. When you click it, a bunch of choices will pop up. Choose the one that says “Filter by color.” This will give you some more options. From those options, pick “Fill color,” and then go ahead and select red from the list.

Once you’ve chosen that color, go back to your spreadsheet, and you’ll quickly notice the clear changes in the data.

In our case, the spreadsheet has been rearranged to show the products that aren’t doing so well.

Check out what it looks like:

When you glance at the picture above, you’ll see that we’ve used color to filter column D. This helps us show only the struggling products. And that’s the simple way to filter by color in Google Sheets.

How To Filter By Color In Google Sheets Using Apps Script

After learning about manual color filtering in Google Sheets, let’s explore something even more fascinating: how to use Apps Script to filter by color in Google Sheets.

Apps Script allows us to automate tasks in Google Sheets, and filtering by color is no exception. This method can be handy when you have larger datasets or want to perform color-based filtering programmatically.

Let’s delve into how you can achieve this. For this example, we will use the same sample data we started with, albeit with some differences.

Step 1: Launch Apps Script

Let’s kick things off by launching the Apps Script Editor. To do that, navigate to the Extensions menu and select the option for Apps script.

Step 2: Write The code

With Apps Script editor launched, clear everything you currently have in the code window and replace it with the following code:

function colorCellsByPerformance() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var dataRange = sheet.getRange("D1:D11" + sheet.getLastRow()); // Assuming your data starts from row 2

// Clear any existing filters

var existingFilter = dataRange.getFilter();

if (existingFilter !== null) {

existingFilter.remove();

}

// Apply filter criteria

var filter = dataRange.createFilter();

// Get values in the Performance Color column

var performanceValues = dataRange.getValues();

// Loop through the values and set background color

for (var i = 0; i < performanceValues.length; i++) {

var cellValue = performanceValues[i][0]; // Column D value

var cell = dataRange.getCell(i + 1, 1); // Get corresponding cell

if (cellValue === "Green") {

cell.setBackground("#00FF00"); // Set background color to green

} else if (cellValue === "Red") {

cell.setBackground("#FF0000"); // Set background color to red

}

Step 3: Run the Script

With the code now written in the code window, as we talked about, here’s the final step: just click on the play icon. This will run the script and put everything into action.

To check if the code we’ve run worked, let’s return to our spreadsheet and look. Here’s how our spreadsheet appears after applying the function we’ve just created.

Looking at the picture above, you’ll notice we’ve accomplished two things. First, we added a filter icon to our spreadsheet. Second, we filtered the data in column D to show different colors. This demonstrates how to use Apps Script to filter by color in Google Sheets.

Alright, time for the next move. See that filter icon? It’s just there, right beside the “Performance Color” column. Give it a click.

When you do, a list of choices will pop up. Choose the one that says “Filter by color.” Then, from the options you see, pick “Fill color” and go with green. This trick will light up those high-performing products, making them stand out.

The video below provides better insight:

Final Thoughts

Without mincing words, we’ve covered quite a lot in today’s guide and by now, you’ll probably agree that filtering by color in Google Sheets isn’t as tricky as it might sound.

In this guide, we’ve not only walked you through the process using clear steps, screenshots, and even video illustrations, but we’ve also taken it a step further by demonstrating how to filter by color in Google Sheets using Apps Script.

If you ever find yourself stuck while trying to filter by color in Google Sheets, don’t worry. You can always revisit the steps outlined in today’s guide for a quick refresher.

We genuinely hope that this guide has been useful to you.