OmniDocConditional Formatting by Cell Value, Text or Number
Learn how to highlight Excel cells by value, text, number type or not-equal-to rules, with formulas and checks that catch common mistakes.
Conditional formatting by cell value is a practical way to make a spreadsheet easier to read. Overdue invoices, scores below a cutoff, or rows that still say "Pending" can stand out without you sorting or filtering anything. The catch is that the four rules people search for often, cell value, text contains, is number and not equal to, each behave a little differently, and a rule that looks right can quietly miss or over-match cells.
This guide is for anyone who keeps data in a spreadsheet and wants to highlight cells based on what they contain. You will set up each of the four rule types, check the results, and fix the usual problems. The examples use a 30-row sales list with a status column and an amount column, but the steps apply to any sheet.
If your table lives inside a PDF, you will need to move it into a spreadsheet first. The PDF editor in the OmniDoc desktop client can export content to Excel format, and you can download the client from download.omnidoc.top. OmniDoc does not add conditional formatting itself, so the rules described below are set in your spreadsheet app after the export.
How do you set conditional formatting based on cell value? The short answer
Select the cells you want to check, create a rule that compares each cell to a number, a word or a formula, and choose the fill or font style to apply when the rule is true.
| Your situation | What to do | Why |
|---|---|---|
| Numbers above or below a cutoff | Use a cell value rule with greater than, less than or between | It is a direct comparison and needs no formula |
| Cells that contain a word or code | Use a text contains rule, or a formula with SEARCH | It matches part of the text, not only the whole cell |
| Cells that must hold a number | Use a formula rule with ISNUMBER | It catches text entries and blanks that look like numbers |
| Everything except one value | Use a not equal to rule, adding a blank check | It flags every other entry, including empty cells unless you exclude them |
Before you start
- Confirm the values are real numbers. A number pasted from a PDF can carry hidden spaces or be stored as text, so it will look right but fail a number check. Select the cell and look at the formula bar: a stored number usually aligns right in the cell by default, and text usually aligns left.
- Select only the data cells. Leave out the header row. If you include it, the rule will treat the column title as data, and formula references will be off by one row.
- Save an unformatted copy. Conditional formatting is easy to remove, but a copy lets you compare results and roll back a bad rule without guessing what you changed.
- Pick a highlight that survives printing. A pale fill may disappear on a black-and-white printout. A bold font or a darker fill is safer if the sheet will be printed.
- Decide what counts as blank. Some cells look empty but hold an empty string from a formula or a paste. Knowing this now saves you from a confusing result later.
Applying a cell value or formula rule, step by step
- Open the workbook in your spreadsheet app and select the data cells, starting with the first value below the header row.
- Open the conditional formatting feature and choose the option to create a new rule.
- For a cell value rule, pick the comparison you need, such as greater than, less than or between, and enter the threshold. This suits amounts or scores.
- For a text contains rule, enter the word or code to look for. The rule matches it anywhere inside the cell, so "Overdue 30 days" matches "Overdue".
- For an is-number check, create a formula rule and enter =ISNUMBER(A2), where A2 is the top-left cell of your selection. Relative references shift down the column automatically.
- For a not-equal check, create a formula rule with =AND(A2<>"", A2<>"Paid"). The first part is the not-equal test, and the second part skips blank cells.
- Choose the highlight style, then confirm in the rule manager that the range covers your selection and the formula points at the first data row.
Check the result
Once the rules are in place, scan the sheet against the conditions you wrote. These checks catch many of the problems people run into.
| Check | How to see it | If it's wrong |
|---|---|---|
| Highlighted cells match the rule | Spot-check five highlighted cells and five unhighlighted cells against the condition | Confirm the range and the relative reference in the formula, and that the first row is a data row |
| Text rule catches variations | Test a cell that contains the word in a different case or with extra words around it | Use SEARCH, which ignores case, rather than an exact match |
| Number rule skips text | Look for numbers that did not light up in a column that should be numeric | Convert the values to real numbers, or clean stray spaces, then reapply the rule |
| Not-equal rule ignores blanks | Find empty rows in the range and check whether they are highlighted | Add the blank check to the formula, as shown in step 6 |
| Dates behave as expected | Check a date cell against the number rule | Dates are stored as numbers, so ISNUMBER returns true for them too |
If a result still looks wrong after these fixes, check whether another rule on the same range is overriding the one you just added. Many spreadsheet apps show the full list of rules on a range, and removing duplicates usually settles it.
📊 At a glance
| Rule type | Good fit for | Typical formula or setting | Watch for |
|---|---|---|---|
| Cell value | Thresholds on amounts or scores | Greater than, less than, between | Works only on the values as entered |
| Text contains | Words or codes inside a longer entry | SEARCH inside a formula | Partial matches can catch unintended text |
| Is number | Finding text in a column meant for numbers | ISNUMBER | Dates and some codes count as numbers |
| Not equal to | Flagging everything except one status | <> with a blank check | Blank cells match unless excluded |
❓ FAQ
1. Why isn't my number cell highlighting when the rule should match it?
The value is most likely stored as text, often from a paste or an export. Clean the cells, such as by removing stray spaces, or convert them to numbers, then check the rule again.
2. Does a text contains rule care about capital letters?
A rule built with SEARCH ignores case, so "overdue" and "OVERDUE" both match. FIND is case-sensitive if you need that behavior.
3. How do I highlight only the blank cells in a range?
Use a formula rule that checks for an empty cell, such as =A2="", applied to the same range. Blanks that contain an empty string from a formula may need a different check, so test one before applying it to the whole column.
4. Can I set conditional formatting directly in OmniDoc?
No. OmniDoc does not add conditional formatting. Its PDF editor can export a table to Excel format, and you then apply the rules in your spreadsheet app.
📝 Summary
Cell value rules handle thresholds, text rules find words, ISNUMBER checks for real numbers, and not-equal rules need a blank check to behave. Start your selection below the header, test a few cells against each condition, and fix stored-as-text values before you trust the highlights.
