Excel Conditional Formatting: Duplicate, Top, and Icon Rules
Excel conditional formatting can highlight duplicate or unique values, emphasize top or bottom performers, and display icon sets that show relative values. Choose a built-in rule from the Conditional Formatting menu, select the range and options, and Excel formats cells that meet the rule without changing their underlying values.
What These Conditional Formatting Rules Do
Conditional formatting changes a cell’s appearance when its value meets a specified condition. The three rule groups in this question serve different purposes:
- Duplicate or unique values: Marks repeated entries or entries that appear only once within the selected range.
- Top or bottom rules: Highlights values near either end of a ranked group, such as the top 10 numbers or the bottom 10 percent.
- Icon sets: Adds symbols such as arrows, traffic lights, or ratings to communicate how values compare with thresholds.
These are visual cues, not data-cleaning operations. A duplicate rule does not remove repeated rows, a top rule does not sort the data, and an icon set does not replace the values in cells.
How to Apply a Rule
In desktop Excel, select the cells you want to evaluate, then open Home > Conditional Formatting. Choose a rule from the relevant menu, set its options, and confirm. The available choices and dialogs can differ somewhat between Excel versions and platforms, but the main rule categories are similar.
- Select the target range, including the data cells but usually not the header row.
- Open Home > Conditional Formatting.
- Choose Highlight Cells Rules > Duplicate Values for repeated or unique entries, or select a command under Top/Bottom Rules.
- For icons, choose Icon Sets and a set that fits the data.
- Review the preview and options, then apply the rule. If the result is unexpected, check which cells the rule applies to in Manage Rules.
For example, selecting B2:B100 and choosing Duplicate Values highlights entries that occur more than once in that range. Selecting C2:C100 and applying a top rule ranks the numbers in that selected range. If the list grows later, check that the rule’s “Applies to” range includes the new rows.
Duplicates and Unique Values
The Duplicate Values dialog lets you choose between Duplicate and Unique, then select a formatting style. “Duplicate” means a value occurs more than once in the selected range; it does not necessarily mean every occurrence after the first is marked. Excel generally highlights all cells in the range whose value is repeated.
The comparison is based on the selected cells, so selection boundaries matter. If the same customer ID appears in two different columns but only one column is selected, the rule will not compare those columns as one combined list. Likewise, if a header is included, it can be evaluated as a value.
- Use a duplicate rule to spot repeated invoice numbers or product codes.
- Use a unique rule to find one-off entries in a list.
- For duplicates based on multiple fields—such as matching both a date and an account—use a helper column or a formula-based conditional-formatting rule. A simple duplicate rule checks the cell values, not a custom combination of columns.
Formatting does not resolve duplicates. To delete or consolidate records, use an appropriate data-cleaning method and verify which records should remain.
Top and Bottom Rules
Top/Bottom Rules can highlight a fixed number of items, a percentage of items, values above or below the average, and related extremes. Common built-in choices include Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, and Below Average. The “10” is a starting setting, not a fixed requirement: the rule dialog lets you change the number or percentage.
For instance, a top-10-items rule applied to a range of 80 test scores highlights the ten highest values. A top-10-percent rule highlights roughly the highest tenth of the values in the range. Ties at the cutoff can affect how many cells are highlighted, so the displayed count may not always match the nominal number exactly.
These rules rank the values in the selected range; they do not identify the top rows across an entire table unless the rule is applied to the relevant value column. Blank cells and text generally do not behave like numeric scores in a numeric ranking. Check the selected range and the rule scope before interpreting the result.
Icon Sets and Thresholds
An icon set assigns symbols to value bands, often using three, four, or five levels. Depending on the set, icons may be arrows, colored indicators, or rating symbols. By default, Excel typically evaluates the selected values and divides them according to thresholds, often using percentages or percentiles. The exact setup is visible in the rule’s editing options.
To adjust a set, open Conditional Formatting > Manage Rules, select the icon rule, and choose Edit Rule. You can set threshold types and values, change the icons, reverse their order, or choose to show only the icon. Threshold types matter: a percentage threshold is not the same thing as a numeric cutoff. A value of 90 in a numeric threshold means 90 units, while 90 percent describes a position or proportion under the selected rule’s logic.
For a performance table, you might use green, yellow, and red indicators for high, middle, and low results. If “high” should mean at least 80 points, use numeric thresholds rather than relying on automatic relative divisions. Otherwise, the same score can receive a different icon when the surrounding data changes.
Choosing the Right Rule and Managing Conflicts
Choose a rule according to the question you want the spreadsheet to answer:
- “Which entries repeat?” Use Duplicate Values.
- “Which entries occur only once?” Use Unique Values.
- “Which numbers are at the extremes?” Use a top or bottom rule.
- “How does each number compare with a threshold or the group?” Use an icon set.
A cell can be affected by more than one conditional-formatting rule. For example, a duplicate might also fall within the top 10. Excel evaluates rules according to their order in Manage Rules; rule priority and settings such as Stop If True can affect which formatting is visible. If colors or icons seem inconsistent, inspect the rules for the selected worksheet and check their order and “Applies to” ranges.
For lists that expand, use a range that includes expected new rows or an Excel table where appropriate, then verify that the conditional formatting extends as intended. Copying and pasting cells can also bring formatting with them, so review rules after substantial edits.
Common Problems and Limitations
Unexpected results often come from applying a rule to the wrong range, including headers, or comparing values that look alike but are stored differently. For example, a number stored as text may not behave like a numeric value in a ranking or threshold rule. Extra spaces can also make text entries appear different even when they look similar.
- Check the selected range and the rule’s Applies to field.
- Confirm that numbers are stored as numbers and that text values do not contain unwanted spaces.
- Review icon thresholds and whether they are numeric, percentage, or percentile-based.
- Use Manage Rules to find overlapping rules and adjust priority.
Conditional formatting is useful for scanning patterns, but it is not a substitute for sorting, filtering, validation, or a careful audit. Color and icons can also be hard to interpret when printed, viewed with limited color perception, or separated from their legend. Keep important meaning in the underlying values or labels as well.
Frequently Asked Questions
Does Excel highlight every occurrence of a duplicate?
In general, the duplicate rule marks each cell whose value occurs more than once in the selected range, rather than marking only later occurrences. The range determines what counts as a repeat.
Can I highlight duplicates across two columns?
The built-in rule evaluates the range you select, but it does not define a multi-column record match. To flag rows where a combination of fields repeats, create a helper value or use a formula-based rule.
Can I change the top 10 rule to top five?
Yes. Open the rule’s dialog and change the item count. You can also choose a percentage-based rule when you want a proportion of the selected range rather than a fixed count.
Do icon sets change the cell values?
No. Icons are visual formatting. The original values remain in the cells and are still available for calculations.
Why did an icon change after I added data?
Automatic thresholds can depend on the values in the range. Adding or changing data may shift the relative bands. Set explicit numeric thresholds if the icons should represent fixed cutoffs.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.