Ever need to find duplicate cells in Excel? There are many ways you can do this. You can use a formula, Excel’s built-in “remove duplicates” functionality, even code a solution yourself in VBA. However, you can use conditional formatting to see where the duplicate rows are without deleting them. Normally, people use conditional formatting for highlighting simple things like if a cell is above or below a specific number, but with a simple formula, we can create out own custom conditional formatting scheme.
With that said, let’s say we have some data like so:
We want to see where there are multiple Unique IDs since we don’t want any data with duplicate Unique IDs.
Before we apply a conditional format, let’s select the entire
TIP: The reason for selecting the entire
A column is to apply the formatting whenever your data increases to more rows. If we just selected the data you have now, the conditional format will not apply to later cells when your data grows.
Now go to the Home tab –> Styles Group –> Conditional Formatting –> New Rule…
The New Formatting Rule dialogue box pops up. Select “Use a formula to determine which cells to format” and the formula is the following:
The first equal sign is to denote that we’re going to enter a formula. Then the condition becomes
A1=A2 which means to say if the value of
A1 is the same as
A2, then the condition is true and we want to apply a format.
Now let’s setup a format. Click on the Format… button and go to Fill to put a light red background color.
Press OK. You will be brought back to the New Formatting Rule dialogue box. Click OK on this box, too. Now the sheet is applying the format:
This is great, but how does it work?
We entered the formula as
=A1=A2, so how does it know
A11=A12? The way that conditional formatting works is that it evaluates each cell that the conditional format is applied to. And when it moves from cell to cell, so does the formula. If you remember my cell references for beginners post, this formula is using relative referencing. So you can imagine this formula as being copied from cell to cell, so the formula changes in each cell. The conditional format formula in cell
A11 is actually
Wait a second…now the entire A column is pink after the data!
Nice catch! Can you figure out why? In cell
A51, what would the conditional format formula be?
If you guessed
=A51=A52, you deserve a cookie. Go ahead, I won’t tell anyone 😉
So how do we fix this?
We have to tell the conditional format formula to ignore blank cells. We need to change the formula for all of the cells that we applied the conditional format to. This time, go to the Conditional Format Manager by going to Home tab –> Styles group –> Conditional Formatting –> Manage Rules…
Depending on the cell you have selected, you may or may not see the conditional format you applied. This is because the Conditional Formatting Rules Manager opens by showing you the conditional formatting of the current selection. This is an important point to remember when editing conditional formats. You wouldn’t want to fix just one cell’s conditional format formula when you meant to fix all of them.
Select “This Worksheet” for the “Show formatting rules for” dropdown to ensure we are editing the conditional formatting for the entire range we want.
Now select the rule we created by clicking in the text of “Formula: =A1=A2” and click “Edit Rule…” Now change the formula to:
We added the
AND() function. Check out Microsoft’s documentation on the
AND() function for more info. Basically, the
AND() function helps us apply two conditions where both must be true to apply the formatting:
- The next cell below the current one must not be blank
- The current cell value equals the value of the next cell below
Press OK and then OK again. Look below your data:
That looks much better 🙂
If you like this content and would like to be notified of new posts, please subscribe!
Questions? Comments? Please post a comment below! I’d love to hear anything you would like me to write about or if you need me to clarify anything.