Consolidating Data with the Pivot Table Wizard in Excel

Consolidating Data with the Pivot Table Wizard in Excel

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.

Share This Post:

23 thoughts on “Consolidating Data with the Pivot Table Wizard in Excel”

  1. […] Use the Pivot Table Wizard: Excel has an inbuilt feature called the Pivot Table Wizard that can efficiently consolidate data in multiple sheets. To activate the Pivot Table Wizard, go to the Data tab in the ribbon, click the dropdown arrow next to the PivotTable button, and select PivotTable Wizard. From there, you can choose the data range and the location to place the pivot table. […]

  1. 皇冠登1 January 22, 2024

    I value the article post.Thanks Again. Great.

  2. Java代写 January 23, 2024

    Major thankies for the article post.Much thanks again. Really Cool.

  3. Kobold ai January 24, 2024

    Thanks so much for the post.Really looking forward to read more. Really Great.

  4. home theater furniture January 25, 2024

    Thanks for the post.Much thanks again. Fantastic.

  5. litter box enclosure January 29, 2024

    I think this is a real great blog.Really looking forward to read more. Keep writing.

  6. properties in brahmanwada nagpur January 30, 2024

    I really enjoy the blog.Thanks Again.

  7. latest technology gadgets January 31, 2024

    Hey, thanks for the article.Really looking forward to read more. Want more.

  8. home decor items near me January 31, 2024

    Im obliged for the blog article.Really thank you! Awesome.

  9. esta usa singapore February 1, 2024

    Really informative blog.Much thanks again. Cool.

  10. canada work permit jobs February 2, 2024

    Really enjoyed this post. Great.

  11. ai meetings February 2, 2024

    Hey, thanks for the blog.Much thanks again. Will read on…

  12. Laser Particle Counter February 3, 2024

    I value the blog article.Much thanks again. Want more.

  13. Fortune Tiger February 3, 2024

    Very neat post.Thanks Again. Fantastic.

  14. liposuction February 4, 2024

    Thank you ever so for you post.Thanks Again. Awesome.

  15. hydrofoil board February 5, 2024

    wow, awesome article post.Really thank you! Cool.

  16. aiチャット February 6, 2024

    A big thank you for your blog article.

  17. leather bow dog collar February 7, 2024

    Say, you got a nice post.Thanks Again. Fantastic.

  18. Nainital hotel near lake February 8, 2024

    Fantastic blog.Really looking forward to read more. Keep writing.

  19. nsfw character ai February 8, 2024

    Major thankies for the article.Really looking forward to read more. Fantastic.

  20. underress ai February 18, 2024

    Thanks so much for the blog article. Awesome.

  21. Health-Conscious Materials February 21, 2024

    Hey, thanks for the article post.Really thank you!

  22. End packaging cartoning machine February 25, 2024

    Enjoyed every bit of your blog post.Really looking forward to read more. Much obliged.

Add a Comment

Your email address will not be published.