4 methods to filter by color in Google Sheets
- Using Filter by color from the Filter icon in the toolbar
- Using Filter by color from the Filter option in the menu bar
- Using Google Apps Script
- Using a third-party add-on
Method 1: Using Filter by color from the Filter icon in the toolbar
This method uses the filter icon present in the toolbar.
Step 1: Select the column you want to filter.
Tap on the column name that you want to filter.
Step 2: Create new filter view.
Find the filter icon in the toolbar. Click the dropdown near it and select Create new filter view.
Step 3: Change the filter name.
In the Name textbox, you can find the default name Filter 1. You can change this to a meaningful name. This would help you identify the purpose of the filter for later uses. However, this step is optional. You can choose to leave the default name as well.
Step 4: Choose the color to filter.
The first cell of the column contains the filter icon at the extreme right. Click on it and select Filter by color. Now, choose Fill Color because we are filtering based on the cell’s background color. If you’re filtering based on text color, choose Text Color. Now, select the color you want to filter out.
In this instance, light green is selected to filter out the students who’ve passed.
Step 5: Check your filtered list.
After choosing your color, you can see the list filtered. Ensure that the filtered data is correct by cross-checking a few values.
Method 2: Using Filter by color from the Filter option in the menu bar.
This method is similar to the previous method. It uses the Data option from the main menu bar.
Step 1: Highlight the desired column.
Choose the column you want to filter and select the column name. This will highlight the entire column. In this instance, column A is selected.
Step 2: Select Create a filter.
From the main menu bar, click on Data. This will open a dropdown. Select Create a filter.
Step 3: Choose your desired color.
Create a filter would create a filter icon at the extreme right on the first cell of the selected column. Tap on it and select Filter by color. Choose Fill Color as you are filtering based on the background color. You can choose Text Color if you want to filter based on the text color. Now, choose the color that you want to be filtered.
In this instance, light red is selected to filter out the students who’ve failed.
Step 4: Check the results.
You can now see the filtered list. Ensure that these are your expected results.
Method 3: Using Google Apps Script
This method involves some code added to your Google Sheet via the Apps Script.
Step 1: Select Apps Script.
From the main menu bar, click on Extensions. Now, select Apps Script. This will open a new window.
Step 2: Copy-paste the code.
Delete any existing code in the code editor. Copy-paste the below code into the code editor. Alternatively, you can also copy-paste the same code from this GitHub link.
/**
* Create custom menu
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Color Tool')
.addItem('Filter by color...', 'filterByColorSetupUi')
.addItem('Clear Ranges','clearProperties')
.addToUi();
}
/**
* Filter By Color Setup Program Flow
* Check whether color cell and filter columnn have been selected
* If both selected, move to filter the data by color
*/
function filterByColorSetupUi() {
var colorProperties = PropertiesService.getDocumentProperties();
var colorCellRange = colorProperties.getProperty('colorCellRange');
var filterColumnLetter = colorProperties.getProperty('filterColumnLetter');
//if !colorCellRange
if(!colorCellRange) {
title = 'Select Color Cell';
msg = '<p>Please click on cell with the background color you want to filter on and then click OK</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.filterByColorHelper(1); google.script.host.close();" />';
dispStatus(title, msg);
}
//if colorCellRange and !filterColumnLetter
if (colorCellRange && !filterColumnLetter) {
title = 'Select Filter Column';
msg = '<p>Please highlight the column you want to filter, or click on a cell in that column. Click OK when you are ready.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.filterByColorHelper(2); google.script.host.close();" />';
dispStatus(title, msg);
}
// both color cell and filter column selected
if(colorCellRange && filterColumnLetter) {
title= 'Displaying Color Cell and Filter Column Ranges';
msg = '<p>Confirm ranges before filtering:</p>';
msg += 'Color Cell Range: ' + colorCellRange + '<br />Filter Column: ' + filterColumnLetter + '<br />';
msg += '<br /><input type="button" value="Filter By Color" onclick="google.script.run.filterData(); google.script.host.close();" />';
msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
dispStatus(title,msg);
}
}
/**
* display the modeless dialog box
*/
function dispStatus(title,html) {
var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(350)
.setHeight(200);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
/**
* helper function to switch between dialog box 1 (to select color cell) and 2 (to select filter column)
*/
function filterByColorHelper(mode) {
var mode = (typeof(mode) !== 'undefined')? mode : 0;
switch(mode)
{
case 1:
setColorCell();
filterByColorSetupUi();
break;
case 2:
setFilterColumn();
filterByColorSetupUi();
break;
default:
clearProperties();
}
}
/**
* saves the color cell range to properties
*/
function setColorCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('colorCellRange', colorCell);
}
/**
* saves the filter column range in properties
*/
function setFilterColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var filterColumn = SpreadsheetApp.getActiveRange().getA1Notation();
var filterColumnLetter = filterColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // extracts column letter from whatever range has been highlighted for the filter column
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('filterColumnLetter', filterColumnLetter);
}
/**
* filter the data based on color cell and chosen column
*/
function filterData() {
// get the properties
var colorProperties = PropertiesService.getDocumentProperties();
var colorCell = colorProperties.getProperty('colorCellRange');
var filterColumnLetter = colorProperties.getProperty('filterColumnLetter');
// get the sheet
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
// get an array of background colors from the filter column
var filterColBackgrounds = sheet.getRange(filterColumnLetter + 2 + ":" + filterColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
// add a column heading to the array of background colors
filterColBackgrounds.unshift(['Column ' + filterColumnLetter + ' background colors']);
// paste the background colors array as a helper column on right side of data
sheet.getRange(1,lastCol+1,lastRow,1).setValues(filterColBackgrounds);
sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
// get the background color of the filter cell
var filterColor = sheet.getRange(colorCell).getBackground();
// remove existing filter to the data range
if (sheet.getFilter() !== null) {
sheet.getFilter().remove();
}
// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();
// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);
// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);
// clear out the properties so it's ready to run again
clearProperties();
}
/**
* clear the properties
*/
function clearProperties() {
PropertiesService.getDocumentProperties().deleteAllProperties();
}Once you’ve pasted the code into the code editor, click the save icon to save the code.
Step 3: Select Color Tool from your Google Sheet.
Go back to your Google Sheet and find Color Tool added to the main menu bar. Click on it and select Filter by color…
Step 4: Select the column and color.
After clicking the Filter by color option, the script will prompt you for the color. Highlight a cell with the desired background color. Click the OK button.
Now, you will be prompted for the desired column. Select a cell from the column you want to filter. Click the OK button.
Step 5: Confirm your choices.
You will see a dialog box with your choices. You can confirm them by clicking the Filter By Color button. If your choices are incorrect, click the Clear Choices and Exit button.
You can now see the filtered data. The extra column is added as part of the code.
Method 4: Using a third-party add-on
This method is a bit long. If you want to filter and transfer particular data to another sheet, you can use this method.
Step 1: Select Get add-ons.
Click Extensions from the main menu. This will open a dropdown. Select Add-ons and tap on Get add-ons.
Step 2: Install the add-on.
Clicking on Get add-ons will open a dialog box. Search for filter by color. Select the Sheetgo add-on.
Click the blue Install button.
It will prompt you to allow access. Click Allow.
After successful installation, you can see a dialog box stating the same.
Step 3: Start the Sheetgo add-on.
Click Extensions from the main menu bar. Hover over the Sheetgo add-on. Select Start.
Step 4: Select Start connecting.
Once you click Start, you can see a dialog box at the extreme right of your Google Sheet. Fill out the required details and click the blue Start connecting button.
Step 5: Click Select data from Export data.
In the EXPORT DATA section, click on Select data.
Step 6: Choose the source file.
Click the blue Select file button.
Grant permission to access your drive. This allows the add-on to transfer the filtered data to another sheet.
Once the sheet is selected, click the blue Continue button.
Step 7: Select an action.
Choose the Filter by color option.
Step 8: Select the column and color to be filtered.
Select the column and color to be filtered out. Once you’ve confirmed your choices, click the blue Continue button.
Step 9: Select the destination file.
Choose a destination file from your drive. Then, click the Done editing button. In this instance, the same sheet is selected. The data will be transferred to a different tab in the same sheet.
Then, click Finish and Save.
Step 10: Open the destination file to see the filtered data.
Once you open the destination file, you can see the students’ names in your destination sheet that was highlighted in green.
Wrapping up
You can use one of the above methods to filter your data by color. No more struggling to filter by color in Google Sheets.