Do you want to make your Google Sheets stand out by highlighting distinct values in a row, column, or even the entire table? Look no further! In this article, I will share formulas and instructions that will help you achieve this effortlessly.
Easy Formulas to Highlight Distinct Values
Google Sheets offers a variety of functions that can be used to highlight distinct values, such as COUNTIF, UNIQUE, MODE, MODE.MULT, and QUERY. However, I will focus on the simplest ones: COUNTIF and UNIQUE.
To get started, let’s take a look at examples of highlighting distinct values in rows.
Highlight Distinct Values in Rows
Consider the scenario where you have a table and you want to identify the cells that contain unique values in each row. The formulas below will do the trick:
=match(B2,unique($B2:$F2,true,true),0)
=countif($B2:$F2,B2)=1
The first formula utilizes the UNIQUE function to return distinct values, while the MATCH function matches them within the row. The second formula uses the COUNTIF function to count the occurrences of each value. If a value occurs only once, it is considered distinct.
To apply these rules to a range of cells, go to Format > Conditional formatting and customize the settings accordingly.
Highlight Distinct Values in a Table
If you want to highlight distinct values in the entire table, not just row by row, a slight modification is required. Use the COUNTIF formula with absolute references for the range:
=countif($B$2:$F$6,B2)=1
The logic behind the formula remains the same: it counts the occurrences of each value and highlights the ones that occur only once. Remember to use dollar signs to make the references absolute.
In case you prefer using the UNIQUE and MATCH formulas, there is one additional step. Since the UNIQUE function can only return distinct values in a row or column, you will need to use the FLATTEN function to virtually move the values into a single column. Here’s the formula:
=match(B2,unique(flatten($B$2:$F$6),false,true),0)
Conditional Format Distinct Values in Columns
To highlight distinct values in columns, simply transpose the data and apply the formulas accordingly. For example, to highlight distinct animals in column E and distinct colors in column F, use these formulas:
=match(B2,unique(B$2:B$6,false,true),0)
=countif(B$2:B$6,B2)=1
The main difference here is that the UNIQUE function’s second parameter changes from TRUE (by row) to FALSE (by column).
That’s all there is to it! By applying these formulas and conditional formatting, you can easily highlight distinct values in Google Sheets and make your data visually appealing.
For more tips and tricks on working with Google Sheets, visit Crawlan.com.
Happy highlighting!
