Microsoft

Highlight Excel list values when they change

If your Excel lists repeats values and you need help finding changes, try this easy conditional format.

One of the most frequent requests I receive from readers is how to highlight list values when they change. Conditional formatting is the answer, however, even though you might try the right formula, if you don't start at the right cell, it won't work.

The figure below shows a list of text values. Now suppose that want to highlight a cell when the value above that cell is different.  Specifically, the first item is Actuaries and it occurs twice. The value changes to Actuary in cell A4, so you'd want to highlight cell A4.

You might consider a simple formula such as =A3<>A2 using the conditional formatting feature, but as I mentioned, positioning is everything in this solution. Let's illustrate what might happen:

  1. Select A2:A35.
  2. Click the Home tab (if necessary). In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 4.
  3. In the Styles group, choose New Rule from the Conditional Formatting dropdown.
  4. In the top pane, choose the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the Condition 1 dropdown.
  5. Enter the following formula: =A3<>A2
  6. Click the Format button.
  7. On the Fill tab, choose a color. In Excel 2003, click the Patterns tab.
  8. Click OK twice. As you can see, the formula doesn't work.

Viewing the results of the formula in a couple of helper columns provides insight we need. Our selection (step 1) was incorrect.

Let's use the simple formula with a different selection. Repeat the steps above, but this time, select A3:A35.

Thanks to Steve Erbach for submitting this easy solution!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

23 comments
Chadyoung1
Chadyoung1

I have been struggling with what should be an easy problem?!?!? I developed an employee schedule and want the work hours shaded to show coverage for each day. I am using conditional formatting for each cell but want it to change color only if that cell has text in it and the first cell in that row has text in it. Sounds simple but I can seem to get it right!!! Any help would be greatly appreciated.!!

albert.tsang
albert.tsang

If you use conditional formatting for the first time, formulas like = (A2A3) shall be easier for you. This method will bring you error if you delete Row 3 for deduplication because cell A3 is deleted. I would suggest using =(A2 offset(A2,1,0)) for cell A2; or =(A3 offset(A3,-1,0)) for cell A3. This would not cause you error even you try to delete duplicated rows. Lastly, you may want to use "Advance Filter" to etract unique records. This will not affect your original data source.

Murrel
Murrel

Works for me! Seems like a lot of work when you just chose the wrong formula to start with.

RU7
RU7

Should have been Apply to A:E Formula: =$A1 NE OFFSET($A1,-1,0) Format: Yellow .

RU7
RU7

Apply to A:E Formula: =$A1OFFSET($A1,-1,0) Format: Yellow

sparent
sparent

I use conditional formatting to help me during the development of my spreadsheet solution. One example is with drop-down lists. I usually have my list items defined in specific cell ranges, rather than hard-coded into the data validation range box. For example, I may have cells E1 to E3 contain list1, list2, list3. I will give E1..E3 a range name. (This is important if you want to access the range from other sheets.) This gives me the flexibility of adding new list items as my development proceeds. The problem I have is when I decide that list1 should now be list4. While the drop-down list shows up with the new item, I still have a bunch of cells pre-populated with list1. What I do is tag some conditional formatting to highlight values when they become obsolete. Simply follow the instructions above but use a formula such as =ISNA(VLOOKUP(A1,ListRange,1,FALSE)). The VLOOKUP function tries to find the cell value into the cell range used for my drop-down - ListRange. If the VLOOKUP doesn't find it, it raises a #N/A. The ISNA function will then return TRUE, triggering the conditional formatting. Now all my cells with obsolete values get highlighted, reminding me to do a search & replace. Conditional formatting is a great tool for the developer, not just the end-user.

chip_long
chip_long

This article only deals with how to do this with a sorted list. If your list is unsorted, you can use the following formula in your conditional formatting: =countif($A$1:A2,A2)=1 Notice the dollar signs ($). This is going to count the number of occurences of the value in the currrent cell from the 1st cell in the range. By saying it is less than 2, it is saying the value is equal to 1, you are saying it is the first unique value - so highlight it.

chip_long
chip_long

