How to find duplicates in Excel spread sheet database?

Hi Andrea, Welcome to the forum, Thank you for your post, sorry its taken a while to get round to answering it;
I suggest using conditional formats and the COUNTIF formula. So follow these steps: You will want to set up conditional formatting for the whole range, but it is easier to set up a conditional format for the first cell in the range and then copy that conditional format. In this case, let’s assume cell A1 has a heading of invoice number, so I will select cell A2 and from the menu, select Format > Conditional Formatting. The Conditional Formatting dialog starts with the initial dropdown saying "Cell Value Is". If you click the arrow next to this, you can choose "Formula Is".
After selecting "Formula Is", the dialog box changes appearance. Instead of boxes for "Between x and y", there is now a single formula box. This formula box is incredibly powerful. You can type in any formula that you can dream up, as long as that formula will evaluate to TRUE or FALSE.
In our case, we need to use a COUNTIF formula. The formula to type in the box is;
=COUNTIF(A:A,A2)>1
In Plain language, this says, "look through the entire range of column A. Count how many cells in that range are the same value as what is in A2. It is really important that the "A2" in the formula be pointing to the current cell - the cell that you are setting the conditional formatting up in. So - if your data is in column E and you are setting the first conditional formatting up in E5, the formula would be;
=COUNTIF(E:E,E5)>0
Then, we compare to see if that count is > 1. Ideally, with no duplicates, the count will always be 1 - because cell A2 is in the range - we should find exactly one cell in column A that contains the same value as A2.
Click the Format... button
Now it is time to select an obnoxious format. There are three tabs across the top of this Format Cells dialog. The Font tab is usually first, so you could select a Bold, red font, but I like something more obnoxious. I usually click the Patterns tab and choose either bright red or bright yellow. Choose the colour, then click OK to close the Format Cells dialog.
You will see the selected format in the "Preview of format to use" box. Click OK to close the Conditional Formatting dialog...
...and nothing happens. If this is your first time setting up conditional formatting, it would be really nice to get some feedback here that it worked. But, unless you are lucky enough that the 'figure/value/label' in cell A2 is a duplicate of some other cell, the condition is not true, and it looks like nothing happened.
You need to copy the conditional formatting from A2 down to the other cells in your range. With the cursor still in A2, Do Edit > Copy. Hit Ctrl+Spacebar to select the entire column. Do Edit > Paste Special. In the Paste Special dialog, click Formats. Click OK.
This will copy the conditional formatting to all cells in the column. Now - finally - you see some cells with the red formatting, indicating that you have a duplicate.
It is useful to go to cell A3 and look at the conditional format after you have completed the copy. Select A3, bring up conditional formatting. The formula in the Formula Is box has changed to count how many times A3 appears in the column A:A.
Note: In this case I've set up 65536 cells with conditional formatting and each cell is comparing the current cell to 65536 other cells. Also, when copying the conditional format to the entire column, you could have instead selected just the rows with data before doing the Paste Special Formats
More: The other issue that I described after the question is that you really cannot sort a column on the basis of a conditional format. If you need to sort this data so that the duplicates are in one area, then follow these steps. First, Add a heading to B1 called "Duplicate?". Type this formula in B2:
=COUNTIF(A:A,A2)>1
With the cell pointer in B2, double-click the autofill handle (the little square in the lower right corner of the cell) to copy the formula all the way down the range.
You can now sort by column B descending and A ascending to have the problem invoices at the top of the range.
Note: This solution assumes that you want to highlight BOTH of the duplicate invoices so that you can manually figure out which to delete or correct. If you don't want to mark the first occurrence of the duplicate, you can adjust the formula to be:
=COUNTIF($A$2:$A2,A2)>1
It is important to enter the dollar signs exactly as shown. This will look at all cells from the current cell up only, looking for duplicate entries.
Let me know if that works, best regards Pete