- Skill level:
Other People Are Reading
Open your spreadsheet. Select all the cells in the data range.
Select "Sort" from the "Data" menu. Sort the data in your spreadsheet by the column that you want to check for duplicate values. If the column containing duplicate values is column A, for example, sort the data by column A.
Select the first empty cell in the first row; for example, D1. You will use this cell's column to identify duplicate values in column A. Type "=IF(A1=A2;1;0)" without quotes into the cell and press "Enter." This formula will check for duplicate rows and display a "1" in the cell if a row is duplicated.
Select D1 and press "Ctrl" and "C" to copy the data. Highlight the D column from D1 to the last row in your data range. Press "Ctrl" and "V" to paste the formula into each cell. All duplicate rows will now be marked with a "1."
Select the D column. Press "Ctrl" and "C" to copy the data. Press "Ctrl," "Shift" and "V" to open the "Paste Special" dialogue. Select the "Numbers" option and deselect all other options. Press "OK." This will allow you to sort the data in your table by the D column to quickly identify the duplicate rows.
Select your entire data range again, including the D column. Select "Sort" from the "Data" menu. Sort your data by the D column and choose "Descending." All duplicate rows will appear at the top of your spreadsheet.
Tips and warnings
- Attempting to sort your data by column D without performing the "Paste Special" in Step 5 will change the data contained in the D cells, giving you incorrect data.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for