Question

Compare Excel tables - this is how it works

Answer 1

Compare Excel sheets with conditional formatting
1st step:
2nd step:
3rd step:
4th step:
5th step:
Manual comparison of Excel tables
1. Step:
2nd step:
3rd step:
Quick guide: formatting
Quick start guide: Manual

---

Don't waste time looking for small differences. You can easily let Excel compare your documents..

Comparing two Excel files, or comparing two sheets in the same file, can be tricky because an Excel workbook only displays one sheet at a time. This proves to be even more difficult when you have a lot of data to compare. Fortunately, however, there are some cool features in Excel that allow you to open two Excel spreadsheets and compare them easily.

• Compare Excel sheets with conditional formatting
• Manual comparison of Excel tables

Compare Excel sheets with conditional formatting

Using conditional formatting is particularly useful for large sheets of paper with lots of data. Changing the color or formatting of cells that have differences makes it much easier to spot those differences, even in tables with many rows and columns of data. Follow our step-by-step instructions or take a look at the brief instructions .

1st step:

If the two tables are in different files , select all the cells in the table to be compared with. You can do this by holding down the [Ctrl] key . Then copy the table with [Ctrl] + [C] and paste it into the second table as a new sheet . Both tables are in one workbook . You can skip this step if both tables to be compared are already in a workbook.

2nd step:

Now click on " Start " at the top and then select " Conditional Formatting ". Then go to the " New rule ... " entry in the menu .

3rd step:

Then, under " Rule Type " select " formula to determine the use to format cells from". Under " Rule Description " enter the following formula: = A1 <> Table 2 A1! . Pay attention to the name of the table you are comparing with in your workbook and, if necessary, enter the appropriate name in the formula instead of "Table2". Then click on the " Format ... " button .

4th step:

There you now select a desired color in the " Fill in " tab , in which all cells with differences are then stored. Confirm all open windows with " OK ".

5th step:

You should now see that all cells in the table that contain differences are highlighted in the color you selected.

Manual comparison of Excel tables

A quick and easy way to compare two worksheets is by visual comparison. Excel provides an easy way to visually compare two sheets of paper side by side. Follow our step-by-step instructions or take a look at the brief instructions ..

1. Step:

Open your spreadsheet and click " View " at the top . Then select " New Window ". You only need to do this if both tables are in one workbook . In the event that the Excel spreadsheets are in two different files , all you have to do is open both files in two windows.

2nd step:

Then select the " View " tab again . Here you can click on the button " Show side by side ". This places the two workbook windows side by side, each filling half of your screen.

3rd step:

In a window, select the sheet that you want to compare. If you scroll in this sheet in the window on one side, you will see how the sheet in the other window is scrolled at the same time. This means that you always see the same section of both worksheets.

Quick guide: formatting

1. Both tables must be in the same workbook . If so, continue reading in the next step. If not, mark all cells of the table to be compared with, then copy them with [Ctrl] + [C] and paste them into the second table as a new sheet.
2. Click on " Start " at the top , then select " Conditional Formatting ", then select " New Rule ... ".
3. Under " Rule type " select " Use formula to determine the cells to be formatted ".
4. Under " Rule description " then enter the following formula: = A1 <> Table 2 A1! . Pay attention to the table name and, if necessary, enter the name of your table in the formula instead of " Table2 ".
5. Then click on " Format ... ".
6. There you now select a desired color in the " Fill in " tab , in which all cells with differences are then stored.
7. Then confirm all open windows with " OK ".

Quick start guide: Manual

1. With your tables in a workbook , click the " View " button at the top, then select " New Window ". However, if your tables are in different files , all you have to do is open both files in two windows.
2. In the " View " tab you can now click on the " Display side by side " button.
3. The two workbook windows are then placed side by side, each filling half of your screen.

---