Everything You Need to Learn on How to Create a Pivot Table From Multiple Sheets

Every data analyst desires to develop an interactive way of presenting critical insights to the clients, and pivot tables are the best for the desired job. Pivot tables are for the rescue when you sum up the data and answer critical business-related questions.

This article will give you a detailed walk-through to work with pivot tables by using the data from not one but multiple sheets 

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

What Is a Pivot Table in Excel?

An Excel Pivot table is a built-in interactive method to summarize vast data loads. Users can employ a PivotTable to calculate and analyze numerical data in detail with a high level of granularity and answer business-related queries about their business data.

Understanding the technicalities of pivot tables in excel now brings us to the practical part. Let us try to create a pivot table using multiple sheets.

How to Create a Pivot Table From Multiple Sheets in Excel?

Creating a pivot table is not as complicated as it sounds. Excel has had a Pivot Table Wizard since its early versions. We can use the same and implement a pivot table with ease in a step-by-step manner.

In this example, we are considering the superstore dataset. This data is divided into four varieties based on the geographical regions as Central, East, West, and South. The same data will be shown in the four different and segregated sheets below.

Central

Pivot_Table_From_Multiple_Sheets_1

East

Pivot_Table_From_Multiple_Sheets_2.

West

Pivot_Table_From_Multiple_Sheets_3.

Free Course: Introduction to MS Excel

Master the Fundamentals of MS ExcelEnroll Now
Free Course: Introduction to MS Excel

South

Pivot_Table_From_Multiple_Sheets_4

Now, let us use the shortcut ALT + D. This will activate the office access keys as shown below.

Pivot_Table_From_Multiple_Sheets_5

Now, try pressing the letter 'P' on the keypad. This will enable the Pivot Table Wizard, as shown below.

Pivot_Table_From_Multiple_Sheets_6.

Now, select the third option that reads as "Multiple Consolidation Ranges" and choose the "Pivot Table" option from the available options.

In the next step, you will find two options. Select the second option that reads as "I will create the page fields." Selecting the first will help you as the wizard will create a pivot table. The only drawback would be a slight compromise in customization. The Image looks as shown below.

Pivot_Table_From_Multiple_Sheets_7

You can see a window asking to select the data range in the next stage. Here I am selecting the range for the central region and using the add button to add the details to the wizard window. Similarly, we will add the ranges for all the sheets. The final wizard window will look like the image shown below.

Pivot_Table_From_Multiple_Sheets_8

Next, we shall manually add the fields to all the selected ranges by selecting the option of one-page fields option as shown below. The wizard allows up to four-page fields. Following the fields.

Pivot_Table_From_Multiple_Sheets_9.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

You'll be redirected to the pivot table wizard once again. Here, select the option of the new worksheet and click on the finish, as shown below.

Pivot_Table_From_Multiple_Sheets_10

A new pivot table is built successfully. Now, adjust the calculations. In this data, we found the calculation to be count; We needed Sum. 

Pivot_Table_From_Multiple_Sheets_11

So change it to SUM, and now you can see the exact sales data based on the region as shown below.

Pivot_Table_From_Multiple_Sheets_12

Also, the pivot table gives you an option of selecting the sales data to be displayed based on all or a particular region.

Pivot_Table_From_Multiple_Sheets_13

With that, we have reached the conclusion of this article on Creating a pivot table from multiple sheets in excel. 

Looking forward to a career as a Business Analyst? Check out the Business Analytics Certification Training with Excel and get certified today.

Next Steps

"Print Page Setup in Excel" can be your next stop. Page setup will help you preview or print your sales data as per your requirement and present it to your potential clients in an impressive manner.

Interested in business analytics and finding the right mentor? Simplilearn's business analytics has got you covered. This Business Analytics certification course offered by Simplilearn teaches you the basic concepts of data analysis and statistics to help data-driven decision-making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from available data to present your findings using executive-level dashboards.

If you find any queries, do let us know in the comments below, and our excel experts will get back to you at the earliest.

About the Author

Kartik MenonKartik Menon

Kartik is an experienced content strategist and an accomplished technology marketing specialist passionate about designing engaging user experiences with integrated marketing and communication solutions.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.