How to Highlight the Latest N Values in Google Sheets

This guide will explain how to highlight the latest N values in a Google Sheets spreadsheet.

We will define a custom formula we can add as a conditional formatting rule. This formula will compare date values with sorted data to find which cells to highlight.

We can use conditional formatting to highlight cells based on specific criteria. If you want to highlight the latest cells, we can perform a comparison of every cell to a specific value. If we want to highlight only the top ten latest dates, we can simply create a rule comparing each date with the tenth latest date.

Let’s take a look at a quick example of when highlighting the latest values in your dataset can be useful.

Suppose you have a spreadsheet of employee data. The employees are arranged in alphabetical order according to surname. Whenever an employee fulfills a task, a column is updated to the date and time the task was accomplished.

You want to learn which employees have recently submitted tasks. Since you prefer not to rearrange the data, you decide that the latest ten employees to submit data will have their row highlighted. How can you accomplish this in Google Sheets?

We can determine the latest N employees by using several Google Sheets functions together. We’ll use the FILTER function to remove blank rows and the SORT function to sort the array in descending order. Once the selection is sorted, we’ll use the INDEX function to determine the Nth latest date. 

Now that we know when to use this conditional formatting method, let’s dive into how to use it and work on an actual sample spreadsheet.

 

 

A Real Example of Highlighting the Latest N Values in Google Sheets

Let’s take a look at a real example of a spreadsheet with conditional formatting added to highlight the latest values.

In the example below, we have a list of employees, their position, and their onboarding date. Using conditional formatting, we were able to highlight the five most recent employees to be onboarded.

highlight latest N values in Google Sheets

 

To get the proper highlighting, we use the following custom formula:

=AND($B2>0,$A2>=INDEX(SORT(FILTER($A$2:$A,$B$2:$B>0),1,0),5))

Let’s try to understand the formula above. First, we use the FILTER function to filter out rows where column B is not empty. Next, we use the SORT function on this filtered list to sort the array by date in descending order. 

Afterward, we use the INDEX function to find the value of the fifth date. This value determines which cells to highlight. For example, if the fifth most recent date is July 4th, 2022, then our rule should only highlight dates equal to or more recent than this date.

The result of the INDEX function is compared to the current row’s date. The final formula checks that comparison and whether the second column is greater than zero. The formula will only return TRUE if both arguments are TRUE. Cells are highlighted if the formula returns TRUE.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out this method in Google Sheets, read our step-by-step guide in the next section!

 

 

How to Highlight the Latest N Values in Google Sheets

This section will guide you through each step needed to start highlighting the latest N values in Google Sheets. You’ll learn how we can use a custom conditional formatting rule to highlight the right cells in your dataset.

Follow these steps to start using conditional formatting to highlight the latest N values:

  1. First, select the cells you want to highlight. In this example, we’ll highlight cells in column A.
    select date column
  2. Next, click on the Conditional formatting option under the Format menu.
    add conditional formatting to selection
  3. In the dropdown menu under Format rules, select the ‘Custom formula is’ option. In the next textbox, paste the formula =AND($B2>0,$A2>=INDEX(SORT(FILTER($A$2:$A,$B$2:$B>0),1,0),5)) then click on Done.
    use a custom formula to only highlight latest N values in Google Sheets
  4. The conditional formatting should now highlight the latest N values in your selected range.
    example of highlighting latest N values in Google Sheets
  5. You may also apply conditional formatting to the entire dataset to highlight the entire row instead.
    applying conditional formatting to entire row

 

 

This step-by-step guide should be all you need to start highlighting rows with the latest dates in Google Sheets. Our guide shows how easy it is to use a custom conditional formatting rule to show the latest N values without sorting the dataset. 

This method is just one way you can use Google Sheets to format your data. The conditional formatting feature is a powerful tool to use since it allows you to define custom rules using various functions. With so many other Google Sheets functions available, you can certainly find the right formula for your use case.

Are you interested in learning more about what Google Sheets can do? Do subscribe to our Google Sheets newsletter to keep up with the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like