How to Highlight the Latest N Values in Google Sheets

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.

Highlight the Latest N Values in Google Sheets

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!

Conditional Format Rule - Settings Panel

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

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.