How to use conditional formatting to highlight rank in Microsoft Excel

Microsoft Excel’s RANK.EQ() function ranks a set of values, but in a busy sheet, the top-ranking items might get lost. You can combine RANK.EQ() with a conditional formatting rule to highlight the top-ranking items so they’re easy to find.

Microsoft Excel’s RANK.EQ() function shows the relationship between values by ranking those items by some numerical value. Unfortunately, in a busy sheet, the highest or lower ranking items might be difficult to spot visually.

A PivotTable can help you wrangle a lot of data, but you might not want that structure. In this tutorial, I’ll show you how to use a conditional format to highlight the top-three ranking values. First, we’ll look at the two functions used and then we’ll get down to business by building a conditional formatting expression.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 desktop on a Windows 10 64-bit system, but you can use older versions down to Microsoft Excel 2010. Excel for the web supports everything in this article.

How to use RANK.EQ() in Microsoft Excel

The first step to using a conditional format to highlight the first three ranking values in a data set is to use RANK.EQ() as a helper column:

=RANK.EQ([@Views],[Views])

as you can see in Figure A. If you’re not using a Table object, use the following function:

=RANK.EQ(C3:C42, C3:C42)

Figure A

The result is a list of values — the ranking of each item in relationship to the other items. Try to find the top-three ranking values. It’s difficult, and you might make a mistake.

Before we continue, let’s look at how Excel’s RANK.EQ() function works. This function returns the rank of a number within a list of other numbers and uses the following syntax:

RANK.EQ(number, ref, [order])

An explanation of the arguments follows:

• Required Number: Identifies the number you’re ranking.
• Required Ref: References the range of values you’re comparing Number to.
• Optional Order: Identifies the type of sort – 0 is the default and ranks in ascending order, while any non-zero value ranks ref in descending order.

Microsoft Excel’s RANK.EQ() function replaces the older RANK() function but performs the same as RANK(). To learn more about RANK.EQ(), read How to calculate rank in an Excel sheet. RANK() is still around for backward compatibility only. You don’t need to update existing RANK() functions (at least not yet) but use RANK.EQ() going forward.

In a busy sheet, finding the numbers 1, 2 and 3 — the top three ranking values — would surely be difficult. That’s why you might want to add a conditional format rule to make these values stand out among the others.

How to use OR() in Microsoft Excel

Microsoft Excel’s OR() function will help us find the top-three ranking values: 1, 2 and 3. We’ll use the OR() function in our conditional format formula.

This function returns TRUE if any logic arguments are true. OR() uses the following syntax:

OR(logic1, logic2, logic3, …)

where the function has only one required argument – logic1. If even one logic argument returns true, the function itself returns TRUE. Now, let’s add the conditional format expression.

How to apply conditional formatting for the top three ranking values

Using RANK.EQ() at the sheet level lets you see both the numerical rank of each value and the conditional formatting that will make the top-three ranking titles easy to find.

Now, let’s add the conditional format, which we’ll base on the RANK.EQ() results:

1. Select the columns you want to highlight.
   1. In this case, that’s B3:D42; we want to highlight the entire record for the top titles.
2. On the Home tab, click Conditional Formatting and then choose New Rule.
3. In the resulting dialog, click Use a Formula to Determine Which Cells to Format in the top pane.
4. In the bottom pane enter =OR($D3=1,$D3=2,$D3=3).
   1. The OR function allows us to use one rule to format three titles.
5. Click Format and then click the Fill tab.
6. Choose red from the Background Color gallery and click OK once.

Figure B and Figure C show the results.

Figure B

Figure C

The conditional formatting exposes something you might easily miss if visually checking for the first three ranking titles: There’s a tie for third place!

The OR() function results are easy to understand, but let’s work through the first highlighted rank in row 20. When the function encounters the value 3, the OR function evaluates as follows:

OR($D20=1,$D20=2,$D20=3)

OR(FALSE,FALSE,TRUE)

TRUE

Consequently, the conditional formatting rule flips on the format for this record. You don’t have to stop at 3 though. You could easily rank the top 5, top 10, and so on by adding new logic arguments.

You can hide the helper column, but I shy away from doing so. It’s easy to forget that something out of sight is in play, which can lead to errors and frustration.

How to flip the ranked results in Microsoft Excel

Earlier, I mentioned that RANK.EQ() has an optional argument to determine the sort. Our earlier use of RANK.EQ() omits the argument, which means an ascending sort. Subsequently, the values 1, 2 and 3 represent the three highest titles by view.

We can quickly reverse that by modifying the function to include the optional argument:

=RANK.EQ([@Views],[Views],1)

Instead of ranking the highest-ranking numbers as 1, 2 and 3, this function will identify the three lowest ranking articles, as shown in Figure D. In addition, the conditional formatting still works.

Figure D

To learn more about ranking in Microsoft Excel, you can read the following articles: