Browsing all articles tagged with Duplicate Records

How to Locate Duplicate Records Quickly By Using Conditional Formatting in MS Excel

Microsoft Excel is one of the most beneficial computer programs that are widely used throughout the whole world. This software is employed for making spreadsheets. Maybe, some of you may think that Excel is as simple as its definition, but it’s not actually as simple as that. This computer program has the capability to enhance efficiency, not time-consuming and the best thing is that, it has the ability to perform complex computer operations. Microsoft Excel is designed for a well organized data. In addition, MS Excel contains numerous functions and one of which allows the user to format the worksheet to find and remove errors that can cause massive problems on the spreadsheet. This specific function of MS Excel is most beneficial to those who are working with databases containing large sets of data. It helps the user for a quicker scanning of errors so that they can be easily edited or removed from the database. Microsoft Excel can be a very useful program that is utilized to accomplish complicated tasks, whether you use it at work or even at home.

Spreadsheet software like Microsoft Excel is usually utilized as databases for mailing lists, sales records, and parts inventories. Normally, databases are arranged into rows of values called records. In a record, the values in every cell or field in the row is connected like phone number, address and company’s name.

However, you would find it difficult when you are already working with huge databases. One of the common consequences that take place in a large database is that of repetition or duplication of records. This duplication can take place if:

  • Several records contain one or more fields like the address or name field, which contain similar data.
  • The whole record is entered into the database several times which can result in two or more similar value.

Duplication of records can cause big problems in your database so it is a very good idea to locate and eliminate those duplicate data regularly.

When you are using Microsoft Excel 2007, it is not difficult to locate duplicate values like data records, dates, numbers and formulas using conditional formatting. This article will provide you an example and the steps of using conditional formatting to locate duplicate data in Microsoft Excel 2007. But before using conditional formatting to locate duplicate values in your spreadsheet, here is a brief overview of conditional formatting.

Overview of Conditional Formatting

Conditional formatting instructs Microsoft Excel to format all the cells that meet a specific condition in a certain way. For example, you can employ conditional formatting to alter the background color of cells having duplicate data. Through this, it will be easy for you to locate duplicate values in your spreadsheet. You can also use it to alter the font color of all cells having negative values.

Of course, there is a manual method to alter the formats of cells to meet a certain condition. However, if you do it manually, it would be time-consuming since formatting cells is a repetitive procedure. Especially if you are working with a huge set of data or your data change often. That’s the reason why there is conditional formatting, which will let you to formal cells automatically to meet a certain condition.

Locate Duplicate Data by Using Conditional Formatting

  • As an example, you can input the following values into cells B1 to B6: 10, 11, 15, 11, 14, 11
  • The drag cells B1 to B6 on the worksheet.
  • Click Home on the menu bar.
  • Choose and click over the Conditional Formatting symbol that is on the ribbon and then a drop down menu will open.
  • Then choose and click over the Highlight Cell Rules, then Duplicates Values. After that, the dialog box related to conditional formatting will open.
  • From the list in the dialog box, select “Light Red Fill with Dark Red Text”.
  •  Click OK to activate you selections. Then close it.
  • On your spreadsheet, you will see that cells B2, B4 and B6 are formatted having a light red background color and the text are colored with dark red since the three cells enclose duplicate data, which is the number 11.

The same procedure can be utilized appropriately to make use of conditional formatting to trace duplicate records in an Excel database, as well as duplicate dates, formulas or even other values contained in your available data range.