Hey there, my fellow spreadsheet enthusiasts! Today, I’m going to let you in on a little secret that will make your data analysis in Google Sheets even more powerful. We’re going to dive deep into the world of conditional formatting based on another column. Trust me, once you learn this technique, you’ll wonder how you ever lived without it!
Applying Conditional Formatting Based on Another Column
Let’s start by exploring how we can apply conditional formatting by comparing values from another column. This is incredibly helpful when you want to highlight certain cells based on specific conditions.
Imagine you have a spreadsheet with a list of names and corresponding sales figures. You want to apply conditional formatting to highlight the names when their sales value is less than $3,000.
Here’s what you need to do:
-
Select the range of cells where you want to apply conditional formatting. In our example, we’ll apply it to the column containing the names, so we’ll highlight all the data in that column except for the header row.
-
Click on “Format” in the File menu, then select “Conditional formatting.” This will open up the conditional formatting rules side panel.
-
In the “Format cells if” drop-down menu, select “Custom formula is.” This allows us to create a custom formula for our condition.
-
Enter the custom formula
$C3<3000. Let me break it down for you:$C3: This refers to the column where the formula will perform the comparison, followed by the number of the first row in your selection. Make sure the row number matches the first row of your selected range.<: This is the operator we’ll use to check if the value is less than the specified number.3000: This is the value against which the formula will compare. Text values should be enclosed in double quotation marks, while numerical values shouldn’t.
-
Specify the formatting style you want to apply. In the “Formatting style” section, you can choose various formatting options like font color, cell color, and text styling.
-
Click on the “Done” button to apply the conditional formatting.
Voila! Now you can see the magic of conditional formatting in action. In our example, the formatting has been applied to the name “Steven” because the sales value next to it is less than $3,000, as specified in our custom formula.
Comparing Two Columns with Conditional Formatting
But wait, there’s more! We can take conditional formatting to the next level by comparing data from two columns. This allows us to apply formatting to cells where one column’s values exceed or fall short of another column’s values.
Let’s say we have a spreadsheet with sales data and target sales data. We want to highlight the cells where the sales fall short of the target.
Here’s how you can make it happen:
-
Select the range of cells where you want to apply the formatting. In our case, we’ll select the complete range of both columns, excluding the header row.
-
Click on “Format” in the File menu, then select “Conditional formatting.”
-
In the “Format cells if” drop-down menu, select “Custom formula is.” The conditional formatting rules panel will pop up on the right side of your screen.
-
Enter the custom formula
$C3<$D3. Let’s break it down:$C3: This refers to the first column the formula will search, followed by the number of the first row in your selection.<: This is the operator we’ll use to check if the value is less than.$D3: This refers to the second column the formula will search, followed by the number of the first row in your selection.
-
Choose the formatting style you want to apply. In the “Formatting style” section, you can select various text formatting options like bold, italic, underline, or strikethrough. You can also choose different font or cell colors.
-
Click on the “Done” button to apply the conditional formatting.
And just like that, you’ve unleashed the power of conditional formatting to compare two columns! In our example, formatting has been applied to all cells where the sales value in column C is less than the target value in column D.
Now that you know how to master conditional formatting in Google Sheets, you’ll be able to analyze your data like a pro. But don’t stop here! If you want to dive even deeper into the world of Google Sheets optimization, make sure to check out Crawlan.com. They’re a treasure trove of knowledge when it comes to getting the most out of Google Sheets.
Happy formatting, my friends!
P.S. If you enjoyed this article, you might also love our pieces on conditional formatting in Google Sheets based on cell color or another sheet. And if you’re curious about automating email sending from Google Sheets, we’ve got you covered with our comprehensive guide.
