MVP #49: A Simple Practical Explanation of How To Use PivotTable and PivotChart in Excel

PivotTable is Excel’s premium tool for working with huge data table and even data stored in other database systems like Access, SQL servers and MySQL servers.

Below is an example of a large data table we will use PivotTable on to do some very relevant quick analysis. It is a table of sales for a particular Pizza Restaurant for a day and it has 5000 entries. You can download the practice file here: PivotTable and PivotChart File


So how can we make a report that will show us the sales performance that day by the different type of Pizzas the restaurant sells.  A report like the one below:


It’s quite easy with PivotTable.

You start by selecting the sales transaction table or selecting one of the cells in it. Then go to Insert menu and click on PivotTable.


In the screenshot above, I selected one of the cells in the table, clicked on Insert menu, clicked on PivotTable, confirmed that my entire table has been selected and clicked on OK.

You will be taken to a new sheet that looks like the one below:


At first it looks really different, like you are no longer in Excel. But it is very easy to work with. The core part is the part of the right with name PivotTable Fields. It has a list of all the fields in the original data table. The part below the field names are where you actually set up your report.

Whatever field you want to display its unique entries, one per line/row, you will drag to ROWS. Let’s do that for the Pizza Sold field so we will be able to see all the pizza types the restaurant sells.


Then if it is that you want to display those unique entries one per column, drag the field to COLUMNS. Let’s see what will happen if drag that Pizza Sold field from ROWS to COLUMNS.
`

So now you understand how ROWS and COLUMNS work. 

Drag Pizza Sold back to ROWS, that is where we need it for our quick analysis.

Next is VALUES. Whatever you want to do a mathematical calculation on, you drag to the VALUES part. Common calculations you will find yourself doing are counts (to see the number of times each unique entry occurred in the original table), sum (to add the values a particular field) and average (to average the values of a particular field).

In our case, let’s drag Quantity and Amount Sold fields to VALUES. 


Can you see how quick this is? We have just analyzed a 5000 sales record table in seconds. Now we have a report that shows us how many of each Pizza type was sold and the total sales amount generated.

Those are the type of lightning fast analysis PivotTable allows you to do.

There is now one part we haven’t touched: FILTERS. As the name suggests, it simply gives us the capability to filter our report. We will drag Time Range to FILTERS to see which sales occurred at the peak period (before 9:00am) and after the peak period.



And that’s how PivotTable works. Very easy to use and powerful.

PivotChart

Whenever you insert a chart using data generated via a PivotTable, that chart is a PivotChart. It has some extra functionalities it inherits from the PivotTable which makes it a little different from the regular charts we have already discussed.

Below is what the PivotChart for the PivotTable we just created look like. To replicate that, just click anywhere in the result of the PivotTable, go to Insert menu and click on the chart you want to insert.



Notice the extra elements on it? Even the Time Range filter is showing on the chart. Besides those extra elements, a PivotChart is same as the regular charts and the same kind of formatting you can do on the regular charts work on PivotCharts.

Once again, you can download the practice file here: PivotTable and PivotChart File