How to compare files in Excel. Comparing data in Excel on different sheets. Showing book links

We have two order tables copied into one worksheet. It is necessary to compare data from two tables in Excel and check which positions are in the first table but not in the second. There is no point in manually comparing the value of each cell.

Compare two columns for matches in Excel

How to compare values ​​in Excel of two columns? To solve this problem, we recommend using conditional formatting, which quickly highlights items that are in only one column. Worksheet with tables:

The first step is to name both tables. This makes it easier to understand which cell ranges are being compared:

1. Select the FORMULAS - Defined Names - Assign Name tool.
2. In the window that appears, in the “Name:” field, enter the value – Table_1.
3. Use the left mouse button to click on the “Range:” input field and select the range: A2:A15. And click OK.

For the second list, perform the same steps, only give it a name – Table_2. And specify the range C2:C15 - respectively.

Helpful advice! Range names can be assigned more quickly using the names field. It is located to the left of the formula bar. Simply select ranges of cells, and in the name field, enter the appropriate name for the range and press Enter.

Now let's use conditional formatting to compare two lists in Excel. We need to get the following result:

Items that are in Table_1 but not in Table_2 will be displayed in green. At the same time, positions that are in Table_2, but not in Table_1, will be highlighted in blue.

The principle of comparing data between two columns in Excel

When defining the conditions for formatting the column cells, we used the COUNTIF function. In this example, this function checks how many times the value of the second argument (for example, A2) appears in the list of the first argument (for example, Table_2). If number of times = 0 then the formula returns TRUE. In this case, the cell is assigned the custom format specified in the conditional formatting options.

The link in the second argument is relative, which means that all cells of the selected range will be checked one by one (for example, A2:A15). For example, to compare two price lists in Excel, even on different sheets. The second formula works similarly. The same principle can be applied to various similar tasks.

Every month, the HR person receives a list of employees along with their salaries. It copies the list to a new sheet in the Excel workbook. The task is as follows: compare employee salaries, which have changed compared to the previous month. To do this, you need to compare data in Excel on different sheets. Let's use conditional formatting. This way we will not only automatically find all the differences in cell values, but also highlight them with color.

Comparing two sheets in Excel

A company may have more than a hundred employees, among whom some quit, others get hired, others go on vacation or sick leave, etc. As a result, it may be difficult to compare salary data. For example, the last names of employees will always be in different sequences. How to compare two Excel tables on different sheets?

Conditional formatting will help us solve this difficult problem. For example, let's take data for February and March, as shown in the figure:

To find changes on pay slips:

After entering all the conditions for formatting, Excel automatically highlighted in color those employees whose salaries have changed compared to the previous month.

The principle of comparing two data ranges in Excel on different sheets:

In certain conditions, the MATCH function is essential. Its first argument contains a pair of values ​​that should be found in the source sheet of the next month, that is, "March". A browseable range is defined as a pair of range values ​​defined by names. In this way, strings are compared based on two characteristics: last name and salary. For matches found, a number is returned, which is essentially true for Excel. Therefore, you should use the =NOT() function, which allows you to replace the TRUE value with FALSE. Otherwise, formatting will be applied to cells whose values ​​match. For each pair of values ​​that is not found (that is, a mismatch) &B2&$C2 in the range LastName&Salary, the MATCH function returns an error. The error value is not a boolean value. Therefore, we use the IFERROR function, which will assign a logical value for each error - TRUE. This facilitates the assignment of a new format only for cells without matching salary values ​​in relation to the next month - March.

Often the task is to compare two lists of elements. Doing this manually is too tedious, and the possibility of errors cannot be ruled out. Excel makes this operation easy. This tip describes a method using conditional formatting.

In Fig. Figure 164.1 shows an example of two multi-column lists of names. Using conditional formatting can make differences in lists obvious. These list examples contain text, but the method in question works with numeric data as well.

The first list is A2:B31, this range is called OldList. The second list is D2:E31, the range is called NewList. Ranges were named using the command Formulas Defined names Assign a name. It is not necessary to name the ranges, but it makes working with them easier.

Let's start by adding conditional formatting to the old list.

1. Select cells in a range OldList.
2. Select.
3. In the window Create a formatting rule select the item called Use formula
4. Enter this formula in the window field (Fig. 164.2): =COUNTIF(NewList;A2)=0.
5. Click the button Format and set the formatting to be applied when the condition is true. It is best to choose different fill colors.
6. Click OK.

Cells in range NewList use a similar conditional formatting formula.

1. Select cells in a range NewList.
2. Select Home Conditional Formatting Create a Rule to open a dialog box Create a formatting rule.
3. In the window Create a rule formatting select item Use formula to define the cells to be formatted.
4. Enter this formula in the window field: =COUNTIF(OldList;D2)=0 .
5. Click the button Format and set the formatting to be applied when the condition is true (different fill color).
6. Click OK.

As a result, names that are in the old list, but not in the new one, will be highlighted (Fig. 164.3). In addition, names in the new list that are not in the old list are also highlighted, but in a different color. Names appearing in both lists are not highlighted.

Both conditional formatting formulas use the function COUNTIF. It calculates the number of times a certain value appears in a range. If the formula returns 0, it means the item is not in the range. This way, conditional formatting takes over and the background color of the cell changes.

After installing the add-on, you will have a new tab with a function call command. When you click on the command Range comparison A dialog box appears for entering parameters.

This macro allows you to compare tables of any size and with any number of columns. Table comparisons can be made on one, two, or three columns at a time.

The dialog box is divided into two parts: the left one for the first table and the right one for the second one.

To compare tables you need to do the following:

• Specify table ranges.
• Place a checkbox (check mark/bird) under the selected range of tables if the table includes a header (title line).
• Select the columns of the left and right tables for comparison (if the table ranges do not include headings, the columns will be numbered).
• Specify the comparison type.
• Select an option for displaying results.

Table comparison type

The program allows you to select several types of table comparisons:

Find rows from one table that are missing from another table

When you select this type of comparison, the program looks for rows in one table that are missing in another. If you match tables based on multiple columns, the result will be rows that have a difference in at least one of the columns.

Find matching strings

When you select this type of comparison, the program finds rows that match in the first and second tables. Rows in which the values ​​in the selected comparison columns (1, 2, 3) of one table completely match the values ​​of the columns of the second table are considered to be matching.

An example of how the program works in this mode is shown on the right in the picture.

Match tables based on selection

In this comparison mode, opposite each row of the first table (selected as the main one), the data of the matching row of the second table is copied. If there are no matching rows, the row opposite the main table remains empty.

Comparing tables with four or more columns

If you lack the functionality of the program and need to compare tables by four or more columns, then you can get out of the situation as follows:

• Create an empty column in your tables.
• In new columns using the formula = CONNECT combine the columns you want to compare to.

This way you will end up with 1 column containing the values ​​of multiple columns. Well, you already know how to compare one column.