Consolidating data in Excel allows you to combine data from multiple worksheets or workbooks into a single worksheet, so you can summarize and analyze the data in one place. You can consolidate data in Excel by using one of three methods: SUM, AVERAGE, or COUNT.
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. Make sure each worksheet or workbook has the same structure and layout. Ensure that each worksheet or workbook contains the data you want to consolidate.
You'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.
Select the location where you want the summary table and click Consolidate. You get the following 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.
Now, add references to tables. Click on the button and add each of the tables turn. In the Consolidate dialog box, select the data type you want to consolidate: SUM, AVERAGE, or COUNT.
In the Reference box, enter the range of cells for each worksheet or workbook you want to consolidate. If you are consolidating data from multiple worksheets or workbooks, repeat this step for each worksheet or workbook.
Consolidation is ready. All data are summed up individually and have created a summary table. The result looks like this:
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.
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.
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.
Combining Data from Multiple Sheets into one file
Combining data from multiple spreadsheets is extremely useful. There are different motivations to its usefulness, and with some steps, it would be done.
But first, you'd need to have data in multiple spreadsheets, and they would look something like this:
Click Data (1), and then on consolidate (2).
Choose the function you'd like (1), and then click on the cells symbol (2).
Click on spreadsheet (sheet1).
Mark all the data (1), and then click expand (2).
Note: repeat steps for the other spreadsheets. Then remember to check on where you would like to have the labels. You should also make sure that the spreadsheet you are following these steps on is empty.
By using the Consolidate feature in Excel, you can quickly and easily combine data from multiple worksheets or workbooks into a single worksheet, allowing you to summarize and analyze your data in one place.
Note: If you need to update the source data, you will need to repeat the Consolidate process, as the consolidated data is static and does not automatically update when the source data changes.
You can also use PivotTables to consolidate data in Excel. PivotTables allow you to quickly summarize and analyze large amounts of data, and they automatically update when the source data changes. To create a PivotTable, go to the Insert tab, select PivotTable, and follow the prompts to create your PivotTable.
Further reading: What-If Analysis How Excel handles different data types?