If you’re like most people, you probably have a lot of duplicate data in your Excel spreadsheet. Duplicate data can be a real pain to deal with, because it can slow down your work and make it difficult to analyze your data. Fortunately, there are a few simple techniques you can use to find and eliminate duplicate data from your spreadsheet. The first step is to use conditional formatting to identify duplicate cells. You can do this by setting up a rule that formats the cells in your spreadsheet according to a certain condition. For example, you could conditionally format the cells based on their value or column name. This will help you quickly identify any duplicates within your data. Once you’ve identified the duplicates, it’s time to get rid of them. You can do this by using the delete command in Excel or by using some of the other tools available in the software. Either way, be sure to keep track of which duplicates were eliminated so that you don’t accidentally reintroduce them later on. ..
Whether you obtain data from an external source or have someone performing data entry, mistakes can happen. If you’re concerned that you may have duplicate data in Microsoft Excel, you can find out quickly with conditional formatting.
You might have details for customers, locations, vendors, or something similar where duplicate data can be inconvenient. But maybe you have data like product, order, or payment identifiers where duplicates can be detrimental. Here’s how to find that duplicate data fast in your Excel sheet.
Highlight Duplicate Data in Excel
Setting up conditional formatting rules can sometimes get complicated. Fortunately, locating duplicates in your Excel sheet using the feature isn’t one of those situations. You can actually accomplish this task in just a few clicks.
Start by selecting the cells you want to review for duplicates. If your entire spreadsheet is in question, you can select it instead by clicking the triangle in the upper left corner of the sheet.
Go to the Home tab and the Styles section of the ribbon. Click “Conditional Formatting,” move to “Highlight Cell Rules,” and choose “Duplicate Values” in the pop-out menu.
When the Duplicate Values window displays, you should immediately see your duplicates highlighted with the default formatting applied. However, you can change this if you wish.
Confirm that “Duplicate” displays in the first drop-down box. Then, click the second drop-down box to select a different format. Click “OK” to apply the format to your duplicate data.
If you prefer a format that isn’t listed, click “Custom Format” in the drop-down box to pick a font, border, or fill style in the subsequent pop-up window. Click “OK.”
You’ll see the Custom Format applied to the cell immediately. If you like it, click “OK” to apply it.
Once you have your duplicate data highlighted using conditional formatting, you can make the corrections or adjustments you need to. And once you do, the formatting will disappear as long as it’s not duplicated elsewhere in your cell selection.
Wondering what other ways conditional formatting can help you find data entry mistakes? Check out how to highlight blank cells or errors with it in Excel.
