Need to format cells based on another cell’s value, like turning a whole row red when the stock count in column C drops below 10? Excel handles this with a conditional formatting rule built on a formula. The setup takes about a minute, and the formatting updates on its own whenever the data changes.
Key Takeaways
- Use Conditional Formatting with the ‘Use a formula to determine which cells to format’ rule type.
- Select the full range to format first, then write the formula for the top-left cell of that range.
- Lock the column with a dollar sign (for example $C2) so every cell in the row checks the same column.
- Leave the row number unlocked so each row checks its own value.
- Edit or remove the rule later from Manage Rules.
Step-by-Step Guide to Format Cells Based on Another Cell’s Value
The example below uses a small inventory list in A1:E20. Row 1 holds headers, and column C holds the stock count. The goal is to highlight each entire row in which the stock is below 10. The same method works for any comparison, such as greater than, equal to, or not equal to.
- Step 1: Select the cells to format. Click A2 and drag to E20 so the whole data area (without the header row) is selected. Make sure A2 is the active cell, which is the one shown in the Name Box at the top left.
- Step 2: Go to the Home tab, click Conditional Formatting in the Styles group, and choose New Rule.
- Step 3: In the New Formatting Rule dialog box, select ‘Use a formula to determine which cells to format’ from the rule type list.
- Step 4: Click into the ‘Format values where this formula is true’ box and type the formula =$C2<10 (the dollar sign locks the column while the row number stays flexible). The formula is written for A2, the top-left cell of the selection, and Excel adjusts it automatically for every other cell.
- Step 5: Click the Format button. On the Fill tab, pick a background color such as light red. Use the Font tab as well if the text should change color or turn bold, then click OK.
- Step 6: Check the Preview area in the dialog box, then click OK. Every row with a stock value below 10 now shows the chosen formatting.

Tip: Type the formula in the box rather than clicking cells while the box is active. Clicking a cell inserts an absolute reference like $C$2, which breaks the row-by-row behavior. If a reference does get inserted, delete the dollar sign in front of the row number.
Format Based on a Value Typed in Another Cell
Sometimes the condition lives in a cell rather than inside the formula. Say F1 holds a target stock level. Use the formula =$C2<$F$1 in Step 4. The reference to F1 is fully locked because every row should compare against that same cell. Changing the number in F1 then reformats the whole table instantly.

Format Only One Cell Instead of the Whole Row
To color just the Stock cell, select only C2:C20 in Step 1 and use =$C2<10 or simply =C2<10. The formula logic stays the same. Only the selected range changes.
Edit or Delete the Rule Later
Select any cell in the formatted range, go to Home, click Conditional Formatting, and choose Manage Rules. Select the rule, click Edit Rule to change the formula or color, or click Delete Rule to remove it. The ‘Applies to’ column shows exactly which cells the rule covers.
Tip: Switch the ‘Show formatting rules for’ dropdown at the top of Manage Rules to This Worksheet. That reveals every rule on the sheet, which helps when formatting looks wrong and the cause is an older rule.
Common Problems and Fixes
The Wrong Rows Are Highlighted
This almost always means the formula was written for a different cell than the active one. If the selection starts at A2, the formula must reference row 2. If it points at row 3, every row ends up checking the row below it. Open Manage Rules, edit the rule, and correct the row number.
Everything or Nothing Gets Formatted
When both the column and the row are locked, as in $C$2, every cell checks the same single cell. The result is that the whole range formats at once or not at all. Change $C$2 to $C2 to let the row number move.
Only the Column C Cells Change, Not the Whole Row
The rule’s ‘Applies to’ range is too small. Open Manage Rules, click the Applies to box, and enter the full range, for example =$A$2:$E$20.
Numbers Are Not Triggering the Rule
Values stored as text do not compare like numbers. A small green triangle in the corner of the cell is the usual sign. Select the cells, click the warning icon, and choose Convert to Number. The formatting should then respond right away.
New Rows Added Below the Data Are Not Formatted
The rule only covers the range it was created for. Extend it by editing the Applies to range in Manage Rules, or convert the data to a table with Ctrl+T so new rows inherit the formatting.
Excel Shows an Error When Clicking OK
A formula typed without the starting equals sign is read as plain text and rejected. Make sure the formula begins with = and that every bracket and reference is complete.
Two Rules Fight Over the Same Cells
When several rules apply to the same cells, the one higher in the Manage Rules list wins any conflict. Select a rule and use the up and down arrows to change the order.
Frequently Asked Questions
Can formatting be applied to a cell based on the value in a different cell?
Yes. Use Conditional Formatting with the ‘Use a formula to determine which cells to format’ rule type, and point the formula at the cell that holds the condition, such as =$C2<10.
Why is the dollar sign needed in the formula?
The dollar sign locks the column so that every cell in the selected range keeps checking column C. Without it, the reference shifts sideways as Excel moves across the columns.
How can an entire row be highlighted based on one cell?
Select the full data range, create a formula rule that locks the column but not the row, such as =$C2<10, and choose a fill color. Every cell in a matching row receives the formatting.
Does the formatting update automatically when the data changes?
Yes. Conditional formatting recalculates every time a value changes, so the colors always match the current data without any manual work.
How are multiple conditions combined in one rule?
Wrap the tests in the AND or OR function. For example, =AND($C2<10,$D2>0) formats a row only when both tests are true, while =OR($C2<10,$D2>0) formats it when either one is true.
How is a rule removed without clearing the data?
Select the range, go to Home, click Conditional Formatting, choose Clear Rules, and then pick Clear Rules from Selected Cells or Clear Rules from Entire Sheet. The cell values stay untouched.
Why does the rule not work on a cell containing text?
Text comparisons need the right formula. A reliable method is to type the target text in a helper cell, for example F1, and use =$B2=$F$1. Text comparisons in Excel are not case sensitive.
Can the same rule be copied to another range?
Yes. Copy a formatted cell, select the new range, and use Paste Special with the Formats option. The rule is copied along with the formatting, and its formula references adjust to the new location.