Highlighting Top X values with Conditional Formatting in #Excel

Well… think this topic is too simple?  How about doing this with

  1. a dropdown menu to make the Top X a dynamic one?
  2. with Icon Set?

Excel Tips - Highlight Top X with drop down

You may download a Sample File to follow along.

Let’s start with the basics first.  Here is

The simple way of highlight Top X

  1. Select the range of data (where you the conditional formatting applies to)
  2. Go to Home Tab –> Conditional Formatting
  3. Top 10 items
  4. The select the Top X (tip: you can customize the format)

Excel Tips - Highlight Top X with drop down 1

Super easy!  Indeed it is a very handy way to highlight Top X values in a dataset.  The only drawback is the Top X is statics.  If you want to change the number of values to be highlighted later, you need to go deep into the conditional formatting to revise the Top X value:

  1. Conditional Formatting –> Manage Rules…
  2. Select the rules –> Edit Rules…
  3. Change the value, then OK, OK

This slideshow requires JavaScript.

Not quite ideal…

So why don’t reference the (Top) X to a cell, say G2, where a user can input directly?

Like this:

Excel Tips - Highlight Top X with drop down 2

Ooops…..

I hope the above would work… but it is not that easy.  Sometimes Excel prefers to giving you hard time, or challenge (be more positive). 🙂

To achieve this, we need a workaround to build the conditional formatting rules by using formula.

Setting the condition using a formula

A) Setting up New Rule for conditional formatting:

  1. Select the range of data
  2. Home tab –> Conditional Formatting
  3. New RuleExcel Tips - Highlight Top X with drop down 5

B) Define the condition by a logical formula:

  1. In the dialog box of “New Formatting Rule”, select “Use a formula to determine which cells to format”
  2. Input the following formula:

=B2>=LARGE($B$2:$B$11,$G$2)

‘Note: Pay attention to the relative and absolute references.

3. Click “Format…” to set the format you want

Excel Tips - Highlight Top X with drop down 6

C) Setting up the format when the condition is met

  1. Go to the “Fill” tab
  2. Select the Fill Color that you want
  3. OK

Excel Tips - Highlight Top X with drop down 7

Tip: You may set other formats in the other tabs

All set.  Let’s see the result:

Excel Tips - Highlight Top X with drop down 7

How this work?

Let’s examine what the formula does.

=B2>=LARGE($B$2:$B$11,$G$2)

Syntax – LARGE(array, k)

  • Array    Required. The array or range of data for which you want to determine the k-th largest value.
  • K    Required. The position (from the largest) in the array or cell range of data to return.

In a word, to return the Top X value.

The LARGE($B$2:$B$11,$G$2) returns the Top X value in the range of $B$2:$B$11, where X is the value in $G$2.  This result is then compared to the value in the range by the following portion:

=B2>= Top X value

In short, the formula compares the cell values to the Top X value in the range, to see if the cell value is larger or equal to the Top X value.  When it is TRUE, then the format is applied.

Put it into other words, it is the formula expression of the Top X rule of conditional formatting.  Make sense?

Let’s make a dropdown in G2 to make it interactive

  1. Select the cell G2
  2. Go to the Data tab
  3. Select “Data Validation” on “Data Tools” group
  4. Select the “Settings” tab (selected by default)
  5. Select “List” under “Allow:”
  6. Input 1,2,3,4,5 under “Source:”
  7. OK

Excel Tips - Highlight Top X with drop down 8

Note: Make sure the In-cell dropdown is checked.

Now you will see the dropdown button when G2 is selected.

Excel Tips - Highlight Top X with drop down 9

Yeah!  All done… but one final trick…

The Final Trick

What?  Did you notice that in the first image of the post, we have “Top ” before the number when it is selected?

Excel Tips - Highlight Top X with drop down 10

Using Custom Format to make a number more “meaningful”

Excel Tips - Highlight Top X with drop down 11

In G2, Apply the following custom format:

“Top “#,##0

This custom format adds the prefix “Top ” to the numeric input.

Important: We cannot use “Top 1”, “Top 2”, “Top 3”, etc as the dropdown directly as it will turn the cell content into Text instead of Number.  The LARGE function would not work with “text” argument.

Note: Pay attention in the formula bar.  Although you see “Top X” in G2, you see only “X” in the formula bar.

I use this trick a lot to make my worksheet more “interactive” while maintaining “readability”. 🙂

I hope you like it too.

What about doing the same with Icon Set?  well… I will talk about it next week.  Meanwhile, I highly encourage you to try and explore.  The logic behind is more or less the same.  You just need to figure out where to put the formula.  🙂

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.