Microsoft Excel’s RANK.EQ() function ranks a set of values, but in a loaded sheet, the highest ranked items may be lost. You can combine RANK.EQ() with a conditional formatting rule to highlight top-ranked items so they’re easy to find.
Microsoft Excel’s RANK.EQ() function displays the relationship between values by ranking those items according to a numeric value. Unfortunately, in a busy sheet, the top-ranked or bottom-ranked items can be hard to spot visually.
A pivot table can help you shuffle a lot of data, but you might not want this structure. In this tutorial, I’ll show you how to use a conditional format to highlight the top three ranking values. We will first look at the two functions used and then we will get down to business by constructing a conditional formatting expression.
TO SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I’m using Microsoft 365 desktop on a 64-bit Windows 10 system, but you can use older versions up to Microsoft Excel 2010. Excel for the web supports everything described in this article.
How to use RANK.EQ() in Microsoft Excel
The first step to using a conditional format to highlight the top three ranking values in a dataset is to use RANK.EQ() as a helper column:
as you can see in Figure A. If you are not using a Table object, use the following function:
= RANK EQ (C3: C42, C3: C42)
The result is a list of values — the ranking of each item relative to the other items. Try to find the top three ranking values. It’s difficult, and you could make a mistake.
Before continuing, let’s look at how Excel’s RANK.EQ() function works. This function returns the rank of a number in a list of other numbers and uses the following syntax:
RANK EQ(number, ref, [order])
An explanation of the arguments follows:
- Number Required: Identifies the number you are filing.
- Required Reference: Refers to the range of values to which you are comparing the number.
- Optional order: Identifies the sort type – 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 old RANK() function but works the same way as RANK(). To learn more about RANK.EQ(), read How to Calculate Rank in an Excel Sheet. RANK() is still there for backward compatibility only. You don’t need to update existing RANK() functions (at least not yet), but use RANK.EQ() in the future.
In a busy sheet, finding the numbers 1, 2 and 3 – the first three ranking values - would surely be difficult. That’s why you might want to add a conditional formatting 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 will use the OR() function in our conditional format formula.
This function returns TRUE if any logical arguments are true. OR() uses the following syntax:
OR(logical1, logical2, logical3, …)
where the function has only one required argument – logic1. If even a logical argument returns true, the function itself returns TRUE. Now let’s add the conditional format expression.
How to apply conditional formatting for the first three ranking values
Using RANK.EQ() at the sheet level allows you to see both the numeric rank of each value and the conditional formatting that will make it easier to find the top three ranking titles.
Now let’s add the conditional format, which we’ll base on the results of RANK.EQ():
- Select the columns you want to highlight.
- In this case, it’s B3:D42; we want to highlight the entire record for top titles.
- On the Home tab, click Conditional Formatting, then choose New Rule.
- In the resulting dialog box, click Use formula to determine cells to format in the upper pane.
- In the bottom pane, enter =OR($D3=1,$D3=2,$D3=3).
- The OR function allows us to use a ruler to format three headings.
- Click on Format and then click on the Fill tab.
- Choose red from the Background Color gallery and click OK once.
Figure B and Figure VS show results.
Conditional formatting exposes something you could easily miss if you visually check the top three ranking titles: there’s a tie for third place!
The results of the OR() function are easy to understand, but let’s look at the first highlighted row in line 20. When the function encounters the value 3, the OR function evaluates as follows:
Therefore, the conditional formatting rule switches to the format for this record. But don’t stop at 3. You can easily rank top 5, top 10, etc. by adding new logical arguments.
You can hide the support column, but I’m hesitant to do that. It’s easy to forget that something out of sight is at stake, which can lead to mistakes and frustration.
How to Return Ranked Results in Microsoft Excel
Earlier I mentioned that RANK.EQ() has an optional argument to determine the sort. Our previous use of RANK.EQ() omits the argument, meaning ascending sort. Subsequently, the values 1, 2 and 3 represent the three highest titles per view.
We can quickly reverse this by modifying the function to include the optional argument:
Instead of ranking the top-ranked numbers as 1, 2, and 3, this function will identify the three lowest-ranked articles, as shown in Figure D. Also, conditional formatting still works.
To learn more about ranking in Microsoft Excel, you can read the following articles: