This article describes the creation of basic pivot table. It was written for Excel 2016, but is generally valid even for older versions of Excel.

So, what the hell is a pivot table?

Pivot table is a report providing information, that cannot be easily recognized in original data. 

In this tutorial we will use following table, representing cars from fictional used car dealership.

So, what the hell is a pivot table? – Pivot tables – quick start

Thanks to the pivot table we can quickly respond questions like this:

  • What are the total prices of various brands?
  • How much cars were bought in which month?
  • Are there any differences of average prices based on color?
  • …

Now we will solve the first question – calculate the total prices of cars based on brands.

You can download the sample table here.

You can download the sample table here.

How to do it?

We will start by clicking anywhere into the table – there is no need to select anything more. Then we will click on Insert / Pivot table.

How to do it? – Pivot tables – quick start

Following form can be left as it is and simply click “OK”. 

How to do it? – Pivot tables – quick start

Now new sheet with Pivot Table was created. There is no need to worry about the original data – the table with cars is still available on another sheet.

How to do it? – Pivot tables – quick start

Let’s check the right pane with. There is a list of column headers from original data and four empty rectangles bellow them. The pivot table is created / modified by moving the headers bellow to one of these four places.

We wanted to see the total prices of cars, grouped by brands. So let’s move the “Brand” from upper list to the “Rows” rectangle. This creates the list of all cars on the left side of screen.

How to do it? – Pivot tables – quick startNow we must find out the total prices. Lets move the “Price” to “Values”.

How to do it? – Pivot tables – quick startNow we can see the total prices for any brand present in original data and the original task is finished.

But what if we wanted to know how many pieces of which brand do we have? I mean not the total price, but the number of cars.

Let’s double-click on Sum of Price and in then select Count instead of Sum.

How to do it? – Pivot tables – quick start

You can see this menu also when clicking Sum of Price / Value field setting in the right bottom corner of screen.

If you need both (sum and count), just drag the Price to Values two times – and one of them modify to Count. 

Few tips:

  • If the right pane disappears, just click to the report and the pane appears again.
  • It is very easy to create a pivot chart from pivot table – just click on Chart icon and select the type of chart.
  • More tutorials related to pivot tables