At
some point in your Excel work, you’ll probably come across a spreadsheet that
you wish was formatted a little differently. In this series, you’ve learned about reformatting your spreadsheet
to look different and have learned how to handle data presentation issues
through the use of subtotals and sorting. But, what about the actual structure of your information? Through
the use of PivotTables, Excel provides
a powerful means for you to temporarily modify structural elements of your
worksheet without having to change your source data.

In
this document, I’ll continue using the budget spreadsheet example I’ve been
using for this Excel data analysis series and show you how to create PivotTables.

Data analysis series

This data
analysis series consists of these articles:

  1. Part
    1: Sorting and Filtering
  2. Part
    2: Subtotals
  3. Part
    3: PivotTables
  4. Part
    4: Simple Graphs
  5. Part
    5: Advanced graphing and PivotCharts

Prepping the data

For
example, suppose you have a list of transactions sorted by date (Figure A), but, for a particular report
you’ve been asked to provide, it would be beneficial for the data to be
presented in a more tabular fashion. Maybe, for example, senior management has
asked for a table similar to the one in Figure
B.

Figure A
This list, which we’ve been using for all of the examples in this series,
can provide you with good information when you use the right tools.

Suppose
senior management has asked you to develop a report using the format similar to
the one shown in Figure B and then
to keep it updated every month. Sure, you could use subtotals and do it
manually each month. However, using Excel’s PivotTables feature,
you can avoid this tedious work and let Excel do the work for you.

Figure B
But, suppose senior management wants a list like this.

A
PivotTable is nothing more than a different, flexible look at your data. A
PivotTable combines the best of subtotals, sorting, filtering and rearranging
to allow you to see your data in a new light.

A
PivotTable is based on source data,
in this example, the data shown in Figure
A. The great part about a PivotTable is that, even though it is created
separately from your data, the PivotTable stays current with changes to your data.
So, as you make changes to the source data, those changes are immediately
reflected in a PivotTable meaning that you can create a PivotTable just once. For
the example above, a monthly look at that budget report using the format shown
in Figure B is an exercise in
simplicity when created with a PivotTable.

A
little advanced planning can make your foray into PivotTables significantly
easier. For example, in this example, I want to create a PivotTable that is
broken down by month. However, you’ll quickly notice that there is no
standalone month field. However, there is a date field that includes the month.
So, I can insert a column into my work that includes just the month field.

To
start, I’ve inserted a new column between columns A and B, and given it a
heading of “Month”. Next, in cell B2, I’ve inserted the formula “=MONTH(A2)” and copied this formula all the way
down my data list. (See Figure C)

Figure C
This new column contains just the month for each record. This will make
creating the PivotTable much easier.

How
did I know that I would need this field? Honestly, trial and error. I initially
tried the PivotTable without this field, and was not able to get my data into a
useable format. What does this mean for you? Simply put, if you can’t make a
PivotTable work exactly like you want it to, sit back and figure out what you
need and then create it. The nice part about the formula I created to make my
PivotTable work is that it’s automatic. As I add records for future months, I
don’t need to manually add the month field.

For
this section, I’ll use the example I outlined above. By the end, you’ll see a
PivotTable with the layout in Figure B
that uses the data from Figure A.

Creating a PivotTable

To
create a PivotTable, make sure you have selected a cell inside your data, and
then choose Data | PivotTable and PivotChart Report. This starts a wizard that
walks you through the process of creating a PivotTable.

You
can create PivotTables using various data sources. For this example, I will use
the data from the budget chart you saw earlier. Therefore, I chose “Microsoft
Office Excel list or database” for the first option on Step 1 of the
wizard. On this same screen, I opted to create a PivotTable rather than a
PivotChart report. PivotChart reports will be discussed in part four of this
series.

Step 2
of the wizard asks you to identify the data range that should be used for the
PivotChart information. For this example, I’ve selected all of my data, as
shown below in Figure D. This is
called the source data.

Figure D
Click the Next button to move to step 2.

The
final step of the wizard asks you to decide where you want your PivotTable to
appear. You can have it added to the same worksheet as your source data, or you
can choose to create a new worksheet with the table. Note that this new
worksheet is not a new file on your computer. It’s just another tab at the
bottom of your Excel workbook. Figure E
shows you that step, while Figure F
shows you the new worksheet, with a blank PivotTable.

