Consolidation In Excel

Data consolidation in Excel can be useful when you want to combine data from different table, even from different Excel spreadsheets. In this post you will learn how to add together data from various data table. I prepared three tables of sales.Data Consolidation TableYou’ll see how to add up all the data in such a way as to form one summary table.

For starters, it is noteworthy that the tables are not identical and there are even different sizes. This is not an obstacle to their consolidation. Consolidation of the sales table in Excel start with ribbons. Required Consolidate button located in the Data tab.

Concolidate button ribbon

Select the location where you want the summary table and click Consolidate. You get the following dialog box:

Consolidate dialog box

You can select up to 11 different functions for data consolidation. You just want to summarize data, so we choose Sum. The tables contain the label in the top row and left column, then select the appropriate check boxes in the dialog box. Select a check box with which you will see a link to the source data. This is a very useful option, which will facilitate analysis of data in the summary table.

Consolidate dialog box checks

Now, add references to tables. Click on the button and add each of the tables turn.

Consolidate references

Consolidation is ready. All data are summed up individually and have created a summary table. The result looks like this:

Data Consolidation

On the left side shows the digits 1 and 2 and pluses. We also see that the individual lines are not in sequence as if some rows were hidden. This is precisely the result of the creation of links to the source data. The data source table have been grouped and summarized in the table you created. 1 and 2 are different levels of detail table. After clicking on the two show up all the individual data including those from the source table. As you can see it developed all the pluses on the left side of the table.

Data consolidated

There were individual data from all the consolidated table and file name. If you do not need as this level of detail, you can develop only a plus, eg. sales data Salesman2.

Consolidation deal with

By consolidating data in a simple way you can work with data in Excel. Consolidate data most likely to be useful in the preparation of reports or periodic data aggregation.

Further reading: 
What-If Analysis
How Excel handles different data types?


You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell