We have the answers to your questions! - Don't miss our next open house about the data universe!

PivotTable function

- Reading Time: 3 minutes

Excel’s PivotTable function is a tool of choice for Data Analysts. It brings to Microsoft’s spreadsheet software functions that are usually found in database management systems:

  • analysis
  • exploration
  • synthesis

In other words, it can be used to group data from a table and obtain statistical information on all or part of the data.

New with Excel 5

Pivot Tables were introduced by Microsoft in Excel in 1993 with version Excel 5.0.

Before pivot tables, analysts who wanted to explore the information in a table had to use Excel’s standard functions and formulas to sort and filter the data and obtain totals and averages from the samples. The process was long and rather complex.

It was therefore necessary to offer Excel users a simpler and more flexible method, one that didn’t require them to write formulas and filter the data.

The PivotTable function was the answer. It quickly became one of Excel’s most widely used tools for data analysis. Many management consultancies (such as McKinsey, Boston Consulting Group and Bain & Company) have been won over by it.

The same has been true of many banks and financial institutions, keen to be able to analyse data on loans, investments and other financial activities. Retail companies have also adopted it, as have technology companies. More generally, in any business with huge amounts of data to manage, PivotTables have been adopted, because of the ease of use of this Excel tool.

Concrete examples of application

The PivotTable function can answer questions such as :

  • What was the best-selling product over the past month?
  • Which sales person or territory performed best?
  • Which tasks on a project are taking longer than expected?
  • How do expenses vary from month to month?
  • Which marketing strategies are most successful?
  • What is the number of clicks generated by a given campaign?

As we can see, the PivotTable function is very powerful and can be compared to a function such as GROUP BY in SQL databases, which also helps to group data together in order to obtain summary information. The difference, of course, lies in the volume of data that can be analysed, which can be much greater in an SQL database. Remember that Excel tables can hold a maximum of 1,048,576 rows, which is still a considerable amount.

What uses for pivot tables?

The PivotTable function is particularly useful for :

  • Summarising vast quantities of data: thousands of rows can be summarised to produce a simple table that is easy to interpret.
  • Statistical analysis of data: This function makes it easy to obtain sums, averages, counts, etc.
  • Customised reports: The ability to organise information as you see fit can help to illustrate a report’s arguments, especially as the pivot table can be represented graphically.
  • Comparing data: You can compare different performances from one quarter or year to the next.
  • Bringing out trends: The summary provided by a pivot table will inevitably bring out trends that might otherwise have escaped observation.
  • Filtering and sorting: A pivot table makes it easy to sort or filter information according to various criteria.

A few rules to follow

Before a table can be submitted to the PivotTable function, it must comply with a certain number of principles:

  • The table must not contain any empty cells.
  • The format of a given column must be consistent for all the values in that column: dates only, numbers only, text only.
  • A header row must of course be present and filled in for each column.
  • There must be no merged cells.
  • The data must be reliable and verified: if the original table contains incorrect information, this will inevitably find its way into the analysis.

Using pivot tables

First, you need to click anywhere in the table to be analysed so that Excel can locate it.

Then select PivotTable from the Insert menu.

Next, you need to indicate where you want to place the PivotTable – on the same sheet or on another sheet.

In the Design PivotTable window, you can tell Excel what type of summary table you want. The column headers appear and some of them need to be dragged into the “ROWS”, “COLUMNS”, “VALUES” and “FILTERS” areas.

  • The “ROWS” fields appear as rows in the pivot table.
  • COLUMNS” fields appear as columns.
  • The “VALUES” fields are the data which will be summarised.
  • The “FILTER” fields are used to filter the original table.

This allows you to define analyses such as: total amount per day. Note that sum is the default function, but you can choose other formulas such as average or count.

💡Related articles:

Excel VBA: How to automate tasks with this language
Pie Chart Excel: Mastering Data Visualization
Creating Stunning Excel Charts: A Step-by-Step Guide
Efficient Excel Data Formatting Based on Cell Values
How do I insert columns and rows in Excel?

Updating the pivot table

Please note: by default, PivotTables are not updated when the original table is modified. You should therefore remember to update this summary table so that it reflects an up-to-date situation.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter


Get monthly insider insights from experts directly in your mailbox