There are some great ways to hunt down duplicate values in the desktop version of MS Excel. Today I will take a look at using the free online SkyDrive version of Excel that is integrated with Outlook.com to find duplicates between two columns.
For the Desktop version of Excel, Microsoft has published some useful how-to articles for finding duplicates data:
- How to compare data in two columns to find duplicates in Excel
- Find duplicate values fast by applying conditional formatting
I tried out both of these techniques in the Office 2007 desktop version of Excel, and both worked well. However when I opened up MS Skydrive, I noticed that many of the functions available in the desktop version of Office do not exist.
Even though I wasn’t able to find the functions I needed on the Skydrive tab-strip, I was still able to use a keyboard shortcut command that I needed for the first technique. Unfortunately I was not able to find a keyboard shortcut for the second technique. At least one of the two ways of finding duplicates exists.
Summarizing the First Technique of Finding Duplicates (Using a Formula)
I tried using this comparison function to find duplicates with both text and numbers. Both types of duplicate search worked flawlessly in the desktop version of Excel as well as in the SkyDrive version.
- For the first version, you need to have two excel data columns (Say columns A and B).
- For simplicity you need to then make sure to put the larger of the two data sets into the first column.
- Then into row 1 of column C you write in the formula:
- Change the value highlighted here in red for the maximum number of rows to compare in column B. In this case the number of rows is 7.
- Then highlight the formula you set in Row 1 Column C all the way down to where the number of rows in Column A ends.
- Hit the keyboard shortcut Ctrl+d. This is a shortcut to AutoFill your formula down.
- Now look at the data in Column C.
- All duplicate values should be output in Column C.
Summarizing the Second Technique of Highlighting Duplicates (Using Conditional Formatting)
This technique works well in the desktop version of Excel, but I was not able to get it to work in the SkyDrive version since there is no apparent Conditional Formatting option.
- Highlight the rows of cells that you want to check for duplicates
- Look on the Home tab and click Conditional Formatting
- Then select Highlight Cells Rules
- Then click Duplicate Values
I do like this way of finding duplicates for the Desktop version of MS Excel since it results in the duplicate values being highlighted in a color of your choice in the original data.