What Is a Pivot Table? (Create & Analyze Data)

What is a Pivot Table in Excel? Perhaps, the PivotTable feature is the most key component in Excel. PivotTable is making one or more new tables from a given data table. This is the answer we shall search for in this article. We’ll answer this question by making PivotTables from fictitious data with an adequate number of proper illustrations.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Introduction to Dataset

For clarification, we have a file named Bank Accounts Information in our hand. Let’s start with the following figure. This figure shows a portion of the dataset we have used to create the pivot tables in this chapter. The table has 712 rows. Each row represents a new account opened in the month of November at the bank.

what is a pivot table

The dataset has the following columns:

  • The Date when someone opened the account.
  • 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)

Why PivotTable Is Necessary

This database contains a good amount of information. But in its current form, the data doesn’t reveal much to you. The 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 PivotTable is a better choice, a PivotTable takes a few seconds, requires a few clicks, doesn’t require a formula, and produces a professional-looking report.

Basically, you may do the following things using the PivotTable:

In addition, analyzing data with PivotTable makes fewer errors than creating formulas.


3 Easy Steps to Create a Pivot Table

Now, we’ll create a PivotTable using the above-mentioned dataset. So, let’s explore the steps one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


Step 01: Specify the Data Range

At first, we’ve to select the specific range of data from which we want to create our PivotTable. It’s simple & easy, just follow along.

  • At the very beginning, select any cell inside the dataset. In this case, we selected cell B4 in our dataset.
  • Then, go to the Insert tab.
  • Later, click on PivotTable on the Tables group.

Specify the Data Range to Create Pivot Table

  • Correspondingly, the PivotTable from table or range dialog box opens.
  • Here, we can see that our data range got automatically detected and sat in the Table/Range box.
  • In the Choose where you want the PivotTable to be placed section, select New Worksheet. This will place our PivotTable in a new worksheet.
  • After doing that, click OK.

Specify the Data Range to Create Pivot Table


Step 02: Create a Blank Pivot Table

As a result of our previous actions, Excel created a blank PivotTable in a new worksheet.

Create a Blank Pivot Table

  • Now, select any cell on the PivotTable. For example, we chose cell B4.
  • Immediately, the PivotTable Fields task pane opens on the right side of the worksheet.

Create a Blank Pivot Table

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 the Date, Weekday, Amount, AcctType, OpenedBy, Branch, and Customer fields. The lower part has Filters, Columns, Rows, and Values area.


Step 03: Lay out the Pivot Table

Now we shall work on the PivotTable Fields task pane. The following steps will create a simple PivotTable. So, follow along.

  • Drag the Amount field into the Values area. The PivotTable will display the total of all the values in the Amount column.
  • Drag the Branch field into the Rows area. Now, The PivotTable will show the total Amount for each of the Branches.

Lay out the Pivot Table

And the final output looks like the following one.

Lay out the Pivot Table


Getting Rid of “Row Labels” and “Column Labels” Headings

In the picture above, in cell B4, the heading shows Row Labels. But these are the branches of the bank. So, the heading should be Branch. Therefore, we could change this to a PivotTable. Follow these simple steps.

  • Firstly, select any cell inside the PivotTable.
  • Secondly, go to the Design tab.
  • In the Layout group, click on the Report Layout drop-down.
  • Fourthly, select Show in Outline Form from the list.

Getting Rid of “Row Labels” and “Column Labels” Headings in Pivot Table

At this moment, the heading in cell B4 changed magically.


Analyzing Data with Pivot Table

If you are curious to learn more about PivotTable, this section may come in handy. After creating a PivotTable, if you wish to conduct powerful data analysis, you might want to further enhance your Pivot Table. Let’s go through the topics.


1. Sorting by Value

In the previous image, we saw the Sum of Amount in descending order. Here, we can change the order by sorting. Let’s see the process.

  • Initially, right-click on any Amount value. Here, we selected cell C5.
  • Then, the context menu appears.
  • After that, click on Sort from the menu.
  • Later, select Sort Smallest to Largest on the sub-menu.

Sorting by Value in Pivot Table

  • Currently, the table looks like the one below.

Sorting by Value


2. Adding Second Value Field

Also, you can include more than one field in the Values area. Besides, you can add the same field twice in the same area. For clarification, follow the steps.

  • At first, drag the Amount field again into the Values area.

Adding Second Value Field

  • Thus, it will create a new column Sum of Amount2 in the table.

Adding Second Value Field in Pivot Table


3. Showing Values as Percent of Total

In PivotTable, there are various options for showing values. If you want to show the Sum of Amount2 as a percentage of the Grand Total, you can do that easily. Allow me to demonstrate the process below.

  • Primarily, right-click on cell D5 to open the context menu.
  • Secondarily, click on Show Values As on the menu.
  • Then, select the % of Grand Total from the sub-menu.

Showing Values as Percent of Total

Hence, we can see the result in the image below.

Showing Values as Percent of Total in Pivot Table

From the above image, we can see that the Central branch made the highest stake of 47.67%.


4. Changing Calculation in Value Field

Excel shows the summary in summation by default. But, we can change this factor. Just execute the steps below.

  • First of all, right-click on cell D5 to open the context menu.
  • Secondly, click Summarize Values By on the menu.
  • Thirdly, select Count on the sub-menu.

