Home-Excel-Pivot tables excel lessons

Pivot tables excel lessons

The pivot table is perhaps the most useful tool in Excel. With its help appear ample opportunities for analyzing large data sets and fast calculations.

What are Pivot Tables in Excel? Pivot tables in Excel for dummies

Pivot tables are Excel tool for summarizing and analyzing large amounts of data.

Let's imagine that we have a table with sales data for clients for the year with a size of 1000 lines:

The table shows:

  • Order dates;
  • The region in which the client is located;
  • Client type;
  • Client;
  • Number of sales;
  • Revenue;
  • Profit.

Now, imagine that our leader has set the task of calculating:

  • TOP five clients by revenue;

You can use various functions and formulas to find the answer to these questions. But what if there are not three tasks based on this data, but thirty? Each time you will have to change formulas and functions and adjust them for each type of calculation.

This is exactly where the PivotTable tool comes in handy for you. an indispensable assistant. With its help, you can answer any question in a matter of seconds, according to the data from the table.

I hope you realized from the example above how useful and necessary a pivot table is. Let's figure out how to use them.

How to make a pivot table in Excel

To create a pivot table in Excel, follow these steps:

  • Select any cell in the table with the data on which you want to create a pivot table;
  • Click on the “Insert” tab => “Pivot Table”:

  • In a pop-up dialog, the system will automatically determine the boundaries of the data, based on which you can create a pivot table. This method works by default in most cases. I recommend that each time you create a pivot table, make sure that the system has correctly defined the parameters for its creation:
    – Table or range: The system automatically determines the boundaries of the data to create a pivot table. They will be correct provided that there are no spaces in the table headings or rows. If necessary, you can adjust the data range.
  • By default, the system creates a table in a new tab of the Excel file. If you want to create it in a specific place on a specific sheet, then you can specify the boundaries for creation in the “On an existing sheet” column.



  • Click OK.

After you clicked the “OK” button, the pivot table will be created.

Once the PivotTable is created, you will not see any data on the worksheet. All that will be available is the name of the pivot table and a menu for selecting data to display.



Now, before we start analyzing the data, I propose to understand what each field and area of ​​the pivot table means.

PivotTable Areas in Excel

In order to effectively use pivot tables, it is important to thoroughly know how they work and what they consist of.

Below you will learn more about the areas:

  • Pivot table cache
  • “Values” area
  • “Strings” area
  • Columns area
  • Filters area

What is a pivot table cache?

When you create a PivotTable, Excel creates a cache (an intermediate buffer of data with quick access, containing information that is most likely to be requested) data on which the table will be built.

When you perform calculations on the created table, Excel does not access the original data each time, but uses information from the cache. This feature significantly reduces the amount of system resources spent on data processing and calculations.

It is important to remember that the data cache increases the size of the Excel file.

“Values” area

The “Values” area contains all the table values ​​and can be displayed as the main component of the pivot table. Let's imagine that we want to reflect the sales volume of regions by month (from the example at the beginning of the article) using a pivot table. Values ​​are shaded yellow in the image below and are the values ​​we specify in pivot table in the “Values” area.



In the example above, we created a pivot table that displays sales data by region, broken down by month.

“Strings” area

The table headers, located to the left of the values, are rows. In our example, these are the names of the regions. In the screenshot below, the lines are highlighted in red:



“Columns” area

The headings at the top of the table values ​​are called “Columns”.

In the example below, the “Columns” fields are highlighted in red; in our case, these are the month values.



Filters area

The “Filters” area is used optionally and allows you to set the level of detail of the pivot table data. For example, we can specify “Client Type” data as a filter – “ Grocery store” and Excel will display data in a table relating only to grocery stores.



Pivot tables in Excel. Examples

Using the examples below, we'll look at how to use pivot tables to answer the three questions from the beginning of this article:

  • What is the revenue volume of the North region in 2017?;
  • TOP five clients by revenue;
  • What place in terms of revenue does the client Ludnikov IP occupy in the East region?

