How to Highlight Matching Values By Comparing Two Columns In Excel

Searching for the existence of similar values between the excel worksheet columns manually is fine when we have few values in a column, but if you have thousands of values in a column which has to be compared with another column for identical values, it may take a decade to do so.
Microsft's Excel is a powerful tool which can be used efficiently to do such a complex task in no time.

Read Also: How to protect Microsoft Excel documents with password 

How to Find Duplicate Values In Excel by Comparing Columns:-
For example, in the below image representation, In an Excel worksheet, we have values in two columns A and column C.
In order to identify and highlight the values of column A which are also available in column C, the Microsoft's Excel tool has several built-in functions and techniques to do so, whereas not every option is simple and efficient, so here we share one of the easiest ways among them.
Sample Data With Employee ID's in Column A and Column C
  1. Step: Click on column header to select entire column values and Tap on "conditional formatting" on the menu bar and click on New rule option.
    Select Column to be highlighted and tap on conditional formatting
  2. Now select rule type as "use a formula to determine which cells to format".
  3. And in "format values where this format is true field' enter the formula as =countif($C:$C, $A1).
    New formatting rule window
    • $C:$C = denotes the column to be compared.
    • $A1 = selected column (column values to be highligted for same entries in the column C.)
    Note: change the alphabet in the above formula according to the column used in your excel sheet, here in this article, I have used column A and column C.
  4. Then click on format on the same(New Formatting rule) window to open "format cells" window, here select any color of your choice to be highlighted when matching values found in the compared column.
    Format Cells - Fill Tab
  5. Now click on OK to confirm the chosen color.
  6. And the final result will look something like the below image representation.
    Result - highlighted matching values
Also Read: How to customize Windows Sticky Notes fonts, color, text etc.

Additional Note: With the above steps we found the matching values by comparing a column with the other column and to segregate the highlighted and non highlighted values, use filter options in Excel itself to do so, follow the below steps. 
  1. Select the column to be filter out.
  2. On the menu bar Tap on Data -> click Filter and a drop down arrow icon appears on the top of the selected column, click on it and select Sort by Color and select the color which is used to highlight the matching values.
Sort By Color

No comments :

Post a Comment


Do not Copy this Web Page Content Registered & Protected  0K43-TWEU-OGOE-SNU2 Protection Status
The content displayed on this website are copyright protected, reproducing the article in any kind is strictly prohibited.
Copyright © . How to Comp - Instant Solution for Computer and Mobile Problems | All Rights Reserved.
Designed By Way2themes | True Tech Tips | Teentack