How to Delete Duplicate Information in Excel Sheets on Macs

By Sonia Waring

Various iterations of Microsoft Excel for Macs have worked more or less different than their Windows siblings. Excel 2008 for Mac did not incorporate the ribbon interface introduced with Excel 2007 for Windows, but Excel 2011 for Mac does -- and it includes a brand-new button called "Remove Duplicates." Actually removing the duplicates you want can be a bit more complicated than that, but either recent Mac Excel version does let you filter out duplicate information in an array of data. The effect on the rest of your data may be a bit unpredictable or destructive, so it is safest to copy the information, or make a backup of the entire file, before removing duplicates.

Step 1

Remove cells containing duplicate data in a single column by highlighting the column, or a range of cells in that column, choosing the "Data" tab and clicking "Remove Duplicates." Confirm the column choice in the dialog box that appears.

Step 2

Delete cells containing duplicate data in a single or multiple columns by selecting more than one column and clicking "Remove Duplicates" in the Data tab. Choose the columns from which Excel should remove the duplicate data. In this scenario, Excel will delete all the cells in all the selected columns in a row in which it finds duplicate data. For example, if you choose cells A1 through E10 and there is duplicate information in cells A2 and A3, all the cells in rows 2 and 3 of columns A through E will be deleted, even if there is nonduplicate content in some of those cells. This method does not exist in Excel 2008.

Step 3

Delete duplicate rows by selecting the rows, including headers, and clicking on the arrow next to the Filter button, which is the second button from the left in the Data tab. Choose "Advanced Filter." In the resulting dialog box, choose "Copy to another location," and place a check mark in the box labeled "Unique records only." In the field labeled "Copy to," set the a cell to serve as the top, left-most cell for the copied range. This method is available in both Excel 2011 and Excel 2008; it's located in the Data menu in Excel 2008, and the Advanced Filter dialog box is identical.