Tutorial Playlist

Excel Tutorial


Your One-Stop Solution to Know About Excel Worksheets

Lesson - 1

Your One-Stop Solution For Excel UserForms

Lesson - 2

The Best Guide on How to Send an Email in Excel

Lesson - 3

Your One-Stop Solution That Will Help You Learn How to Convert PDF to Excel

Lesson - 4

A One-Stop Solution for Acing the Excel MIS Report

Lesson - 5

All You Need to Know About Barcode in Excel

Lesson - 6

Your One-Stop Solution to Learn Mail-Merge in Excel and Its Implementation

Lesson - 7

A Comprehensive Guide on Excel Flash Fill

Lesson - 8

Slicers in Excel: The Ultimate Guide to Help You Design and Develop Excel Slicers

Lesson - 9

One-Stop Solution to Learn Everything About Excel Budget Template

Lesson - 10

All You Need to Learn About Match Function in Excel and Its Real-Time Implementation

Lesson - 11

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

Lesson - 12

The Major Fundamentals About XLookUp in Excel Along With Practical Examples

Lesson - 13

The Ultimate Guide to Learn How to Compare Two Columns in Excel

Lesson - 14

The Best Guide and Your One-Stop Solution to Master the Fill Series in Excel

Lesson - 15

What Is the DATE Function in Excel and How to Implement It?

Lesson - 16

Your One-Stop Solution to Designing and Implementing Hyperlinks in Excel

Lesson - 17

One-Stop Solution to Master Everything You Need to Know About Auto-Sum in Excel

Lesson - 18

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

Lesson - 19

A Perfect Guide for All You Need to Know About Data Formatting in Excel

Lesson - 20

The Perfect Guide to Learn How to Add Columns in Excel

Lesson - 21

The Perfect Guide That Will Explain to You How to Remove Page Break in Excel

Lesson - 22

The Perfect Guide to Explain to You How to Remove Blank Rows in Excel

Lesson - 23

The Perfect Guide to Learning How to Highlight Duplicates in Excel

Lesson - 24

One-Stop Solution Understand the Fundamentals of Indirect Function in Excel

Lesson - 25
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 

PCP in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
PCP 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.







Free Course: Introduction to MS Excel

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



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


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


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.


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.


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.


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.


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


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


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


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

Find Business Analytics with Excel in these cities

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