Highlighting Top X values with Icon Set in #Excel

Excel Tips - Highlight Top X with drop down Part 2.1

This post is a continuation of the previous one – Highlighting Top X values with Conditional Formatting in #Excel

So I will go straight to the point.  For background information, please read the previous post. 🙂

To insert Icon Set

Select the data range –> Go to Home Tab –> Conditional Formatting –> Icon Sets

Excel Tips - Highlight Top X with drop down Part 2.3

Choose the icon set you like.  For this demonstration, the set of flags is used.

Excel Tips - Highlight Top X with drop down Part 2.4

This is the result you will see.  However, we don’t want all three flags.  We just want to have a green flag for the Top X value(s).  So let’s manage it.

Excel Tips - Highlight Top X with drop down 2

Modify the Icon Set and Formatting Rules

Select the Rule, then “Edit Rule…”

Excel Tips - Highlight Top X with drop down Part 2.5

Here we go…

Excel Tips - Highlight Top X with drop down Part 2.6

Note: The Rule Type “Format all cells based on their values” is selected by default if you have been following the steps.

Guess where we are going to modify?

… The “Icon”

… The “Value”

… The “Type”

Bingo!

Let’s watch it in action

Excel Tips - Highlight Top X with drop down Part 2.7

Note: The “logical operator” selected is “>=” (larger or equal to)

This is the formula we input:

=LARGE($E$2:$E$11,$G$2)

‘What this formula does? Please read previous post

Note: Even though we have selected “No Cell Icon” for other rules, it does not mean you can leave the rules empty. Also we should not have contradicting rules set. As a simple rule, I would you select the same Type, and input the same numbers in the two value boxes. Do pay attention to the operator selected for the rules to get your desired result.

You may download a Sample File file to follow along… but you may want to clear the existing rules first.

Excel Tips - Highlight Top X with drop down Part 2.2

Like it?  Please leave us comment to share your thoughts. 🙂

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.