Color ranking

Learn how to combine the Excel RANK function and conditional formatting for color ranking and sorting purposes.

In the example, first, we want to create a sorted list using the RANK function. After that, we will highlight the top three products by sales using conditional formatting.

Our result is shown below:

ordered list example

But first, let us create a formula!

Color ranking using the RANK formula

How does the formula work?

The Excel RANK function returns a ranking of any number in the data (products, sales), and it is a built-in function in Excel.

We’ll apply two easy steps. At first, we’ll use the rank function to determine the sorting order (ranking).

The syntax is the following:

RANK( number, array, [order] )

As you see, only two things and optional parameters necessary:

  • The number is the value to find the rank for
  • An array is the range of numbers that we will use for rankings
  • Order is optional. We can decide the sort order (increase, decrease)

Steps to create dynamic ranking in Excel

Step 1: Insert a new column for ranking:

insert a new column

Step 2: To rank the range E3:E17, select cell F3 and enter the formula below:

RANK formula

Copy the formula down until cell F17.


new column with color ranking

Step 3: Now, we have a list with ranks. Select the range!

On the Home tab, select Conditional Formatting. Next, choose the New Rule from the list.

add new rule for color ranking

Step 4: A ‘New Formatting Rule’ window appears.

To add a new formula, select ‘Use a formula to determine which cells to format.‘ Under the ‘Edit the Rule Description‘, enter


Because you want to use colors for ranking, choose a formatting style. Click Format and the Format Cells dialog will appear. In the example, we use yellow to highlight column E’s maximum sales.

add new rule and choose formatting style


find the maximum in a range

Step 5: if you want to highlight the second, third, etc., highest value in a range and get the position, add new formulas to the highlighted range.
Use multiple rules and add =$F=2 for the second, =$F=3 for the third result.

The rule window looks like this:

color ranking conditional formatting

Click OK to add the second and third ranks.

Tip: The list is dynamic! If you change any values in column E (Sales), Excel will also change the rank and color.

dynamic color ranking excel

Use color ranking to highlight the top n items in a range.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.