Now, add references to tables

Consolidating data and what-if analysis button

Consolidating Data Automatically Suppose you want your summary sheet to be updated automatically, whenever there are changes in the data. All the ranges of data have the same layout. Now, add references to tables. There are no blank rows or columns within each range.

Hence ensure that

The data source table have been grouped and summarized in the table you created. Hence, ensure that the column with the label Product is the first column and it contains the Product labels. In this post you will learn how to add together data from various data table.

This method is most effective when you're working in a single Excel file with several worksheets. There were individual data from all the consolidated table and file name. You might need to consolidate this data and present a product wise summary of sales from time to time. These could be in the same workbook or different workbooks. However, it is likely that the data is maintained separately for each region and is updated region wise.

The data source table have

Preparing Data for Consolidation Before you begin consolidating the data, make sure that there is consistency across the data sources. Each worksheet has same layout, representing the details of product, number of units, and amount. This is not an obstacle to their consolidation. Required Consolidate button located in the Data tab.

You need to consolidate the data product wise. Each range of data is in list format, with labels in the first row. Consolidating Data from Different Workbooks In the previous example, all the data that you need to summarize is in the same workbook. Share on Facebook Microsoft Excel features a data-consolidation function that allows several tables to be consolidated into a single summary report.

You'll see how to add up all the data in such a way as to form one summary table. The source of the data could be from one place, or several places.

In case the data sources are external, ensure usage of a predefined layout in the form of an Excel template. Another challenge could be that the data might be updated by other people from time to time.

As you can see it developed all the pluses on the left side of the table. You just want to summarize data, so we choose Sum. You can see that the data ranges appear worksheet wise in alphabetical order, in the box under All references. All the ranges of data contain similar facts. Consolidate data most likely to be useful in the preparation of reports or periodic data aggregation.