Pivot Tables : A Working Exercise
Microsoft claims that
they run their entire organization using Pivot Tables. Further, in the
Advanced Excel classes I teach to CPAs, approximately 10% use Pivot
Tables and indicate that they are the most important reporting tool they
use. Given the enormous success of Microsoft, as well as the strong
endorsement from CPAs who use heavily use Excel, perhaps we should pay
more attention.
Pivot Tables may be one
of the most powerful features of Excel. However the first time you use
them, be prepared – they do require more than a few neurons in order to
grasp the Pivoting concept. Simply stated, PivotTables present
multidimensional data views to the user. To re-arrange the worksheet,
users are allowed to drag and drop column headings to move data around.
PivotTables are a great data analysis tool for management. Don’t get
discouraged! Save your information first, and give PivotTables a try.
To help you get started, the following exercise will walk you through
it.
The enhanced PivotTable
now provides multidimensional data analysis and modeling. If you have
never used a pivot table before, initially the concept can be difficult
to grasp. The best way to understand a pivot table is to picture that
you have a stack of financial statements on your desk, where each
statement has multiple columns and rows of data. Imagine further that
you could quickly rearrange the data on these statements by “Pivoting”
the column headings into the row position.
Alternatively, you
could pivot the page by page headings into a columnar format. These
actions allow you to quickly view the data in different formats and in
some cases, may provide a better view of your data which yields more
informative observations. As shown in the screen below, there are
several parts to a PivotTable.
The Page, Row and
Column fields shown above can be rearranged simply by clicking and
dragging them to the page, row, or column position. This action
automatically rearranges your pivot table to provide the newly revised
view. To create a pivot table, start with an Excel spreadsheet –
preferably one which contains several data headings. As an example,
let’s start with a page of data summarizing the results of 4 separate
marketing campaigns conducted in three different cities as shown below:
Next place the cursor anywhere in the data area, and then start the
Pivot Table process by running the Pivot Table wizard as shown below:
The Pivot Table Wizard will walk you though the process of creating your
initial pivot table. The Pivot Table can be easily changed as the Pivot
Table Wizard can be recalled instantly to modify the pivot table
changes.
The results are that Excel creates a blank Pivot Table as shown below,
and the user must drag and drop the various fields from the Pivot Table
Field List onto the appropriate column, row, or data section. As you
drag and drop these items, the resulting report is displayed on the fly.
Presented below are but a few examples of hundreds of possible reports
that could be viewed with this data through the Pivot Table format.
This report shown above shows the total resulting sales for each
marketing campaign for each of the 4 months marketing campaigns were
conducted.
In this screen we see
the same information shown as a percentage of the total. A few
observations include the fact that overall Radio Spots are the most
profitable type of campaign, but only in April in July. In January and
October, Local ads and direct mail respectively produce better results.
Further, April campaigns had the best response overall.
Further analysis in the
screen above tells us that our results vary widely from one city to the
next. In New York, coupons were least effective, but coupons were most
effective in Columbus. Pivot charts based on pivot table data can be
modified by pivoting and/or narrowing the data. They can also be
published on the Internet (or on an Intranet) as interactive Web pages.
This allows users to “play” with the data. The chart below provides a
visual look at the data shown above.
Now please be aware
that this exercise just brushes the surface of Pivot Tables. To push the
limits, you should click through the options in the Pivot Table wizard,
as well as the options in the Pivot Table dialog box that pops up when
you right mouse click on your Pivot Table. You should also read through
the Excel HELP files on Pivot Tables, and finally - apply Formatting,
Subtotals, and AutoFilters to your Pivot Tables. Hey, if you can pass
the CPA exam, you can tackle this tool. Good luck.
If you want to give
this a try yourself, you can download my example Excel 2002 file here:
http://www.exceladvisor.net/sub/creative/web-pivot.xls
- END -
