Have you ever needed to highlight specific cells in Google Sheets based on the values in other cells? Conditional formatting is the perfect solution for this! In this step-by-step guide, we will walk you through the process of applying conditional formatting based on another cell value in Google Sheets. Get ready to make your data visually appealing and informative!
Applying Conditional Formatting in Google Sheets
To apply conditional formatting based on another cell value, follow these simple steps:
- Go to the “Format” tab and select “Conditional formatting”.
- A conditional formatting menu will appear on the right-hand side.
- Select the “Single color” tab in the menu.
- Choose “Custom formula is…”.
- Enter the formula you want to apply to a selected range.
- Set the cell and font style that satisfies the formula.
Now, let’s walk through an example to better understand how this works. Imagine you are a business planner who wants to identify critical projects that generate a profit equal to or greater than a certain amount. Here’s a snapshot of the imaginary dataset:
To implement this criteria, you need to:
- Set the target range as C4:C18.
- Choose “Custom formula is…”.
- Enter the following formula in the text box:
=$C4>= $E$20. - Click “Done” at the bottom right.
Remember these key points:
- Make sure the range selection is correct because the color and style rule is applied to the selected range as a whole.
- Double-check the signs used in the formula to ensure they are correct.
- Ensure the formula is valid and entered correctly, starting with the “=” sign.
- Verify if the reference type (relative, partially absolute, or absolute) is appropriate.
In this example, you want to test each cell (from C4 to C18) in column C, so you need to lock the tested cell column by adding “$” next to “C” in the formula. As for the criteria in cell E20, which is always present regardless of the tested cell, make it an absolute reference by adding “$” to both the column and row indexes, like “$E$20”.
If you want to change the criteria from $100,000 to a different value, simply enter the value directly in cell E20. The color of the cells will change accordingly.
Once you know the number of projects generating a profit equal to or greater than $100,000, you realize that some projects are already finished or canceled. To highlight them with different colors, you also decide to color the entire row for completed or canceled items. Here’s the approach:
- Gray out projects with the status “Canceled”.
- Highlight projects in dark green if they have the status “Completed”.
- Highlight projects in light green if their profit is equal to or greater than $100,000.
The first rule:
- The target range should be the entire table.
- You want to perform a test for column E, so the tested cell should be a partially absolute reference, like $E4, in the custom formula.
- The complete formula should be “$E4=”Canceled”” to check whether a project’s status is “Canceled” or not.
- Finally, change the fill color to dark gray.
The second rule:
- The second rule is the same as the first rule, except for the custom formula and fill color.
- This time, the formula should be “$E4=”Completed”” to check if a selected cell is “Completed” or not.
The third rule:
- The third rule is similar to the approach you created for column C in the first example.
- However, since you want to color an entire row instead of a cell containing a tested value, the range should cover the entire list, just like the first and second rules.
After following these steps, you will have a highlighted list as shown above. The projects you need to add to a watchlist are projects E, K, and N.
Understanding the Priority of Conditional Formatting Rules
Note that the rules are prioritized in ascending order – the first rule in the conditional formatting list has the highest priority. In the approach mentioned above, the priority of the rules is 1 > 2 > 3. If you want to change the order of the rules, you can do so in the list by dragging and dropping a rule.
Now that you know how to apply conditional formatting based on another cell value in Google Sheets, you can organize and highlight your data more effectively. To learn more about the various features of Google Sheets, visit Crawlan.com.
Get ready to make your data visually appealing and informative with conditional formatting in Google Sheets!
