Pmloading623

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]

Learn the Excel keyboard shortcuts (MAC and PC) to use Pivot Tables. Includes a full description. Excel Pivot Table Shortcuts. Open Pivot Table Wizard. VBA Cheat Sheets. PDF 'Cheat Sheet' containing the most commonly used VBA Commands and syntax. Print the PDF or save it to your computer for easy reference! Learn the Excel keyboard shortcuts (MAC and PC) to use Pivot Tables. Includes a full description. Excel Pivot Table Shortcuts. VBA Cheat Sheets. Pivot Charts are connected to pivot tables and provide a visualization of the data in the pivot table. Since the two objects are connected, any changes made to the pivot table will be reflected in the pivot chart. This includes filters. When a filter is applied to the pivot table, the pivot chart will also be filtered.

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.

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

Download the working file from the link below:

An Introduction to Pivot Tables in Excel by Guest Author and Microsoft MVP, John MacDougall

Everyone deals with tracking income and expenses. It's a fact of modern day life, and if you don't track your money, you should.

Excel can be a great tool to track your money. The Income and Expense Template by Vertex42 is an example of a spreadsheet you can use to track where your money is coming from and where it is going.

One of the most basic tasks of managing your money is categorizing your expenses so that you know what you are spending your money on each month. In this post we'll take a look at how you can analyze and summarize your income and expenses using Pivot Tables!

This Article (bookmarks):

To follow along as we build a simple pivot table, download the file that we are using for the examples in this article:

Download the Example File (IncomeExpense-PivotTable.xlsx)

Watch the video we created to go along with this article!

What are Pivot Tables?

A Pivot Table is one of the most powerful and useful tools available for quickly summarizing data in a spreadsheet. The PivotTable feature was introduced in Excel 5 back in 1994, and has seen dramatic improvements in function and ease-of-use since then.

Pivot Tables are both fast and flexible. With pivot tables, you can easily filter, sort and summarize your data and turn thousands of rows of data into actionable insights.

How to Create a Pivot Table in Excel

Creating a pivot table is really simple!

(1) First, select the data you want to include in your pivot table:

In this example, our data is inside an Excel Table named Vertex42 and if we select any cell inside this table, Excel will know we want to use the whole table of data. Even if the data is not in a table, Excel will guess the range of data based on selecting a single cell of the data.

(2) Next, go to the Insert tab and press the PivotTable command.

This will open the Create PivotTable dialog box:

This menu allows us to choose the location of the data we want to analyze and where we want the resulting pivot table to live in the workbook.

(3) Because we already selected a cell inside our table, Excel has already populated the Table/Range field with the name of our table, so we don't need to change anything here.

(4) Next, choose where you want the new pivot table (New Worksheet or Existing Worksheet).

The default choice is to appear in a new worksheet. Excel will create a new worksheet that contains the pivot table. The other option is to choose the location in an already existing sheet and we can use the up arrow icon on the right of the location input box to do this. We will stick with the default option.

(5) When you are happy with the options, press the OK button to create the new pivot table.

Now we have a new blank pivot table:

It doesn't look like much now (because it's blank), but there is a lot we can do with it. If you'd like to learn just about everything pivot tables can do, check out my article 101 Pivot Table Tips! We'll be using a few of these tips to help analyze our income and expense data.

Pivot Table Tools

With the active cell inside the pivot table, you will see two new PivotTable Tools ribbon tabs labeled Analyze and Design:

These only show up when a pivot table is selected.

You should also see a new window pane docked on the right side of the worksheet called the PivotTable Fields list:

This window pane is the command center we'll be using to build and edit our pivot table.

If the PivotTable Fields window pane isn't visible, then you can right-click anywhere inside the pivot table and select Show Field List from the menu, as shown in the image below:

Building a Pivot Table

A blank pivot table isn't useful, so let's make something with it.

Adding fields to a pivot table is easy. Simply click-and-drag any of the fields listed in the top area into any of the Filters, Columns, Rows or Values areas.

(1) For our example, drag the Account field into the Rows area and both the Income and Expense fields into the Values area.

This will create a summary of the income and expenses for each account, like this:

(2) Remove the Account field from the Rows area by dragging it back to the field list or anywhere outside the PivotTable Fields pane.

(3) Drag the Category field into the Rows area to get a view of spending by category:

This example shows how with pivot tables we can easily slice and dice the data into many different views to summarize the data.

Adding a Calculated Field to a Pivot Table

Instead of showing the Sum of Income and Sum of Expense as separate columns, we might be more interested in the net transaction for each row of our data.

The net transaction is the income amount minus the expense amount. We can add this to our pivot table with a calculated field.

(1) With the pivot table selected, go to the Analyze tab and select the Fields, Items & Sets command then choose Calculated Field from the menu.

(2) In the Insert Calculated Field window, name the new field Net Transaction and add the formula Income - Expense. Tip: Double-click on any field name in the Fields area to use it in the formula.

(3) Press the OK button and the field is now listed in our PivotTable Fields window:

You can use this field just like any of the other fields in the data. You can drag it into the Values area of the pivot table, and you don't even need to include the fields it's based on.

Creating a Monthly Summary

A monthly summary is a common type of report we want to see when analyzing our spending. Summarizing spending by category and month will allow us to see how income and expenses vary from month to month and compare this to our budgeted amounts.

Pivot tables are ideal for analyzing date and time data. When adding a date or timestamp field into a pivot table, Excel will automatically group it into Months, Quarters and Years. Other grouping options are available too, like hourly and by minute.

If you add the Category to the Rows area, the Date to the Columns area and the Net Transaction to the Values area, you will get a nice monthly breakdown of spending by category, as shown in the image below.

The result is a table that allows us to easily compare our spending month-to-month. For example, we over spent our monthly entertainment budget of $300 in February.

Pivot Table Excel For Mac Cheat Sheet Excel

Note: If Excel doesn't automatically group the dates by month, you can right-click on any of the dates in the pivot table and select Group from the options. Then in the grouping options, select the group By Months option.

Show Values as Running Totals

Most bank statements will show the new account balance after each transaction. This is very useful for reconciling our spreadsheet with our bank statements.

If our original data doesn't show a running balance, we can create one easily with a pivot table.

(1) Add the Date field into the Rows area, the Account field into the Columns area and the Net Transaction field into the Values area.

This will show the net transaction amount by day and by account, but this is not yet a running balance:

Note: Excel might automatically group the date field into months, quarters and years. You can remove this grouping by right-clicking on any of the dates and selecting Ungroup from the menu.

The account balance is the opening balance plus any previous transaction amounts. So, you need a running total in order to see the account balance for any given day.

(2) Right-click anywhere on the net transaction field inside the pivot table and select Show Value As, then choose Running Total In, then select Date for the base field.

The pivot table now shows the account balance each day for each account:

Conclusions

In this post we explored what pivot tables are, how to create them and a bit about what they can do.

Excel Pivot Tables Cheat Sheet

We were able to quickly summarize our data by account or category without any formulas. It only required a few point-and-click steps.

We learned how to add new calculations into our pivot tables using calculated fields, which allowed us to analyze the net transaction amounts in our income and expense data. We then used a running total calculation inside our pivot table to find the account balances at any given day.

Pivot tables are incredibly powerful and useful tools. Make sure to add them to your toolbox.

About the Author

John MacDougall is a Microsoft MVP and former actuary who regularly shares his knowledge and passion for Excel and Power BI through his website HowToExcel.org. John eventually left the financial world to join the stimulating tech world where he worked on data analysis in advertising. He currently works as an independent Excel and Power BI consultant.