How to Highlight Latest Value Change Rows in Google Sheets

This guide will explain how to highlight the latest value change rows in Google Sheets.

In other words, you can highlight the latest value or status change rows in Google Sheets to make it easier to identify certain values in your data set.

Google Sheets has a built-in feature that allows you to execute these actions. One of these features is Conditional Formatting. It is a tool that applies customized formatting to the selected range. 

Hence, you will utilize Conditional formatting to highlight the latest value change rows in Google Sheets. To do this, you will create or use existing conditional formatting rules and formulas. 

Let’s take an example.

Suppose you are a small online business owner who runs the shop alone. And you always have difficulty identifying your orders’ statuses, which can delay the business. 

For this reason, you used conditional formatting to highlight the latest value change rows in your data set. In this case, whenever the status of an order is changed from out for delivery to delivered, the row will be highlighted. So this makes it easier for you to know when an order is done and successful. 

Now that’s just one out of the many situations you can utilize conditional formatting to highlight the latest value change rows in Google Sheets. 

It’s simple, right?! Let’s dive into a real example where we will use conditional formatting to highlight the latest value change rows in Google Sheets. 

 

 

A Real Example of Highlighting Latest Value Change Rows in Google Sheets

Take a look at the data set below. First, let’s focus on how to highlight the latest value change rows in Google Sheets. For instance, let’s say you want to quickly identify what month a trainee turned into a regular employee. So the initial data set would look like this:Dataset before highlighting latest value change rows

 

First, you need to identify the latest value change rows. And you can do this in three simple steps which make use of the ARRAYFORMULA function in Google Sheets. 

An ARRAYFORMULA is used to apply a formula to an entire column. So you will only need to apply the formula in one cell and it will apply to the entire column. And this will help you code the conditional format rule later on. 

So the first formula, =ArrayFormula(countifs(B2:B&C2:C,B2:B&C2:C,row(A2:A),”<=”&row(A2:A))) will return the cumulative count of the combined Employee and Position, which are columns B and C, respectively. 

While the second formula, =ArrayFormula(countifs(B2:B,B2:B,row(A2:A),”<=”&row(A2:A))) will return the cumulative counts of Employee, which is column B.

Then, you can use the result of those two formulas to identify the latest value change rows in Google Sheets using this formula: =ArrayFormula((E2:E=1)*(F2:F<>1)). So the cell that returns as 1 except in blank rows is the latest value change rows. 

Finally, you can go to conditional formatting and input the conditional format rule. And tada! This is what your final data set will look like when you finish highlighting the latest value change rows. Example spreadsheet with latest value change highlighted

 

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

Now that you’ve seen what it would look like to highlight the latest value change rows in Google Sheets, it’s time to try it yourself!

 

 

How to Highlight Latest Value Change Rows in Google Sheets

This section will explain how to highlight the latest value change rows in Google Sheets. 

1. First, you need to sort the columns in ascending order for the conditional format rule highlighting the latest value change rows to work. Next, sort the Employee column and then sort the Date column. Sample dataset that you need to highlight latest value change rows

 

2. Next, you need to have the cumulative count of combined Employee and Position, which are columns B and C, respectively. Type the formula ‘=ArrayFormula(countifs(B2:B&C2:C,B2:B&C2:C,row(A2:A),”<=”&row(A2:A)))’ in cell E2. Finally, press Enter to show the results.Cumulative count of combined Employee and Position

 

3. Then, in cell F2, type the formula ‘=ArrayFormula(countifs(B2:B,B2:B,row(A2:A),”<=”&row(A2:A)))’. And this will return the cumulative count of Employee, which is column B. Press Enter to show results.Highlight Latest Value Change Rows in Google Sheets

 

4. Next, you need to identify the latest value change rows. So type the formula ‘=ArrayFormula((E2:E=1)*(F2:F<>1))’ in cell G2, and then press the Enter key. Then, identify which rows have the result of 1. That is your latest value change rows.Highlight Latest Value Change Rows in Google Sheets

 

Note: Do not mind the blank rows that have a result of 1. 

5. Now you can use conditional formatting to highlight the latest value change rows. Firstly, go to Format. In the dropdown menu, click Conditional formatting.Highlight Latest Value Change Rows in Google Sheets

 

6. On the right side, the Conditional format rules menu will open. First, make sure you are in the Single color tab. Next, go to Apply to range and input the range of your data set. In this case, the range would be ‘A2:C15’.Highlight Latest Value Change Rows in Google Sheets

 

7. Next, under the Format cells if. Then, click the dropdown menu and click Custom formula is.Highlight Latest Value Change Rows in Google Sheets

 

8. After choosing Custom formula is, a blank space will appear below it. In that space, type the formula =and(len($A2),$G2=1)’.Highlight Latest Value Change Rows in Google Sheets

 

9. Then, you can go to Formatting style. For instance, if you don’t like the default format highlight color green, you can make use of the tools and change the highlight color. Highlight Latest Value Change Rows in Google Sheets

 

Also, you can bold, underline, italicize, strikethrough, and even change the font style of the latest value change rows. When you’re satisfied with the formatting style, click Done to apply the conditional format rules.

1o. And tada! After doing all the steps and highlighting the latest value change rows, here is what your data set would look like.Highlight Latest Value Change Rows in Google Sheets

 

That’s pretty much it! Since you’ve learned how to highlight the latest value change rows in Google Sheets, you can now isolate specific data to make it easier to identify them on your data set.  

Are you interested in learning more about what Google Sheets can do? Make sure to subscribe to our newsletter to be the first to know about 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'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

 

Frequently Asked Question (FAQ)

1. How to highlight the latest value change rows in more than one column?

The steps in highlighting the latest value change rows when you have more than one column are not that different. In fact, there is only one thing you will have to do which is to change the column names and row numbers in the formula found in step 2.

Instead of the original formula, you simply replace B2:B&C2:C with B2:B&C2:C&D2:D, or whichever columns you’re data set is in.  

So the revised formula you will type in cell E2 is ‘=ArrayFormula(countifs(B2:B&C2:C&D2:D,B2:B&C2:C,row(A2:A),”<=”&row(A2:A)))’. Then, follow the rest of the steps as usual.

0 Shares:
Leave a Reply

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

You May Also Like