How to compare data on 2 different sheets in Excel file

How to compare data on 2 different sheets in Excel file Picture 1

To compare data on 2 different sheets but in the same Excel file, we have many different methods such as using VLOOKUP or using the COUNTIF function and combining with Conditional Formating to create different comparison effects. or no difference in data in two different sheets. But in this article of TipsMake.com, we will use COUNTIF and Conditional Formating methods to compare data.

First of all we have a column of data as follows in Sheet 1. It is a list of items on stock on day 1.

How to compare data on 2 different sheets in Excel file Picture 2

And on sheet two, we have a list of items to stock on day 2. Now we can use the COUNTIF function to calculate the number of duplicates of items compared to the first day.

How to compare data on 2 different sheets in Excel file Picture 3

Before you start writing the formula you need to limit the data to be compared in sheet 1 and give it a distinguished name.

Highlight the data range to select and then click on the name of the cell in the left corner of the sheet, enter a name for the range. The name you enter must be spaces without Excel or Excel will not be able to read the name of the data range.

How to compare data on 2 different sheets in Excel file Picture 4

Or you can also name the area by going to the Formulas ribbon on the toolbar and selecting Define Name in the Defined Names section .

How to compare data on 2 different sheets in Excel file Picture 5

Enter a name for the range in the Name box, then click OK to confirm the name.

How to compare data on 2 different sheets in Excel file Picture 6

Now that your data range in Sheet 1 has a custom name, you can call that range in a formula without having to write an address sign. This method is very convenient for using data on many different sheets.

The COUNTIF formula we need to use here to count duplicates for those two data columns is:

= COUNTIF (array of data to be counted, different conditions to count)

And for the example in the image, we have the function formula:

= COUNTIF (input1; A2)

With this formula we can count the number of times "School notebooks" appear in the first day of inventory data.

How to compare data on 2 different sheets in Excel file Picture 7

Next, you just need to copy the formula down by clicking on the lower right corner of the available formula box and dragging the mouse down.

How to compare data on 2 different sheets in Excel file Picture 8

As you will see a zero result shows that this item was never stocked in the first day.

The above steps can show you the specific number of items in the duplicated inventory but do not show the duplication right on the data column. We can do this by using the Conditional Formating function , creating a different format so that we can immediately see which item is the item without duplication when compared to the inventory data. first.

Highlight the array to compare, then go to the Home ribbon on the toolbar and select Conditional Formating located in the Styles section of this ribbon.

Click on the New Rule line .

How to compare data on 2 different sheets in Excel file Picture 9

Then the New Formatting Rule function dialog box will appear, select the last line of the list of rule types is Use a formula to determine which cells to format .

In the Format values ​​where this formula is true box (format the cells with the following formula), enter the following formula:

= B2 = 0

To explain a bit for this. Because we need to color data cells without duplicates, it means that in that data line the result in the " Duplicate with Day 1 " column we just filled out with the COUNTIF formula is 0.

So when we fill in the condition for this cell, we need to include the condition that finds the zero cells in that " Duplicate with Day 1 " column .

You leave B2 as a non-fixed parameter cell so that it can be modified with every row in the column.

Then don't forget to change the format for the cell with the appropriate condition by going to Format .

How to compare data on 2 different sheets in Excel file Picture 10

Change the format of the cell to suit the conditions in the different tabs of the Format Cells dialog box , with Font being the font change, Border to change the border of the cell and Fill to fill the background color for that data cell. Then click OK to confirm.

How to compare data on 2 different sheets in Excel file Picture 11

After looking at a series of settings, click OK to start changing the format for the cells matching the conditions set.

How to compare data on 2 different sheets in Excel file Picture 12

As you will see, the boxes that match the conditions set out are items that do not coincide with the first day of storage will be colored blue on your box.

How to compare data on 2 different sheets in Excel file Picture 13

If you want to directly change the format for cells with non-duplicated data, you can use the Excel COUNTIF formula directly into the Format values ​​where this formula is true .

How to compare data on 2 different sheets in Excel file Picture 14

From there, we have the formula for Format values ​​where this formula is true :

= COUNTIF (input1; A2) = 0

How to compare data on 2 different sheets in Excel file Picture 15

And the results received:

How to compare data on 2 different sheets in Excel file Picture 16

Note: If you change the formula from = 0 to> 0, the cells with duplicate data from the first date will have a format change. This part, TipsMake.com will leave you guys to try it out.

The article about how to compare data on 2 different sheets of Dexterity Software is here, thank you for following our article. Hope you can understand the knowledge we want to impart and successfully implement it for our data.