OmniDocHow to Use Conditional Formatting in Excel
Learn where Excel's Conditional Formatting tool lives, how to build a first rule step by step, and how to check the result before you share the workbook.
If you keep recoloring spreadsheet cells by hand every time a number changes, you are doing work a rule can do for you. Conditional formatting in Excel applies a color, font style, or icon only to cells that meet a condition you define. When the data changes, the formatting updates on its own. This is useful for sales reports, budgets, grade sheets, and inventory lists, where you want low stock or overdue invoices to stand out without reading every row.
This guide is for people who have used Excel for basic entries and sums but have not yet set up a rule. It walks through where the Conditional Formatting command sits, how to create a first rule, and how to check the result. It does not cover every rule type, since each one has its own use case. The goal is to get one working rule in place and understand the logic behind all of them.
You can also use OmniDoc's Excel-compatible tools to open and check the workbook. That step matters when you need to send the file to someone else or verify it on a different device.
How do you use conditional formatting in Excel? The short answer
Select the cells, open the Conditional Formatting command, choose a rule type, enter the condition and the format, then confirm. The rule stays attached to those cells and reapplies itself whenever their values change.
| Your situation | What to do | Why |
|---|---|---|
| You want to flag numbers above or below a limit | Use a highlight rule such as "greater than" or "less than" | It compares each cell to one value you choose |
| You want to see the top or bottom values in a list | Use a top or bottom rule | It ranks the values and formats the extremes |
| You want to spot duplicate entries | Use the duplicate values rule | It catches repeated names, IDs, or codes |
| You want a quick visual scale across many numbers | Use a color scale or data bar | It shows relative size without a fixed threshold |
| You need a custom condition, such as "overdue" | Use a formula-based rule | It applies any logic you can write as a formula |
Before you start
Check these things first. They prevent most of the confusion beginners run into.
- Confirm your values are numbers, not text. Numbers pasted in as text, often with a leading apostrophe or extra spaces, will not match a "greater than" rule. Right-aligned numbers are usually numeric; left-aligned ones may be text.
- Decide the exact range. Rules apply only to the cells you select. If you select B2:B13 and later add row 14, the new entry may not be covered until you extend the rule.
- Keep a copy of the original file. Formatting rules are easy to undo, but a backup protects you if you later change many rules at once.
- Know which program you are using. The command location and menu wording vary between Excel versions and between Excel and other spreadsheet apps. Look for a command named Conditional Formatting, usually on the Home tab near other styling tools.
- Plan the test values. Choose three or four cells whose results you already know, such as one above the threshold, one below, and one equal to it. You will use them to confirm the rule works.
Setting up your first rule in Excel, step by step
This example uses a column of monthly sales figures in B2 through B13 and highlights any month above 5,000.
- Select the cells. Click B2 and drag down to B13. Selecting the range first keeps the rule limited to the data you want to check.
- Open the Conditional Formatting command. On the Home tab, find Conditional Formatting in the styling group. Opening it shows the list of rule categories.
- Choose a highlight rule. Pick the option for values greater than a number. This compares each selected cell to a single threshold.
- Enter the threshold. Type 5000 in the value box. The rule will test each cell against this number.
- Choose a format. Select a light fill color or a font color. Pick something that stands out but still keeps the text readable.
- Confirm the rule. Click OK. Cells above 5,000 should change color right away.
- Review the rule later. To edit or remove it, reopen Conditional Formatting and choose the option for managing rules. This list shows every rule attached to the sheet and the range each one covers.
- Save the workbook as .xlsx. Saving in the standard Excel format keeps the rules and formats intact for the next person who opens the file.
Check the result
Once the colors appear, verify that the rule does what you intended. A rule that looks right can still cover the wrong range or use the wrong threshold.
| Check | How to see it | If it's wrong |
|---|---|---|
| The range is correct | Click the first and last highlighted cells, then look at the range shown in the rule manager | Edit the rule and extend or shorten the range |
| The threshold is correct | Compare a known value just above and just below 5,000 | Reopen the rule and fix the number |
| Text and numbers are treated the same | Look for numbers aligned to the left, which often indicates text | Convert the cells to numbers and reapply the rule |
| Multiple rules don't conflict | Check the rule list for overlapping ranges | Change the rule order or remove the duplicate |
| The file looks right elsewhere | Open a copy in OmniDoc's UniCell spreadsheet tool in the desktop client, or upload the .xlsx to omnidoc.top | If colors are missing, confirm them in Excel and share the original file instead |
For the last check, OmniDoc's UniCell spreadsheets in the desktop client let you open a workbook and review its values and layout on another machine. OmniDoc's public documentation does not describe how it displays conditional formatting, so treat it as a check on the data, then confirm the colors in the program you plan to use.
❓ FAQ
1. Can I use conditional formatting in Excel online?
Web-based spreadsheet apps support some form of conditional formatting, but the menus and rule types can differ from the desktop version. Open the file in the app you plan to use, then confirm that your rule appears and that the colors match your test values.
2. Does conditional formatting update automatically?
Yes. Once a rule is set, Excel re-evaluates it whenever a value in the range changes. You do not need to reapply it after editing numbers, though you should extend the range if you add rows below it.
3. How do I remove conditional formatting?
Select the affected cells, open Conditional Formatting, and choose the option to clear rules from the selected cells. Clearing rules this way removes only the formatting, so the values stay unchanged.
4. Can I apply a rule to a whole column?
You can, but it is usually better to select only the rows that contain data. Whole-column rules also check empty cells, which can produce highlights where you did not expect them. If you do apply a rule to a full column, test it on a few blank rows as well.
📝 Summary
Conditional formatting in Excel saves time by applying color only where your data meets a condition you set. Start with one highlight rule on a clearly defined range, then check the threshold and the result before you share the file. Once that rule works, the other rule types will be easier to learn.
