To conditional format or highlight up and down in ranking, you must follow two steps in Google Sheets.

The first one is the ranking of data for two periods and the second one is consolidating/compiling the ranked data.

Once done these two steps we can highlight up and down in ranking in two periods.

We can use red color to highlight a fall (down) in ranking and green to highlight a rise (up) in the ranking. Want to see what I am talking about? Scroll down and see the last screenshot.

Let me first walk you through the above mentioned two steps in Google Sheets.

For ranking, you can use the functions RANK or RANK.AVG in Google Sheets. I prefer the RANK function here.

Are you totally new to these functions and want to understand its difference?

Here is the link to my Google Sheets function guide. Both these functions are included in that guide along with several other functions.

Datasets to Consolidate and Highlight Up and Down in Ranking

For testing purposes, you can consider sales volume in two periods, the average marks of students in two different exams, or football ranking (FIFA world rankings [men’s]) in the current period and previous period, etc.

I am opting for the latter here, which is the FIFA world rankings, for highlighting up and down in ranking in Google Sheets.

Please don’t rely on the accuracy of the sample data that I am providing you below as it may contain typos.

The data is sourced just for the example purpose. You can refer to this Wiki for the source or the official FIFA website.

Sheet1: Top 10 Ranking as on 19-Sept-2019 (current period).

=ArrayFormula({"Rank";rank(C2:C11,C2:C11)})

I have used the Rank function in cell A1 to find the ranking of countries based on the points they earned.

That data is sorted in pointwise (column C). So obviously, the top-ranking countries will come on the top. That’s why you are seeing sequential numbers in the ranking in column A.

You can find the rank of a specific value in a dataset (eg. cell C2) or all the values (eg. C2:C11) in a dataset by including the function ArrayFormula with Rank a formula. The above formula is an example of the latter.

Sheet2: Top 10 Ranking as on 25-July-2019 (previous period).

Here also I have used the same Rank formula.

Before proceeding further to how to highlight ranking based on its position in two periods, let’s consolidate the datasets in Sheet1 and Sheet2 in a third Sheet (Sheet3).

Consolidating Data to Compare Ranking in Two Periods

The next step to highlight up and down in ranking is consolidating the ranking in two periods.

The Sheet1 contains the FIFA ranking of the current period and the Sheet2 contains the FIFA ranking of the previous period. In Sheet3, in cell A1, enter the following formula.

={Sheet1!A1:B11,Sheet2!B1:B11}

The formula copies Sheet1 first two columns (the rank column and country name column) and Sheet2 second (country) column.

I don’t want to repeat the rank column twice that is why I have omitted to copy the first column from Sheet2.

Now we can compare ranking for the current and previous periods via highlighting.

I am going to highlight country names in column B based on up and down in ranking in the current and previous periods.

Custom Conditional Formatting Rules to Highlight Up and Down in Ranking

Take a look at column C above. In that column, you can see country names as per previous periods ranking order.

I want to highlight ranking, I mean highlight country names in column B, based on the below conditions.

• If the country names are moved up in column B, in comparison to column C, highlight that cell in green color.
• Both are in the same row, I mean not moved up or down, leave the coloring as there is no change in ranking.
• If the country names are moved down in column B compared to column C, highlight such cells in red color.

For example, in the above column B, “France” moved up in ranking. So I want to highlight the cell B2 in green.

How to Find Up/Rise in Ranking in Two Periods?

To find ranking up or rise in ranking compared to the previous period, use the below Vlookup.

=vlookup(B2,{$C$2:$C$11,$A$2:$A$11},2,0)>A2

I will explain this Vlookup first. In Sheet3 cell D2, enter the above Vlookup formula and copy down (you can delete it later).

Wherever the formula returns TRUE, the corresponding country names in column B are moved up in ranking or you can say improved ranking (highlight green).

Formula Explanation:

Vlookup Syntax and Input Arguments:

VLOOKUP(search_key, range, index, [is_sorted])
search_key - B2
range - {$C$2:$C$11,$A$2:$A$11}
index - 2

Vlookup searches down the country name “Belgium” in $C$2:$C$11 (Country [Previous Period]) and returns the previous ranking from $A$2:$A$11.

If that Vlookup output is greater than the value (ranking) in A2, that means the current ranking is higher than the previous ranking.

Highlight Ranking in Conditional Formatting:

In conditional formatting, you can use the same above formula. How?

1. Select the range B2:B11.
2. Go to the menu, Format > Conditional formatting.
3. For other settings, please refer to the screenshot below.

How to Find Down/Fall in Ranking in Two Periods?

Here is a minor change in the formula. To highlight a drop or fall in ranking, change the >A2 in the previous formula to <A2.

=vlookup(B2,{$C$2:$C$11,$A$2:$A$11},2,0)<A2

Use this as the second rule in conditional formatting and choose the color red.

Conclusion

Did you ever try to compare ranking in two periods in Google Sheets?

You can use the above same two conditional formatting formulas to highlight cell values by comparing row numbers or row position. How?

Instead of ranking, enter sequential numbers in column A up to the row you want. No doubt you can depend on the Sequence function for numbering.

Select the range accordingly (eg. B2:B100 instead of B2:B11) in conditional formatting. Use the above two Vlookup formulas in custom formula rule.

From this, you can understand whether the value in column B is available up or down in another column C.

This will only work in unique value columns.