# Discussion on: Highlight Excel list values when they change

23

### Join the conversation!

View:
Show:
###### Excel 2003
1000048875 6th Feb
Those commands are not available in Excel 2003....or the version I have in work
Contributr
###### Where are you getting lost?
ssharkins@... 20th Feb
2003 instructions are included -- where are you getting stuck?
###### Other formula
gabrielgoldstein@... Updated - 6th Feb
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.
###### my excel 2003 solution
jbenton@... 6th Feb
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
###### Highlight Duplicate Values
rcstan Updated - 6th Feb
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
1 Vote
###### IF(..., TRUE, FALSE)
sparent 6th Feb
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.
###### Think about what is being done.
chip_long@... Updated - 6th Feb
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.
Contributr
###### Maybe
ssharkins@... 8th Feb
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!
###### Doing this with an unsorted list
chip_long@... 6th Feb
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.
###### Multiple columns, unknown length, not necessarily starting in column A
N / A 7th Feb
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.
###### Use of conditional formatting during development
sparent 6th Feb
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.
###### List of multi-column data of unknown length
N / A 6th Feb
Apply to A:E
Formula: =\$A1OFFSET(\$A1,-1,0)
Format: Yellow
###### Comment didn't accept GT - LT to indicate NE
N / A 6th Feb
Should have been
Apply to A:E
Formula: =\$A1 NE OFFSET(\$A1,-1,0)
Format: Yellow .
###### Step 5: =A2A1
Murrel 6th Feb
Works for me!

Seems like a lot of work when you just chose the wrong formula to start with.
1 Vote
###### A2 A3 or offset()
If you use conditional formatting for the first time, formulas like = (A2 A3) 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.
###### Changing cell color based on text in two cells
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.!!
###### Changing cell color based on text in 2 cells
rcstan Updated - 13th Feb
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
1 Vote
N / A 13th Feb
Without the IF
###### Will work without 'IF'
rcstan 14th Feb
You are correct.
AND(\$A6>0,B6>0) works just fine.
I guess we all have more to learn...
Thanks, Russ
###### Changing cell color based on text in 2 cells
Thanks for the help. Problem solved!!
###### RE: Changing cell color based on text in two cells
jbenton@... 13th Feb
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()
###### This could apply to multiple columns
N / A 13th Feb
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.
###### cumbersome
jbenton@... Updated - 22nd Feb
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!
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.