Changing a cell color based on another cell value in Google Sheets is one of the most practical ways to turn a basic data table into a dynamic visual dashboard. This technique, driven by conditional formatting rules, allows you to highlight key metrics, flag important statuses, and draw attention to critical information without lifting a finger. Instead of manually scanning rows of numbers, you can configure your sheet to instantly signal when values hit a target, exceed a limit, or fall below an acceptable range.
Understanding the Core Concept: Conditional Formatting
The entire process relies on Google Sheets’ conditional formatting engine, which evaluates your data against rules you define. When the engine detects that a cell, range, or formula result matches your conditions, it applies a specific format, such as a background color or text style. To change a cell’s color based on another cell’s value, you create a rule that uses a custom formula rather than basic text or number comparisons. This formula typically links the formatting to a source cell and instructs the sheet when to trigger the color change.
Setting Up Your Data Structure
Before applying any rules, it helps to have a clear layout. You might have a status column that reflects task completion, inventory levels, or sales performance, and you want a separate indicator column to light up based on those values. For example, cell B2 might contain a numerical score, and you want cell C2 to turn green if the score is "Pass" or red if it is "Fail". Keeping your source data and your visual indicators in adjacent columns makes it easier to manage and audit the rules later.
Step-by-Step: Creating the Rule with a Custom Formula
To begin, select the cell or range you want to color, then open the Format menu and choose "Conditional formatting". In the sidebar that appears, under "Format rules", select "Custom formula is". This is where the magic happens, because you will write a formula that returns TRUE or FALSE based on the value of another cell. A simple example is =$B2="Complete", which checks whether cell B in the same row contains the word "Complete". If it does, the formatting you set, such as a green background, is applied automatically.
Using Numerical Thresholds for Color Gradients
For numerical data, you can create more nuanced rules to show gradual changes. You might use one rule to highlight values above 90 in blue, another to flag values below 50 in orange, and a third for anything in between. When building these formulas, it is important to anchor your references correctly. Using =$B2>90 ensures that the rule always looks at column B, even if you paste the formatting into columns C, D, or beyond. You can stack multiple rules in order of priority, and Google Sheets will apply them from top to bottom in the list.
Advanced Techniques and Best Practices
As your sheets grow more complex, you can combine functions like AND, OR, and IF to handle multi-condition logic. For instance, you might color a row green only when status is "Complete" and the due date has passed, using a formula such as =AND($B2="Complete", $C2
Troubleshooting Common Issues
Sometimes the colors do not appear as expected, often due to relative references or overlapping rules. If your formula only works in the first row, you may need to lock the column with "$" while leaving the row relative, so the rule scales correctly. When multiple rules apply to the same range, check the order and stop if true settings, as earlier rules can block later ones. Verifying that your source data matches the expected text case or number format is another quick way to resolve unexpected results.