Before analyzing the data, it is important to decide how the table data should look (what data should be laid out in columns, rows, values, filters). For example, if we need to display customer sales data by region, then we should place the region names in rows, months in columns, and sales values ​​in the “Values” field. Once you have imagined how you see the final pivot table, start creating it.

The “Pivot Table Fields” window contains areas and fields with values ​​to be placed in the pivot table:

The fields are created from the source data used for the PivotTable. The Areas section is where you place the fields, and based on which area the field is placed in, your data is updated in the PivotTable.

Transferring fields from area to area is user-friendly interface, in which, as you move, the data in the pivot table is automatically updated.



Now, let’s try to answer the manager’s questions from the beginning of this article using the examples below.

Example 1. What is the revenue volume of the North region?

In order to calculate the sales volume of the North region, I propose to place the sales data of all regions in a pivot table. For this we need:

  • create a pivot table and move the “Region” field to the “Rows” area;
  • place the “Revenue” field in the “Values” area

We get the answer: sales in the North region are 1,233,006,966 ₽:



Example 2. TOP five clients by sales

  • in the pivot table, move the “Customer” field to the “Rows” area;
  • place the “Revenue” field in the “Values” area;
  • set the financial number format for the pivot table cells with values.

We will get the following summary table:



By default, Excel sorts the data in the table into alphabetical order. To sort data by sales volume, follow these steps:

  • go to the “Sort” menu => “Sort in descending order”:



As a result, we will receive a sorted list of clients by revenue volume.



Example 3. What place in terms of revenue does the client Ludnikov IP occupy in the East region?

To calculate the place in terms of revenue of the Ludnikov IP client in the East region, I propose to create a summary table that will display revenue data by region and clients within this region.

To do this:

  • place the pivot table field “Region” in the “Rows” area;
  • place the “Client” field in the “Rows” area under the “Region” field;
  • let's set the financial numeric format to the cells with values.

As soon as you place the “Region” and “Client” fields below each other in the “Rows” area, the system will understand how you want to display the data and offer the appropriate option.

  • The “Revenue” field will be placed in the “Values” area.

As a result, we received a summary table that reflects customer revenue data within each region.



To sort the data, follow these steps:

  • click right click on any of the lines with revenue data in the pivot table;
  • go to the “Sort” menu => “Sort in descending order”:



In the resulting table, we can determine what place the Ludnikov IP client occupies among all clients in the East region.



There are several options to solve this problem. You can move the “Region” field to the “Filters” area and place customer sales data in the lines, thus reflecting revenue data only for customers in the East region.

If you've never used a spreadsheet to create documents before, we recommend reading our guide to Excel for Dummies.

After this, you can create your first spreadsheet with tables, graphs, mathematical formulas and formatting.

Details about basic functions and opportunities table processor MS Excel. Description of the main elements of the document and instructions for working with them in our material.



Working with cells. Filling and formatting

Before proceeding with specific actions, you need to understand the basic element of any document in Excel. An Excel file consists of one or several sheets divided into small cells.

A cell is a basic component of any Excel report, table or graph. Each cell contains one block of information. This could be a number, date, monetary amount, unit of measurement, or other data format.

To fill a cell, just click on it with the pointer and enter necessary information. To edit a previously filled cell, click on it double click mice.

Rice. 1 – example of filling cells

Each cell on the sheet has its own unique address. Thus, you can carry out calculations or other operations with it. When you click on a cell, a field will appear at the top of the window with its address, name and formula (if the cell is involved in any calculations).

Select the “Share of Shares” cell. Its location address is A3. This information is indicated in the properties panel that opens. We can also see the content. This cell has no formulas, so they are not shown.

More cell properties and functions that can be applied to it are available in the context menu. Click on the cell with the right mouse button. A menu will open with which you can format the cell, analyze the contents, assign a different value, and other actions.

Rice. 2 – context menu cells and its basic properties

Sorting data

Often users are faced with the task of sorting data on a sheet in Excel. This feature helps you quickly select and view only the data you need from the entire table.

In front of you is an already filled out table (we’ll figure out how to create it later in the article). Imagine that you need to sort data for January in ascending order. How would you do it? A banal reprinting of a table is extra work, besides, if it is voluminous, no one will do this.

