# 8 Excel Pivot Table Examples – How to Make a PivotTable!

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

The best way to understand a pivot table is to see one. Start with the following Figure. This figure shows a portion of the data we have used to create 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 the 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
• On 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)

## Creating/building 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 a pivot table is a better choice, a pivot table takes few seconds, doesn’t require a 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

## 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 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 the 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 the total amount of each AccType is calculated on the right side of the pivot table. At the same time, the 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 the amount opened in the Westside branch. The pivot table is in the “PT1” sheet. We have changed the sheet name to “PT1” after the creation of the 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 the Amount field in the VALUES area, the Branch field in the COLUMNS area, and the Date field in the ROWS area. The pivot table is in the “PT2” sheet. We have changed the sheet name to “PT2” after the creation of the pivot table.

Here is a screenshot.

Pivot Table 2.

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

To get this pivot table, we shall place the Weekday field in the ROWS area and the 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 the “PT3” sheet. We have changed the sheet name to “PT3” after the creation of the 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 the Amount field in the VALUES area, AcctType field in the COLUMNS area, and Branch field in the 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.

Pivot Table with count values.

The pivot table is in the “PT4” sheet. We have changed the sheet name to “PT4” after the creation of the 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% of 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 the 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.

Data are grouped in ranges.

### 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 to Summarize Values By⇒ Count. Your pivot table will look like this.

Data summarized by count

### 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.

Final Pivot Table

The pivot table is in the “PT5” sheet. We have changed the sheet name to “PT5” after the creation of the 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.

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.

### 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 the “PT6” sheet. We have changed the sheet name to “PT6” after the creation of the 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 the “PT7” sheet. We have changed the sheet name to “PT7” after the creation of the 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.

This pivot table uses three Filters.

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

Happy Excelling 🙂

Bank-accounts5.xlsx

#### Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1. great course I am on an exploration journey thanks

2. Hi, Kawser,
For PT8, the title asks for the savings account whereas the example is filtering for only checkings. Otherwise great tutorial.
Many thanks!
Yun

3. hi kawser, thank you for your work its wonderfull!! i learned a lot cheers! more power!

4. Good day Kawser,

Thank you a lot. Found what I was looking for.

Regards,
Zayniddin

5. Thank you very much for the exercise on Pivot Table. It was quite revealing and interesting. I learnt a lot from it.

• You’re most welcome. Glad to know that it helped you. Thanks for the feedback.

6. Great tutorial, thanks for your help.