A pivot table in Excel is a summarization tool to automatically sort, filter, count, and perform mathematical calculations on data stored in a table. When you work with large data sets in Excel, the pivot table makes an interactive summary from many different records.
Another advantage of using pivot tables in Excel is that you can set up and change the structure of your summary table by dragging and dropping the source table’s column.
For instance, you have a table with the sales information of a product at different branches by a different salesperson. You are interested in knowing the total sales, sales of each product, and sales made by individuals. PivotTable in Excel has features that perform all these operations.
In an Excel spreadsheet, you can find the PivotTable under the Insert tab.
You can use SUMIF and SUMIFS formulas to sum a long list of numbers using one or more conditions. However, if you are interested in comparing several facts and finding statistics regarding sales, profit, or grand total, the PivotTable is efficient for that. The PivotTable is customizable and you can get the totals of any field (either a row-wise or a column-wise total) you want.
Table of Contents
- What is a Pivot Table in Excel and What Is it Used For?
- How to Create a Pivot Table in Excel
- Parts of the PivotTable
- Arranging the PivotTable
- Value Field Settings
- Analyzing Data in Your Excel PivotTable
- PivotChart
- Frequently Asked Questions
- Closing Thoughts
What Is a Pivot Table in Excel and What Is It Used For?
A PivotTable in Excel is a tool that summarizes data and allows you to perform various mathematical calculations using the data. It is a versatile tool to help you explore, analyze, and summarize large amounts of data. It can summarize data along the row or column and return the sum, count, average, maximum, minimum, and other statistical data.
PivotTables can filter, group, sort, or conditionally format different subsets of data to display results based on your criteria. It can rotate rows with columns and vice versa. That’s the reason it is called a pivot table. You can expand or collapse the levels of data and move further deep to check the substeps of the total and grand total.
How to Create a Pivot Table in Excel
Pivot tables in Excel can be created with ease with an existing table. If you don’t know how to create a pivot table in excel, we’ll show you. Let’s take the above table as the source to create a pivot table. This screenshot shows a sales report across different branches, including various salespeople and products. A business owner or a marketer would likely be interested in learning more about the total sales in each branch by a particular salesperson, or the total sales in a quarter, i.e from January to March, of a product.
To start with, click on the Insert tab, and then click on the Table . It prompts you for the location of data in the table. By default, it takes the starting and ending cells of the table. Select the checkbox ‘My table has headers’ in the prompt if you require headers for each column.
Then click OK and proceed. Your table becomes dynamic. That is, you get the filter option in the column name and you can choose to sort and filter the column data with the options given in the drop-down menu.
For usability, name the columns with headings relevant to the data in the column. For example, column A contains data, such as mouse, laptop, keyboard, and cables. It is a good practice to name the column with a relevant collective noun such as Product or Items Sold .
Creating a Pivot Table in Excel
Click on the Insert tab, then click PivotTable , this opens a dialogue box PivotTable from table or range . You will be prompted with the message Select a table or range . Here, mention your table range in the dialogue box Table/Range, and choose the location where you want the PivotTable to be placed. It can be either in a New Worksheet or in the Existing Worksheet .
If you select the option New Worksheet, the PivotTable will be placed in a new worksheet starting at cell A1. If you choose the option Existing Worksheet, it will place your table where you put your cursor on the existing worksheet.
Note : Make sure to click on a cell in the worksheet other than your source table for creating a pivot table.
Parts of the PivotTable
After you mention the table range (the location where you want the PivotTable to appear) and click OK, you get a small window next to your table displaying the PivotTable fields, as you can see below. You first notice the field Choose fields to add to report . Click on the Settings option next to it, and you will find the following formats in which your PivotTable can be displayed.
- Fields Section and Areas Section Stacked.
- Fields Section and Areas Section Side-By-Side.
- Fields Section Only.
- Areas Section Only (2 by 2).
- Areas Section Only (1 by 4).
You can choose any of the formats in which your report has to appear. To start with, choose the first option, Fields Section and Areas Section Stacked , and you find that all the columns in your table appear below the field, Choose fields to add to report , which you can drag and drop accordingly.
For the PivotTable summary, you need to mention the column based on which you intend to generate the report. For instance, if you wish to see the summary of sales in each Branch, mention the field Branch in the filter by dragging and dropping the field Branch in the Filters field. Choose the column you wish to appear in the Columns and the Rows fields.
Note : Mention non-numeric columns, such as Product and Salesperson , in the Rows and Columns and numeric data in the Values field. After you are done with this step, click outside the window and you will see this report generated as shown below.
Arranging the PivotTable
The pivot table is arranged to give a detailed summary report on sales of all the products by all the salespeople across all the branches. The Row has all the salespeople’s names and the Column has the products listed up. The Branch column is mentioned in the Filter field and you can generate a report based on the different branches mentioned.
For an instance, when you click on the button next to the Branch column, you get the options, (All), East, North, South, West. Click on the drop-down menu and choose the branch East. When you refer to your table, you see that, in the East branch, Mark is the salesperson and he has sold 2 products (keyboard and monitor).
Sales of each product are mentioned individually and the total sales made by Mark are also mentioned in the report. You can also click on the row labels and the column labels and choose a particular item to display the report of that particular product or salesperson.
The report contains total sales of the product and total sales of the salesperson also. Such analysis helps the manager to analyze the sales that assist them to predict profit or loss in the business.
Value Field Settings
The report generation doesn’t stop with these details alone. When you click on the Values field, you get a new window Value Field Settings . There, you get options to summarize the value field by different functions, such as Sum, Count, Average, Max, Min, Product, Count Numbers, Standard Deviation, and Variance. You can apply these functions to your table. All these help managers decide and forecast inventory management and sales predictions.
The below screenshot shows the different statistical functions that you can choose from to summarize data from the selected fields:
Analyzing Data in Your Excel Pivot Table
You can refine your Pivot Tables in Excel further to make effective data analysis. After you create a pivot table, you can see two more new tabs along with the already existing tabs. They are PivotTable Analyze and Design.
The PivotTable Analyze has many options, such as Active Field, Group, Filter, Data, Calculations, and Tools. When you click on Active Field→Field Settings, you find the Value Field Settings and you can choose any of the statistical functions from there.
Click on the Design tab to improve the table’s design, where you will find plenty of predefined styles. You can hover and apply the style that suits you.
PivotChart
PivotChart is the visual representation of the table and it displays the data in the summary table. Click on PivotTable Analyze→ PivotChart , and you can choose from any of the chart designs from the options.
FAQs
1. What is a Pivot Table in Excel and what are its uses?
A Pivot Tables calculates and summarizes data and presents you with a report to see comparisons, patterns, and trends that allow you to forecast your sales. First, you need a table populated with data to work with pivot tables in Excel. You can also use tables from existing worksheets or other sources.
It is generally used by managers, financial advisors, strategists, analysts, auditors, sales managers, HR managers, and statisticians.
2. How to delete a Pivot Table?
- If the source table is in a separate worksheet, delete the worksheet itself.
- Select the entire pivot table and press the delete button on your keyboard.
- Click on the PivotTable Analyze→ Actions group→Entire PivotTable, and then press Delete.
Closing Thoughts
In this guide, you learned what a Pivot Table in Excel is and how to work with it. Many know how to create a pivot table in Excel, but might not have worked with other functions available in it. The summary report from the source table displays data that has various interpretations.
Pivot Tables in Excel allow you to work with customized tables and manipulate data that you can use to find recurring patterns. A PivotTable has many interesting features and you can experiment with them on your table. You can also use Lookup functions, such as VLOOKUP along with PivotTable. Check our courses in Excel and Microsoft Office Applications to learn how to insert slicers and timelines in the PivotTable. You will earn Micro-credentials on completion of the course.
