Removing Duplicates in Excel 2008

Excel 2008 for Mac requires more work to remove duplicate values in a data set because of it’s lack of features. Excel 2007 and 2010 have the Remove Duplicates feature. Excel 2003 and Excel 2008 for Mac don’t.

In my last post I used a formula and the Find box to remove duplicate values in Excel 2003. However, Excel 2008 for Mac has no “Find All” button in the Find box.

Find All Button

You can’t “Find All” values of TRUE because that functionality doesn’t exist.

No Find All Option in Excel 2008

How to Remove Duplicates in Excel 2008

Nevertheless, even if the function does not exist, I still need to know how to remove duplicates in excel 2008. First, I have to remove some duplicate values in the Weight column on my spreadsheet. So I used the following procedure:

Sort the spreadsheet in ascending order by Date.

In cell C2 add the formula: =B2=B1, which will be FALSE if C2 is unique, and TRUE if it’s a duplicate value to the cell above it.

Copy the formula down by double clicking the fill handle.

Remove Duplicates Formula Excel 2003

Select all formulas by making C2 the active cell, and using the keyboard shortcut Cmd+Shift+Down arrow.

Copy, and then Edit»Paste Special»Values .

Enter a label in cell C1. (I used ID)

Select cell C2 and click the Sort Ascending button on the toolbar.

Sort Ascending Icon

Scroll down column C and select the first TRUE value.

Use the keyboard combination Cmd+Shift+Down arrow to select the remaining TRUE values.

Right click on one of the TRUE cells, and select Delete… from the popup menu

In the Delete dialog box, select Entire Row and then click OK.

Sort Ascending Icon

Remove column C by right-clicking the header, and click Delete from the popup menu.

What’s left are the unique Weight values in chronological order.

10 thoughts on “Removing Duplicates in Excel 2008”

    • I’m not exactly sure what you are referring to. Are the different sort values in different columns, or in the same column? Perhaps you could be a bit more precise, or send me a spreadsheet example. Thanks.

    • When a cell is active, the bottom right corner has a little “square” that is called a “fill handle.” Your cursor will change when you hover over this fill handle. You can click and drag the fill handle down to copy a formula, or a double click will do the same thing.

  1. Thank you so much, that’s just what I needed! Now, if only the TRUEs could be also color coded… This is my very first experience with formulas. (I’m a graphic designer — Spread sheets are Greek to me!)

Comments are closed.