Learn Excel London: How To Display Unique Entries in an Excel List

I used to work with a colleague who maintained a postal mailing list for a brochure we would send out. He used to spend some of his time cleaning up that list by removing repeat entries of customer email addresses who had signed up more than once. “De-duping” he called it. He would block out hours to perform this task. A long time spent just cleaning up a column of data, every six weeks.

That is until I showed him how Excel can trim out duplicate entries in a list. He nearly cried.

There’s a couple of different ways to be left with only the unique entries in a column in Excel, so look up the version that applies to you.

Excel 2007 and later – Remove Duplicates:

Excel remove duplicates wizard

  1. Go to the Data tab on the ribbon
  2. Select Remove Duplicates.
  3. Check or uncheck the columns for which you want to search for duplicates.
  4. Click OK.

Excel 2003 and earlier (this still works on newer versions as well) – Advanced Filter:

excel advanced filter to generate unique itemsBefore this process you need to copy the heading for the unique value to a spare cell e.g. if you need the “Email Addresses” then copy that text to a spare cell.

  1. Go to the Data menu, choose Filter then Advanced Filter (on 2007 and later go to the Data tab and select Advanced in the Filter group).
  2. To output the unique values only to another cell range, select:
    1. Copy to another location
    2. In List range specify where the source data is
    3. In Criteria range point to the cell with your heading in
    4. In Copy to point to the cell with your heading in
    5. Check Unique records only.
    6. Click OK.

The unique items will then be output to a separate list. Very useful!

Using advanced filters and other filtering techniques are covered in Best STL’s Excel Intermediate course, offered London and UK wide.

At Best STL we offer Learn Excel London courses in the city and throughout the UK at a level to best suit your needs. Whether that’s an introduction, intermediate or advanced, there’s a training course to make your day run smoother.

Page Break Preview in Excel 2010

Printing out Excel spreadsheets can be frustrating. Often you end up with one piece of paper with only one column of data on! If you are printing a spreadsheet that will take up more than one page, you have the ability to control were the page breaks.

To do this you need to be in the Page Break Preview. The instructions below are for Excel 2007 and later.

To enable Page Break Preview:

  1. Go to the View tab
  2. Click on the Page Break Preview button in the Workbook Views group.

The view of your document will change to something like this:

excel page breaks

The blue line(s) represent where the page breaks are. Simply drag them up or down, left or right and position them where you want the pages to break. If the line is dashed, it is a line that Excel has inserted itself. If the blue line is solid, it is one that the user has changed.

To insert a page break manually:

page setup group in excel 2010 for inserting a page break

  1. Put the cursor where you would like the break to be
  2. In the Page Layout tab, click Breaks then Insert Page Break.

If you’d like to learn more about page breaks and controlling your Excel printouts, attend our Excel Intermediate training course which run in London and UK wide. You’ll also cover IF functions, conditional formatting and many more interesting topics.