OmniDocIcon Sets in Excel Conditional Formatting
Learn how to use Excel conditional formatting icon sets, pick arrows or traffic lights, and set thresholds so the icons show what your numbers mean.
If you keep a sales tracker, a project status sheet or a budget in Excel, you have probably wanted a quick visual cue next to each number. Icon sets do this. They place a small arrow, traffic light or shape in each cell, chosen by the cell's value, so a reader can see at a glance which rows are on track and which need attention. This guide is for people who already have data in a spreadsheet and want to use Excel conditional formatting icon sets without guessing at the thresholds.
The short answer is to select your numbers, apply an icon set from the conditional formatting options, and then set the cutoff values yourself. The icon style should match the meaning of the data, and the thresholds should match the targets your team uses. Once the icons appear, you should check the cells that sit right on a cutoff, because those are where mistakes usually show up.
Icon sets are a separate visual layer from fill colours. A fill colour changes the background of a cell, while an icon sits inside it and can stay readable on a printed page. You can use both, but many readers find icons easier to interpret when the cell background stays plain.
How to use Excel conditional formatting icon sets? The short answer
Use an icon set when the meaning of a number can be sorted into a few bands, and set the thresholds from your own targets rather than from the defaults.
| Your situation | What to do | Why |
|---|---|---|
| Sales or progress against a target | Use arrows or a 3-icon set, with thresholds at your target and a warning level below it | Arrows show direction, so readers see whether a figure is rising or falling |
| Status of tasks or risks | Use traffic lights, with green, yellow and red | Colours and shapes map to a clear action, such as continue, watch or escalate |
| Lower values are better | Reverse the icon order | Otherwise a low error rate would show the red icon |
| Numbers must stay visible in a printed report | Choose the option that shows the icon only, or keep the numbers and use a small icon style | Hiding values can help a dashboard, but a report often needs both |
| Several columns share one scale | Apply the same icon set and thresholds to each column | Readers compare columns more reliably when the rules match |
Before you start
Check these points before you apply any icons. Each one prevents a common problem that makes the result hard to trust.
- Confirm the column is numeric. Icon sets read numbers, dates and percentages. If a value was typed as text, such as "42 units" with a unit label, Excel may not evaluate it, and the cell will show no icon. Remove the text and keep the unit in the header.
- Decide what good and bad mean. Write down the target and the warning level before you open the formatting options. Thresholds set from memory often disagree with the team's actual goals.
- Check the direction. Ask whether a higher value is better. A rising error count, cost overrun or number of days late means the usual arrow or green-light order is backwards.
- Keep a copy of the original data. Icon sets do not change your values, but you may want to compare later. Save a version before large edits.
- Look for hidden rows or filters. If a filter hides rows, the icons on the visible cells still follow the full range you selected. Make sure your selection covers every row you intend to color-code.
Adding and customizing an icon set in Excel, step by step
The steps below use general Excel operations. Menu names can change between versions, so look for the conditional formatting options in your toolbar.
- Select the cells you want to evaluate. Include only the numeric values in one column or range. If you include headers or totals, the icons may be calculated on the wrong numbers.
- Open the conditional formatting options and choose icon sets. Browse the available families, such as directional arrows, traffic lights, shapes, indicators and ratings. Pick the family that matches the meaning of your data.
- Apply a default style to preview the result. Excel will place icons using its automatic thresholds. This gives you a quick picture before you change anything.
- Open the rule settings for the icon set. Here you can change each threshold, the value type (number, percent or formula) and the icon for each band.
- Set your thresholds. Enter the cutoffs from your targets. For example, a sales tracker might use a green light at 90 percent of target and a yellow light at 70 percent, with red below that. Choose percent when the thresholds should follow the range of the data, and number when they should stay fixed.
- Reverse the icon order if lower values are better. This flips which icon represents the strong and weak bands, so that a low error count gets the good icon.
- Decide whether to show the icon only. Hiding the numbers makes a dashboard cleaner, but it also makes the exact values harder to read. Keep the numbers when the report needs exact figures.
If you are working with a spreadsheet that came from a PDF, you can use the PDF editor in the OmniDoc desktop client to export the table to Excel first, then apply the icon sets in your spreadsheet app. OmniDoc's spreadsheet tools are a separate part of the product, so the conditional formatting steps above happen in Excel.
Check the result
Once the icons appear, compare a few cells against the rules you wrote down. Most problems are visible on the boundaries, so check those first.
| Check | How to see it | If it's wrong |
|---|---|---|
| Edge values | Find one value exactly equal to each threshold and see which icon it gets | Excel usually includes the lower bound in the higher band. If your team uses a different rule, change the operator or nudge the threshold |
| Text-stored numbers | Look for cells with no icon in a column that should have one | Convert the text to a number and reapply the rule |
| Reversed direction | Check one clearly good and one clearly bad row | Turn on the reversed icon order |
| Mixed ranges | Confirm the selection covers the rows you want and no totals | Clear the rule and reselect the correct range |
| Printed output | Print or export a preview and check that icons are legible | Choose a larger icon style or keep the numbers visible |
If you translate a workbook into another language, the icons stay in place, but any labels and column headers change. Check that the translated headers still match the thresholds you set. Machine translation can shift a term, so check important headers and notes against the source before you share the file.
🎯 Which approach fits you?
Your choice depends on who reads the sheet and what they decide from it.
- Managers scanning many rows usually benefit from traffic lights or a simple 3-icon set, with the numbers kept visible for follow-up.
- Readers tracking direction over time do better with arrows, because the shape shows whether a figure rose or fell since the last update.
- Teams sharing a printed report often need icons that stay readable in black and white, so a shape-based set can work better than a colour-only set.
❓ FAQ
1. Why don't my icons show up on some cells?
A common cause is that the cell holds text rather than a number. Other causes are a selection that doesn't include the cell, or a value that falls outside the thresholds you set. Convert the text, widen the range and check the rule settings.
2. Can I use icon sets and fill colours in the same column?
Yes. Icons and fills are separate rules, so you can apply both. Keep the colour meaning consistent with the icon meaning, or readers may get mixed signals.
3. How do I handle a value that equals a threshold exactly?
Decide which band the boundary belongs to, then write that rule into your team's notes. Excel's default usually places an exact match in the higher band, so change the operator if your rule is different.
4. Will icon sets stay in place if I export the file?
Icons generally stay with the spreadsheet when you save or export in a standard format. Check the exported file on a second device, because some export paths may change how the icons look.
📝 Summary
Icon sets turn a column of numbers into a status view that readers can scan quickly. Pick a style that matches the meaning of your data, set thresholds from your real targets, and check the boundary cases before you share the sheet. Good thresholds do more work than any particular icon.