This problem is an interesting one. If you think about what you are doing it all makes very clear sense. By selecting the range A2:A35 and comparing A2 to A3, you are saying, "if I am the same as the value below me, I am not unique." This means that the last value is considered the unique value. By selecting the range A3:A35 and comparing A2 to A3, you are saying, "If I am the same as the value above me, I am not unique." Now, the first value is going to be considered the unique value. However in this example, your range doesn't include the entire dataset. You'll notice that the first "Actuaries" is not highlighted and it should be. Since the 1st row is a header row, it should be different from the data so you can do the comparison to the first row, and still select the range of A2:A35 so the complete dataset is included. Now the first "Actuaries" will be highlighted as well.

rcstan
rcstan

I use this simple Condititional formula in all selected cells: =IF (A2=A1, True, False) and set the Font Color to Dark Gray. Next, apply the format for A2 to all other values in Column A. This returns easily identifiable Duplicates and the 'original' values remain Black (or whatever the user's default color is for a column of values). Russ

jbenton
jbenton

I'd select A2:A35 and use the "Cell Value Is" option, then "not equal to" and then "=A1" Specify required format and accept as in main article JB

gabrielgoldstein@hotmail.com
gabrielgoldstein@hotmail.com

I think it's better use range A2:A35 with the formula =A1 not equal A2 so we have formatted A2 as a title of a group.

1000048875
1000048875

Those commands are not available in Excel 2003....or the version I have in work

RU7
RU7

It just tests two but it would be easy to expand to test more columns. =AND(NOT(ISBLANK($A1)),NOT(ISBLANK(A1))) The first test uses absolute column reference to test the cell in column A in the row of the cell being formatted. The second tests the cell being formatted.

jbenton
jbenton

assuming your input cell is C3, use Formula is... option and enter formula as =LEN($A3)*LEN($C3) set formatting options and away you go! NB this checks for any entry, if you specifically only want to find TEXT use ISTEXT()

rcstan
rcstan

In my solution, I assume that Row 1 is used for Headers and that Rows 2 & greater are for data entry. I further assume that Column A contains text, perhaps employee names or weekdays, and that Columns B & greater are for numeric data entry. In my suggested formula, I arbitrarily use cell B6 as the one into which data will be entered to test the validity of the following Conditional Format formula: =IF(AND($A6>0,B6>0),TRUE,FALSE) $A provides an Absolute Reference to Column A whereas B6 is a Relative Reference so you can Copy the format for Cell B6 and apply it anywhere on the worksheet. When you select a Pattern to conditionally shade populated cells, I recommend that you also apply an Outline Border since the shading of cells obliterates default gridlines during Preview and Printing. Of course, there are other formulae that will perform what you want, but this one is straightforward and uncomplicated. Russ

RU7
RU7

Apply to $G:$I (or even $G:$G,$I:$I) =AND(ROW()>1,COUNTIF($G$1:$G1,$G1)=1) Still don't need to sort (nice Chip). Don't highlight the title row.

ssharkins
ssharkins

I didn't want the first item highlighted -- it's not changing because it's the first item in the list -- there's nothing to compare it to. However, I can see where some might need that behavior, so thanks!

sparent
sparent

Russ, You will find that the IF statement is superfluous the way you use it. (A2=A1) will resolve to TRUE or FALSE. It is sufficient to return the value. What your function is doing is saying if A2=A1 is true then return true else return false. It's good to know that Excel converts TRUE to 1 and FALSE to 0. It allows you to do conditional computation without all the nested IFs. As an example, if I want to return 4 if an expression is TRUE but 0 if FALSE, you can simply write =(expression)*4. This is functionally the same as =IF(expression,4,0). Boolean arithmetic can greatly simply your formulae.

ssharkins
ssharkins

2003 instructions are included -- where are you getting stuck?

jbenton
jbenton

NOT(ISBLANK($A1)) can be replaced with $A1 NE "" (NE means less than followed by greater than - why can't we use these?) unless A1 is an error, in which case you've got more problems than your formatting going awry!

Chadyoung1
Chadyoung1

Thanks for the help. Problem solved!!

RU7
RU7

Without the IF

rcstan
rcstan

You are correct. AND($A6>0,B6>0) works just fine. I guess we all have more to learn... Thanks, Russ