OmniDocConditional Formatting With Formulas in Excel
Learn how to write Excel conditional formatting formulas, anchor cell references with $, and read TRUE/FALSE logic so your highlights land on the right cells.
Highlighting cells that meet a condition is one of the fastest ways to spot overdue invoices, duplicate IDs or budget overruns in a spreadsheet. The built-in rule options cover simple cases, like "greater than 100" or "contains text." They fall short when the condition depends on another column, a date or a lookup. That is where a custom formula rule helps, and learning how to write Excel conditional formatting formulas opens up that flexibility.
This guide is for anyone who has a workbook with a few hundred rows and wants the right cells to light up without manual color changes. You will learn how the formula is evaluated, how to anchor references so the rule copies correctly down a column, and how to read the TRUE/FALSE result when something looks off. The examples use a simple invoice list, so you can reproduce each one in a few minutes.
How do you write an Excel conditional formatting formula? The short answer
Write a formula that returns TRUE when a cell should be formatted and FALSE when it should not, then point it at the top-left cell of the range you selected.
| Your situation | What to do | Why |
|---|---|---|
| Highlight a whole row based on one column | Lock the column with $ (for example, $C2) and select the full row range | The rule checks the same column on every row while the row moves down |
| Compare two columns in the same row | Write a comparison such as =$D2>$E2 | A TRUE result means the row meets the condition |
| Flag duplicates in a list | Use COUNTIF with an absolute range, such as =COUNTIF($A$2:$A$200,A2)>1 | The range stays fixed while each row checks its own value |
| Highlight dates in the past | Check that the cell is not blank and that TODAY() is later than the date, such as =AND(LEN($B2)>0,TODAY()>$B2) | Blank cells are excluded and the check updates each day |
| Highlight weekend dates | Use WEEKDAY, such as =WEEKDAY($B2,2)>5 | Returns TRUE for Saturday and Sunday when the week starts on Monday |
Before you start
- Confirm your data types. Dates must be real dates and numbers must be numeric values, not text. A date stored as text will fail a "less than TODAY()" check without any error message, so the rule simply never fires.
- Decide the range first. Know which rows and columns the rule should cover, such as A2 through F200. A rule applied to the wrong range highlights the wrong cells even when the formula is correct.
- Keep a header row out of the range. Starting at row 2 prevents the rule from testing column titles, which usually produces TRUE or FALSE results that don't match your data.
- Save a copy of the file. Conditional formatting is easy to edit but can be hard to untangle when several rules overlap. A copy lets you compare the original and the changed version.
- Prepare two or three test rows. Include one row that should be highlighted, one that shouldn't, and one edge case such as a blank cell. Testing on these rows shows whether the logic works before you apply it everywhere.
Writing your first custom formula rule, step by step
- Select the full range you want to format. For a row highlight, select A2 through the last column of your data, starting at row 2 so the header stays out.
- Open the conditional formatting options and choose the option that uses a formula. This lets you enter your own logic instead of picking a preset condition.
- Type a formula that starts from the top-left cell of the selection. For example, to highlight a row when the Status column says "Overdue," enter =$C2="Overdue". The $ locks column C, so every row checks its own status.
- Set the format for matching cells. Choose a fill color, font color or both. Keep the style simple, since a bright fill on many rows can make the sheet harder to read.
- Apply the rule and check the first few rows. Confirm that rows with "Overdue" in column C are highlighted and rows with other statuses are not. If the highlight appears on the wrong row, the anchor or starting cell is the likely cause.
- Add a second rule only after the first one works. Build rules one at a time so you can tell which rule produces which highlight when several apply to the same cells.
- Set the rule order if two rules conflict. When two rules format the same property, such as the fill color, the rule higher in the list usually wins. Reorder them if the wrong color shows up.
- Save the workbook and test it with a changed value. Change a status from "Open" to "Overdue" and confirm the row updates. This proves the rule reacts to live data rather than to a static snapshot.
Reading TRUE and FALSE logic
The formula is evaluated for each cell in the range, and the result decides the format. A TRUE result applies the format; a FALSE result leaves the cell alone. If you can read the result in your head for a sample row, you can debug almost any rule.
Comparisons such as =$D2>$E2 already return TRUE or FALSE, so wrapping them in IF adds nothing. =IF($C2="Paid",TRUE,FALSE) gives the same answer as =$C2="Paid". Use IF only when you need a different result in each branch, which conditional formatting rarely requires.
Blank cells often produce surprises. An expression like =TODAY()>$B2 returns TRUE for a blank cell, because a blank counts as zero and zero is less than today's date. Add a check for an empty value, such as =AND(LEN($B2)>0,TODAY()>$B2), to keep blanks out of the highlight.
Check the result
| Check | How to see it | If it's wrong |
|---|---|---|
| The highlight lands on the right rows | Compare five highlighted rows with the source values | Look for a missing $ or a starting cell that doesn't match the top-left of the range |
| Blank cells are excluded | Find a blank row in the range and confirm it has no color | Add a non-blank test, such as LEN($B2)>0 |
| Dates are compared as dates | Enter a known past date and a future date in test rows | Convert text dates to real dates before writing the rule |
| The rule updates with new data | Change one value and watch the highlight change | Check that the range covers the rows you are editing |
| Conflicting rules show the expected color | Review each rule's color and the order in the list | Move the rule you want to win higher in the list |
If you need the workbook in another language, you can upload the .xlsx file on omnidoc.top and choose bilingual or translation-only output. OmniDoc's translation keeps tables and formulas as described in its product notes, but conditional formatting rules are a separate layer of the file. After you download the translated copy, run the same checks in the table above before you share it.
❓ FAQ
1. Can a conditional formatting formula refer to another sheet?
Newer versions of the spreadsheet app accept references to other sheets in these formulas. If your version rejects the reference, copy the value you need onto the same sheet and point the rule at that cell.
2. Why does my rule highlight the wrong rows?
The usual cause is a reference that is not anchored. If the formula reads =C2="Overdue" and you want the column locked, change it to =$C2="Overdue". Also confirm that the starting cell in your formula matches the top-left cell of the selected range.
3. Why doesn't my rule work on dates?
The cells are probably stored as text, so the comparison with TODAY() never returns TRUE. Re-enter the values as dates or convert the column, then test again.
4. Does the translated copy keep my highlight rules?
OmniDoc's translation keeps tables and formulas as described in its product notes, but the facts do not cover conditional formatting rules specifically. Open the translated file and check at least five highlighted rows against the original.
📝 Summary
Writing an Excel conditional formatting formula comes down to one question: does this cell meet the condition, yes or no? Anchor your references with $, start the formula at the top-left cell of the range, and read the TRUE or FALSE result on a few sample rows before you apply it everywhere. With those checks in place, custom highlights stay accurate as your data grows.
