What is a Pivot Table in Excel? This is the answer we shall search for in this article. I’ll answer this question by making a Pivot Table from fictitious data.
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 the 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.
Our example shows that data is in a table, but you can make pivot tables from any kind of data.
This 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 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 types of bank accounts do tellers open most often?
- How does the North County 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.
Creating the first pivot table: What is the total amount of new deposits, broken down by account type and branch?
Now we shall create this pivot table using the above file. 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:
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:
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:
- Drag the Amount field into the Values area. The pivot table will display the total of all the values in the Amount column.
- Drag the AcctType field into the Rows area. The pivot table will show now the total amount for each of the account types.
- 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.
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.
Pivot Table’s another name can be Summary Table. Pivot tables are created from a data table/ database with few mouse clicks, or we can say data table/database is sliced and diced in different ways to produce pivot tables as per your necessity. This data table/ database can be in a worksheet (in the form of a table) or in an external data file. Applying formatting to a pivot table, you can make it attractive.
Further Reading: The Ultimate Guide for Pivot Table by Spreadsheeto
A minor drawback of Pivot Table
There is a minor drawback to using a pivot table. In a formula-based summary report, the summary is updated automatically when you change information in the source data. But in Pivot Table, the summary is not updated automatically when you change information in the source data. This is not a serious problem. Use Refresh after you have changed information in your data source and the pivot table will be automatically updated.
Download Working File
Happy Excelling 🙂