Comparing Columns in Microsoft excel is much often feature that is used while managing database. We have many ways to compare the columns in excel and can be used as per the requirement. We will be learning them in very easy and simple steps in below section..
Here we have two data sets and need to match the names available in column A with column B and identify the values which are matching or not matching with each other. We will be using a syntax to get the desired output.
Syntax: =Cell1=Cell2
Here Cell1 and Cell2 are the data cell range that we are comparing with each other. This is very simple and gives results in “TRUE” and “FALSE” format. “TRUE” means that value is matching with each other, whereas “FALSE” says value is not matching.
Things To Remember
We need to remember that above function is not “Case Sensitive”, means “John” and “JOHN” will be considered as cells are matching with each other and output of function will be “TRUE”
Column C represents the output, where values in Column A and B are compared. We can see the output, in cell C2 shows “True” i.e. “Robert” is matching in cell A2 and B2.
Also, name “John” (cell A3) and “JOHN” (cell B3) are considered as matched and output is “TRUE”. Values in cell A4 (Bill) and B4 (Don) are not matching that is why output is “FALSE”
Syntax: =(Cell1=Cell2) is not Case Sensitive means “John and JOHN” will be considered as “TRUE”. However, if we need to compare the database to exact match means, name “John” and “JOHN” should be considered as two then we need to follow equal function along with “Exact”
Syntax: =Exact(A2,B2)
Here “Exact” excel function is used to compare the exact values, “A2, B2” are the cells comparing with each other. Output of the syntax will be “TRUE or FALSE”. “TRUE” means that value is matching whereas “FALSE” says that value is not matching.
Things To Remember
We need to remember that above function is “Case Sensitive”, means “John” and “JOHN” will be considered as “FALSE”
Column C represents the output, where values in Column A and B are compared. We can see the output, in cell C2 shows “FALSE” because “John (cell A2)” and “JOHN (cell B2)” are considered as not matching
Sometimes we need to customize the output, means instead of “TRUE” or “FALSE” if we need any other output to be shown then we need to use the equal function along with “IF”.
For example, if we need “Name is Matching” text instead of “TRUE” and “Name is not Matching” text instead of “FALSE” then we need to follow below:
Syntax: =IF(A2=B2,"Name is Matching", "Name is not Matching")
Here “IF” excel function is used to get the customized result. We are comparing cells “A2, B2” and if both cells are matching then output will be “Name is Matching” whereas if cells are not matching then output will be “Name is not Matching”
Things To Remember
We need to remember that we can customize the output to any text, means instead of “Name is Matching, or Name is not Matching” we can mention like “Result is Matching, or Result is not Matching”
Column C represents the output, where values in Column A and B are compared. We can see the output, in cell C2 shows “Name is Matching” because “Robert (cell A2)” and “Robert (cell B2)” are matching.
We can also highlight the duplicate values in the database using Conditional Formatting. This is very effective option to bring the duplicate values out within few steps. We can choose any color and formatting to highlight the values of the database. Follow the below steps to explore:
> Select the Data Range:
> Click to Hone Menu -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values..
> Select to “Duplicate” and values with, choose the appropriate formatting of your choice. Default option is selected, i.e. “Light Red Fill with Dark Red Text”
> Click to “OK”
Report will be highlighted as above. Results can be seen that all the duplicate values in the Selected Data Range is highlighted to the Formatting provided.
We can also highlight the unique values in the database using Conditional Formatting.
Follow the similar steps as “Duplicate” values, just select “Unique” in the below option provided
Click to “OK”
Report will be highlighted as above. Results can be seen that all the Unique values in the Selected Data Range is highlighted to the Formatting provided.
To subtract numbers in Excel, follow these steps:
Start by typing an equal sign (=) in the cell where you want the result.
Enter the first number or cell reference you want to subtract from.
Type a minus sign (-).
Enter the second number or cell reference you want to subtract.
Learn how to insert and use checkboxes in Excel. Find out how to change text value, link check boxes to cell and formatting of check boxes.
How to Get RGB Codes of a Color? Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this through…
If you need to keep This quick guide explains what Excel circular references are and why you should be careful when using them. You’ll also learn how to check for, find, and remove circular references in Excel worksheet.a circular reference, it will show you how to turn on and use circular formulas safely
You tried to enter a formula in your Excel sheet, but it’s not working. Instead, Excel is showing you a message about a circular reference. Is that why you’re here?
Thousands of people run into this issue every day because they accidentally make an Excel formula try to calculate its own cell. When this happens, Excel gives the following error message:
Unhide Cells in Excel means you’ve probably hidden a row, column, or worksheet before to focus on important data. But what if you need to see those hidden parts again? Don’t worry—Excel makes it easy…
In Excel, a line break lets you finish one line of text and begin another within the same cell. It helps create separate lines of information without needing to move to a different cell.
To subtract numbers in Excel, follow these steps:
Start by typing an equal sign (=) in the cell where you want the result.
Enter the first number or cell reference you want to subtract from.
Type a minus sign (-).
Enter the second number or cell reference you want to subtract.
Learn how to insert and use checkboxes in Excel. Find out how to change text value, link check boxes to cell and formatting of check boxes.
How to Get RGB Codes of a Color? Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this through…
If you need to keep This quick guide explains what Excel circular references are and why you should be careful when using them. You’ll also learn how to check for, find, and remove circular references in Excel worksheet.a circular reference, it will show you how to turn on and use circular formulas safely
You tried to enter a formula in your Excel sheet, but it’s not working. Instead, Excel is showing you a message about a circular reference. Is that why you’re here?
Thousands of people run into this issue every day because they accidentally make an Excel formula try to calculate its own cell. When this happens, Excel gives the following error message:
Unhide Cells in Excel means you’ve probably hidden a row, column, or worksheet before to focus on important data. But what if you need to see those hidden parts again? Don’t worry—Excel makes it easy…
In Excel, a line break lets you finish one line of text and begin another within the same cell. It helps create separate lines of information without needing to move to a different cell.
To subtract numbers in Excel, follow these steps:
Start by typing an equal sign (=) in the cell where you want the result.
Enter the first number or cell reference you want to subtract from.
Type a minus sign (-).
Enter the second number or cell reference you want to subtract.
Learn how to insert and use checkboxes in Excel. Find out how to change text value, link check boxes to cell and formatting of check boxes.
How to Get RGB Codes of a Color? Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this through…
If you need to keep This quick guide explains what Excel circular references are and why you should be careful when using them. You’ll also learn how to check for, find, and remove circular references in Excel worksheet.a circular reference, it will show you how to turn on and use circular formulas safely
You tried to enter a formula in your Excel sheet, but it’s not working. Instead, Excel is showing you a message about a circular reference. Is that why you’re here?
Thousands of people run into this issue every day because they accidentally make an Excel formula try to calculate its own cell. When this happens, Excel gives the following error message:
Unhide Cells in Excel means you’ve probably hidden a row, column, or worksheet before to focus on important data. But what if you need to see those hidden parts again? Don’t worry—Excel makes it easy…
In Excel, a line break lets you finish one line of text and begin another within the same cell. It helps create separate lines of information without needing to move to a different cell.