Skip to main content

How to Highlight Matching Values By Comparing Two Columns In Excel

By John Bennet
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.
Microsoft Excel is a powerful tool that 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, 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 the 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 highlighted 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 the 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.
    The 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 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
Labels:

Comments

Post a Comment

Popular posts from this blog

How to Register and Activate SBI Internet Banking Online

By John Bennet
Internet Banking is the most trusted and efficient way of making transactions, bill payments, view accounts, view statements, deposits and many more facilities that can be accessed from any corner of the world for free. To avail these facilities in SBI (or) any  State Bank Groups new customer's will be given the PPK kit along with the passbook at the time of creating accounts in the bank itself if the internet banking facility request was mentioned in the Account opening form, but for existing customer's they have to register separately to get access to the internet banking either by visiting a branch or without visiting a branch. Also Read:   10 safety tips for secure banking online - That You Must Know If the main motive of internet banking is to ease transactions and payments for clients in the leisure, without visiting a bank home branch in person, then why not to make the registration and activation process of internet banking also in the leisure from their ...
81 comments
Read more

How to Format Write Protected USB Disk Drive and SD Card

By John Bennet
The main intention to append the write protection switches in USB flash drives and SD cards is to prevent accidental file deletion and to avoid suspicious files such as malware/trojan etc. from unauthorized sources delivering/storing into the USB disk. If the USB disk is in read-only mode, any attempts to create (or) edit file into the USB disk will be denied, But when you really want to make changes in the USB disk to add some more information (or) to edit the information that is already present in it, the restriction that you added on your computer initially will now prevent you from making changes such as adding new files or deleting existing files from any USB disk drive plugged into it. However, you can still read the files available in it, but you'll not be allowed to make changes and format the disk. The disk is write-protected -  You cannot format Having a flash drive/ SD card which is not completely accessible even by the owner of that device, is like having a l...
1 comment
Read more

How to Reset SBI Profile Password Online Without Branch Visit

By John Bennet
Though passwords are meant to protect any third party access, however having more than one authentication within the same portal which force us to remember additional password for accessing any of its service will be hectic and most of the time we may forget or confused between passwords set in same portal. Nationalized banks use this add on passwords to re-authenticate the actual user for accessing its crucial services. We are aware writing passwords either in any form of physical/digital medium or disclose it to third person is not advisable, so the only choice must be keep it locked in mind. How to Unlock SBI Profile Password in Online Portal ?  In case if you forget the second level password (i.e. Profile Passwords) State bank of India has provided few options to reset the password offline and Online. Step 1: Login to SBI online portal  Step 2: Under Quick Links - > Profile ->Click on My Profile Step 3: Under My Profile page - > Click on Forgot Pro...
Post a Comment
Read more