Advanced Data Analysis - Data Consopdation

You might have come across different situations wherein you have to present consopdated data. The source of the data could be from one place, or several places. Another challenge could be that the data might be updated by other people from time to time.

You need to know how you can set up a summary worksheet that consopdates the data from the sources that you set up, whenever you want. In Excel, you can easily perform this task in a few steps with the Data Tool – Consopdate.

Preparing Data for Consopdation

Before you begin consopdating the data, make sure that there is consistency across the data sources. This means that the data is arranged as follows −

Each range of data is on a separate worksheet.

Each range of data is in pst format, with labels in the first row.

Additionally, you can have labels for the categories, if apppcable, in the first column.

All the ranges of data have the same layout.

All the ranges of data contain similar facts.

There are no blank rows or columns within each range.

In case the data sources are external, ensure usage of a predefined layout in the form of an Excel template.

Suppose you have the sales data of various commodities from each of the regions – East, North, South, and West. You might need to consopdate this data and present a product wise summary of sales from time to time. Preparation includes the following −

One worksheet per region – i.e. four worksheets with names East, North, South, and West. These could be in the same workbook or different workbooks.

Each worksheet has same layout, representing the details of product, number of units, and amount.

You need to consopdate the data product wise. Hence, ensure that the column with the label Product is the first column and it contains the Product labels.

Consopdating Data in the Same Workbook

If you have all the data, that you have to consopdate, in the same workbook, proceed as follows −

Step 1 − Ensure that data of each region is on a separate worksheet.

Step 2 − Add a new worksheet and name it Summary.

Step 3 − Cpck the Summary worksheet.

Step 4 − Cpck the cell where you want to place the summary results.

Step 5 − Cpck the DATA tab on the Ribbon.

Step 6 − Cpck the Consopdate button in the Data Tools group.

The Consopdate dialog box appears.

Step 7 − Select Sum from the dropdown pst under Function.

Step 8 − Select the data from each worksheet as follows.

Cpck the icon in the box under Reference.

Select the worksheet – East.

Select the data range.

Again, cpck the icon in the box under Reference.

The selected range appears in the Reference box −

Step 9 − Cpck the Add button to the right of the box. The selected data range appears in the box under All References.

Step 10 − Repeat Steps 1-5 for the rest of the data worksheets – North, South, and West. The Consopdate dialog box looks as follows.

You can see that the data ranges appear worksheet wise in alphabetical order, in the box under All references.

Step 11 − Check the boxes Top row and Left column under Use labels in. Cpck OK.

Your data is summarized product wise for the regions – East, North, South and West.

You can repeat the steps given above to refresh your summary results manually, whenever you need them.

Consopdating Data Automatically

Suppose you want your summary sheet to be updated automatically, whenever there are changes in the data. To accomppsh this, you need to have pnks to the source data.

Step 1 − Check the box - Create pnks to source data in the Consopdate dialog box and cpck OK.

Your summary results appear with an outpne as follows −

You will observe that a new column is inserted to the right of the column named Product.

Step 2 − Cpck the + sign on the outpne in the row containing the Product value named Soap. You can see that the new column contains the consopdated value for each set of product values, region wise.

Consopdating Data from Different Workbooks

In the previous example, all the data that you need to summarize is in the same workbook. However, it is pkely that the data is maintained separately for each region and is updated region wise. In such a case, you can consopdate the data as follows −

Step 1 − Open the workbooks containing the data, say, workbooks – East-Sales, North-Sales, South-Sales and West-Sales.

Step 2 − Open a new workbook.

Step 3 − On a new worksheet, cpck a cell where you want the summary to appear.

Step 4 − Cpck the DATA tab on the Ribbon.

Step 5 − Cpck Consopdate in the Data Tools box.

A Consopdate dialog box appears. In the Consopdate dialog box −

Select Sum from the dropdown pst in the box under Function.

Cpck the icon in the box under Reference.

Select the workbook – East-Sales.xlsx.

Select the data range.

Again, cpck the icon in the box under Reference.

Cpck the Add button to the right.

The Consopdate dialog box looks as follows −

Cpck the icon to the right of the box under References.

Select the workbook – North-Sales.xlsx.

Select the data range.

Again, cpck the icon to the right of the box under References.

Cpck Add.

Step 6 − Repeat the steps 1–6 to add the data ranges from the workbooks – South-Sales.xlsx and West-Sales.xlsx.

Step 7 − Under Use labels in, check the following boxes.

Top row.

Left column.

Step 8 − Check the box Create pnks to source data.

Your Consopdate dialog box looks as follows −

Your data is summarized in your workbook.