Excel Magic Trick 514: Conditional Formatting Duplicate Values (3 Methods)
Ғылым және технология
Download Files:
people.highline.edu/mgirvin/Y...
Methods: 1) Excel 2007 built-in method, 2) COUNTIF functions, 3) COUNTIF function for whole row
See how to
In Excel 2007 / 2010 use Conditional Formatting:
1) Highlight cells with seat names
2) On the Home ribbon, go to the Styles group, then to Conditional Formatting, then to Highlight Cells Rules, Then to Duplicate Values
In Excel 2003 and earlier use Conditional Formatting
1) Format menu, Conditional Format
2) Select Is Formula from first textbox
3) Enter formula: =COUNTIF($A$1:$A$8,A1) Greater than 1 (this is simple example where names are in range A1:A8.
4) add formatting
Пікірлер: 25
amazing!!! Thank you!!!
@excelisfun
Жыл бұрын
You are welcome!
You are welcome!
More great tutorials. You have no idea how much you have helped me solve some of the problems I have had with Excel. You work is much appreciated. I hope to be able to spend some time doing some of the series at some point.
THANK YOU! Plus, I got a freebie shortcut with the Ctrl, shift, down arrow....that is even more handy! Thanks!!!!!
Excellent
@excelisfun
Жыл бұрын
Glad you like it!!!
Thank ya. I usually do well with Excel even with conditional formatting. But this one stumped me. How you explained it, whelll sir, the problem was whipped in less than 2 minutes. THANKS!!
@excelisfun
Жыл бұрын
Glad to help, Medic!!!!
If you had data in the range A6:D13, and the Lat was in column C and the Long was in column D: you could highlight the range A6:D13, then with the active cell A6, open CF dialog box and use the formula: =COUNTIFS($C$6:$C6,$C6,$D$6:$D6,$D6)>1
Great work - thank you.
@NextNline2 , glad it helped!
I do not understand your question. If you wnated words in the cell based on the criteria you specified, they maybe a formula like this would work: =IF(A1>B1,"High",IF(A1=B1,"Middle","Low")) I do not know what you mean when you say "how to add 3 value for IF funtion"
Hi there, awesome tricks. I'd like to know how to remove conditional formatting (rules) but leaving there just the results form removed rules?. Thanks a lot
@BkD1979 , see this video title: Excel Magic Trick # 270: Conditional Format Based On Diff Cell
How do I find duplicate values across multiple columns? For example: I have a list of lat/long coordinates and I need to identify when there is a duplicate Latitude AND Longitude in 2 separate columns. Please help...
Hi there, how do I compare and highlight differences across two identical worksheets that only has date changes?
Good trick
I tried a few COUNTIF functions to find duplicate value that appears in ALL 4 columns but always get an error. Can you please help?
Tks a lot ^_^
How to make if funtion wth this situation: IF A1 larger than B1 = High IF A1 equal B1 = Middle IF A1 smaller than B1 = Low that mean, how to add 3 value for IF funtion
great knowledge
@excelisfun
5 жыл бұрын
Glad it is great for you Arc!!!! Thanks for watching and commenting : )
Are you from Texas?
@gaf0461 , I am not sure. Try: mrexcel [dot] com/forum