What is a Pivot Table in Excel ?

The aim of this page is to introduce Excel Pivot Tables, what do they do and how they allow the user to summarize and explore the data of an Excel table. This a feature enabling to prepare tables using an Excel 'table like' data as a source is integrated into Excel.

It's a Pivot table as it allows the user to perform cross analysis of the data -let's call them the facts- following different axis - let's say the dimensions-. The facts are in general measurable data, numerical data (numbers or dates). As they're measurable, calculations or functions can be applied on them (sums, count, minimum, maximum). The dimensions can be of any format of data (numerical or text). A fact can be used as a dimension as long as the number of values is limited. This allows to use in full the available data to perform the analysis.

This table is dynamic as it's stays connected to the source table. Thanks to its 'Refresh' feature, it's possible with a simple click to refresh the table in order to take into account possible modifications of the source data. This offers a great flexibility in the computed tables.

In order to build a Pivot table, it's necessary to set a source a of data, in general in the form of organized Excel data sheet. In particular, the first row must contains the table headers with no blank cells and no duplicate names, and each column below must preferably contain homogeneous data types. That's more or less like a database table

In the case of a non structured file, some controls or simple manipulations, (data transposal) allow to prepare the data for its use with the Pivot table feature.

  Every data analysis task starts as a first step with a careful observation of the data, during which the analyst try to get the first idea of the data and its quality.

  To do so, Pivot tables allow working with the data. Data should be agregated according to different dimensions, to help detect meaningful patterns or evaluating missing/ outlier data.   With some practice an Excel workbook can host different analysis pivot table, and some filtering forms, that can be re-used if for example the source data is updated.

  The aim is to assign a name to the source data of the pivot table, in order to make reference to it more easily.

  It's easier to name the Excel data range corresponding to the monthly Sales "Monthly Sales" than always have to remember that it corresponds to the Excel range "A3:F3223" of the sheet named "Sales".

In order to do that, the user must start by selecting the desired Excel data range and access the Menu "Insert/Name/Define" which will display a dialog box allowing to specify the name of the current selected range.

  As mentionned above, once the data range has a name it simplifies the creation of the pivot tables based on that data, you only need to specifiy the range name, and Excel will use the associated data range to build the tabe. Also in case of data update, the name underlying data range can be updated by re assigning the same name to the newly updated data range. Then there's no need to update the existing pivot tables, the user nly have to refesh them and Excel will update the table with the updated data.

  Some VBA code can also be used to automaticly update the name reference and refresh the pivot table allowing to automate the process.