Sat 10 Jan 2015

Tips and Tricks 73 - Use Conditional Formatting to Highlight Duplicate Cells

By |Saturday, January 10th, 2015|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates.

1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule

2. Put following formula after clicking "Use a formula to determine which cells to format"

=COUNTIF($A$2:$A$100,A2)>1

1

3, Click on Format Button to format the cells accordingly.

Sat 02 Aug 2014

Tips & Tricks 36 - Highlight Unique / Duplicate Rows

By |Saturday, August 02nd, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

If you want to highlight Unique / Duplicate Rows, follow following steps

1. Select the data range where you want to apply this formatting.

2. Home Tab > Conditional Formatting > New Rule

1

3. Select Format only unique or duplicate values. In Format all: box, you can select duplicate / unique.

4. Format appropriately and press OK.

Sat 17 May 2014

Tips & Tricks 1 - A List is Unique or Not (Whether it has duplicates)

By |Saturday, May 17th, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Assuming, your list is in A1 to A1000. Use following formula to know if list is unique.

=MAX(FREQUENCY(A1:A1000,A1:A1000))

=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))

If answer is 1, then it is Unique. If answer is more than 1, it is not unique.