Pivot Table in Excel: 5 Steps to an Easy Report

Table of Contents:

• Why Use Pivot Tables: Benefits and Application Scenarios
• Case Study: Analyzing Sales at a Car Dealership
• Step-by-Step Guide to Creating a Pivot Table
• How to Create a PivotTable in Excel: A Step-by-Step Guide
• Customizing a PivotTable: A Step-by-Step Process
• Customizing PivotTable Filters
• Advanced Calculations in PivotTables
• Updating Data in a Pivot Table: A Step-by-Step Guide

Why Use Pivot Tables: Benefits and Application Scenarios

Pivot Pivot tables allow you to quickly and efficiently process data by summarizing, grouping, and performing additional calculations, which greatly simplifies report creation.
The flexibility of pivot tables allows the user to customize them to suit their needs in just a few clicks. You can change the arrangement of rows and columns, filter results, and move report blocks to improve visualization. This makes pivot tables indispensable in business analytics.

Practical example: sales analysis at a car dealership

Let's imagine a small car dealership with three sales managers. During the quarter, sales information was collected in a standard table containing data on the car model, its characteristics, price, date of sale, and the manager’s full name.

At the end of the quarter, it is necessary to conduct an analysis to determine the manager who brought in the greatest profit. To do this, you need to group all the cars sold by each manager, calculate the total sales amounts, and determine the final percentage of sales for the quarter.

Step-by-step guide to creating a pivot table

Now let's look step by step at how to create a pivot table to analyze the sales data in our example. This will help you not only in this situation, but also in any other scenarios where you work with large amounts of information.

How to Create a Pivot Table in Excel: A Step-by-Step Guide

Before you start creating a pivot table, you need to make sure that your source data meets certain requirements. This will ensure that the pivot table works correctly.

• Each column must have a header so that Excel can identify the data;
• Each column must contain only one type of data - text, number, or date;
• There must be no empty cells or rows in the table.

Go to the "Insert" tab in the top menu of Excel and select the "Pivot Table" option. This action will open a dialog box with settings.

In the window that appears, you need to specify two key parameters:

• the range of the source table from which the data will be extracted;
• The sheet on which the new pivot table will be placed for further analysis.

In this case, select the entire range of the table with sales data, including the headers. It is recommended to select "New sheet" to place the pivot table, this will make it easier for you to navigate between the source data and the report. After that, click "OK."

Excel will automatically create a new sheet for your pivot table. For ease of use, don't forget to rename it immediately.
The new sheet will have an area on the left where the pivot table data will be displayed, and on the right, a "Pivot Table Fields" panel where we will make settings. In the next step, we'll take a closer look at how to use this panel.

Setting Up a Pivot Table: A Step-by-Step Process

To get a visually appealing report, you need to set up your pivot table correctly. The top of the settings panel displays a list of fields we can use. These fields are formed from the column headings of your source table, such as Make, Model, Color, Year, Volume, Price, Sale Date, and Seller.
The settings panel is divided into four key areas: Values, Rows, Columns, and Filters. Each of these areas performs its own unique function:

• "Values" - This is where calculations are performed based on the selected data. Excel sums this data by default, but you can choose other operations, such as average, minimum, or maximum. For example, if you are analyzing sales, the program might sum the total price of cars sold.
• "Rows" and "Columns" - determine how the fields will be arranged in the pivot table. Fields placed in rows will be displayed row by row, and fields in columns will be displayed vertically.
• "Filters" - allow you to filter the data displayed in the pivot table. After creating the table, the filter bar will be available separately, allowing you to choose which data to show and which to hide. This can be useful, for example, for analyzing sales for a specific manager or for a certain period.

There are two ways to customize a pivot table:

• The first option is to simply check the box next to the desired field. Excel will automatically place this value in the table, but this often leads to ineffective data display.
• The second method is to manually drag and drop fields into the desired areas, which allows you to customize the pivot table to suit your needs.

