Check duplicate values in excel | Highlight duplicate value in excel | Find all duplicate value in excel

By
Sometimes we need to find out the duplicate values in excel but we know very well that we can check duplicate value till maximum 25 to 50 records manually. if we have a large list in excel then it will be very defucult to find out all duplicate values.
Solution : Excels provide this capability with just a few mouse click.

Check duplicate values in excel 2010

  • Select the range of cells which you need to check for duplicate value or click on first cell of the column and press Ctrl+A.
  • Click on excel Home tab, and choose Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.
  • You got Duplicate Values Dialog box here you can find the highlight color for duplicate values. you can change Highlight color by selected the required color value. by default its color will be light red.
  • Now press OK within the Duplicate Values Dialog box to identify the duplicate values.
  • Now you can see duplicate values with highlight color.
Figure 1: Excel's Conditional Formatting option makes it easy to identify duplicate values in a selected list.

Check duplicate values in excel 2007

Use Conditional Formatting to Find Duplicate Data in Excel 2007
Figure 2: Excel's Conditional Formatting to Find Duplicate Data in Excel 2007.

Excel's have option to isolate the duplicates values after conditional formatting, as shown in figure Below:

  • Select one of the duplicate cells.
  • Right-Click on it and choose Filter -> Filter by Selected Cell's Color.
  • Now excel cell's collapses the list to show only the duplicate value with Highlight color only as shown in the figure.
Figure 3: Excel's Filter by Selected Cell's Color Option.
Result Sheet
Figure 4: Excel's Filter by Selected Cell's Color Result.
To clear the conditional formatting rules from the excel screen sheet. You can follow the below steps :
  • Click on Home Menu.
  • Click on Conditional Formatting Option In Menu.
  • Select Clear Rules.
  • Then Select Clear Rules from Entire Worksheet, as shown in Figure 5.
Figure 5: Clear Conditional formatting from a worksheet in Excel 2007 and later.

Comments

Post a Comment

Popular posts from this blog

How to use the Excel SUM function | SUM Shortcut in Excel (Alt+=) | Excel's Sum or Autosum Formulla | Add excel cells value by Autosum Formulla

By
Image
The AutoSum or SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: =SUM(A2:A10) Adds the values in cells A2:10. =SUM(A2:A10, C2:C10) Adds the values in cells A2:10 , as well as cells C2:C10 . There are three way to add excel cells value. 1.) Place the cursor on blank cell where you want to sum. and Write the formulla =sum(First cell address:Last cell address) like =SUM(B2:B14) . Press Enter to see the result. Figure 1: Excel's SUM Function. 2.) Place the cursor on blank cell where you want to sum. Click on Formulas Menu -> AutoSum . Figure 2: Excel's AutoSum Function. 3.) Select the cell or cells where you want to sum the rows or colums values and Hold ALT key -> Press equals = sign -> Press Enter . Figure 3: Excel's SUM keyboard shortcut: Alt + =.
Read more

Excel's Important Basic Shortcuts

By
There is no doubt in saying that computers have become a very important part of daily life! If you are frequent computer user then you must know about the keyboard shortcut keys. Excel's shortcuts that can really save you a bunch of time. Shortcut keys help provide an easier and quicker method of navigating and executing commands in computer software. Basically, a computer shortcut is a set of one or more keys that invoke a command in software or an operating system. So, you can increase your productivity by invoking commands with a few keystrokes, otherwise, it will be accessible only through a menu, a mouse, or through any other aspect. You can use these shortcuts to make your work easily. Shortcuts are very useful at the time of generating complex reports in short period of time. Ctrl + Shift + $ makes the number format into currency, with commas at the thousands and two decimal places. Ctrl + Shift + ! automatically formats them into a number with two decimal places and ...
Read more