Sometimes, in a large dataset in Google Sheets, you may want to highlight the latest n values to bring your focus on those particular records.

It may become a must if you often sort your data based on any column other than a date column in the range.

Such sorting causes a shuffled dataset w.r.t. a date column and makes it very difficult for you to identify the latest rows.

Whether you sort the data or not, highlighting the latest N values is the ideal way to bring focus to them.

In Google Sheets, to solve specific problems, there may be a few working formula combinations. That’s applicable here also.

Here I am using a combination of Filter, Sort, and Index functions within the custom formula field in conditional formatting. Let’s code that below.

Formula to Highlight the Latest N Values in Google Sheets

We have data in A1:B, where A contains dates and B contains numbers.

We need to highlight the latest N values in column B based on corresponding dates in column A.

Let’s start with the formula (highlight rule) and how to use it within the conditional formatting.

Formula:

=and(B2>0,A2>=index(sort(filter($A$2:$A,$B$2:$B>0),1,0),N))

Note:- Replace N in the above formula with 5, 10, 20, 50, 100, or the number of recent records you want to highlight.

For example, if N=10, the formula will highlight the latest ten values in Google Sheets.

Inserting the Rule:

To use the above rule for highlighting, do as follows.

Select B2:B, the range in which we want to apply the fill color through conditional formatting.

Then go to the menu Format > Conditional formatting and correct the “Apply to range” if not B2:B1000.

Then select “Custom formula is” and key in the above rule (formula).

Click “Done,” and voila!

Formula Explanation

As you may know, to learn a formula, we must focus on the center portion of it. Here it’s the FILTER function.

step_1

=filter($A$2:$A,$B$2:$B>0)

It filters out rows that don’t have values in column B.

This way, we can ensure that the highlighted latest n value range doesn’t have any blank cells.

step_2

=sort(step_1,1,0)

To SORT the data to move the latest records to the top of the sheet.

step_3

=index(step_2,N)

The INDEX function here offsets N rows and returns the date from the corresponding cell.

Assume N = 10. Remember, the latest records are now on the top (step_2).

In step_3, we want the formula to return the date in the 10th row from the top.

If step_3 output is 19/06/2022, all the dates greater than or equal to it will fall under the latest n records.

step_4

=and(B2>0,A2>=step_3)

Important:

If you have duplicates of the nth date, the formula will highlight them also (it’s also applicable to the Query below the next subtitle). So, sometimes you will see n+ records with fill color applied.

If you want, you can use the below SORTN to extract that records.

=sortn(filter(A2:B,B2:B<>""),n,1,1,0)

Query in Highlighting the Latest N Values – Additional Tips

Actually, in the above rule, we can replace the Filter, Sort, and Index combo (step_3) with a QUERY formula.

Because we can solve the crux of the highlight latest N value problem simply by using the query as it has all the necessary clauses to filter, sort, and offset records in a dataset.

We have used the said combo (step_3) to filter out blanks, sort the data based on dates in Z-A order, and finally offset N rows.

The below query handle them all!

=query($A$2:$B,"Select A where B is not null order by A desc limit 1 offset N-1")

Important:- If N=10, replace N-1 with 9.

See the below formula.

To highlight the latest N values in Google Sheets, in addition to the earlier formula, we can use the below Query-based formula in conditional formatting.

=and(B2>0,A2>=query($A$2:$B,"Select A where B is not null order by A desc limit 1 offset N-1"))

That’s all. Thanks for the stay. Enjoy!

Resources

• How to Lookup Latest Dates in Google Sheets.
• Extract the Earliest or Latest Record in Each Category Based on Timestamp.
• Lookup Latest Value – How It Differs in Excel and Google Sheets.
• Formula to Combine Rows and Get Latest Values in Google Sheets.
• Highlight the Latest Value Change Rows in Google Sheets.
• Highlight Unique Top N Values in Google Sheets.
• Top N Values Including Duplicates in Google Sheets.
• Highlight Top 10 Ranks in Single or Each Column in Google Sheets.