How to Compare Lists Using Data Consolidation in Excel - Step by Step

What is List Comparison Using Data Consolidation?

Excel data consolidation allows for many functions, we could say that the limit is solely on the ingenuity of the user of the tool. One of the most interesting functions that data consolidation allows us is to compare lists or sheets with different elements.

This way you will be able to check many aspects of your table, for example if there is duplicate data that you can remove later in Excel. Also with consolidation you can see if you have data that only appears in one list or the other, and so on. If you want to know more, read the following guide carefully.

How to Compare Lists Using Data Consolidation in Excel - Step by Step" src="/images/posts/a5e8d41d9c2d8a1efd7ee5f3c0ee8a78-0.jpg">

How to Compare Lists Using Data Consolidation in Excel - Step by Step

Compare the lists using the data consolidation found in Excel it is a very useful trick for different circumstances. Note that we recommend that you learn how to sum cells from different sheets in Excel, for this and many other tutorials. Without further ado, read the following guide to compare the list using consolidation:

Part one

1. The first step is to prepare the environment to locate the consolidated data. Assuming you have two datasheets, it will be need to create one third to locate the consolidation list.
2. Performing the previous step will ensure that you get a tidy and easy-to-read table. Having prepared the table correctly, we can now begin the submission process of the consolidated list in our Excel document.
3. The first thing to do is to give a value to each of the data on the different sheets. Assuming you have two datasheets.
4. It would be cheaper use numbers to represent each of the lists, for which you can use the Extract function in Excel, if you have the right ingenuity.
5. It is recommended to use the number 1, 2, 3, etc., in this way the reading of the table will be much easier.
6. For the name of each of the items on each sheet, the first thing to do is locate on the first datasheet. Next to each of the data, add the number you selected, which as we said before, we recommend 1 if it's the first list.
7. Repeat the procedure for the second data sheet.

How to Compare Lists Using Data Consolidation in Excel - Step by Step" src="/images/posts/a5e8d41d9c2d8a1efd7ee5f3c0ee8a78-1.jpg">

Part Two

1. Go to the sheet that has been assigned to you for i Consolidated data, once you are there, tap on the data section, located at the top of the screen.
2. Among the many options present, you have to click on Consolidate.
3. All the options related to this powerful option will be displayed on the screen, in our case the section Function should stay in Sum.
   In the reference or data section, you need to add the values ​​of the document sheets.
4. To do this, go to the first sheet with the data and drag both the data and the respective number assigned to each of them. Once you've done that, click on the Add option.
5. You need to repeat the procedure with the second sheet, i.e. select all the data and click on the option Add.
6. In our case, since we just want to make a comparison, the only box you need to activate is Left column, then click the accept option.
7. Once this is done, the data comparison of the two sheets will be reported on the sheet designated for consolidated data.
8. Those that appear alone with the number 1, are those that are present only in the first list, while those that appear with the number 2, are those that are in the second list. Those that appear with the number 3 are instead those present in each list.

Using the above process you can compare very quickly all the data present in different Excel sheets. This is especially useful for tables with repeated or incorrect data.