Dec 28, 2017 How to create a pivot table from multiple worksheets In a case where the data you want to summarize in this Pivot Table are in say 3 worksheets in the same workbook, a simple method will be to make use of the PivotTable and PivotChart Wizard.
![]()
In Excel 2011 for mac, a PivotTable is a special kind of table that summarizes data from a table, data range, or database external to the workbook. If you’re PivotTable aficionado, you will be in seventh heaven with the new PivotTable capabilities in Office 2011 for Mac. Here’s how to make a PivotTable:
You can change the column names, calculations, and number formats provided by the PivotTable Builder. There’s a little information button at the right end of each field name in the panels at the bottom of the PivotTable Builder. Click the information button to display the PivotTable Field dialog. The properties displayed are for the field name of the button you clicked:
When you select a cell in a PivotTable, look at the Ribbon to find the PivotTable tab, which you click to display all sorts of PivotTable tools. The PivotTable tab is for experts. PivotTable Ribbon offers additional formatting options and still more controls for your PivotTable, but it goes beyond the scope of this book. If you find PivotTables to be useful, then by all means explore the PivotTable Ribbon.
Having the Source Data on a single Worksheet makes it easy to Create Pivot Table. However, it is also possible to Create Pivot Table From Multiple Worksheets, if the Source Data is available in two or more worksheets.
Create Pivot Table From Multiple Worksheets
To Create Pivot Table from Multiple Worksheets, let us consider the case of Sales Data from two stores (Store#1 and Store#2) located on two separate Worksheets.
The task is to use these two separate Worksheets as Source Data for the Pivot Table that we are going to create in this example.
1. Open the Excel File containing Source Data in multiple worksheets.
![]()
2. Create a New Worksheet and name it as Pivot. This is where we are going to Create Pivot Table using Source data from multiple worksheets.
3. Click on any blank cell in the new Worksheet > press and hold ALT+D keys and hit the P key twice to fire up the PivotTable Wizard.
4. On PivotTable and PivotChart Wizard, select Multiple Consolidation ranges option and click on the Next button
5. On the next screen, select I will create the page fields option and click Next.
6. On the next screen, click in the Range Field > click on Store#1 worksheet > select Data Range in this worksheet and click on the Add button.
Next, click in the Range Field again > click on Store#2 worksheet > select Data Range in this worksheet and click on the Add button.
7. Next, select the first data range in ‘All Ranges’ section and type a Name for this Data Range in ‘Field’ section.
Note: Type a descriptive Name for Data Range, so as to makes it easy for you to identify the Data Range on the pivot table.
Similarly, select the second data range in ‘All Ranges’ section > type a Name for this Data Range in ‘Field’ section and click on the Next button.
8. On the next screen, click on Finish to generate a Pivot Table using Data from multiple worksheets.
Once the Pivot Table is generated, the next step is to modify and format the Pivot Table to suit your reporting requirements.
![]() 2. Modify Pivot Table
In most cases, the default raw Pivot Table as generated by Excel needs to be modified and formatted to suit reporting requirements.
To Modify the Pivot Table click anywhere within the Pivot Table and you will immediately see Pivot Table Field list appearing.
Pivot Table Field list allows you to modify the Pivot Table by dragging the Field List items.
If you are New to Pivot Tables, you need to play around with Pivot Table Field List to see what happens when you drag field list items.
3. Format Pivot Table
In order to Format the Pivot Table, you will have to open Pivot Table Options.
1. Right-click on the Pivot table and click on PivotTable Options in the drop-down menu.
2. On PivotTable options screen, you will see multiple tabs and various options within each tab to Format the Pivot Table.
Just go ahead and explore all the formatting options as available in different tabs of the Pivot Table Options screen.
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |