In an earlier post,
I went through the steps of using the Remove Duplicates function in Excel. This is great if you want to completely
remove the duplicate values. But what if
you want to just find the duplicate values in your data set? There are some formulas you could use to do
this, but another easy way to do it is to use the Conditional Formatting
function.
To use this
function:
-Highlight
the cells where you want to find the duplicate values
-The
cells can be in multiple rows/columns and do not have to be adjacent to one
another
-Click
on the Home tab
-In
the Styles section, click on the Conditional Formatting drop-down menu
-Hover
over "Highlight Cell Rules"
-Click
on "Duplicate Values…"
-A
new window will pop up to ask how you would like the cells formatted
-Leave
the left drop-down menu on "Duplicate"
-The
right drop-down menu can be left alone if you are ok with the default
formatting, or you can click on the drop-down menu to adjust the formatting
-There
are multiple default formats, otherwise you can click on "Custom
Format" to create your own
-Once
you have made your formatting choice, click OK
-Now,
looking at your data set, you should see the duplicate values formatted in the
way you chose
Example 1:
-In
this scenario, I want to highlight where I have duplicate names in my data set
-Highlight
cells A2 through A1
-Click
on the Home tab
-In
the Styles section, click on the Conditional Formatting drop-down menu [1]
-Hover
over "Highlight Cell Rules" [2]
-Click
on "Duplicate Values…" [3]
-A
new window will pop up to ask how you would like the cells formatted
-Leave
the left drop-down menu on "Duplicate" [1]
-In
this scenario, I am ok with the default formatting on the right drop-down to
have the cells filled with light red and have dark red text [2]
-Click
OK [3]
-Now
the duplicate names are highlighted in red and have red text
Example 2:
-In
this scenario, I want to highlight where I have duplicates in List 1 and List 2
-Highlight
cells A2 through B8
-Click
on the Home tab
-In
the Styles section, click on the Conditional Formatting drop-down menu [1]
-Hover
over "Highlight Cell Rules" [2]
-Click
on "Duplicate Values…" [3]
-A
new window will pop up to ask how you would like the cells formatted
-Leave
the left drop-down menu on "Duplicate" [1]
-In
this scenario, I am ok with the default formatting on the right drop-down to
have the cells filled with light red and have dark red text [2]
-Click
OK [3]
-Now
the duplicate shapes are highlighted in red and have red text
Excel ya later!
No comments:
Post a Comment