How to compare data on 2 Excel columns
Excel functions are all basic functions on Excel, such as COUNTIF functions. The COUNTIF function is used to count cells that meet certain conditions within the range of conditions specified. With COUNTIF function, users can compare data between 2 columns to find different data. The following article will show you how to compare data between 2 columns with the COUNTIF function.
- How to combine Sumif and Vlookup functions in Excel
- How to automatically display names when entering code in Excel
- How to use Vlookup function in Excel
Instructions for using COUNTIF Excel function
For example, we have a list of import items on Excel as below.
Step 1:
First, black out the data in column 1 (Summarize 1) , remove the title and place your cursor in the Name Box above to write any unsigned name, write as shown and press Enter.
Step 2:
Continue to do the same with column 2 (Summary 2).
Step 3:
In the column between the two columns of data, click in the empty box above and then enter the formula = COUNTIF (danhsach2, A5) and press Enter. We will begin to compare the data of the list column1 with the name column column2, starting from cell A5 of list1.
Note that depending on the Excel version, we use the mark; or, in the formula. If you report an error when entering, please change it again.
Step 4:
The result will be 1 as shown below. That means there will be 1 Raw Fabric data that matches the column 2.
Scroll down below we will get the complete result, with the comparison of the data in column 1 with column 2. The number 1 will be the same value, such as the Fabric in column 1 and column 2. The number 0 is The value does not match, only Lace fabric in column 1 is not available in column 2.
Step 5:
Next click on the Name box and then click on the triangle icon and select list1 .
Click on the Conditional Formatting item and then select New Rule. . in the drop down list.
Step 6:
Display the new New Format Rule dialog box, click Use a formula to determine which cells to format .
In the box below we enter the formula = COUNTIF (danhsach2, A5) = 0 . This means that you will find cells with a value of 0 in the list 1. Continue clicking the Format button .
In the Format Cells dialog box, click the Fill tab and select the color to identify, click OK.
In the Preview section we will see the highlighted text you selected, click OK.
The results of the values in column 1 not found in column 2 will be marked as shown.
Step 7:
With the second list, users also click on the Name box to select list 2 . Then click on Conditional Formatting and select New Rule .
Next, enter the formula = COUNTIF (list1, C5) = 0 to find the value 0 in the second column. Select the color and click OK.
The result will be as shown below. The different data on both tables will be colored to make it easier for statistics and inventory.
So with COUNTIF function in Excel we will know the number of data is the same when comparing between 2 columns. Data that does not match will be marked in the document.
See more:
- 2 ways to separate column Full and Name in Excel
- How to insert watermark, logo sink into Excel
- Instructions for separating column content in Excel
I wish you all success!
You should read it
- How to use Hlookup function on Excel
- How to use the SUMIF function in Excel
- How to use the Search function in Excel
- How to compare data on 2 columns in an Excel file
- Instructions on how to use the Dmax function in Excel
- How to use the SUBTOTAL function in Excel
- How to Compare Data in Excel
- Basic Excel functions that anyone must know
May be interested
- How to combine 2 columns Full name in Excel does not lose contentin excel, to be able to merge content in 2 columns into a single column without losing content, we need to use calculation functions.
- Quickly split data with Text to Columns in Excelthere are many types of data that need to be filtered and separated, which may require a lot of complicated functions. fortunately in excel has integrated text to columns feature
- How to delete, add columns in Exceladding columns in excel or deleting columns is a basic operation and very simple in excel when we process excel tables.
- How to convert data from columns to rows in excel is extremely simpleis there a way to turn a column into a row instead of having to retype the data? very simple with the following 2 ways, tipsmake with you through the article below
- How to delete rows and columns in Excel with mouse or keyexcel is an important and indispensable tool in the work process. however, editing data or deleting rows and columns in excel can be quite complicated for some new users. therefore, to help make work more efficient, we will introduce to readers how to delete rows and columns in excel using the mouse or keys in detail in the article below.
- How to split first and last names into 2 columns in Excelhow to split first and last names into 2 columns in excel. in the process of compiling data, sometimes you need to split the first name column into two first and last column columns to serve different purposes, for example, to sort the list by alpha b. thuthuatphanmem.vn will be directed.
- How to Sort Excel Columns Alphabeticallymicrosoft excel is a great tool for organizing data. this article will introduce to you a simple but extremely useful feature, which is sorting data in alphabetical order.
- How to fix columns and headlines in Excelwhy is it necessary to fix lines in excel. for those who often work with excel files with a large number of records, it is impossible not to use it. the following article details how to fix columns and rows in excel 2013.
- How to Add Columns in a Pivot Tabletoday's tipsmake will show you how to create and insert new columns into a pivot table on microsoft excel using the pivot table tool. you can change existing rows, fields, or data into columns, or create columns of calculated data using custom formulas.
- Types of data hiding in Excel - Hide pictures, graphs, rows, columnstipsmake introduce to you part 3: how to hide photos, graphs, drawings, lines, columns in excel