OmniDocCheck and Manage Conditional Formatting Rules in .xlsx Files
Learn how to open the rules list in a spreadsheet app, review each rule, reorder them and set stop if true on existing conditional formatting rules.
Conditional formatting rules often pile up in a workbook. Someone adds a red fill for negative numbers, another person adds a bold rule for overdue dates, and a year later the colors on a sheet no longer make sense. If you are trying to figure out why a cell looks the way it does, or you need to change which rule takes priority, you have to see the full list of rules first. This article shows you how to check conditional formatting rules in an .xlsx file, reorder them and set stop if true on rules that already exist.
The short version: open the rules list for the sheet, review each rule, reorder the ones that conflict and set stop if true on the rules that should be final for their cells. You do not need to create anything new. You are auditing what is already there.
Because the steps depend on the spreadsheet app you use, this guide describes the general operations rather than exact menu paths. If you need to translate or convert the workbook afterward, do the review first so you know what the file contains.
How to check conditional formatting rules in an xlsx file? The short answer
Open the rules list for the sheet, read every rule, reorder the conflicting ones and set stop if true only where a rule should be the last word.
| Your situation | What to do | Why |
|---|---|---|
| A cell shows a color you cannot explain | Open the rules list with the scope set to the whole sheet | Rules from other ranges can still touch the cell |
| Two rules both match one cell | Compare their positions in the list and move the rule that should win to the top | Higher rules take priority when they conflict |
| A lower rule should never apply where a higher one matches | Turn on stop if true for the higher rule | Lower rules are skipped for cells that the higher rule matches |
| You inherited a file with dozens of rules | Note each rule's range and condition in a separate sheet before editing | You get a record to compare against after changes |
| You plan to translate the workbook | Review rules in the original file first | You know which colors and conditions the translated copy should keep |
Before you start
- Save a copy of the workbook. Reordering rules or changing stop if true takes effect right away, and undoing many changes at once is tedious. A copy lets you compare before and after.
- Pick the scope you want to review. A rules list for only the selected cells hides rules that apply elsewhere on the sheet. Reviewing the whole sheet gives you the complete picture.
- Know what the file is for. A report that people only read on screen might need a different priority order than a sheet that gets printed or exported to a PDF. Decide which outcome matters before you move rules around.
- Have a few test values ready. Keep a handful of numbers or dates that should trigger each rule, such as a negative value, a blank cell and a date from last week. You will use them to confirm the result.
- Check translated text if you are localizing. Rule conditions that use text, like "Overdue" or "Pending", may need to match the labels in the translated copy. Verify those labels after translating, since machine translation can change wording.
Reviewing and reordering rules, step by step
- Open the workbook and select the sheet. Go to the sheet with the colors you want to check. You do not need to select any cells yet.
- Find the conditional formatting command. It usually sits in the formatting area of the ribbon. Open the option that lists existing rules, which many apps call the rules manager.
- Set the scope to the whole sheet. If the list only shows rules for your current selection, change the scope so that every rule on the sheet appears. This is the step most people skip, and it is why rules seem to "disappear."
- Read each rule's three parts. For every rule, note the condition (such as greater than 0 or contains a word), the format it applies (fill, font color or bold) and the Applies to range, which is the set of cells the rule covers.
- Reorder the rules by priority. Rules higher in the list win when two rules match the same cell and set the same property. Select a rule and use the move controls to put the one that should win at the top.
- Turn on stop if true for final rules. Open a rule's settings and check the stop-if-true box, which many apps label Stop If True. For cells this rule matches, rules below it in the list are not applied.
- Apply the changes and test them. Confirm the changes, then enter your test values in a few cells. Watch whether each cell shows the color you expect.
Check the result
After you reorder rules or change stop if true, check the sheet with your test values and the original data.
| Check | How to see it | If it's wrong |
|---|---|---|
| Every rule appears in the list | Reopen the rules list with the whole-sheet scope | Look for rules on other sheets or hidden ranges and repeat the review there |
| Each cell shows the intended color | Enter your test values and compare them with the rule conditions | Move the winning rule higher, or check the condition for a typo |
| Lower rules are skipped where expected | Find a cell that a top rule matches and confirm no lower format appears | Recheck that stop if true is on the right rule, not a neighbor |
| Ranges cover the right cells | Read each Applies to range in the rule settings | Edit the range so it covers the rows and columns you meant |
| Labels still match after translation | Compare text conditions in the translated copy with the originals | Update the condition text so it matches the translated labels |
❓ FAQ
1. What happens if two rules match the same cell with stop if true off?
Both rules are evaluated in list order. If they set different properties, such as one sets fill and the other sets bold, the cell can show both. If they set the same property, the higher rule's format wins.
2. Why doesn't a rule show up when I check only the selected cells?
The rules list is filtered by the scope you choose. Changing the scope to the whole sheet shows rules that apply to other ranges, which often explains colors you could not trace.
3. Can I review rules in a file someone else sent me?
Yes. The review steps work the same way for any .xlsx file you can open. Save a copy first so you do not change the sender's original.
4. Does OmniDoc keep conditional formatting when it translates a spreadsheet?
OmniDoc's translation covers .xlsx files and keeps tables and formulas. The product details do not say how conditional rules are handled, so check the translated copy with the same test values you used on the original.
📝 Summary
Checking conditional formatting comes down to seeing every rule on the sheet, reading its condition and range, and setting the order and stop-if-true behavior you want. Save a copy first, then confirm the result with test values.
