Consolidation of data in Excel – how to perform, and what are the requirements for tables

Data Consolidation is a feature in Excel, thanks to which users have the opportunity to combine data from several tables into one, as well as combine sheets that are in the same or different files into one.

Official requirements for tables that are necessary to complete the consolidation

The option called “Consolidate” will not work if the tables do not meet the requirements. To successfully complete the data merging procedure, you must:

• check the table for empty rows/columns and delete them if there are any;
• use the same templates;
• follow the names of the columns, they should not differ.

Basic Consolidation Methods in Excel

When bringing together data from different reports, tables, ranges of the same type into one common file, you can use several different methods. The two main methods of summarizing data will be discussed below: by position and by category.

• In the first variant, the data in the original regions is in the same order in which identical labels are applied. Roll up by position to combine data from 3-4 sheets that are based on the same template, for example, financial statements are suitable for checking this method.
• In the second option: the data are in random order, but have identical labels. Consolidate by category to combine data from multiple worksheets with different layouts but identical data labels.

Important! This method has much in common with the formation of a pivot table. However, you can reorganize categories in a PivotTable. 

• There is also a third way to combine data – this is consolidation using formulas. True, it is rarely used in practice, due to the fact that it takes a lot of time from the user.

Step-by-step instructions for performing consolidation in Excel

Next, we will consider the easiest and fastest way to consolidate.

So, how to join multiple tables:

1. First you need to create a new sheet, after which the software will automatically add it on the right side. If necessary, you can drag the sheet to another location (for example, to the end of the list) using the left mouse button.
2. The added sheet, stand in the cell with which you are going to work. Then go to the “Data” tab, find the “Working with data” section, click on the item called “Consolidation”.
3. A small settings window will appear on the monitor.
4. Next, you will need to select the appropriate function to combine the data.
5. After selecting a function, go to the “Link” field by clicking inside it. Here you will need to select a range of cells one by one. To do this, first switch to the sheet with the first plate.
6. Then select the plate along with the header. Make sure everything is done correctly, then click on the “Add” icon. By the way, you can update / change the coordinates yourself using the keyboard, but this is inconvenient.
7. To select a range from a new document, first open it in Excel. After that, start the merging process in the first book and switch to the second one, select the appropriate sheet in it, and then select a certain part of the cells.
8. As a result, the first entry will be formed in the “List of Ranges”.
9. Return to the “Link” field, remove all the information that it contains, then add the coordinates of the remaining plates to the list of ranges.
10. Check the boxes next to the following functions: “Top Row Labels”, “Left Column Values”, “Generate Links to Source Data”.
11. Then click “OK”.
12. Excel will execute the procedure and create a new document according to the set parameters and the selected functions.

In the example, linking was chosen, so the output was grouped to help show/hide the detail.

More information about using ranges, adding and removing links

• To use the new range for data consolidation, you will need to select the “Consolidate” option, click on the “Link” field and select the range or insert a link. After clicking on the “Add” button, the link will appear in the list of ranges.
• To remove a link, select it and click on “Remove”.
• To change the link, select it in the list of ranges. It will appear in the “Link” field, where it can be updated. After the done manipulations, click on the “Add” button. Then remove the old version of the modified link.

Data consolidation helps to combine the necessary information that is present not only in various tables and sheets, but also in other files (books). The mixing procedure does not take much time and is easy to start using step-by-step instructions.