Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel 2007 tip: Use conditional formatting to spot duplicate invoices

by
9th Mar 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst shows a practical use for the new conditional formatting features in Excel 2007.

Some time ago we looked at the general changes in conditional formatting in the latest version of Excel. Here we are going to look in a bit more detail at using one of the latest conditional formatting features in practice.

Perhaps we have a list of invoices and we want to check for duplicated IDs. Here is a list of items that we've linked to from the sample Access Northwinds database. We've doctored the data slightly to create some duplicated IDs.

We've selected column A and then from the Home ribbon, Styles group, clicked on Conditional Formatting. We've chosen the Highlight Cells Rules option and from that menu selected Duplicate Values. This rule allows us to highlight either Duplicate or Unique values. We are hoping that most of our values are unique, so we want to find our duplicates by highlighting them rather than the majority unique items. We could click on the formatting dropdown and choose a different format from the list, or create our own custom format:


Given that our main aim is just to find any duplicates, we'll do the minimum amount of work and just stick with the default 'Light Red Fill with Dark Red Text'. As you can see, Excel 2007's Live Preview feature shows us the effect of our choice before we need to accept it.

We can easily see our two adjacent duplicated numbers, but what if there any others? We could of course scroll down to the bottom of the list, but another feature of Excel 2007 is the ability to filter or sort by colour. Because our list is an External Data Range, Excel has automatically set it up as an Excel table. One of the features of an Excel table is the addition of a dropdown to the header cell of each column. If we click on the OrderID heading we have a range of options but the one that we are interested in is Filter by Colour:


We'll choose to filter by our light red fill and then, in order to show the duplicates next to each other, we'll sort 'Smallest to Largest':


If the data was in an ordinary Excel range rather than a table, we could still Filter or Sort by colour. From the Data ribbon, Sort & Filter group, the Filter button will add the filter and sort drop down to the column headings or the Sort button displays a dialog that allows us to sort by Cell colour, Font Colour or Cell Icon.

Related material in ExcelZone

Subscribe to the ExcelZone newswire
Subscribe to the ExcelZone NewswireTo keep up with all our Excel 2007 news, tips and tutorials, click the graphical button to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.