How to Consolidate Data in Excel
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 sheets, even from different Excel spreadsheets. In this Excel tutorial, you will learn how to add together data from various data tables.
Table of Contents
Data preparation
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.
Data consolidation
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 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.
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.
All data are summed up individually and have created a summary table.
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, and then on consolidate. Choose the function you’d like, and then click on the cells symbol.
Click on spreadsheet (sheet1).
Mark all the data, and then click expand.
Click Add.
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.
Leave a Reply