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

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s