# 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:

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:

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

``=RANK(E3,\$E\$3:\$E\$17,0)``

Copy the formula down until cell F17.

Result:

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.

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

``=\$F3=1``

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.

Result:

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:

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.

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