Pivot Table

Only available on StudyMode
  • Download(s) : 230
  • Published : October 19, 2010
Open Document
Text Preview
custom SKILL

custom skill custom skill
Creating and Modifying Pivot Tables and Charts
A PivotTable Report (commonly called a pivot table) is a specialized report in Microsoft Excel that summarizes and analyzes data from an outside source like a spreadsheet or similar table. That is, a pivot table is a tool for taking a large and complete amount of data and formatting it in a table that makes that same information easier to understand and assimilate. You generally will create a pivot table when you want to do one of the following: extract a smaller amount of data from a larger set of data sum up a large amount of data and compare one section of the original data with another or organize sub-categories of data within larger categories. It is important to organize an Excel spreadsheet properly, but especially so when you may want to create a pivot table from it. When creating a spreadsheet, remember the following advice: Label your data well. For example, the first row of an Excel spreadsheet should have clear, descriptive column labels. Verify that each spreadsheet column contains only one set of data. For example, a column labeled Fname should contain only the first names of salespersons or vendors or customers, etc, and a column labeled Total should sum up the same type of data from cell to cell. Keep your spreadsheet free of automatic subtotals. Pivot tables will calculate subtotals and totals for you. A PivotChart Report (commonly called a pivot chart) represents in graphical form the data from a pivot table. You can modify the layout and data from a pivot chart just as you can those of a pivot table. Finally, you can use the GETPIVOTDATA function in a worksheet to create a formula that will produce, under many conditions, a consistent answer even if you later rearrange the pivot table. Lesson Goal: Understand how to plan a pivot table. Create an Excel pivot table, change its summary function, and analyze three-dimensional data. Update a report and then modify its structure and format. Finally, create a PivotChart Report and use the GETPIVOTDATA function.

skills
1. Planning a PivotTable Report 2. Creating a Pivot Table 3. Modifying the Summary Function of a Pivot Table 4. Creating a ThreeDimensional Pivot Table 5. Updating a Pivot Table 6. Modifying the Structure and Format of a Pivot Table 7. Creating and Modifying a PivotChart Report 8. Using the GETPIVOTDATA Function

1

C U S T O M

S K I L L

C U S T O M

S K I L L

C U S T O M

S K I L L

LESSON ONE

EX A.2

Creating and Modifying Pivot Tables and Charts

skill 1
overview how to

Planning a PivotTable Report

A PivotTable Report (commonly called a pivot table) is an interactive, cross-tabulated report in Excel that summarizes and analyzes data from an outside source such as an Excel spreadsheet or similar table. Using a pivot table, you can summarize selected data from a worksheet, list and display it in a table format, and organize the data in meaningful ways. Before you create a pivot table, however, you should plan it out so that the process of creating the table goes more smoothly.

Planning a PivotTable Report involves several steps: 1. Review the information in your source spreadsheet or other table. Before summarizing an Excel spreadsheet or similar set of data in a pivot table, be sure to understand the range of information that the pivot table will cover. Also understand what fields of information will appear in the pivot table. A field is a particular type of data about a person, place, or object. For example, when creating fields related to personal friends, you might want to indicate their names, addresses, phone numbers, and related information. However, when creating fields for a company’s employees, you instead may need to list their job occupations, work location, annual salaries, etc. Figure A-1 displays a list of student interns, their college enrollment dates, their academic class, and so on. 2. Determine the...
tracking img