How to Filter by Color in Google Sheets
Filtering a spreadsheet by color is important. It can help reduce the amount of visual information by only highlighting what’s crucial in any given situation. Whether you use spreadsheets for personal use or work projects, this is something you should know how to do. That way, you’ll avoid getting overwhelmed by the less important data.
Using the Color Filter
You can always just apply one filter to your spreadsheet in order to hide or highlight specific data. But as you’ll soon learn, there’s much more you can do as you delve deeper into the formatting options.
- Open the spreadsheet you want to filter.
- Select your cells.
- Click the Data button.
- Click on the Create a Filter button.
- Click the newly formed Filter triangle icon on the spreadsheet.
- Select a fill color or text color you want to sort by.
- Select the Sort by color option and choose a color from the list.
After you follow these steps, the cells with the color you chose will be immediately placed at the top of the range. Note that you can’t sort by alternating colors instead, cells with the same color (text or fill) will be grouped.
If you want to turn the filter off follow these steps:
- Click ‘Data’ in the upper menu
- Select ‘Filter Views’
- Select ‘None’ from the pop-out menu
You can also delete the filter altogether using this menu.
Differences Between Filters and Filter Views
Google Spreadsheets gives you two ways of filtering your data in order to hide what you’re not interested in seeing.
Here’s where things get interesting. When applying a filter you can sort cells by color. After you turn a filter off, the data will keep the same sorting that the filter applied.
However, with filter views, things are much more complex. By applying filter views, you can essentially create multiple filtered views for the same spreadsheet and even save those views by individual names.
This also means that whoever is looking at the data will have multiple ways of analyzing it, based on the filter view. This can come in handy during a presentation where multiple people open the same spreadsheet but are interested in very specific data sets and information.
You should also know that temporary filter views can also be used when you’re reading a spreadsheet without having edit access. Unfortunately, while you can import and export various filters, you can’t do the same with filter views.
How to Create a Filter View
Creating multiple filter views may take some time but the results can be rewarding if your data depends on the viewer’s ability to interpret it in multiple ways.
- Open your spreadsheet.
- Click on the Data button.
- Click on Filter Views.
- Click on the Create new filter view option.
- Filter and sort your data by colored text or fill color as previously instructed.
- Click the Close icon in the top right corner.
The filter view will be sorted automatically. Be aware that you can also duplicate a filtered view by selecting the Duplicate option from the Options menu.
Here’s how you can rename a filtered view to give it a better description:
- Click on Data.
- Select ‘Filter views’.
- Select ‘Filter View Options’.
- Click ‘Rename’
- Click on the name of that filter, on the black bar, and type a new name.
Conditional formatting gives you a different way to apply a color filter in your spreadsheet. This too can be used on groups of cells, columns, etc.
If you’re wondering how to add color to your cells so that you or your team can filter them, follow these steps:
- Highlight your rows and columns. You can click the empty box between A & 1 to highlight the entire spreadsheet, or simply click and drag your cursor across all of the filled columns and rows
- Click ‘Format’ in the upper menu bar
- Click ‘Conditional Formatting’
- A pop-out menu will appear in the right-hand side of Sheets – Click ‘+ Add another rule’
- The ‘Apply to Range’ should be populated with the proper cells
- Click below the ‘Format Cells if’ option to access the dropdown.
- Select one of the many options that target specific pieces of information. In this example, we chose ‘Text Contains’ and put the number 2.
- Click on the paint can icon to update the fill color in the cells containing the data you wish to organize.
Plus, you can use various formatting rules to set specific colors for specific sets of data for a deeper interpretation.
- Select a range in your spreadsheet.
- Be very specific unless you want to apply conditional formatting to the entire spreadsheet.
- Right-click on your selection.
- Select Conditional Formatting from the bottom of the menu.
- Select either Single Color or Color Scale, depending on how unique you want the filter to be.
- Select the format rules that apply to your selection.
- Google Spreadsheets gives you 19 common format rules and the option to apply a custom formula.
Note that this can’t be used to filter the selection based on a specific color. However, it can be used to color specific cells in a certain way. You can then use a filter based on the colors you want to hide any unwanted data in a multi-filter view presentation.
Multiple Filter Views
Google Sheets will let you create several filter views. Following the steps above, you can click ‘+ Add Another Rule’. Create a new filter and click ‘Done’. Then you can choose the filter icon located inside the first cell to toggle between your available filters.
Google Spreadsheets – Complex Yet Free and Highly Customizable
Google Spreadsheets is an amazing app for anyone that needs access to spreadsheet data and anyone that wants to make in-depth presentations. Although initially there wasn’t any native support for filtering by text color and fill color, Google Spreadsheets now offers this option.
Although it may take serious work, especially when working with large data sets, to get proper sorting arranged, being able to take advantage of multiple filter views and a wide range of colors, as well as formatting rules, can be very valuable.