Transcription
Creating PivotTables in Excel 2010Table of ContentsOVERVIEW OF PIVOTTABLE AND PIVOTCHART REPORTS . 4WHAT IS A PIVOTTABLE REPORT? . 4Ways to work with a PivotTable report . 5WHAT IS A PIVOTCHART REPORT? . 6Differences between a PivotChart and a standard chart . 6COMPARING A PIVOTTABLE REPORT AND A PIVOTCHART REPORT . 6WORKING WITH THE SOURCE DATA OF A PIVOTTABLE OR PIVOTCHART REPORT . 7Creating a PivotTable or PivotChart from worksheet data . 7Using an external data source to create a PivotTable or PivotChart . 7Using another PivotTable report as the data source . 8Changing the source data of an existing PivotTable report . 8CREATE A PIVOTTABLE REPORT . 9DEFINE THE DATA SOURCE FOR THE PIVOTTABLE REPORT . 9CREATE THE PIVOTTABLE REPORT . 9ADD FIELDS TO THE PIVOTTABLE REPORT . 10Copy fields in a PivotTable report. 11CREATE A PIVOTTABLE FROM AN EXTERNAL DATA SOURCE . 11SWITCH BETWEEN AUTOMATIC AND MANUAL UPDATING OF THE REPORT LAYOUT . 12REFRESH PIVOTTABLE DATA. 13Refresh PivotTable data manually. 13View the refresh status or cancel the refresh . 13Refresh PivotTable data automatically when opening the workbook . 13DELETE A PIVOTTABLE REPORT . 13CREATE A PIVOTCHART REPORT FROM AN EXISTING PIVOTTABLE REPORT . 14CREATE A STANDARD CHART FROM SOME OR ALL DATA IN A PIVOTTABLE REPORT . 14CONVERT A PIVOTCHART REPORT TO A STANDARD CHART . 15DELETE A PIVOTCHART REPORT . 15WORKING WITH THE PIVOTTABLE FIELD LIST . 15HOW THE PIVOTTABLE FIELD LIST WORKS . 16GUIDELINES FOR MOVING FIELDS IN THE PIVOTTABLE FIELD LIST. 17CHANGING THE PIVOTTABLE FIELD LIST VIEW . 17ADD FIELDS TO A PIVOTTABLE OR PIVOTCHART . 17Add the fields you want to display . 18Add multiple copies of a field in the Values area . 18Filter data before you add fields . 18CHANGE THE LAYOUT AND FORMAT OF A PIVOTTABLE REPORT. 19REARRANGE FIELDS IN THE PIVOTTABLE OR PIVOTCHART . 19Remove fields from a PivotTable report . 20CHANGE THE LAYOUT FORM OF A PIVOTTABLE REPORT. 20Change a PivotTable report to compact, outline, or tabular form . 21Handout: Excel 2010 Pivot TablesTopics came directly from Microsoft Excel 2010 Help.ICT Training, Maxwell School of Syracuse UniversityPage 1
CHANGE THE LAYOUT OF COLUMNS, ROWS, AND SUBTOTALS . 21Turn column and row field headers on or off . 21Display subtotals above or below their rows . 21Change the order of row or column items . 22Adjust column widths on refresh . 22Move a column to the row labels area or a row to the column labels area . 22Merge or unmerge cells for outer row and column items . 23REPEAT ITEM LABELS IN A PIVOTTABLE REPORT . 23Repeat item labels in rows . 23Repeat item labels in columns . 24Turn repeated item labels on or off for all fields . 24Change the way item labels are displayed in a layout form . 24EXPAND, COLLAPSE, OR SHOW DETAILS IN A PIVOTTABLE OR PIVOTCHART REPORT . 24Expand or collapse to different levels of detail . 25Expand or collapse levels in a PivotTable . 25Expand or collapse levels in a PivotChart . 25Show or hide the expand and collapse buttons in a PivotTable . 25Show or hide details for a value field in a PivotTable . 26Show value field details . 26Disable or enable the option to show value field details . 26CHANGE THE DISPLAY OF BLANK CELLS, BLANK LINES, AND ERRORS . 26Change how errors and empty cells are displayed . 26Display or hide blank lines after rows or items . 26CHANGE OR REMOVE FORMATTING . 27Apply a style to format a PivotTable report . 27Apply banding to change the format of a PivotTable report . 27Remove a style or banding format from a PivotTable report . 28Change the number format for a field . 28GROUP ITEMS IN A PIVOTTABLE REPORT . 29Group numbers in numeric fields . 30Group dates or times . 30Group selected items . 31Ungroup grouped items . 31SORT DATA IN A PIVOTTABLE OR A PIVOTCHART REPORT . 31LEARN MORE ABOUT SORTING . 31SORT ROW OR COLUMN LABEL DATA IN A PIVOTTABLE REPORT . 32SORT DATA IN THE VALUES AREA . 33SORT DATA IN A PIVOTCHART REPORT . 33FILTER ITEMS IN A PIVOTTABLE REPORT . 33LEARN ABOUT FILTERING . 34SPECIFY FILTER OPTIONS . 34Allow multiple filters per field . 34Display or hide field captions and filter drop downs . 34USE A REPORT FILTER TO FILTER ITEMS . 35Add a report filter to the PivotTable report . 35Select items in the report filter . 35Display report filter pages on separate worksheets . 35FILTER ITEMS MANUALLY BY SELECTING ROW OR COLUMN LABEL FIELDS . 36Handout: Excel 2010 Pivot TablesTopics came directly from Microsoft Excel 2010 Help.ICT Training, Maxwell School of Syracuse UniversityPage 2
FILTER ITEMS BY APPLYING A LABEL FILTER . 36How to use wildcard characters . 36FILTER ITEMS BY APPLYING A VALUE FILTER . 37FILTER ITEMS BY APPLYING A DATE FILTER . 37APPLY A FILTER TO DISPLAY THE TOP OR BOTTOM 10 ITEMS . 38FILTER BY SELECTION TO DISPLAY OR HIDE SELECTED ITEMS ONLY . 38REMOVE FILTERS . 38USE SLICERS TO FILTER PIVOTTABLE DATA . 38What are slicers?. 39Using slicers . 39Formatting slicers for a consistent look . 40Sharing slicers between PivotTables . 40Create a slicer in an existing PivotTable . 40FORMAT A SLICER . 41SHARE A SLICER BY CONNECTING TO ANOTHER PIVOTTABLE . 41Make a slicer available for use in another PivotTable . 41Use a slicer from another PivotTable . 41DISCONNECT OR DELETE A SLICER. 41Disconnect a slicer . 41Delete a slicer . 42CALCULATE VALUES IN A PIVOTTABLE REPORT . 42AVAILABLE CALCULATION METHODS . 42Summary functions in value fields . 42Functions that you can use as a subtotal . 42Custom calculations. 43Functions available for custom calculations in value fields. . 43Formulas . 44HOW THE TYPE OF SOURCE DATA AFFECTS CALCULATIONS . 44USING FORMULAS IN PIVOTTABLE REPORTS . 45Create formulas in a PivotTable report . 46Add a calculated field . 46Add a calculated item to a field . 46Enter different formulas cell by cell for calculated items . 47Adjust the order of calculation for multiple calculated items or formulas . 47EDIT A PIVOTTABLE FORMULA. 47Determine whether a formula is in a calculated field or a calculated item . 47Edit a calculated field formula . 47Edit a single formula for a calculated item . 48Edit an individual formula for a specific cell of a calculated item . 48DELETE A PIVOTTABLE FORMULA . 48VIEW ALL FORMULAS THAT ARE USED IN A PIVOTTABLE
ICT Training, Maxwell School of Syracuse University Page 4 Overview of PivotTable and PivotChart reports A PivotTable report is useful to summarize, analyze, explore, and present summary data. A PivotChart report can help you visualize PivotTable report summary data so t
