Check out my brand new beginner’s course: Google Sheets Essentials.
This is a 100% online, on-demand video training course designed specifically to help you boost your spreadsheet skills.
Conditional formatting is a super useful technique for formatting cells in your Google Sheets based on whether they meet certain conditions.
For example, you could use it to apply background colors to cells based on the value in the cell.
You can go further than this though, and apply the formatting across an entire row, based on the value in a single cell.
For example, if the continent is “Africa” in column C, you can apply the background formatting to the entire row (as shown by 1 and 2):
Five steps to apply conditional formatting across an entire row
It’s actually relatively straightforward once you know the technique using the $ sign (Step 5).
Step 1. Highlight the data range you want to format
The first step is to highlight the range of data that you want to apply your conditional formatting to. In this case, I’ve selected
Step 2. Choose Format > Conditional formatting… in the top menu
Open the conditional format editing side-pane, shown in this image, by choosing Format > Conditional formatting… from the top menu:
Step 3. Choose “Custom formula is” rule
Google Sheets will default to applying the “Cell is not empty” rule, but we don’t want this here.
Click on the “Cell is not empty” to open the drop-down menu:
Scroll down to the end of the items in the drop-down list and choose “Custom formula is”. This will add a new input box in the Format cells if section of your editor:
Step 4. Enter your formula, using the $ sign to lock your column reference
In this example, I want to highlight all the rows of data that have “West” in column A. In this new input box, enter the custom formula:
The key point to understand is that you lock the column you want to base your conditional formatting on by adding a $ (dollar sign) to the column reference.
I start inputting the first cell of my highlighted range:
Then I add the $ (dollar sign) in front of the A only:
Then I add the test condition, in this case whether the cell equals “West”:
= $A2 = "West"
As the conditional formatting test is applied across each row, the value from the first cell in column A is used in the check.
More examples of conditional formatting across an entire row
Based on a threshold value
This is a super useful application of this technique, to dynamically highlight rows of data in your tables where a value exceeds some threshold.
In this example, I’ve highlighted all of the students who scored less than 60 in class, using this formula in the custom formula field:
= $C2 < 60
Based on checkboxes
If you haven't heard of or used checkboxes yet you're missing out. They're relatively new to Google Sheets (mid 2018) and are super useful. Read more about them here.
When a checkbox is selected it has the value TRUE, and when it is not selected the cell has the value FALSE. So we can use that property in our custom formula:
= $B2 = TRUE