The Source Data being on one Worksheet allows you to create a Pivot Table. It is possible to create Pivot Table With Multiple Worksheets in the event that your Source Data is on multiple worksheets.
Create a Pivot Table from multiple worksheets
To create a Pivot Tables From Multiple Worksheets Let’s consider the situation of sales data of two different stores (Store#1 as well as Store #2) that are located on two distinct Worksheets.
The goal is to utilize the two Worksheets separately as the Source Data in the Pivot Table which we will to build in this instance.
1. Open the Excel File with the Source Data with multiple sheets.
2. Create a New Worksheet and label it as Pivot. This is where we’re will Create Pivot Table using Source data from several worksheets.
3. Click on any empty cell on the new Worksheet then press and hold the ALT+D keys. Then hit the P key two times to start this PivotTable Wizard.
4. On PivotTable and PivotChart Wizard, choose the Multiple Consolidation Ranges and then click the Next button.
5. On the next screen, choose the I will make the page fields option , and press Next.
6. On the next screen, select the Range Field Click on Store#1 worksheet and select the Data Range on this worksheet, and then click”Add button.
Next, enter then the Range Field again > click on the Store#2 worksheet and choose Data Range in this worksheet and then click on”Add.
7. Next, select the first data range from the the ‘All Ranges section. Next, enter the name of this Data Range in the field section.
NOTE: Type a descriptive name for the Data Range so that it make it easier for you to locate the Data Range in the table pivot.
The same way, select the second range of data in “All Ranges section. Type the name for this Data Range in the “Field section’, and click”Next.
8. On the next screen, select Finish to create the Pivot Table with data from several worksheets.
After your Pivot Table is generated, the next step is to alter and modify and format the Pivot Table to suit your needs for reporting.
2. Modify Pivot Table
In the majority of cases the default Pivot Table that is generated by Excel must be altered and formatted in accordance with requirements for reporting.
To alter to modify the Pivot Table click anywhere within the Pivot Table and you will immediately be able to see the Pivot Table Field list appearing.
Pivot Table Field list allows you to change your Pivot Table by dragging the Field List items.
If you’re brand new to Pivot Tables, it is important to test the Pivot Table Field List to find out the effects of dragging the field items from the list.
3. Format Pivot Table
To format to Format the Pivot Table, you will need to open Pivot Table Options.
1. Right-click on the Pivot table and select PivotTable Options from the dropdown menu.
2. On PivotTable options screen, you’ll see several tabs and a variety of options on each tab to format this Pivot Table.
Explore all options for formatting offered in the various tabs on the Table Options screen. Table Options screen.
