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.
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.
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.
2. For the next examples, remove the filter again by clicking on All.
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.
3. Choose Pie.
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).
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.
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.
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.
As you have noticed, working with pivot charts is very similar to working with pivot tables and charts.
1. Data Set