Figure E
Make sure you include all data you want on the new report. Don’t worry
about it if you select too much information. For example, I probably don’t need
the Authorized column on my report, but it doesn’t hurt to include this column
in the range.

Figure F
I’ve opted to create a new sheet for the PivotTable report.

If you’re
seriously confused by what you’re look at, don’t worry. A lot of people find
PivotTables somewhat confusing and intimidating at first.

Here’s
a look at what each item on the screen (See Figure G) is for:

  • Drop Page Fields Here: These are the fields that will be
    used to break down your data by page. For example, if you wanted to have a
    separate page for each category, you would drag and drop the Category field
    from the “PivotTable Field List” to the “Drop Page Fields Here”
    section.
  • Drop Row/Column Fields Here: I’ll be explaining these as I go.
  • Drop Data Items Here: Into this section, you will drop
    the individual data items that you want to see on your report. In this example,
    I will be dropping the amount field here.
  • PivotTable Field List: Notice that the PivotTable field
    list matches the column headings in the source data.
  • PivotTable: This is the PivotTable toolbar and
    has a lot of options, many of which will be discussed before the end of this
    article.

As a
refresher, for this example, I want to break down the expenditure list by
month. Achieving this goal takes just a few steps.

Figure G
Here’s a look at the results of the PivotTable wizard.

Steps

First,
if you look back at Figure B, you’ll
see a list of months across the top of the page. Each column is a new month. So,
to make this happen, I’ll drag the Month field to the “Drop Column Fields
Here” box.

In Figure H, notice that the month field
in the PivotTable field list is boldface. This signifies that it is in use by
the PivotTable. Look at the column section. Each month is automatically placed
in its own column. I’ll explain more capabilities regarding this section later
on.

Figure H
The column data is automatically placed across three cells, each
representing a month.

Now,
back in Figure B, you also saw that
the rows were made up of each of your expenditure categories. You’ve probably
guessed that this is to where you will drag the “Category” field from
the PivotTable Field List.

Things
are starting to take shape (Figure I).
All that’s left is to throw in the amount for each category and month. To do
this, drag the amount field into the “Drop Data Items Here” section.

Figure I
Like the column data, Excel automatically parses out your row data.

In Figure J, you see the breakdown that
finishes the example. You are looking at a breakdown, by month and category,
which summarizes total expenditures. With information laid out in such a way,
you can begin to try to identify trends in the information. Trending is much
easier when you use graphs, which are presented in the next part of this
series.

Figure J
The completed table.

This
single example was intended to show you how quickly and easily you can create
your own PivotTables. However, with your PivotTable created, there’s a whole
lot more you can do now to clean it up, modify it, and tailor it to your exact
needs.

Suppose
that you want a more granular look at your budget data. For example, perhaps
you want to see how much each department is spending in each category per month.
Even though you already have a field in the row section of
your PivotTable, that doesn’t preclude you from adding another. Take a
look at Figure K. In that example, I
have added the Department field to the row section of my sample PivotTable.

Figure K
Take a look. Now, for each expenditure category, you’re provided with a
departmental look at the spending.

Like
any Excel spreadsheet, you can format your PivotTable information to your
liking. For example, since each of the data points is a dollar amount, you
could apply the currency format to the data so it looks a little neater. (Figure L)

Figure L
The currency formatting cleans things up a little.

Excel’s
PivotTable function also provides you with a number of quick formatting options.
From the PivotTable toolbar, choose PivotTable | Format Report, which opens the
AutoFormat window. (Figure M)

Figure M
Choose the style you like and click OK.

Once
you choose a style you like, your PivotTable report will take on all of the
formatting characteristics of that style, which often results in a much cleaner
and more readable table. Figure N
shows you an example of a formatted PivotTable.

Figure N
This look is a lot cleaner than the default.

As
your PivotTable grows as a result of adding information to your source data,
you might want to get a glance at specific information. Remember back to an
earlier part in this series in which you learned about filtering data. Notice
the down arrows next to each field name. If you click one of the down arrows,
the result is the same as when you have applied the AutoFilter functionality to
an Excel worksheet. You get a list of all of the values in that particular
field, as shown below in Figure O.

Figure O
From this window, you can pick and choose what data in used in your report.

I’m
not going to go over the filtering functionality in great detail since it
performs mostly the same function as the AutoFilter.

Graphing

One of
the more powerful features available with PivotTables is the ability to graph
the results. This kind of graphing, along with other graphing options in Excel,
is presented in the next part in this series.