There is a special function for sorting in Excel. The user is only required to:

  • Select a table or block of information;
  • Open the “Data” tab;
  • Click on the “Sorting” icon;

Rice. 3 – “Data” tab

  • In the window that opens, select the table column on which we will perform actions (January).
  • Next is the sorting type (we group by value) and, finally, the order - ascending.
  • Confirm the action by clicking on “OK”.

Rice. 4 – setting sorting parameters

will happen automatic sorting data:

Rice. 5 – the result of sorting the numbers in the “January” column

Similarly, you can sort by color, font and other parameters.

Mathematical calculations

Main Excel advantage– the ability to automatically carry out calculations while filling out the table. For example, we have two cells with values ​​2 and 17. How can we enter their result into the third cell without doing the calculations ourselves?

To do this, you need to click on the third cell in which the final result of the calculations will be entered. Then click on the function icon f(x) as shown in the image below. In the window that opens, select the action you want to apply. SUM is the sum, AVERAGE is the average, and so on. Full list functions and their names in the Excel editor can be found on the official website Microsoft.

We need to find the sum of two cells, so click on “SUM”.

Rice. 6 – select the “SUM” function

There are two fields in the function arguments window: “Number 1” and “Number 2”. Select the first field and click on the cell with the number “2”. Its address will be written into the argument line. Click on “Number 2” and click on the cell with the number “17”. Then confirm the action and close the window. If necessary mathematical operations with three or a large number cells, just continue entering the argument values ​​in the fields “Number 3”, “Number 4” and so on.

If the value of the summed cells changes in the future, their sum will be updated automatically.

Rice. 7 – result of calculations

Creating tables

You can store any data in Excel tables. Using the function quick setup and formatting, it is very easy to organize a control system in the editor personal budget, list of expenses, digital data for reporting, etc.

Tables in Excel have an advantage over a similar option in Word and others office programs. Here you have the opportunity to create a table of any size. The data is easy to fill out. There is a function panel for editing content. Besides, ready-made table can be integrated into docx file by using regular function copy-paste.

To create a table, follow the instructions:

  • Open the Insert tab. On the left side of the options panel, select Table. If you need to consolidate any data, select the “Pivot Table” item;
  • Using the mouse, select the space on the sheet that will be allocated for the table. And also you can enter the data location in the element creation window;
  • Click OK to confirm the action.

Rice. 8 – creating a standard table

To format appearance the resulting sign, open the contents of the designer and in the “Style” field, click on the template you like. If desired, you can create your own view with a different color scheme and selecting cells.

Rice. 9 – table formatting

Result of filling the table with data:

Rice. 10 – completed table

For each table cell, you can also configure the data type, formatting, and information display mode. The designer window contains all the necessary options for further configuration of the sign, based on your requirements.

Adding graphs/charts

To build a chart or graph, you need to have a ready-made plate, because graphical data will be based precisely on information taken from individual rows or cells.

To create a chart/graph you need:

  • Select the table completely. If a graphic element needs to be created only to display the data of certain cells, select only them;
  • Open the insert tab;
  • In the recommended charts field, select the icon that you think will in the best possible way visually describe tabular information. In our case, this is volumetric pie chart. Move the pointer to the icon and select the appearance of the element;

    • In a similar way, you can create scatter plots, line diagrams, and table element dependency diagrams. All received graphic elements can also be added to text documents Word.

    The Excel spreadsheet editor has many other functions, however, for initial work The techniques described in this article will suffice. In the process of creating a document, many users independently master more advanced options. This happens thanks to the convenient and clear interface latest versions programs.

    Thematic videos:

A pivot table in Excel is a powerful data analysis tool that will help you quickly:

  • Prepare data for reports;
  • Calculate various indicators;
  • Group data;
  • Filter and analyze the indicators of interest.

And also save you a lot of time.

From this article you will learn:

  • How to make a pivot table;
  • How to use a pivot table group time series and evaluate the data over time by year, quarter, month, day...
  • How to calculate a forecast using a pivot table and Forecast4AC PRO;

First, let's learn how to make pivot tables.

