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
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
East
West
South
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.
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.
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 Analyst 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.
