What is a Pivot Table in Excel – Make a Pivot Table Manually!

What is a Pivot Table in Excel? This is the answer we shall search in this article. I’ll answer this question by making a Pivot Table from a fictitious data.

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

What is a pivot table

We shall create the Pivot Tables from this data table 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 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)

Read More: A Pivot Table Example in Excel with Real Data

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

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 our this example, we are going to create our pivot table in a new worksheet. See the following screenshot:

What is a pivot table

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:

What is a Pivot Table

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.
What is a 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.

What is s pivot table

The pivot table is showing the summary of our data.

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.

What is a pivot table

Click on this Refresh button when you have changed some information in your data source to update your pivot table.

Download Working File

Download this file to work with pivot table.

Read More:

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

Reverse Pivot Tables – Unpivot Summary Data

Happy Excelling 🙂

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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

  1. Reply
    Al Firdausi April 3, 2017 at 1:29 PM

    thanks for the post. I just want to practice the guidance and I need the Excel file “Bank-accounts.xlsx”. Where can I download it?

    • Reply
      Kawser April 3, 2017 at 9:02 PM

      Glad to know that it helps. The file is at the end of the article. Just click and download.

    Leave a reply