Pivot Tables in Excel – Getting Started for Beginners

Table of Contents

• Excel Pivot Tables – Getting Started for Beginners
• What Is a Pivot Table and What Is It Used For?
• How to Create a Pivot Table in Excel
• Creating a Pivot Table in Excel
• Parts of the PivotTable
• Arranging the PivotTable
• Value Field Settings
• Analyzing Data in Your Excel Pivot Table
• Pivot Charts and Advanced Pivot Tables
• Customizing Your Pivot Table
• How Do I Make a Pivot Chart in Excel?
• Grouping Data
• Creating Multiple Records
• Refreshing Pivot Cache
• Limitations of Shared Pivot Cache
• Showing Value as a Percentage
• How to Delete Pivot Table?
• FAQs
• Closing Thoughts

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

What Is a Pivot Table in Excel and What Is It Used For?

A Pivot Table 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 a 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 the 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 the 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 the 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. 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 (a 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 the total sales of the product and the 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.

Pivot Charts and Advanced Pivot Tables

Pivot tables in Excel are used to aggregate data. They present an overall view of the table in order to compare, analyze, and forecast. Excel pivot tables generate reports, segregate data into summaries and groups, and create pivot charts. They are user-friendly, allowing users to exchange the rows and columns and drag the pivot table to different places in the worksheet. In this Excel pivot table tutorial, we will learn how to customize a pivot table, create a chart, and group data. We will cover various advanced uses of pivot tables.

Customizing Your Pivot Table

You can choose a different style after you create a pivot table. Including colours in the pivot table helps show banded rows or columns, highlighting important data to make it stand out.

Pivot tables in Excel can be customized to your preferred format and style. Excel offers a few in-built pivot table styles that you can’t change, but you can create your customized style and apply it to one or more pivot tables. In the below example, we will see how to add colours to the pivot table.

Click on any cell on the pivot table, and PivotTable Analyze and Design tabs appear. Click on the Design tab and use any available options in the groups.

The layout group contains the options subtotals, grand totals, report layouts, and blank rows.

• Subtotals – shows or hides subtotals.
• Grand Totals – shows or hides grand totals.
• Report Layout – the compact form optimizes for readability, while the tabular and outline forms include field headers.
• Blank Rows – it makes groups by adding a blank line between each grouped item.

How Do I Make a Pivot Chart in Excel?

An Excel pivot chart is a visual representation of a pivot table. You can create a pivot chart only if you have a pivot table, as they both are connected. A pivot chart can quickly change the portion of data displayed and making it ideal for presenting data in a report. You can quickly change the portion of data displayed, like a pivot table report.

After the pivot table is created, click on the tab PivotTable Analysis, where you can find the PivotChart. Click on that to insert a pivot chart. It opens a dialogue box named Insert Chart, where you have the list of all types of charts. You can hover your mouse over each one and see the different charts available.

For this example, I have chosen the chart type Stacked Column. A pivot chart is inserted with all the fields in the pivot table. The filter appears at the chart’s top position, and other pivot table fields appear at the legends and axis. At the bottom right corner, you find the symbols + and -. Click on the + button, and you find the grouped data expanding in the pivot table. Click on the – button to revert and collapse the columns.

Grouping Data

Grouping data in a pivot table allows you to group a subset of data to analyze. You can group dates, numbers, and text fields in a pivot table. You can group the date column in your table by year, month, and quarter. For instance, the screenshot below shows the policy holders’ names and various other details concerning their policies.

This is the example table for which the pivot chart has been created. This gives a detailed report of the date field, indicating which quarter the policy was taken. The pivot table first arranges the names alphabetically and the years in ascending order, from 1976 to 2016.

The date field stores the year alone in the pivot table. You can see a small + symbol before the year. When you click on that, it expands and displays the quarter in which the policy was started. When you click on the quarter, it displays the month when the policy was taken.

Now, you can group the dates after creating the pivot. Click on any cell that contains the date in the pivot table, then right-click on it. You get the menu, as you can see in the screenshot below. Click on Group; it will prompt you to enter the start and end dates. Based on the dates you mentioned, the values will be grouped and displayed in the pivot table.

In the pivot table, the start and end dates are mentioned as 06-06-1976 and 12-12-2000, respectively. The pivot table displays the names of the policyholders who had taken a policy during that period. At the pivot table’s end, you can see a row with the value >12-12-2000.

The pivot table also displays the names of the policy holders who had taken a policy after the date 12-12-2000 and then displays the Grand Total.

Creating Multiple Records

When you create a pivot table, you are filling the Filters based on your pivot table. This is known as a global filter. In the screenshot, the entire table is filtered by a single overall criterion, the type of policy. The pivot table shows the data of all the policies in one report.

Imagine a situation where you wish to make a separate report for each item in the global filter. If it has to be done manually, you will create a pivot table by changing the type of policy and taking multiple reports for each type.

Instead of performing the same steps iteratively, Excel pivot tables give us the option to create multiple reports in a few clicks.

Place the desired field, and type of policy, in the Filters field, then select the tab PivotTable Analyze. When the tab unfolds, you find the option PivotTable. Click on PivotTable → Options → Show Report Filter Pages.

It opens the dialogue box Show Report Filter Pages, which has the global filter you applied when you created the pivot table. Click on the field you want to apply as a filter to generate multiple reports.

Then, you get a separate report that contains the global filter. You can click on the different options and get reports for that filter. In the screenshot, you can see the filter that we applied when the pivot table was created.

The dialogue box lists all the pivot table’s global filters.

Refreshing Pivot Cache

When working with Excel pivot tables, you must know what a pivot cache is. It is an object repository that holds a replica of the original data. Though a user cannot see it, it is a part of the workbook and is connected to the pivot table. When you make any changes in the pivot table, it does not reflect in the source data. Rather it happens in the pivot cache.

The cache enables fast processing of a pivot table. In reality, you access the pivot cache when you make changes in the pivot table and are not directly linked to the source data. This is also why you need to refresh the pivot table to reflect any changes made in the original table.

Limitations of Shared Pivot Cache

Though pivot cache improves the pivot table performance and memory usage, there are a few limitations, such as:

• It increases the size of your workbook, thereby occupying much memory space.
• When the pivot table is refreshed, all the tables linked to the same cache are also refreshed.
• Grouping is reflected in all associated pivot tables and not just the table on which the grouping function was executed.

Showing Value as a Percentage

The pivot table displays the values across the rows and columns. You can get the values as percentages concerning the grand total, roe total, or column total. Select any cell on the pivot table, and right-click on it. On the menu, choose Show Values As, and select any option mentioned there.

How to Delete Pivot Table?

You can delete the pivot table when you think it is no longer needed. The steps to delete are as follows:

• Click on any of the cells inside the pivot table.
• Click on PivotTable Analyze → Actions → Select → Entire PivotTable.
• The entire pivot table is selected.
• Press the delete button on your keyboard.

FAQs

1. What is a Pivot Table in Excel and what are its uses?

A Pivot Table 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.

3. What are the features of a pivot table?

• It provides an interactive way to summarize large amounts of data.
• You can analyze numerical data and project it from various views.
• It is designed to query large amounts of data.
• It provides a user-friendly interface.
• It allows subtotalling and data grouping summarizes data in categories and subcategories and creates custom calculations and formulas.
• It drills down for details from the summary report to get the data you are interested in.
• It provides an easy way to swap rows with columns and vice versa to see the different summaries of the original table.
• It filters, sorts, groups, and conditional formats data.
• It generates concise, attractive, and annotated reports that can be shared online or printed offline.
• It presents concise, attractive, and annotated online or printed reports.

4. What is the difference between a pivot table and a pivot chart?

Pivot table	Pivot chart
A pivot table summarizes large data in a spreadsheet.	The pivot chart provides a graphical representation of the pivot table.
In a pivot table, you can choose the fields appearing in the rows and columns.	You can choose from multiple layouts and chart types in a pivot chart.
Changes made in the pivot table will appear in the pivot chart.	For one pivot table, you can create many pivot charts.

5. List the difference between normal charts and pivot charts.

Chart	Pivot chart
Charts are a graphical representation of a table, but applying filters, sorts, and calculations over these types of charts requires changing the source data cell formulas or entries.	Pivot charts are also a graphical representation of a table with more access buttons on the chart.
Changes to the chart do not flow back to the data.	A pivot chart is linked to a pivot table. Filters applied to pivot charts are also applied to their associated pivot table.
The option of inserting the slicer is not possible with the normal charts.	Slicers can also be applied to pivot charts, and a single slicer can be applied to multiple pivot tables.

Closing Thoughts

In this guide, you learnt what a PivotTable is and how to work with it. Many know how to create a pivot table 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 and charts are one of the powerful features of Excel. Business analysts use pivot tables and charts for generating reports, forecasting, and planning. Creating a pivot table

requires a few clicks, but mastering it can be time-consuming as it depends on how you want to use your data within your pivot tables.

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 work with these software tools. You will earn Micro-credentials on completion of the course.