Highlight the Latest Value Change Rows in Google Sheets

In this tutorial, we can learn to use conditional formatting to highlight the latest value or status change rows in Google Sheets.

For example, I have an employee database in Google Sheets to keep the status of my employees who are working in my company.

An employee, “Ben,” joined in January as an Engineer.

His designation changed to Manager in May, and no more changes (promotion) after that.

So the latest value change w.r.t. employee “Ben” is in May. So I want to highlight the corresponding record of “Ben.”

Recently I have posted how to filter such records. You can find that tutorial here – Filter Last Status Change Rows in Google Sheets.

In that tutorial, I used a SORTN approach.

But later, I realized that we could solve the same with a running count. I have mentioned the same within that tutorial by updating it.

I am going to use it (running count) here for highlighting.

Here are the steps to highlight the latest value/status change rows/records in Google Sheets.

Sample Data and Explanation

There may be value/status changes in one or more columns.

Below you can find examples to highlight the latest value/status changes in one column or more than one column.

Sample Data 1:

Highlighting the Latest Value Change Rows - 1 column — image # 1

The above employee database contains records of four employees. Out of which the status of two employees is only changed.

I have highlighted the respective records using a custom rule in conditional formatting.

Let’s go to another example before going to the custom conditional format rule that highlights the latest value change rows in Google Sheets.

Sample Data 2:

Highlighting the Latest Value Change Rows - 2 columns — image # 2

Here there is two status column in the employee database.

The latest value changes are assessed based on the last two columns.

Highlighting the Latest Value Change Rows in Google Sheets

Prerequisite:

To work the conditional format rule that highlights the latest value change rows, we must sort the data by Name and Date.

I mean, sort column 2 in Asc order and then by column 1 in Asc order.

Steps to Highlight the Latest Value Change Rows

We will first write the Google Sheets formulas for the sample data in Example 1 (please refer to image # 1 above).

Here are the steps involved in detail.

First, we should mark the respective rows. We can do that in three relatively simple steps.

They are two running (cumulative) count formulas and an IF logical test based on it.

That will help us to code the conditional format rule.

Empty the columns E to G.

Cumulative_Count_of_Name_and_Status_1

Enter the following formula in cell E2, which will return the cumulative count of combined Name and Status 1 (columns B and C) values.

=ArrayFormula(countifs(B2:B&C2:C,B2:B&C2:C,row(A2:A),"<="&row(A2:A)))

Generic Formula: =ArrayFormula(COUNTIFS(criteria_range1, criterion1, criteria_range2,criterion2))

criteria_range1 – B2:B&C2:C (combined name and status)

criterion1 – B2:B&C2:C (combined name and status)

criteria_range2 – row(A2:A)

criterion2 – "<="&row(A2:A)

I’ve used the Google Sheets Array_Formula function since we have used arrays in criterion1 and criterion2 in COUNTIFS. Further, we have combined the name and status.

Cumulative_Count_of_Name

In F2, insert the following formula, which will return the cumulative count of Names (columns B).

=ArrayFormula(countifs(B2:B,B2:B,row(A2:A),"<="&row(A2:A)))

Marking and Highlighting the Latest Value/Status Change Rows

To highlight the latest value/status change rows, we must identify them first.

For that, we can use the output of the above formulas.

Generic Formula: =ArrayFormula((Cumulative_Count_of_Name_and_Status_1=1)*(Cumulative_Count_of_Name<>1))

Insert the following formula as per the above generic formula to identify the latest value change rows in Google Sheets.

=ArrayFormula((E2:E=1)*(F2:F<>1))

Steps - Cumulative Count of Name and Status — image # 3

Wherever the formula returns 1 in cell range G2:G, except in blank rows, the corresponding records in the employee database are the latest value change ones.

We have used the Cumulative_Count_of_Name_and_Status_1 to identify the change in value.

The Cumulative_Count_of_Name helps us to find the LATEST change in value.

Conditional Format Rule

Select A2:C and apply the below rule within the custom formula rule in conditional formatting and voila!

=and(len($A2),$G2=1)

Custom Rule to Highlight Latest Status Change Records — image # 4

What about the Latest Value Changes in More than One Column?

Please refer to image # 2 above for the sample data.

You can follow all the above steps here.

The only change that you should make is in Cumulative_Count_of_Name_and_Status_1.

Here, you may replace B2:B&C2:C in that with B2:B&C2:C&D2:D.

Conclusion

Above I have used helper columns for highlighting the latest value/status change rows in Google Sheets.

We can avoid that and write a custom rule directly within the conditional format rule.

But that may severely affect the performance of the Sheet. So I am sticking with the above approach.

If you have data in thousands of rows, you may still face performance issues because of the combining columns using the ampersand.

In that case, I suggest you use the below QUERY to combine columns (name and status).

You can replace B2:B&C2:C in Cumulative_Count_of_Name_and_Status_1 with transpose(query(transpose(B2:C),,9^9)).

Read more about that here – The Flexible Array Formula to Join Columns in Google Sheets.

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