How to build pivot tables in Excel 

Let's dive into creating a Pivot Table using a sample dataset. Suppose we have a range of cells containing sales data for a fictional company. 

The first thing to do is to convert the range into a Table

We therefore go to the 'Insert' menu and select the 'Table' option

A window will appear asking to select the range to be converted into a table 

Make sure the checkbox 'My Table has headers' is ticked as this is a key feature when using pivot tables 

To transform a range into a table you can also select the entire range and then use the keyboard shortcut CTRL + T

Excel assigns a name to each table created. 

The name of the table can be viewed by selecting the menu 'Table Design' and the first section on the left of the ribbon will show the table's name. 

A table can always be identified by the small black triangle at the bottom right corner of the cell placed in the last row and column of the table 

The triangle can be dragged in all directions in order to modify the number of rows and columns in the table

Next step is to place the cursor in any cell of the table and go to the 'Insert' menu and click on pivot table icon 

A dialog window will appear in which the cells belonging to the table have been already selected ( 'Table1')

Excel then wants to know if the Pivot Table needs to be created in the same sheet where the table is placed ('Same Worksheet') or in another sheet ('New Worksheet')

In this case we will be selecting the ' New Worksheet' option

Clicking on OK will open a new sheet. 

On the left side of the sheet you will see a blank pivot table and on the right side a field list  

The pivot table will be created by choosing in the filed list which columns of the original table will be placed in the Rows, Columns and Values boxes

The PivotTable Fields shows a first section in which the column headers of the table are reported 

Below the first section we find the basic structure of a pivot table consisting of 4 main areas: rows, columns, values and filters.

Dragging the elements in the first section to the second will build a pivot table (more on this below)

The first step to build the pivot table is to decide which column headers will be dragged in the Rows box

When a column header is dragged into the Rows box, each unique values in the designated column become the Rows label in the pivot table

Columns represent a second dimension to organize data in a pivot table 

Unique values in the selected column will represent a column header in the pivot table

The Values box performs a calculation on the data that meets the criteria on the Rows and Columns fields.

Values represent an aggregation (Sum, count, average,..)

Numeric fields, by default, are summed

For non numeric fields the default operation is count 

In upcoming posts we'll show how to change the different types of aggregations in the Values box

Let's build our first pivot table. We drag the Products in the Rows, the Date in the Columns and the Sales Amount in the Values

In the columns section you will find additional elements to the ones you have dragged. This is because the pivot table has recognised the date format and added additional fields to allow the cropping of the columns. We will come back to this aspect in later posts

As the field dragged in the Values field is numerical, pivot table has by default performed a Sum.