Software Tools Snapshots‎ > ‎Excel‎ > ‎

Duplicate Data

This module covers some of the features of Excel for dealing with duplicate data, which is sometimes useful.

  • Home / Conditional Formatting
  • Data / Remove Duplicates

Exercise 1:

Change one of your spreadsheets so that it contains duplicate data in one of the columns. For example, enter one of the Food Items in the Shopping List more than once.

Use “Home / Conditional Formatting” to high-light the duplicate values.

Use “Data / Remove Duplicates” to remove the duplicate values.

Exercise 2:

The following exercise is a little involved, but shows the features using a simple combination of features, one after the other.

Inspect the following sentence and note any interesting features about the letters in the sentence.

(…All letters in the [English] alphabet are present  with some duplicates)               

Now let’s analyze the sentence.

  • First open a spreadsheet on your desktop.
  • Now select and copy the entire sentence. Make sure to copy all the internal blanks.
  • Select a cell in the spreadsheet, right-click, select “Paste special”.
  • Note when you paste the sentence, a little “icon” appears below the pasted text.
  • Select the icon, and select the menu option “text import wizard”.
  • Use the import wizard to separate the letters into distinct columns.
  • The letters of the sentence should be laid out into distinct columns, like this …

[show with a screen shot]

  • Select all the data, right-click, and copy.
  • Select a cell, right-click, select “Paste Special…”.
  • The “Paste Special …” dialogue box should appear.
  • Select the option “Transpose” and “OK”.
  • The letters should be copied into one column.
  • Select all the letters and sort.
  • What can you observe about the letters (… All letters in the alphabet are arranged in alphabetical order with some duplicates.)
  • Ensuring all the cells where there are letters are selected, highlight the duplicates. (Select Home / Conditional Formatting, and any “Duplicate Rule”.) The duplicate letters should be highlighted in pink or some other colour, depending on what you chose.