Pivot Table in Excel 2007 for Data Organization

Pivot tables are an Excel feature that you should learn how to use. Instead of analyzing rows upon rows of records, a pivot table can aggregate your data and show a new
perspective and few clicks. You can also move columns to rows or vice versa. The problem is people believe creating a pivot table is too difficult to learn. Grab a seat and
we’ll walk you through this mini tutorial using Excel 2007.

What is a Pivot Table ?
You might think of a pivot table as a user created summary table of your original
spreadsheet. You create the table by defining which fields to view and how the data
should be displayed.  Based on your field selections, Excel aggregates and organizes
the data so you see a different view of your data.
As example, I’ve uploaded a sample spreadsheet of 4000 fictitious voters, which
includes the following data fields:
 Voter ID
 Party Affiliation
 Their precinct
 Age group
 When they last voted
 Years they’ve been registered
 Ballot status2

Create a Pivot Chart

You can either directly create a pivot chart (A), or create a pivot chart after you've created a pivot table (B). To directly create a pivot chart, execute the following steps.

A1. Select any single cell inside the data-set.

A2. On the Insert tab, click the down arrow under PivotTable and click on PivotChart.

clip_image002

A3. Pivot tables and pivot charts are connected with each other. From here, you can start creating a pivot table like you are used to doing and the visual representation (pivot chart) will automatically be created for you.

To create a pivot chart after you've created a pivot table, execute the following steps.

B1. Open pivot-table.xls for a pivot table.

B2. Select any single cell inside the pivot table.

B3. On the Insert tab, click any chart type under the Charts group. You can create all sorts of pivot charts, such as column charts, line charts, pie charts, etc. For example, choose Clustered Column.

clip_image004

Result:

clip_image006

 

Filter Pivot Chart

Any changes you make to the pivot table are immediately reflected in the pivot chart and vice versa. That means you can either filter the pivot table or filter the pivot chart. Both give the exact same result.

1. Click the country report filter in the chart area and click on UK.

Result:

clip_image002[15]

2. For the next examples, remove the filter again by clicking on All.

Change Pivot Chart Type

You can easily change to a different type of pivot chart at any time. Execute the following steps to achieve this.

1. Select the chart. The PivotChart tools contextual tab activates.

2. On the Design tab, click on Change Chart Type under the Type group.

clip_image004[18]

3. Choose Pie.

clip_image006[5]

4. Click on OK.

Excel creates a nice pie chart using the data series of Williams. Reason: this is first data series, because we sorted the pivot table on last name (before you opened the Excel file).

clip_image008

Pie charts always use one data series. Want another data series? Simply click the Last Name report filter in the chart area and select another name.

Switch Row/Column Pivot Chart

You can easily swap the data over the axis. This will give a pivot chart of each quarter instead of each person.

1. Either click on Switch Row/Column.

clip_image010

2. Or swap the fields in the Categories and Series areas. Once this has been completed, the result should be consistent with the picture below.

clip_image012

Result:

clip_image014

As you have noticed, working with pivot charts is very similar to working with pivot tables and charts.

Download Files

1. Data Set

2. pivot-table.xls

  • Author:

Be The First One To Comment

Add A Comment