Changing Calculation in Value Field

You can do the same task in an alternative way.

  • Suddenly, it opens the Value Field Settings dialog box.
  • Here, select Count in the Summarize value field by section.
  • Lastly, click OK.

Thus, it exhibits the number of times the Branches received Amounts.

Changing Calculation in Value Field of Pivot Table


5. Using Group Feature

Here, we’ll talk about the Group feature of PivotTable. Let’s assume, we’ve got the Date field in the Columns area. Our dataset contains the information from 1st Nov to 30th Nov of 2013. So, the table would look like the one below.

Using Group Feature

  • Now, right-click on any Date (i.e. cell C5) to open the context menu.
  • Then, select Group from the menu.

  • Instantly, the Grouping wizard opens.
  • Hence, select Months in the By section.
  • Therefore, click Ok.

Using Group Feature

Consequently, all the dates in November month get grouped under the heading Nov.

Using Group Feature in Pivot Table


6. Creating Two-Dimensional PivotTable

For deeper analysis, you can construct a two-dimensional PivotTable. So let’s have a look at the procedure.

  • To begin with, place the fields into the areas just like in the following image.

Creating Two-Dimensional PivotTable

As a consequence, it creates the following PivotTable.

Creating Two-Dimensional PivotTable

It displays the breakdown of the Branch into AcctType. Also, it shows the Customer type (i.e. Existing/New) in the columns.


Moving Pivot Table to New Location

To move a PivotTable to a new location, follow these simple steps.

📌 Steps:

  • At first, select cell B4.
  • Then, move to the PivotTable Analyze tab.
  • After that, click on the Actions drop-down group.
  • Later, select Move PivotTable from the available options.

Moving Pivot Table to New Location

  • Immediately, the Move PivotTable input box appears.
  • Here, choose Existing Worksheet to place the PivotTable.
  • In the Location box, give the cell reference of F4.
  • Correspondingly, click Ok.

Clearly, we can see the table in its new location.

Moving Pivot Table to New Location


Deleting Pivot Table

Deleting the PivotTable is another easy job. Just follow us.

📌 Steps:

  • In the first place, select any cell inside the table.
  • Secondly, jump to the PivotTable Analyze tab.
  • Then, click on the Actions drop-down group.
  • After that, click on the Select drop-down.
  • Next, choose Entire PivotTable from the options.
  • Lastly, press the DELETE key on the keyboard.

Deleting Pivot Table

Thus, you can see your table got removed from the sheet.


Advantages of Pivot Table

  • Lucidity: PivotTables are incredibly easy to create and modify. No need to learn challenging formulas.
  • Formatting: Just as the data updates, a PivotTable may spontaneously assign a consistent number and design formatting.
  • Agility: With a PivotTable, you can quickly produce a report that looks nice and is useful. Even if you are an expert with formulas, setting up PivotTables takes far less time and effort.
  • Adaptability: PivotTables, as opposed to formulas, don’t force you to utilize a specific data view. You can simply adjust the PivotTable according to your requirements. Even better, you can duplicate a pivot table and create a different layout.
  • Efficiency: You may be sure that the outcomes of a PivotTable are reliable as long as it is properly configured. In fact, it will frequently reveal issues with the data more quickly than any other method.
  • Filtering: Numerous options for data filtering are included in PivotTables.  Want to look at the Central and Westside branches, but not North County? It is quite simple with a PivotTable.

A Minor Drawback of Pivot Table

There is a minor drawback to using a PivotTable. In a formula-based summary report, the summary is updated automatically when you change information in the source data. But in PivotTable, 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 PivotTable will be automatically updated. To do this, follow us carefully.

📌 Steps:

  • Firstly, select any cell inside the table.
  • Then, proceed to the PivotTable Analyze tab.
  • After that, click on the Data group drop-down.
  • Later, select Refresh.

A Minor Drawback of Pivot Table

Simply, it’ll solve the problem of updating data.


Things to Remember

Make sure to remember some essential things while using PivotTable in Excel. You need to activate some options for your convenience in working. Don’t worry. Carefully follow the following steps.

📌 Steps:

  • Firstly, select any cell inside the table. Here, we selected cell B4.
  • Then, go to the PivotTable Analyze tab.
  • After that, click on the PivotTable group drop-down.
  • Later, select Options.

Things to Remember about Pivot Table

  • Immediately, the PivotTable Options dialog box pops up.
  • Here, check the boxes of these two options like in the following image.
  • Correspondingly, click OK.

  • Later, move to the Data tab in that dialog box.
  • After that, make sure to tick the box of Refresh data when opening the file.
  • Lastly, click Ok.

Things to Remember about Pivot Table

These will update your table when you open the file next time. Also, the first option will keep the Column Width changed according to the components in the cell. And, will preserve the formatting. So, don’t forget to check these tasks.


Practice Section

For doing practice by yourself we have provided a Practice section like below in the last sheet of the workbook.

Practice Section

Also, try to answer all of the questions we threw in the previous section.


Conclusion

Thank you for reading this article. I hope all of the information mentioned above about what is a PivotTable will now prompt you to apply them in your Excel spreadsheets more effectively. Don’t forget to download the Practice file. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Kawser

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/

3 Comments
  1. 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?

Leave a reply

ExcelDemy
Logo