OmniDocConditional Formatting Based on Another Cell or Column
Learn how to write Excel conditional formatting rules that read a different cell or column, including multiple-value matches, with formula examples to copy.
Conditional formatting usually starts with the cell it colors: highlight any value above 500, or any date before today. Many spreadsheets need something different. You want a row's status column to decide whether the amount column turns red, or you want one cell to change color when it matches a value in a separate list. That is conditional formatting based on another cell or column, and it works the same way in Excel and in most web-based spreadsheets.
This guide is for anyone building an order tracker, a budget, a grade sheet or a checklist where the trigger and the highlighted cells are not the same. The short version is that you write a formula rule that points at the other cell. The rest of this article shows the formulas, the anchoring details that trip people up, and how to check the result before you rely on it.
If your sheet is an Excel (.xlsx) file and you also need its text in another language, OmniDoc can translate Excel files and keep the tables intact. The facts we have do not say whether OmniDoc's own spreadsheet editor, UniCell, supports conditional formatting, so build and test your rules in the program you plan to use.
How do you format a cell based on another cell? The short answer
Create a formula-based rule that compares the other cell to a value, and select the cells you want to color so that the formula refers to the first cell of that selection.
| Your situation | What to do | Why |
|---|---|---|
| Color one column using a status in another column | Use a formula such as =$B2="Late", applied to A2:A200 | The column reference stays fixed while the row moves down |
| Color a whole row using one column's value | Select A2:E200 and use =$D2>0 | Every cell in the row reads the same trigger column |
| Compare to a number stored in another cell | Use =$A2>$H$1, where H1 holds the threshold | Changing H1 updates every row without editing the rule |
| Match one of several accepted values | Use =OR($B2="Late",$B2="Missing") or a MATCH formula | A single rule covers more than one trigger value |
| Match against a list of values | Use =ISNUMBER(MATCH($B2,$H$1:$H$4,0)) | The list can change without rewriting the formula |
Before you start
- Know which cell is the trigger. Write down the column or cell that decides the color and the range that will be colored. Mixing them up is a common reason a rule seems to do nothing.
- Decide whether the trigger is text or a number. Text in a formula must sit in double quotes, as in "Late". Numbers do not. Mixing these up returns no match without any error message.
- Check for stray spaces. A value like "Late " with a trailing space will not equal "Late". Trim the source column first, or the rule will skip rows that look correct.
- Plan for a threshold cell if values change often. Putting a limit in its own cell, such as H1, lets you update the rule's result without opening the rule again.
- Pick a sample range. Use 10 to 20 rows to test before you apply the rule to a 2,000-row sheet. Errors are easier to spot in a short range.
Writing the rule step by step
- Select the range you want to color. For a status column in a 200-row sheet, that might be A2:A200. Start the selection at the first data row, not the header.
- Open the conditional formatting options and choose the formula-based rule. Look for the option that accepts a custom formula rather than the built-in comparisons like "greater than." The built-in comparisons cannot point at a different column.
- Enter a formula that refers to the first row of the selection. If your selection starts at A2 and the trigger is in column B, enter =$B2="Late". Write the formula as it applies to the top-left cell of the range, because the program adjusts it for every other cell.
- Anchor the column with a dollar sign and leave the row unanchored. The $ before B keeps the reference on column B, while the 2 lets the row number change as the rule moves down. Without the $, the reference shifts sideways when the rule applies across columns, which is a common source of wrong colors.
- Add more trigger values with OR or a list. For two or three values, write =OR($B2="Late",$B2="Missing"). For a longer list stored in H1:H4, write =ISNUMBER(MATCH($B2,$H$1:$H$4,0)), which returns TRUE when the value appears anywhere in that list. The $ signs on H1:H4 keep the list fixed as the rule copies down.
- Guard against blank trigger cells when needed. An empty cell can match a rule that checks for "not equal to" something. To skip blanks, combine the test with a check such as =AND($B2<>"",$B2="Late"). Use this when a row with no status should stay uncolored.
- Set the format and apply the rule. Choose a fill color, font color or both. Review the preview, then apply the rule to the full range. If you need the same logic on whole rows, repeat the steps with the full row range (A2:E200) and a formula that anchors only the trigger column.
Check the result
Once the rule is in place, look at the colored cells against the trigger values. Several quick checks catch most problems.
| Check | How to see it | If it's wrong |
|---|---|---|
| Top row is correct | Compare the first colored or uncolored row with its trigger value | Reopen the rule and confirm the formula refers to the first row of the selection, not the header |
| Column stays fixed | Scroll right or copy the formula to a different column and see if the reference moves | Add a $ before the trigger column letter |
| Text matches exactly | Find a row you expect to color that does not | Look for extra spaces or different capitalization, then trim the source data |
| Blank rows behave | Scroll to empty trigger cells and see whether they are colored | Add the blank-check from step 6 |
| Threshold updates | Change the value in the threshold cell and watch the colors change | Check that the rule uses the absolute reference, such as $H$1, rather than H1 |
| Rules don't conflict | Open the list of rules for the sheet and look for overlapping ranges | Adjust the order or the range so the intended rule wins |
Spot-check at least five rows from the top, middle and bottom of the sheet. If a formula works at the top and fails near the bottom, the reference is usually not anchored correctly.
❓ FAQ
1. Can the trigger cell be on a different sheet?
Yes, in most modern spreadsheet programs you can reference another sheet in a formula rule, but older versions sometimes restrict it. If the rule refuses the reference, copy the value you need onto the same sheet, or place the threshold on the sheet where the rule lives.
2. Why does my rule color cells that are empty?
An empty cell often equals nothing, and a "not equal to" test treats it as a match. Add an explicit blank check, such as =AND($B2<>"",$B2<>"Done"), so the rule only fires when the trigger has a value.
3. Does this work in a web-based spreadsheet?
Most web-based spreadsheets support formula-based rules with the same $ anchoring logic. The menu names and the list of functions can differ, so test your formula on a few rows before you rely on it.
4. Will the colors survive exporting or translating the file?
Not every formatting detail carries over every time a file is converted. After you export or translate a file, open it and check that the colors still appear on the rows you expect. The facts we have do not promise that conditional formatting rules survive every conversion.
📝 Summary
Conditional formatting based on another cell comes down to a formula rule that reads a trigger in a different column or cell, with the column anchored by a dollar sign. Use OR or MATCH when several values should trigger the same color, and test the first few rows before you apply the rule to the whole sheet.
