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.
76 thoughts on “Consolidating Data with the Pivot Table Wizard in Excel”
[…] 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. […]
… [Trackback]
[…] Read More here to that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Here you can find 83797 additional Info on that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Read More Info here to that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Find More on to that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Find More on that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Find More Info here on that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] There you can find 35215 additional Information on that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
… [Trackback]
[…] Read More here on that Topic: skillfine.com/consolidating-data-with-pivot-table-wizard-excel-guide/ […]
I value the article post.Thanks Again. Great.
Major thankies for the article post.Much thanks again. Really Cool.
Thanks so much for the post.Really looking forward to read more. Really Great.
Thanks for the post.Much thanks again. Fantastic.
I think this is a real great blog.Really looking forward to read more. Keep writing.
I really enjoy the blog.Thanks Again.
Hey, thanks for the article.Really looking forward to read more. Want more.
Im obliged for the blog article.Really thank you! Awesome.
Really informative blog.Much thanks again. Cool.
Really enjoyed this post. Great.
Hey, thanks for the blog.Much thanks again. Will read on…
I value the blog article.Much thanks again. Want more.
Very neat post.Thanks Again. Fantastic.
Thank you ever so for you post.Thanks Again. Awesome.
wow, awesome article post.Really thank you! Cool.
A big thank you for your blog article.
Say, you got a nice post.Thanks Again. Fantastic.
Fantastic blog.Really looking forward to read more. Keep writing.
Major thankies for the article.Really looking forward to read more. Fantastic.
Thanks so much for the blog article. Awesome.
Hey, thanks for the article post.Really thank you!
Enjoyed every bit of your blog post.Really looking forward to read more. Much obliged.
Im obliged for the blog article. Fantastic.
Really enjoyed this blog article. Cool.
Thank you for your blog.Really looking forward to read more. Really Cool.
I value the blog. Fantastic.
Thanks so much for the article post.Really looking forward to read more. Want more.
I am so grateful for your post.Much thanks again. Keep writing.
Very good article.Really thank you! Want more.
Thanks-a-mundo for the blog post. Fantastic.
Very neat article.Thanks Again. Cool.
I am so grateful for your article post.Thanks Again. Will read on…
Im thankful for the article post.Much thanks again. Really Great.
Really enjoyed this blog article.Thanks Again. Fantastic.
I truly appreciate this article post.Really looking forward to read more. Keep writing.
Looking forward to reading more. Great blog.Really looking forward to read more. Want more.
Enjoyed every bit of your post. Awesome.
A big thank you for your blog.Thanks Again. Fantastic.
I loved your blog. Keep writing.
I appreciate you sharing this blog. Fantastic.
Thank you ever so for you article.Thanks Again.
A round of applause for your blog article.Thanks Again. Want more.
I really enjoy the article post.Really looking forward to read more.
Enjoyed every bit of your blog.Really looking forward to read more. Much obliged.
Appreciate you sharing, great blog.Really looking forward to read more. Will read on…
Very informative article post.Much thanks again. Great.
Thanks a lot for the blog article.Really looking forward to read more. Will read on…
Im obliged for the article. Awesome.
Really informative blog.Much thanks again. Awesome.
Major thankies for the blog post.Really thank you! Awesome.
Thank you ever so for you article.Really thank you! Much obliged.
Fantastic blog post.Much thanks again. Keep writing.
Really appreciate you sharing this post.Really thank you!
Hey, thanks for the blog article.Much thanks again. Will read on…
The degree to which I admire your work is as substantial as your own sentiment Your sketch is refined, and the material you have authored is of an exceptional standard Nevertheless, you appear to be anxious that you may be on the verge of presenting something that could be considered questionable I believe you will be able to rectify this situation promptly
Thank you very much for sharing, I learned a lot from your article. Very cool. Thanks.
I’ve been browsing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all webmasters and bloggers made good content as you did, the net will be much more useful than ever before.
Hello there, I found your site via Google while searching for a related topic, your website came up, it looks good. I have bookmarked it in my google bookmarks.
When I originally left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and from
now on each time a comment is added I get 4 emails with the same
comment. There has to be an easy method you are able to remove me from that service?
Thanks a lot!
Hello my friend! I want to say that this post is amazing, nice written and include approximately all vital infos. I would like to see more posts like this.
hi!,I love your writing so much! share we communicate extra about your article on AOL? I require an expert in this area to unravel my problem. May be that is you! Having a look ahead to peer you.
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.
I have seen a great deal of useful points on your internet site about computer systems. However, I have the view that laptops are still more or less not powerful adequately to be a good option if you frequently do tasks that require plenty of power, just like video editing and enhancing. But for world wide web surfing, word processing, and the majority of other popular computer work they are just great, provided you can’t mind the little screen size. Thanks for sharing your ideas.
Thanks for ones marvelous posting! I actually enjoyed reading it, you are a great author.I will ensure that I bookmark your blog and will often come back at some point. I want to encourage that you continue your great posts, have a nice day!
One thing is that often one of the most prevalent incentives for utilizing your credit card is a cash-back as well as rebate provision. Generally, you will get 1-5 back for various purchases. Depending on the card, you may get 1 in return on most expenses, and 5 again on expenses made in convenience stores, gas stations, grocery stores plus ‘member merchants’.
Thanks for your article. It’s very unfortunate that over the last one decade, the travel industry has had to deal with terrorism, SARS, tsunamis, bird flu virus, swine flu, as well as the first ever entire global economic downturn. Through all this the industry has proven to be robust, resilient and also dynamic, acquiring new strategies to deal with trouble. There are usually fresh problems and opportunities to which the field must once more adapt and answer.
Thanks for the helpful write-up. It is also my opinion that mesothelioma has an incredibly long latency time, which means that signs and symptoms of the disease would possibly not emerge right up until 30 to 50 years after the initial exposure to mesothelioma. Pleural mesothelioma, that’s the most common form and has an effect on the area round the lungs, might cause shortness of breath, torso pains, and a persistent cough, which may lead to coughing up our blood.