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:
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.
