In our earlier article, we discussed how we can do quick data analysis using Pivot Table functionality in Excel. In this article, we will discuss how we can consolidate data across multiple sheets and prepare quick reports with the help of Pivot Tables.
Let us quickly understand with the help of an example.
We have multiple data sheets showing revenues by regions. Each sheet has name of products on rows and months listed in the columns. We are asked to prepare a summary showing performance by countries.
Before starting to consolidate the data, we have to ensure that
- Each sheet has the same structure. Meaning, that apart from the data the number of rows, columns, column headers should be the same.
- There are no blank rows or columns in between
- There are no totals, subtotals in between.
- Each column has a header.
Once we ensure that all the above criteria are met, we can start consolidating the data.
If we have limited number of sheets, we can add up all the data to create a summary, but this is not the most efficient method. It requires lot of manual intervention and there are also chances of errors. Also, we cannot easily change the output once the consolidation is done as it would require lot of manual intervention.
Excel has an inbuilt feature which can be used to consolidate data in multiple sheets efficiently. It is the Pivot Table Wizard. To activate the Pivot Table Wizard, Press Alt & D. Pressing Alt & D activates the Office Access Key. Then Press P. This activates the Pivot Table Wizard.
With Pivot table wizard, we can create a data summary with Just 3 steps.
Step 1
First, it asks if we want to consolidate data using Microsoft Excel list or database, External data source or Multiple consolidation ranges.
We will select Multiple consolidation ranges, as all the data is present in multiple sheets in a single file.
Then it asks if we want to create a Pivot Table or a PivotChart Report.
We will select Create a Pivot Table.
Then Click on Next.
Step 2a
In the second step, it asks if we want to create a Pivot Table report that uses ranges from one or more sheets. Also, how many page fields are required.
Here we will choose the option, I will create the Page Fields.
Then Click Next.
Step 2b
This is the most important step.
Here we have to select the data range.
Click on range and then select the data range from the first sheet.
Then Click Add.
Repeat this step for all the sheets which has the data to be consolidated.
Once we select all the data ranges. It asks, how many page fields do we want? We will select the option 1.
Then, Select the ranges (one by one) in the Range section, and then give a name to them in Field one (as shown in the above screen shot).
One we give a name to all the data ranges, Click Next.
Step 3
In the third step it asks, where we want to put the pivot table report. Whether in a new worksheet or the existing worksheet?
We will select the first option (New worksheet) and then Press Finish. We get the Pivot table report in a new sheet.
We get a consolidated data Pivot table in a new sheet.
This pivot table has all the features which were covered in our previous article. Some of them are as follows.
- We can change the report layout by changing the selection of the Pivot table fields.
- We can change the number format
- Sort the products by totals
- Calculate the percent of total sales, or average sales based on the requirement
- Change the Pivot table design
- Remove grand totals and Pivot table headers.
- Use the Pivot table slicer to create dynamic outputs.
There are multiple other options available which can be explored for further refinements as needed.
This is how we can consolidate data in multiple sheets with just a few clicks for data analysis or preparing dynamic reports. In case you have any questions, please post in the comment section. Enjoy learning.
