Download Creating and Modifying Pivot Tables and Charts in Excel: A Comprehensive Guide and more Study notes Design in PDF only on Docsity!
skills
1. Planning a PivotTable **Report
- Creating a Pivot Table
- Modifying the** Summary Function **of a Pivot Table
- Creating a Three-** Dimensional Pivot **Table
- Updating a Pivot** **Table
- Modifying the** Structure and Format **of a Pivot Table
- Creating and** Modifying a **PivotChart Report
- Using the** GETPIVOTDATA Function
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 informa- tion 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 exam- ple, a column labeled Fname should contain only the first names of salesper- sons 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 sum- mary 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.
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
custom skill custom skill
custom SKILL
skill 1
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 creat- ing the table goes more smoothly.
overview
Planning a PivotTable Report involves several steps:
1. Review the information in your source spreadsheet or other table. Before summariz- ing 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 infor- mation 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 objective of your pivot table and identify the names of the fields that related to that objective. The objective of the pivot table is to identify student interns, their work locations, and their current job performance ratings. To create such a pivot table, you should include the students’ last names, the locations of their internship, and their job scores. 3. Identify which fields you want to summarize in the pivot table and which summary function you want to use. The summary function is an automatic subtotal or other calcu- lation used in pivot tables and pivot charts. To gauge the typical grade that instructors give to the current group of interns, you would select the Job Score field and use the MODE function to determine the most frequently appearing grade. 4. Determine how to organize your data. You must organize your pivot table properly to present the desired information in the desired way. To best display the most frequently occurring job score for the data that you want to include, define the Internship as a col- umn field, the Fname (first names) as the row field, and the Job Score as the data summa- ry field. Figure A-2 displays the empty pivot table and indicates the location of the col- umn field area, the row field area, and the data summary area. 5. Decide which worksheet should display the pivot table. You can place the newly created pivot table on the same sheet as your worksheet data or on a new worksheet. To keep your raw worksheet data separate from the interpretive pivot table, you should put your pivot table on a separate worksheet. Figure A-3 displays the completed pivot table on a new worksheet with a new name, along with an added table title and the MODE displayed in cell F.
how to
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
skill 2
Creating a Pivot Table
After you plan a pivot table, you then can create it from an Excel worksheet or similar table. To create a pivot table, you use the PivotTable and PivotChart Wizard. A wizard is a series of interrelated dialog boxes that ask you for data and usually offer options on how to format your data. Using a wizard breaks down a complex task into more manageable steps, thereby helping you to enter correct data and then properly format it so that viewers can easily understand it.
overview
Create a pivot table.
1. Open student file pivot_exhowto2.xls and save it as Pivot_Table_One.xls. This worksheet contains data about student interns, their enrollment dates as students, their current acade- mic class (freshman, sophomore, junior, or senior), their intern level and their job perfor- mance rating—or job score. 2. Click cell A1 if necessary, click the Data menu, and then click the PivotTable and PivotChart Report command. This action opens the first of three dialog boxes in the PivotTable and PivotChart Wizard (Figure A-4). In this dialog box, you specify the location of the data to be converted into a pivot table and whether you want to create a pivot table or a pivot chart from that data. 3. In the section entitled, Where is the data that you want to analyze? , verify that the Microsoft Office Excel list or database option button is selected. In the section entitled What kind of report do you want to create? , verify that the PivotTable option button is selected. 4. Click the Next button to move to Step 2 of 3 in the wizard (Figure A-5). In the Where is the data that you want to use? text box, the cell range for the entire worksheet selected in Step 2 appears by default. You can accept this default cell range or specify a smaller range of cells. Since you want to create a pivot table that shows data for all student interns, leave the default data in the text box. 5. Click to move to Step 3 of 3 of the wizard (Figure A-6). In this dialog box, you select whether to place the pivot table on the same worksheet from which you obtained your data for the upcoming pivot table or on a new worksheet within the same Excel workbook. Accept the default option button, New worksheet.
how to
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
tip
In Step 4, the worksheet selected in Step 2 appears with an animat- ed border (“marching ants”) around it. The range of cells within this border matches the cell range in the Where is the data that you want to use? text box.
Figure A-4 PivotTable and PivotChart Wizard - Step 1 of 3
Figure A-5 Step 2 of 3
Animated border indicates source of data for Step 2 of wizard; cells within border match cell range in text box
In this section, choose the source of the data for the upcoming pivot table
Figure A-6 Step 3 of 3
Choose whether to display pivot table on new worksheet or on existing worksheet
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Cell range indicates data on which upcoming pivot table will be based
In this section, choose whether to create a pivot table or a pivot chart
Figure A-7 Blank pivot table
Figure A-8 Completed pivot table
Open the my_pivot_exprac.xls file, which you created in the previous Practice. Using all data on the Interns worksheet, work through the PivotTable and PivotChart Wizard to create a blank pivot table. Name the pivot table tab InternsTable. Drag the Class field to the Column Fields area, drag the Lname field to the Row Fields area, and drag the Hourly Wage field to the Data Items area. Autofit the columns to their data and resave the file as my_pivot_exprac2.xls.
Practice
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Drag Internship field to here
Drag Lname field to here
Drag Job score field to here
Drag field list off of completed pivot table to display whole table
Double-click right edge of each column to autofit it to its related data
Pivot Table toolbar
skill 3
Modifying the Summary
Function of a Pivot Table
A summary function is an automatic subtotal or other calculation and is used in pivot tables and pivot charts. The phrase “other calculation” indicates that a summary function can be other than just the SUM function, which totals the data in a cell range. For example, a sum- mary function can be the COUNT function, which calculates the number of values in a cell range. Since the SUM function in the previous Skill supplied a meaningless statistic for indi- vidual student interns, you will replace it in this Skill with the AVERAGE function so that pivot table users can see how well students in general are doing in their internships.
overview
Modify the summary function of a pivot table to calculate the average student job score rather than the total score per internship location and rename the pivot table worksheet tab.
1. If needed, open Pivot_Table_One.xls , created in Skill 2. On the PivotTable toolbar, click the Hide Field List button to conceal the field list so that you can work more easily with the pivot table. ( Note : To conceal the field list, you also can click the Close button at the right end of the field list Title bar.) 2. In cell A21 , type Job Score Averages and press [Enter]. If needed, double-click the right edge of the gray column A header to autofit the column width to the new row label. 3. Click any cell in the pivot table. Click the Field Settings button on the PivotTable toolbar to display the PivotTable Field dialog box. The Name text box displays the cur- rent default function of the pivot table ( SUM ) and the field ( Job Score ) that the function is using to calculate a result. The Summarize by list box displays other functions you can use, with the default function (SUM) selected at the top of the list box. 4. In the Summarize by list box, click Average (Figure A-9). Click the OK button to calculate the average (rather than total) of the job scores and to close the PivotTable Field dialog box. 5. Right-click the Sheet4 worksheet tab (i.e., for the worksheet with the modified pivot table). Click the Rename command to select the tab name. Type PivotTable and press [Enter] to confirm the new name (Figure A-10). Resave the workbook.
how to
extra
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
tip
Based on your experi- ence with Excel tables, you might think that you could just delete the data in cell range B21:F21 and replace it with data based on the AVERAGE function. However, if you try this approach, you will dis- play a box warning you that you cannot change this part of a pivot table.
Table A-1 PivotTable toolbar buttons Button Button Name Function PivotTable Displays menu of pivot table commands Format Report Displays palette of formatting options for pivot table Chart Wizard Automatically creates chart from active pivot table Hide Detail Conceals specific data in table groupings Show Detail Displays detailed data in table groupings Refresh Data Reloads list changes in a table Include Hidden Items in Totals Ensures that pivot table will calculate concealed data Always Display Items Makes pivot table show all data at all times Field Settings Displays PivotTable Field dialog box Show/Hide Field List Displays/conceals the PivotTable Field List
skill 4
Creating a Three-
Dimensional Pivot Table
A basic pivot table has two dimensions to it: height, created by the number of rows, and width, created by the number of columns. However, a pivot table has three dimensions if you add a page field to it, creating a kind of layered table. A page field enables you to filter a whole pivot table to display data for all items or for just one item in the pivot table. Using a page field enables you to filter your data field by field. ( Filtering involves specifying crite- ria by which you will select a smaller set of data from a larger set.)
overview
Convert your pivot table to a three dimensions by adding a page field.
1. If needed, open the file Pivot_Table_One.xls. 2. Drag the Internship button in cell B3 to the area labeled Drop Page Fields Here (in row 1 above the pivot table). The pivot table re-forms with the Internship button in cell A1 , the word (All) and an Internship list arrow in cell B1 , a list of all students in the cell range A5:A20 , a list of their corresponding job scores in cell range B5:B20 , and the aver- age job score for all students in cell B21 (Figure A-11). 3. Click the Internship list arrow, click Action Films (Figure A-12) , and then click the OK button. The pivot table displays the individual job scores for the students named Lauterbach, Nassam, Smithers, and Williams and the average job score for those four students (Figure A-13). 5. Click the Internship list arrow again, click Stanley Furniture , and then click the OK button again. The pivot table now displays the individual job scores for Goldberg, Johnson, Kenyon, and Rodriguez and the average job score for those students (Figure A-14). 6. Save the workbook.
how to
extra
and then clear the check mark from the Disable pivoting of this field check box.
You also cannot drag a field to a new location if the worksheet that serves as the data source for the pivot table is pro- tected. To solve this problem, click the Tools menu, point to the Protection command, and then click the Protect Sheet subcommand to display the Protect Sheet dialog box. Clear the check mark from the Protect worksheet and contents of locked cells check box, and then click the OK button.
Finally, if the list arrow for a field does not work, activate the Always Display Items button on the PivotTable toolbar. If you do not want to use this tool, then drag a field to the data area so that list arrows will work for all fields in the pivot table report.
Problems can arise when converting a two-dimensional pivot table to a three-dimensional format. For example, if you cannot drag a field to a new location, it may be locked. To unlock it, double- click the field to display the PivotTable Field dialog box, click the Advanced button ,
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
tip
To create a separate worksheet for each field, click the PivotTable button on the PivotTable toolbar, click the Show Pages command, and then click the OK button.
Figure A-11 Creating a three- dimensional pivot table
Figure A-12 Selecting Action Films
Open the my_pivot_exprac3.xls file , completed in the previous Practice. Drag the Class field to the Page Fields area of the pivot table. In the Class drop-down list, display the data for just the senior-level interns (labeled Sen ). Resave the file as my_pivot_exprac4.xls.
Practice
Figure A-13 Data for Action Films interns
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Internship button appears in cell A1 and list arrow in cell B
Figure A-14 Data for Stanley Furniture interns
Click list arrow, click Action Films, and then click OK button
Data for interns at only Action Films internship displays in column B
Data for interns at only Stanley Furniture internship displays in column B
Figure A-15 Inserting a blank row
Figure A-16 Revised worksheet
Open the my_pivot_exprac4.xls file, created in the previous Practice. On the Interns worksheet, add data for a new student named Everett Redeagle as follows: Enrollment Date = 8/26/2004 , Class = Soph , and Wage = 6.75. Format the new line of data to match that of the rest of the worksheet. Sort the worksheet by last name and then first name. On the pivot table, redisplay the data for All interns. Refresh the pivot table to reflect the new worksheet data. Sort the pivot table by last name. Resave the workbook as my_pivot_exprac5.xls.
Practice
Figure A-17 Updated pivot table
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Witherspoon data added to pivot table and job score average updated
The Insert command
New student data added to row 17
Internship field set to All
skill 6
Modifying the Structure and
Format of a Pivot Table
In the previous Skill, you learned that you can change pivot table data only by refreshing it after you have changed the data in its source list. However, you can modify the format of a pivot table at any time. For example, you can indent or unindent a label, reformat the num- bers in the data area, change character and cell formatting, and even return a pivot report to its default format. You also can use the Format > AutoFormat command sequence to apply one of 21 preset formats to a pivot table. There are ten Report formats, ten Table formats, and one Classic format.
overview
Add an additional field to the pivot table and reformat some of its elements.
1. Open the Pivot_Table_One.xls file, if needed, and verify that a cell within the pivot table is selected. Be sure that the Internship field is set to All. 2. On the PivotTable toolbar, click the PivotTable button and then click the Pivot Table Wizard command to display the Step 3 of 3 dialog box (Figure A-18). 3. Click the Layout button to open the PivotTable and PivotChart Wizard - Layout screen of the wizard. 4. Drag the Fname button from the right side of the dialog box on top of the Lname button near the upper left of the pivot table diagram so that the Fname button sits atop the Lname button. (Figure A-19). 5. Click the OK button to confirm adding the students’ first names to the pivot table and to close the dialog box. In the Step 3 of 3 dialog box of the wizard, click the Finish button to apply the structural change to the pivot table and to close that dialog box (Figure A-20). 6. Hold down the [Ctrl] key and click the gray row headers for the Adam Total , Carole Total , Geena Total , etc., until you have selected all Total rows for all student interns.
how to
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
Table A-2 Functions of additional buttons in dialog boxes on page EX A.
Button Button Name Function
Back Moves to previous dialog box in wizard
Cancel Closes dialog box without activating choices made in box
Help Opens the “Change the layout of a PivotTable report” Help topic
Options Opens PivotTable Options dialog box, which offers Format options and Data options
skill 6
Modifying the Structure and
Format of a Pivot Table (cont’d)
7. Release the [Ctrl] key. Without deselecting any rows, right-click the gray header of a selected row to display a shortcut menu and then click the Hide command. This action will conceal all of the student total rows, making it easier to see all of the restructured pivot table at one glance. (Figure A-21). 8. In cell A1 , click the Internship field and then click the Bold button on the Formatting toolbar to bold the field text. With the cell pointer still in cell A1 , double- click the Format Painter button and click in the relevant cell to bold the Average of Job Score , Fname , Lname , and Total fields. Click the Format Painter button again to cancel it (Figure A-22). 9. Click cell C39 , which contains the average job score for all students. Click the Decrease Decimal button seven (7) times to format the job score with only two decimal places. 10. Click cell E39 so that you can see the actual border lines of the table. 11. Save the workbook.
how to
extra
Format menu, and then click the Cells command to open the Format Cells dialog box that you use with regular
worksheets. To format numerical data, you can use the Currency Style , Percent Style , Comma Style ,
and similar buttons on the Formatting toolbar or use the Format Cells dialog box. You can open the Format Cells dia-
log box by right-clicking a cell in the pivot table to display a shortcut menu and then clicking the Format Cells com-
mand. Finally, you can right-click a cell containing numerical data to display the shortcut menu, click the Field
Settings command to open the PivotTable Field dialog box, and then click the Number button in that
dialog box to display just the Number tab of the Format Cells dialog box. (Of course, if a cell with numerical data is
active, you also can access the PivotTable Field dialog box just by clicking the Field Settings button on the
PivotTable toolbar.)
To format the labels that identify data, you can use the Italic , Underline , Align Left , and similar buttons on the Formatting toolbar. You also can select a cell or cell range, click the
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
Figure A-21 Pivot table after hiding total rows
Figure A-22 Pivot table after bolding fields
Open the my_pivot_exprac5.xls file. On the pivot table, drag the Fname field from the PivotTable Field List to the left of the Lname field. Change the following labels in the pivot table to the Arial Black font: Class , Average of Wage , Fname , and Lname. Boldface the last row of the pivot table, which contains the Average of Hourly Wage label and data. If needed, autofit all column widths to the newly formatted labels. Save the file as my_pivot_exprac6.xls.
Practice
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Bolded fields
Student names now appear in alphabetic order by first name
Reformatted average job score
Figure A-23 Pivot table sorted by last name
Figure A-24 Pivot chart with all intern scores
Figure A-25 Pivot chart with five intern scores
EX A.
I n t e r @ c t i v e L e a r n i n g S e r i e s
Default chart type has two-dimensional appearance in both columns and back- ground
Pivot chart appears on new worksheet tab
Click list arrow to display list from which to select desired subset of students
Name of selected subset of student interns appears in Internship field
Chart toolbar
skill 7
Creating and Modifying
a PivotChart Report (cont’d)
7. In the Chart sub-type section, click the first chart in the second row. Below the chart images will be the name of the selected chart sub-type—namely, Clustered column with a 3-D visual effect (Figure A-26). 8. Click the OK button to change the chart type and to close the dialog box. The blue chart columns now look three-dimensional, and the gray background is slightly angled to match the three-dimensional appearance of the columns (Figure A-27). 9. Save the workbook.
how to
LESSON ONE Creating and Modifying Pivot Tables and Charts EX A.
extra
In a standard Excel chart or a pivot chart, a data series is the set of data points that are plotted in a chart. For example, in a column chart, the data series is the group of columns. In a pie chart, the data series is the group of slices. A data marker is one data point in the data series. A column, bar, slice, or similar chart element is a data marker. The X axis in a column, bar, stock, or similar chart is the horizontal plane of the chart. The X axis usually displays names of prod- ucts, customers, stocks, or similar labels in a chart. The Y axis is the vertical plane of the chart. Items on the Y axis usually indicate quantities, dollar amounts, percentages, or similar numerical data.
To modify a chart more than you did in the Skill steps, right-click the specific area of the chart that you want to change, and then click the shortcut command that relates to what you want to do. For example, if you right-click the gray wall of a column chart and then click Format Walls , you can change the border, color, and fill effects of the walls in the Format Walls dialog box.
When you create a pivot chart from a pivot table, the row fields of the table become category fields in the chart. The column fields of the table become series fields in the chart. Finally, the page fields of the table become page fields in the chart.