In order to make a pivot table, we need to build the data in the form of a simple table. Each column must contain 1 analyzed parameter. For example, we have 3 columns:

  • Product
  • Sales in rub.

And in each line The 3rd parameters are interconnected, i.e. for example, on 02/01/2010 Product 1 was sold for 422,656 rubles.

After you have prepared the data for the pivot table, set the cursor to the first column to the first cell of a simple table, then go to the "Insert" menu and click the "Pivot Table" button

A dialog box will appear in which:

  • you can immediately press the "OK" button and the pivot table will be displayed in a separate sheet.
  • Or you can configure the parameters for displaying data from the pivot table:
  1. Range with data that will be displayed in the pivot table;
  2. Where to display the summary (in new leaf or to existing (if you select to existing, you will need to specify the cell in which you want to place the pivot table)).


Click "OK", the pivot table is ready and displayed in a new sheet. Let's name the sheet "Summary".

  • On the right side of the sheet you will see fields and areas that you can work with. You can drag fields into areas and they will be displayed in the pivot table on the sheet.
  • On the left side of the sheet is a pivot table.


Now, left-click the “Product” field and drag it to the “Line Name” field, “Sales in rubles.” - in "Values" in the pivot table. Thus, we received the amount of sales by product for the entire period:


Grouping and filtering time series in a PivotTable

Now, if we want to analyze and compare sales of goods by year, quarter, month, day, then we need add relevant fields to the pivot table.

To do this, go to the “Data” sheet and insert 3 empty columns after the date. Select the “Product” column and click “Insert”.

Important so that the newly added columns are within the range of an existing table with data, then we will not need to redo the summary table to add new fields, it will be enough to update it.

We name the inserted columns “Year”, “Month”, “Year-Month”.

Now in each of these columns we add the corresponding formula to obtain the time parameter of interest:

  • Add a formula to the "Year" column =YEAR(with reference to date);
  • Add the formula to the "Month" column =MONTH(with reference to date);
  • Add the formula to the "Year - Month" column =CONCATENATE(link to year;" ";link to month).

We get 3 columns with year, month and year and month:


Now go to the “Pivot” sheet, place the cursor on the pivot table, right-click the menu and click the "Update" button. After updating, in the list of fields we have new pivot table fields"Year", "Month", "Year - Month", which we added to a simple table with data:


Now let's analyze sales by year.

To do this, we drag the "Year" field into the "column names" of the pivot table. We get a table with sales by product by year:


Now we want to go even deeper down to the month level and analyze sales by year and by month. To do this, drag the “month” field under the year into the “column names”:


To analyze the dynamics of months by year, we can move the months to the summary area “Row names” and get the following view of the summary table:


IN this submission in the pivot table we see:

  • sales for each product in total for the whole year (line with the name of the product);
  • in more detail, sales for each product in each month over 4 years.

Next task, we we want to remove sales for a certain month from the analysis(for example, October 2012), because We don't have sales data for a full month yet.
To do this, drag "Year - Month" into the summary area "Report Filter"

Click on the summary filter that appears above and Check the box "Select multiple elements". Then in the list with years and month numbers, uncheck 2012 10 and click OK.


This way you can add new parameters for changing time and analyze those time periods that are interesting to you and in the form in which you need it. The pivot table will calculate indicators for the fields and filters that you set and add to it as a field of interest.

Forecast calculation using a pivot table and Forecast4AC PRO

Let's build sales using a pivot table by product, by year and by month. We will also disable the overall totals so that they are not included in the calculation.

In order to disable totals in the pivot table, place the cursor on the “Grand Total” column and click on the “Delete Grand Total” button. The summary result disappears.



and click the "Graph Forecast Model" button in the Forecast4AC PRO menu

We get a forecast calculation for 12 months and beautiful graph with analysis of the forecast model (trend, seasonality and model) against actual data. The Forecast4AC PRO program can calculate forecasts, seasonality coefficients, trends and other indicators for you and build graphs based on the data displayed in a summary table.


A pivot table in Excel is a powerful tool for data analysis that allows you to quickly calculate indicators and construct data in the form you are interested in quickly and easily.



Related publications: