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.

1. Pivot Table Cheat Sheet Pdf
2. Pivot Table Excel For Mac Cheat Sheet Download

Create Pivot Table From Multiple Worksheets

CHEAT SHEET PIVOT TABLE. INSERTING A PIVOT TABLE Click anywhere your data source or Table and choose Insert PivotTable Alt N V T Alt F5 REFRESH A PIVOT TABLE. A pivot table is a tool that is part of Microsoft Excel (and other spreadsheet applications, like Google Sheets) that helps users not only to quickly view and analyze data in a more visual way, but also to just as easily change the arrangement of the data so that it can be seen from multiple perspectives.

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.

The PivotTable feature is perhaps the most important component in Excel. PivotTable is making one or more new table from a given data table.

The best way to understand pivot table is to see one. Start with the following Figure. This figure shows a portion of the data we have used creating the pivot tables in this chapter.

We shall create the Pivot Tables from this data table in this chapter.

Related: Excel Pivot Table Tutorials for Dummies [Step by Step]

Our example shows that data is in a table, but you can make pivot tables from any kind of data.The above table consists of new account information of a bank. The bank has three branches: Central, North Country, and Westside. The table has 712 rows. Each row represents a new account opened at the bank. The table has the following columns:

• The date the bank account was opened
• The day of the week the bank account was opened
• The opening amount
• The bank account type (CD, checking, savings, or IRA)
• Who opened the bank account (whether a teller or a new account representative)
• The branch at which the bank account was opened (Central, Westside, or North County)
• The type of customer (whether an existing customer or a new customer)

Table of Contents

• 1) What is the total amount of new deposits, broken down by account type and branch?
• 5) What’s the dollar distribution of the different account types?
• 6) What types of bank accounts do tellers open most often?

Creating a pivot table manually

In our sample file Bank-accounts.xlsx, our database worksheet is named “data”. This database contains a good amount of information. But in its current form, the data doesn’t reveal much to you.

These following questions, the bank’s management may want to know:

• What is the total amount of new deposits, broken down by account type and branch?
• What is the daily total new deposit amount for each branch?
• Which day of the week generates the most deposits?
• How many new bank accounts were opened at each branch, broken down by account type?
• What’s the dollar distribution of the different account types?
• What types of bank accounts do tellers open most often?
• How does the Central branch compare with the other two branches?
• In which branch do tellers open the most savings accounts for new customers?

You can sort the data and create formulas to answer these questions. But using pivot table is a better choice, pivot table takes few seconds, doesn’t require formula and produces a professional-looking report.

In addition, analyzing data with pivot tables makes less error than with creating formulas.

Read More: How to Modify an Excel Pivot Table

Pivot Table Examples

1) What is the total amount of new deposits, broken down by account type and branch?

Now we shall create a pivot table using the sample file to answer this question. Follow this process:

Step 1: Specifying the data range

If your data is in a worksheet range, just select any cell in the range. We select cell A2 in our “data” worksheet. Now choose Insert ➪ Tables ➪ PivotTable. The Create PivotTable dialog box will appear. Excel automatically guess your data range. For our this example, we are going to create our pivot table in a new worksheet. See the following screenshot:

In the Create PivotTable dialog box, you tell Excel where the data is and where you want the place the pivot table.

Step 2: Creating a blank pivot table

Click OK to choose the options as it is. Excel creates an empty pivot table and displays a PivotTable Fields task pane. Look at the following figure:

We shall use this PivotTable Fields task pane to build our pivot table.

Step 3: Laying out the pivot table

Now we shall work on the PivotTable Fields task pane. PivotTable Fields task pane has two parts: the upper part, where the field names reside, and the lower part, where you will place the upper part’s field names as per your necessity. In our example, the upper part of PivotTable Fields task pane holds Date, Weekday, Amount, AcctType, OpenedBy, Branch, Customer fields. The lower part has Filters, Columns, Rows, and Values area.

The following steps will create the pivot table:

1. Drag the Amount field into the Values area. The pivot table will display the total of all the values in the Amount column.
2. Drag the AcctType field into the Rows area. The pivot table will show now the total amount for each of the account types.
3. Now, drag the Branch field into the Columns area. The pivot table will show now the amount for each account type, cross-tabulated by branch. Observe closely. You will find that total amount of each AccType is calculated on the right side of the pivot table. At the same time, total amount opened in every branch is also calculated at the bottom of the pivot table.

Dragging the fields to the lower part of PivotTable.

The following figure gives us our desired Pivot Table. From this Pivot Table, we can find out easily grand total of amount opened in Westside branch. The pivot table is in “PT1” sheet. We have changed the sheet name to “PT1” after the creation of pivot table.

The pivot table is showing the summary of our data.

Read More: How to Use Sort and Filter with Excel Table

2) What is the daily total new deposit amount for each branch?

This time we shall place Amount field in VALUES area, Branch field in COLUMNS area, and Date field in ROWS area. The pivot table is in “PT2” sheet. We have changed the sheet name to “PT2” after the creation of pivot table.

Here is a screenshot.

3) Which day of the week generates the most deposits?

To get this pivot table, we shall place the Weekday field in ROWS area and Amount field in the VALUES area.

We want to use conditional formatting to the data range B4: B9 to make you understand easily which day is making more deposits. To do this select cell range B4: B9 and choose Home ➪ Style ➪ Conditional Formatting ➪ Data Bars ➪ Gradient Fill ➪ Green Data Bar.

The pivot table is in “PT3” sheet. We have changed the sheet name to “PT3” after the creation of pivot table.

Using conditional formatting, we have made this pivot table more understandable.

4) How many new bank accounts were opened at each branch, broken down by account type?

To get this pivot table, we shall place Amount field in VALUES area, AcctType field in COLUMNS area, and Branch field in ROWS area.

You will get a pivot table, but this one shows the total amount of deposits, broken down by account types and branch like our first created pivot table. We have to change some options to get our required one.

Right-click on any value of the pivot table and then use the following figure to change the option.

Right-click on any value in the pivot table and select Summarize Values By ⇒ Count

After selecting the Count option, our pivot table will look like this.

The pivot table is in “PT4” sheet. We have changed the sheet name to “PT4” after the creation of pivot table.

Read More: Data appropriate for a pivot table

5) What’s the dollar distribution of the different account types?

Dollar distribution means, say you may want to know how many accounts were opened in the range of 1-5000. In our example, total 712 accounts were opened, among them, 253 accounts were opened in the range of 1-5000 range. In percentage, we can say about 35.53% accounts were opened in 1-5000 dollar range.

We shall create a pivot table showing all the dollar distributions.

Step 1: Grouping the amount values

At first place the Amount field in the rows section at the bottom of the PivotTable Fields task pane. This will create a figure like this. Right-click on any value in the pivot table, a shortcut menu will appear.

When Amount data is placed in Rows section, data is placed ungrouped. To make data in the group, click the Group option as per this image.

Select Group from the options, Grouping dialog box will appear. Enter 1, 100000, and 5000 in three fields respectively.

Grouping data with the help of the Grouping dialog box.

Click OK, your pivot table will look like this.

Step 2: Counting accounts

Place the Amount field again in the Values section. The pivot table will look like this.

Pivot table summarized as Sum.

We have to summarize our pivot table by Count. To do this, right-click on any values, a shortcut menu will appear and choose Summarize Values By⇒ Count. Your pivot table will look like this.

Step 3: Dollar distribution in %

Repeat what we have done in step 2. Another same column will be added. Click any cell in the last column and choose Shows Values as ⇒ % of Grand Total. The table will look like this.

Pivot Table is showing in the percentage of Grand Total.

Change the headers name to Amount, Count, and % of Grand Total respectively. This is your final pivot table.

The pivot table is in “PT5” sheet. We have changed the sheet name to “PT5” after the creation of pivot table.

Read More: Reverse Pivot Tables – Unpivot Summary Data

6) What types of bank accounts do tellers open most often?

To find out what types of bank accounts tellers open most is simple.

Step 1:

Place OpenedBy field in the FILTERS area PivotTable Fields at first.

Step 2:

Now place the AcctType field in the ROWS area in the PivotTable Fields task pane.

Step 3:

Now place the Amount field in the VALUES area in the PivotTable Fields task pane.

Step 4:

Repeat step 3.

Step 5:

Right-click on any value in the last column and choose Shows Values as ⇒ % of Grand Total from the shortcut menu.

Pivot Table Cheat Sheet Pdf

Step 6:

Change the header name of the last two columns to Accounts and PCT respectively.

Step 7:

Select cell range C4: C7 and apply this conditional formatting: Home ➪ Style ➪ Conditional Formatting ➪ Data Bars ➪ Gradient Fill ➪ Green Data Bar. Click OpenedBy drop-down list and choose Teller.

Final Pivot Table we get

The pivot table is in “PT6” sheet. We have changed the sheet name to “PT6” after the creation of pivot table.

Read More: What is a Pivot Table in Excel – Make a Pivot Table Manually!

7) How does the Central branch compare with the other two branches?

To achieve this pivot table, we shall learn how to combine two columns into a pivot table.

• Drag the AcctType field is in the Rows section.
• Drag the Branch field is in the Columns section.
• The Amount field is in the Values section.

You will get a pivot table like this.

Pivot Table when dragged the above fields in the bottom part of the PivotTable Fields task pane

Now select both North County and Westside columns and right click and choose Group from the shortcut menu. This will combine these two branches into a new category (Group 1). Grouping also creates a new field in the PivotTable Fields task pane. In our case, the new field is named Branch2 in the PivotTable Fields. You can change the label in the pivot table to Other Branches.

After grouping the North County and Westside branches, you can now easily compare between the Central branch and the other two branches. See the following figure.

Pivot table when grouped two branches and transformed into one.

We want to add a chart for easy comparison. Choose PivotTable TOOLS ⇒ ANALYZE ⇒Tools ⇒ PivotChart ⇒ Clustered Column chart. We have got our desired Pivot Table. Look at this.

This pivot table and pivot chart compare the Central branch with the other two branches combined.

The pivot table is in “PT7” sheet. We have changed the sheet name to “PT7” after the creation of pivot table.

8) In which branch do tellers open the most savings accounts for new customers?

To get this pivot table, follow these steps:

• Drag down the Customer field is in the Filters section.
• Place the OpenedBy field is in the Filters section.
• Now place the AcctType field is in the Filters section.
• Drag the Branch field is in the Rows section.
• Place the Amount field is in the Values section, summarized by Count.

We have to use three REPORT FILTERS in this pivot table. We have filtered the Customer Field to show only New, OpenedBy field to show only the Teller, and the AcctType field to show only Checking. See the following figure.

The pivot table is in “PT8” sheet. We have changed the sheet name to “PT8” after the creation of pivot table.

Happy Excelling 🙂

Download Working File

Pivot Table Excel For Mac Cheat Sheet Download

Download the working file from the link below: