How to find duplicates in Calc

Updated April 17, 2017 Calc is a free spreadsheet application that mimics many of the features of Microsoft Excel. Unlike Excel, Calc does not feature an easy way to identify and remove duplicate rows from a spreadsheet. Instead, you can use Calc's sort features and formulas to manually identify duplicate rows in your spreadsheet. After your duplicate rows are identified, you can remove, isolate or mark the rows to identify them as duplicates.

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.


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.

bibliography-icon icon for annotation tool Cite this Article

About the Author

William Nagel is a graduate of the Georgia Institute of Technology, where he studied science, technology and culture. He has been writing since 2007 and specializes in computer hardware, operating systems and software documentation. His work has been published in the "North Avenue Review."