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.