# 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.

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`.

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.

2. Next, click on the Conditional formatting option under the Format menu.

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.

4. The conditional formatting should now highlight the latest N values in your selected range.

5. You may also apply conditional formatting to the entire dataset to highlight the entire row instead.

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.

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.

## How to Find the Last Value in Each Row in Google Sheets

To find the last value in each row in Google Sheet using the LOOKUP function is useful if…

## How to Use the CUMIPMT Function in Google Sheets

The CUMIPMT function in Google Sheets is used to calculate the cumulative interest over a range of constant-amount…

## How to Use Wildcard Characters in Google Sheets Functions

Knowing how to use wildcard characters in Google Sheets is useful when you want to represent or replace…

## How to Use AVERAGE Function in Google Sheets

The AVERAGE function in Google Sheets is useful if you want to get the average value of a…