We recommend using the second method, as it gives more control over how the data will be displayed. In our case, we want to display the names of sales managers, cars sold and their prices. Other data (technical specifications, sales dates) can be used for filtering.
To create an informative table, we will place the names of the managers in rows. Find the "Salesperson" field at the top of the panel and drag it to the "Rows" area.
After this, a block with the names of sales managers will appear on the left side of the sheet.

Now let's add the car models that were sold by these managers. Similarly, drag the "Make, Model" field into the "Rows" area.
Now a second block has been added to the left side of the sheet, and the pivot table has automatically grouped all the cars by the managers who sold them.

Now we need to add the prices of the cars sold and their quantity for a more complete report.
To make the pivot table automatically sum these values, drag the "Make, Model" and "Price" fields to the "Values" area.

Now the pivot table clearly shows which cars each manager sold, how many, and at what price. Additionally, next to the managers' names, you can see the total number of cars sold and the total sales amount for the quarter.
You can also add and remove other fields to the desired areas. All changes will be automatically updated in the pivot table. In our example, the entered data is sufficient, and later we will look at how to set up filters for further analysis.

Setting up PivotTable Filters

To make your pivot table more informative and visual, you need to set up filters. This will allow you to easily manage the display of data and get the information you need quickly and efficiently.
In this example, we will move all fields that are not part of the basic structure of the pivot table to the "Filters" area. This includes sales volume, date of sale, year of manufacture, and color of the car.

Now, to filter the data by year of manufacture, we will set the filter to display only cars sold in 2024.
To do this, in the filter block, click on the arrow to the right of the "Year of manufacture" field. This will open a menu for setting up filtering.

In the window that opens, uncheck the box next to "Select all" and check the box next to "2024". After that, close the window.

The pivot table will now display only 2024 vehicles that were sold by managers during the quarter. To return the full amount of data, simply remove the filter set in the same block.

Filters can be selected and removed depending on your needs - this greatly simplifies data analysis and allows you to focus on the most important information.

Additional calculations in pivot tables

Currently, our managers' sales are displayed in rubles, but for a more in-depth analysis, we need to know what percentage of total sales each transaction represents. One way is to perform the calculation manually, but a more efficient solution would be to use built-in pivot table functions.
To perform the necessary calculations, right-click on any cell with a price. In the menu that opens, select the "Advanced Calculations" option, and then enter "% of total." This will allow you to quickly and accurately obtain the data you need.

Now, instead of amounts expressed in rubles, we display percentages. This indicator shows what share of the dealership's total sales for the quarter each car sold represented. The percentages next to the managers' names indicate their overall contribution to sales for the period.

For ease of analysis, you can collapse the details with the lists of cars by clicking on the minus sign (-) to the left of the manager's name. This will make the table more compact, and the key metrics showing which managers achieved the best results in the quarter will be easily accessible.

To return to the car details, simply click the plus sign (+).
If you need to return the values ​​to their original rubles, right-click and select "No calculations" under "Additional calculations." This will allow you to flexibly manage the data presentation depending on your analytical needs.

Updating Data in a PivotTable: A Step-by-Step Guide

Imagine that two new sales appeared in the source table on the last day of the quarter. These changes will not be automatically reflected in your pivot table because the source data range has changed.

To update the data in the pivot table, you need to change the source parameters. Go to the sheet with the pivot table and in the PivotTable Analysis section, click the Change Data Source button.

The button will take you to the source table sheet. Here, select the new range, including the new rows, and click "OK." The pivot table will automatically update, and you will see the changes.

After the update, the data in the pivot table will change: for example, the number of sales for manager Tregubov M. will increase from eight to ten.

If you need to change information in the current range of the source table, the pivot table will not update automatically. You will need to do this manually.
For example, let's change the prices of two cars in the sales table.

To ensure your pivot table data reflects these changes, return to the pivot table sheet and, in the PivotTable Analysis section, click Refresh.

Now manager Sokolov P. has updated data in the "Price, RUB" column.
How to effectively use pivot tables in Google Sheets? Go to the "Insert" tab and select the "Create Pivot Table" option. The process is similar to that described for Excel: select the table range and the sheet on which you want to create the pivot table. Then, on the selected sheet, in the PivotTable Editor window, specify all the necessary settings.