Basic Pivot Table Example

Microsoft describes PivotTables as “A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so that you can easily see comparisons, patterns, and trends. Both reports enable you to make informed decisions about critical data in your enterprise. The following sections provide an overview of PivotTable and PivotChart reports.” (see here).

And indeed PivotTable is a query tool that quickly generates reports on top of a base table of data. Once you get the basics, it is easy, interactive and very useful tool to know.

As always, let’s see that ability in a simple example:

Let’s assume we have a detailed report of product sales transactions. The report has a lot of information about each transaction, including the product code, the geo, the channel, and the amount.

sales data

And we want to run reports and analyze this date. We do that by creating Pivot Table like this:

Select the entire data area and go to the “Data” menu and select “PivotTable…”. A menu like below will open:

PivotTable dialog

Adjust the range if needed and for this simple example just leave all other parameters without a change.

The result is a new sheet that looks like this:

PivotTable Empty

Now we can dynamically select items to display and to summarize.

The simplest example: Let’s create a report of the total sales by GEO.

1. In the “PivotTable Builder” dialog, Drag & Drop the “Geo” field to the “Row Labels”.

2. Drag & Drop the “Price” field to the “Values”. By default the Count function will be applied.

3. Click on the little “i” near the Price field and a new dialog will be shown like below. Select there the function “Sum”.

PivotTable Function selection

4.Format the PivotTable column to show nice $ formatted data.

You should get a report like this:

Basic PivotTable report

Now let’s extend this example and create a report showing Total and Average price by Geo/Channel.

We will continue from the previous report.

1. Open again the “PivotTable Builder” dialog by either clicking on the previous report or by navigating to the PivotTable tab option and clicking on the Builder icon.

2. Drag & Drop the “Channel” field to the “Row Labels”. Make sure it is the second row on that list.

3. Drag & Drop the “Price” field to the “Values”. Should now have two lines under “Values”.

4. Adjust the second “Price” line to use Average function. At this stage the Builder dialog should look like this:

PivotTable Builder 2

5. Format the result report as needed.

You should have a report like this:

Basic PivotTable report 2

We are now masters of the simple PivotTable example.

Advertisements