Pivot Tables

What are Pivot Tables?

• A pivot table gives you a way to group, summarize and compare data from a spreadsheet

• You can do some of the same tasks with COUNTIFs and SUMIFs, but they are much easier with pivot tables

• John Walkenbach says, “Excel’s pivot table feature is, arguably, its most innovative and powerful feature.” [pg. 565]

Getting Started

• We’ll start with a small example created with the RealEstateLoops application.

• In reality pivot tables really shine when applied to large file, but this will let us see everything in action.

• [Note.. These examples were created on a Windows computer. We’ll briefly show the Mac version as well. The examples transferred over to the Mac with no problem.]

Example Data

About the Data

• Your data should be in a contiguous rectangular table and every column should have a heading

• Get rid of any blank rows or columns within the data before you start

• Highlight your data range and then you are ready to start (although if you just select the upper left corner, and your data is surrounded by blank rows and columns, Excel will figure things out; useful for very large tables)

Pivot Tables are on the Insert tab(Data tab on the Mac)

•

Mac screen shot showing data tab and pivot table button

The Pivot Table Dialog

I created a sheet named pivot in my current workbook earlier and entered this information

I started with cell A1 selected and Excel decided I wanted to use my whole table

Dialog to Create Table

My next step is to choose some fields

I chose Neigborhoods and got this…

Clicking the box to the right of Row Labels gives me a bunch of choices

I decided to sort…

Now I have this

I also chose Price

Excel totaled the sales for each neighborhood

Now I added Agent

Excel broke down the neighborhood information by agents

Starting over, select Agent first

Now add Price to see Agent totals

And here it is broken down by neighborhood for each agent

The final result, with formatting added for the dollar amounts.

Another Approach

• Instead of clicking the boxes by the field names, you can drag them to the boxes at the bottom

• On the next slide, I dragged Agent and then Neighborhood to the Row Labels box, and then Sales and then Commission to the Values box. Then I formatted the columns with the dollar values in them

How many times does each appear?

Here I dragged Agent to both the Row Labels and Sum of Values boxes. I got the count of how many sales each agent had.

I also dragged price to the Sum Values box.

Filters

• You can apply a filter to your data to only see selected items

• For example, you might want to only see entries greater than or less than a certain number, or the top 10 values, or something similar

To Add a Value Filter…

First, click the drop-down arrow next to the Row Labels heading and choose Value Filters

Choose a Filter…

Let’s look at who sold over $1M

Complete the Dialog…

Here’s the Result

You can clear the filter so all the data shows again…

Mac Version…

I selected the upper left corner of the data and clicked the pivot table button

I dragged Agent to Row Labels

I interchanged Agent and Price

Bottom Line

• Pivot tables are a very useful and powerful tool

• You can do everything with programming that you can do with pivot tables, and more

• But if a pivot table takes care of what you need, then it’s much quicker to use

• Playing with the pivot tables for a bit will help you learn how to use them