Transcription

Faculty and Staff Development ProgramProgramMicrosoft Excel Pivot TablesEssentials 2019 WorkshopLast Updated: 11/01/19

Technology Help Desktechnology.pitt.eduMicrosoft Excel Pivot Tables Essentials 2016 WorkshopOverviewPivot Tables are one of Microsoft Excel's most powerful features. Pivot Tables are interactivetables that allow you to manage and summarize substantial amounts of data, in a concise formatfor easy reporting and analysis. Therefore, this manual is an introduction to Excel Pivot Tables2016. These instructions illustrate how you can summarize and manipulate your worksheet databy using Pivot Tables, Filters, Slicers, and Pivot Charts. This manual will also help yousummarize a vast amount of data contained in a spreadsheet, in a concise format for easyreporting and analysis. The instructions will include efficient ways to control, organize, andpresent your data by Managing Totals, Grouping Data, Conditional Formatting, and CreatingMacros. This systematic manual shows the basics of creating a Pivot Table in Excel and helps youunderstand how powerful Pivot Tables can be.File: Excel PivotTables 2019Page 2 of 5311/01/19

Table of ContentsI.Create a PivotTable .4II. Update PivotTable Data .8III.Pivoting Data .9IV.Add Columns .11V.Filters .12VI.Slicers .14A.Add a Slicer . 14VII.Pivot Charts .16A.B.C.Create a PivotChart . 17Use Slicer to Change Data. 19Use Filters to Change Data . 21VIII.IX.X.Connect to an External Data Source .22Create a Recommended PivotTable .25Manage Subtotals and Grand Totals .28XI.Group PivotTable Fields .31A.B.Hide Detail . 31Group Data . 33XII.Data Field Number Format.35XIII.A.B.C.XIV.Conditional Formatting .37Highlight top or bottom values . 37Apply Cell Rules . 40Data Bars . 43Create PivotTable Macros .46A.B.C.Recording a Macro . 46Running a Macro . 49Quick Access Macros . 50XV.Practice.53File: Excel PivotTables 2019Page 3 of 5311/01/19

I.Create a PivotTableOpen the Excel2016 Pivot Practice.xlsx file. The instructor will direct you to the file location.1. Click inside any cell on the spreadsheet that contains data you want to use.2. Select the Insert tab, and then click on the PivotTable button.File: Excel PivotTables 2019Page 4 of 5311/01/19

3. The Create PivotTable dialog box will appear.4. Choose the Select a table or range button (use Table1 as your source data).5. Choose the New Worksheet button (this will place your PivotTable on a newworksheet).6. Click on the OK button.7. A blank PivotTable along with the PivotTable Fields will appear on a new worksheet.File: Excel PivotTables 2019Page 5 of 5311/01/19

Note: In this PivotTable, you can decide which fields to add. Each field is simply a column headerfrom your source data. In the PivotTable Fields section, click on the box for each field you chooseto add.8. The scenario question for this exercise will be: What is the total amount sold by eachsalesperson?9. Click inside the boxes next to the Salesperson and Order Amount fields.10. The selected fields are added to one of the four areas below the PivotTable Fields. Inthis exercise, the Salesperson field is added to the Rows area, while the Order Amounthas been added to the Values area.Note: You can also place your mouse pointer on a field, then hold down the right mouse button,and drag and drop the field to the desired area (FILTERS, COLUMNS, ROWS, VALUES).File: Excel PivotTables 2019Page 6 of 5311/01/19

The PivotTable will calculate and summarize the selected fields. In this exercise, the PivotTableshows the amount sold by each salesperson.Total amount soldby each salespersonFile: Excel PivotTables 2019Page 7 of 5311/01/19

You can sort the data on your PivotTable by using the Sort & Filter button, located on theHome ribbon.Note: You can also apply any type of number formatting to your data. Some types offormatting may disappear when you modify your PivotTable.II.Update PivotTable DataIf you change any data on your source worksheet (Bookstore Sales), the PivotTable will not updateautomatically. To update your PivotTable data, select any cell on your PivotTable, and then thePIVOTTABLE TOOLS ribbon will appear.1. Click on the Analyze tab.2. Click on the Refresh button. Your PivotTable will reflect any changes to your sourceworksheet.File: Excel PivotTables 2019Page 8 of 5311/01/19

III.Pivoting DataPivotTables allows you to quickly reorganize data, so that you can view your worksheet data indiverse ways. Pivoting data can help you experiment the data to discover trends and patterns.1. The earlier exercise scenario question was: What is total amount sold by eachSalesperson?2. This exercise scenario question will be: What is the total amount sold in each Term?You can do this by changing the field in the Row section.3. To select a different Field for the ROWS section, go to the PivotTable Fields sectionand deselect the Salesperson field. This will remove the Salesperson field from theROWS section.4. Click on the box next to the Term field. The Term will appear in the ROWS section.File: Excel PivotTables 2019Page 9 of 5311/01/19

5. The PivotTable will adjust to show the new field choice. Your data now shows the totalorder amount for each term.Note: You can also add or remove Field names to the ROWS or VALUES sections by using thedrag and drop feature. To add a Field to the ROWS or VALUES area, place your mouse pointeron the desired Field name found in the PivotTable Fields section, hold down the right mousebutton, and then drag and drop in the ROWS or VALUES section. To remove a Field from theROWS or VALUES section, place your mouse pointer on the desired Field found in the ROWSor VALUES section, hold down the right mouse button, and then drag and drop anywhereoutside of the ROWS or VALUES section.File: Excel PivotTables 2019Page 10 of 5311/01/19

IV.Add ColumnsYou can show multiple columns on your PivotTable. As noted above, you can drag and dropFields into any of the PivotTable sections (Filters, Columns, Rows, and Values). In this exerciseyou will use the drag and drop feature.1. The earlier exercise you created a PivotTable showing the total order amount for eachterm. For this exercise, you will include columns with each Branch campus that reflectstheir order amount for each Term.2. Place your mouse pointer on the Branch field, hold your left mouse button down, thendrag Branch to the COLUMNS section, and release your mouse button.3. The PivotTable will include a column for each Branch campus.Note: There are automatic filter buttons on the Row and Column labels. This enables you tocustomize your data by selecting or omitting any label item.File: Excel PivotTables 2019Page 11 of 5311/01/19

V.FiltersSometimes you may want focus on just a certain choice of your data. Filters are used to narrowdown the data on your PivotTable, allowing you to view only the information you need.1. In this exercise, you will filter certain salespeople to find how they affect the total sales.You will drag a field item from the PivotTable Fields to the Filters section.2. Place your mouse pointer on the Salesperson field, hold your left mouse button down,then drag Salesperson to the FILTERS section, and release your mouse button.3. The Salesperson filter will appear on the top row of your PivotTable.File: Excel PivotTables 2019Page 12 of 5311/01/19

4. Click on the filter drop-down arrow next to Salesperson.5. The tree list for Salesperson will appear.6. Click in the check box next to Select Multiple Items.7. Notice all the salespeople contained in your data are a part of the selection.8. Now you can deselect any sales person within your data. The sales total will not reflectany deselected person’s sales.9. Deselect the box for any field item you do not want to include in the PivotTable. In thisexercise, you will deselect the boxes for two different salespeople (Melissa Post andChris Walters).10. Click on the OK button.File: Excel PivotTables 2019Page 13 of 5311/01/19

11. The PivotTable will adjust to reflect your changes. Notice the decrease in the GrandTotal due to omitting the two salespeople.VI.SlicersSlicers make filtering data on PivotTables easier and faster. Slicers are Filters, but allows you toinstantly pivot your data. If you often use Filters on your PivotTables, you may want to considerusing Slicers instead of Filters.A.Add a Slicer1. Select any cell on your PivotTable.2. The PIVOTTABLE TOOLS ribbon will appear.File: Excel PivotTables 2019Page 14 of 5311/01/19

3. Click on the Analyze tab, found on the PIVOTTABLE TOOL ribbon, and then click onthe Insert Slicer button.4. The Insert Slicers dialog window will appear.5. Select your desired Field/s. In this exercise, click on the box next to Salesperson.6. Click on the OK button.7. The Slicer will appear on your PivotTable. Each selected Field item is highlighted.File: Excel PivotTables 2019Page 15 of 5311/01/19

8. Just like Filters, you can select or deselect items on the Slicer. The PivotTable willinstantly reflect your choice. Try selecting different Field items to see how it will affectyour data. To select multiple Field items from a Slicer, click on your first choice, thenhold down the Ctrl key and select your other desired item/s.9. You can also click on the Filter icon on the top-right corner of the Slicer window toselect all items at once.VII. Pivot ChartsPivot Charts are like Charts and displays data from your PivotTable. Like Charts, you can selecta chart type, layout, and style that best represents your data.In this exercise, you will change the PivotTable data to show each Salesperson's total sales byTerm.1. Deselect Salesperson, Branch, and Term fields by clicking on their check boxes.File: Excel PivotTables 2019Page 16 of 5311/01/19

2. Place your mouse pointer on the Salesperson field, hold down the left mouse button andthen drag and drop Salesperson into the ROWS section.3. Place your mouse pointer on the Term field, hold down the left mouse button and thendrag and drop Term into the COLUMNS section.A.Create a PivotChartA Pivot Chart will help you see your information more concise.File: Excel PivotTables 2019Page 17 of 5311/01/19

1. Select any cell in your PivotTable.2. Click on the Insert tab, and then click on the PivotChart button.3. The Insert Chart dialog box will appear.4. Select your desired chart type and layout. For this exercise, select the defaultColumn chart type.5. Click on the OK button.File: Excel PivotTables 2019Page 18 of 5311/01/19

6. The PivotChart will appear next to your PivotTable.B.Use Slicer to Change DataThe following instructions will illustrate how to change your data display by using the Slicer andFilters. The PivotChart and PivotTable will automatically adjust to show the data change.1. Click on the Salesperson, Walters, Chris found on the Slicer. Notice the PivotChartand PivotTable automatically reflects your choice.File: Excel PivotTables 2019Page 19 of 5311/01/19

2. Click on the Salesperson, Albertson, Kathy found on the Slicer. Notice the PivotChartand PivotTable automatically reflects your new choice.3. Albertson is the selected on the Slicer, so now hold drop the Ctrl key while clicking onthe salespeople, Davis, William and Post, Melissa. Notice your selections automaticallyreflect information for all three sales people.File: Excel PivotTables 2019Page 20 of 5311/01/19

4. Click on the Filter button, found on the Slicer. Notice all the salespeople are reselectedand the PivotChart and PivotTable automatically reflect your action.C.Use Filters to Change Data1. Click on the drop-drown arrow next to the Column Labels, found on the PivotTable.Notice you have the choice of selecting one or multiple columns that will reflect yourdata choice.File: Excel PivotTables 2019Page 21 of 5311/01/19

VIII. Connect to an External Data SourceYou can use data that is not stored in your active worksheet. This section illustrates how toconnect to an external data source that is located on your computer’s system drive. (For futurereference – workshop hands-on instruction starts in section 2.)1. Launch an active Excel worksheet.2. Select the Insert ribbon tab, and then click on the PivotTable button.3. The Create PivotTable window will appear.4. Select the Use an external data source radio button, and then click on the ChooseConnection button.File: Excel PivotTables 2019Page 22 of 5311/01/19

5. The Existing Connections window will appear.6. Click on the Browse for More button.7. The Select Data Source window will appear.8. Navigate to where your file is located, and then select it.File: Excel PivotTables 2019Page 23 of 5311/01/19

9. Click on the Open button.10. The Select Table window will appear.11. Your external data file will appear selected in the Name section.12. Click on the First row of the data contains column headers check box. (Your datafile should have the column headers in the first row.)13. Click on the OK button.14. The Create PivotTable window will appear.15. Select the Existing Worksheet radio button. (Notice that the Location of yourexternal file begins in the first cell.)16. Click on the OK button.17. The bare-bone PivotTable sheet will appear.File: Excel PivotTables 2019Page 24 of 5311/01/19

18. Now you can create a PivotTable by selecting the desired PivotTable fields.IX.Create a Recommended PivotTableMicrosoft Excel provides multiple recommended PivotTables to choose from that correlate withyour spreadsheet data. To illustrate the various recommended PivotTables, open theExcel2016 PivotTable Level 2 PracticeFile.xlsx file. (The instructor will direct you to the filelocation.)11. Click on any cell in the spreadsheet that has data.File: Excel PivotTables 2019Page 25 of 5311/01/19

12. Select the Insert ribbon tab, and then click on the Recommended PivotTable button.13. The Recommended PivotTable dialog box will appear. On the left-side of the dialogbox, several recommended PivotTable will appear based on your spreadsheet data.14. Select the PivotTable that best represents your desired data structure. For this exercisechoose the Sum of Sales by Department PivotTable preview.15. Click on the OK button.Note: There may be Recommended PivotTables that do not represent an appropriate datastructure. If so, click on the Blank PivotTable button to create your PivotTable fromscratch.File: Excel PivotTables 2019Page 26 of 5311/01/19

16. The Sum of Sales by Department PivotTable will appear.File: Excel PivotTables 2019Page 27 of 5311/01/19

X.Manage Subtotals and Grand TotalsMicrosoft Excel allows you to manipulate the default structure of your PivotTable’s Subtotalsand Grand Totals. This section will illustrate how to manage you Subtotals and Grand Totals byusing the previous Recommended PivotTable from section II with a slight pivot.1. Place your mouse pointer over the Campus field, and then hold down the left mousebutton.2. Drag the Campus field to the ROWS section, and then drop (release the mouse button)above the Department field.3. Place your mouse pointer over the Term field, and then hold down the left mouse button.4. Drag the Term field to the COLUMNS section, and then drop (release the mousebutton).5. Your new PivotTable will appear.6. Each Campus Bookstore and its associated Departments are on the PivotTable rows.The Terms are listed in the PivotTable columns. Notice that the Subtotals are listedabove the Department entries. Individuals who read from top to bottom may desire tohave the Subtotals at the bottom of the Department entries. The following steps willillustrate how to reverse your Subtotals from top to bottom.File: Excel PivotTables 2019Page 28 of 5311/01/19

7. To reverse the location of your Subtotals from top to bottom, go to the PIVOTTABLETOOLS ribbon.8. Select any data cell in your PivotTable.9. Select the DESIGN ribbon tab, and then click on the Subtotals button.10. Select the Show all Subtotals at Bottom of Group from the menu options.File: Excel PivotTables 2019Page 29 of 5311/01/19

11. The Subtotals appear below your campus department list.12. You also have the option of removing your Subtotals and only have the Grand Totalsappear.13. On the PIVOTTABLE TOOLS ribbon, select the DESIGN tab.14. Click on the Subtotals button.15. Select the Do Not Show Subtotals from the menu options.File: Excel PivotTables 2019Page 30 of 5311/01/19

16. Only the Grand Totals appear on your PivotTable.XI.Group PivotTable FieldsWhen a large amount of data is contain in the PivotTable’s rows, then it is difficult to read orunderstand specific data. The Group feature in PivotTable allows you to show and hide data tomake sense of only the data you desire to view.A.Hide DetailMicrosoft Excel allows you to hide data in your PivotTable, so that your desired data is visible.The PivotTable below list each campus bookstore and its associated department sales for eachTerm. This exercise will illustrate how to hide each bookstore department sales entry and onlyshow the campus totals.File: Excel PivotTables 2019Page 31 of 5311/01/19

1. The Hide Detail button appears on the left side of each Campus name.2. Click on each of the Hide Detail buttons. The Campus Bookstores’Department Sales are hidden and only the Subtotals and Grand Totals appear.3. When the Department data is hidden, the Show Detail button will appear next to theCampus name.4. Click on the Show Detail button to view each department sales again.File: Excel PivotTables 2019Page 32 of 5311/01/19

B.Group DataThe Group feature allows you to group data that you desire to be hidden in PivotTable.1. In this exercise the Greensburg Campus Bookstore data will be used to illustrate how tohide department entries. Only two department entries (General Books and Textbooks) willbe viewed.2. Select the entire 7 thru 10 rows. These data entries will be grouped then hidden.3. Select the DATA ribbon tab, and then click on the Group button.4. The Grouping Level indictor appears with a Hide Detail button on the left-side of rows 7 thru 10.17. Click on the Hide Detail button.18. The department entries that were grouped are hidden. Only the General Books andTextbooks Departments entries appear. The Bookstore’s Subtotals and Grand Total stillreflect the totals from the hidden departments as well.File: Excel PivotTables 2019Page 33 of 5311/01/19

8. Click on the Show Detail button to view the hidden data rows.9. To remove Grouping Level indicator, select the entire 7 thru 10 rows again.10. Select the DATA ribbon tab, and then click on the Ungroup button.File: Excel PivotTables 2019Page 34 of 5311/01/19

XII. Data Field Number FormatWhen you create a PivotTable, Microsoft Excel does not apply number formatting. This sectionwill illustrate how to apply number formatting to the body of your PivotTable.10. Select any cell with a value in the body of your PivotTable. (Do not select text.)11. Place your mouse pointer on the selected cell and hold down the right mouse button.12. Select the Number Format option from the menu options.File: Excel PivotTables 2019Page 35 of 5311/01/19

13. The Format Cells dialog window will appear.14. In the Category section, select Currency.15. In the Sample section, select 2 Decimal places for this exercise.16. In the Symbol section, select the symbol (represents U.S. currency).17. Click on the OK button.18. The Currency and Decimal formats are applied to all of your values.File: Excel PivotTables 2019Page 36 of 5311/01/19

XIII. Conditional FormattingA.Highlight top or bottom valuesConditional Formatting allows you to highlight the top, bottom, or average values in the body ofyour PivotTable.1. This exercise will illustrate how to highlight the top four values in the body of yourPivotTable.2. Select any cell with a value in the body of your PivotTable other than a Subtotals orGrand Totals.3. On the Home ribbon, click on the Conditional Formatting button located in the Stylesgroup.4. The Conditional Formatting menu options will appear.File: Excel PivotTables 2019Page 37 of 5311/01/19

5. Select the Top/Bottom Rules option, and then click on the Top 10 Items options. (Youhave the option of selecting the top, bottom, average, or percentage items.)6. The Top 10 Items window will appear.7. In the Format cells that rank in the TOP box, click on the drop-down arrow until youget to the number 4. (You can also select a range from 1 to 10.)8. In the with box, select Light Red Fill. (You can select your desired color for the text andhighlight.)9. The formatting is only applied to your cell selection.10. Click on the Formatting option button that appears next to the highlighted cell.File: Excel PivotTables 2019Page 38 of 5311/01/19

11. The Apply formatting rule to menu options will appear.12. Select the All cells showing “Sum of Sales” values for “Department” and Term”option.Note: Selected cells will apply formatting only to your cell section. All cells showing “Sumof Sales” values will include values with Totals in your cell selections. All cellsshowing “Sum of Sales” values for “Department” and Term” will omit values withTotals from your cell selections.13. Your selected Conditional Formatting highlights the scope of your top 4 sales.14. To apply Conditional Formatting highlight to the bottom or average values, follow theinstructions in this section and select the appropriate option in step 5.File: Excel PivotTables 2019Page 39 of 5311/01/19

Note: To remove Conditional Formatting, hold down the Ctrl / Z keys.B.Apply Cell RulesConditional Formatting applies specific rule conditions to your cell values. For example, identifysales that are less than or greater than to a determined value.1. This exercise will illustrate how to find values in your PivotTable greater than 1100.2. Click in any cell with a value in the body of your PivotTable other than Subtotals orGrand Totals.File: Excel PivotTables 2019Page 40 of 5311/01/19

3. On the Home ribbon, click on the Conditional Formatting button, located in the Stylesgroup.4. Select the Highlight Cells Rules on the menu options.5. Click on the Greater Than from the options panel.6. The Greater Than window will appear.7. In the Format cells that are GREATER THAN box, type 1100.8. Click on the drop-down arrow in the with box, and then select the Green Fill with DarkGreen Text option (select your desired option).19. Click on the OK button.File: Excel PivotTables 2019Page 41 of 5311/01/19

20. The Conditional Formatting will appear on the cell section in the body of yourPivotTable.21. Click on the Formatting options button that appears next to the highlighted values cell.22. The Apply formatting rule to menu options will appear.23. Select the All cells showing “Sum of Sales” values for “Department” and Term”option.Note: Selected cells will apply formatting only to your cell section. All cells showing “Sumof Sales” values will include values with Totals in your cell selections. All cellsshowing “Sum of Sales” values for “Department” and Term” will omit values withTotals from your cell selections.File: Excel PivotTables 2019Page 42 of 5311/01/19

24. Your selected Conditional Formatting highlights all department sales greater than 1100.Note: To remove Conditional Formatting, hold down the Ctrl / Z keys.C.Data BarsConditional Formatting also makes it easier for you to compare the ratio among the data on yourPivotTable by adding data bars next to the values.1. This exercise will illustrate how to apply Data Bars to your PivotTable values.File: Excel PivotTables 2019Page 43 of 5311/01/19

2. Click in any cell containing a value in the body of the PivotTable other than Subtotals orGrand Totals.3. On the Home ribbon, click on the Conditional Formatting button located in the Stylesgroup.4. Select the Data Bars on the menu options.5. Select on the Solid Fill from the options panel.Note: Do not use the Gradient Fill options, because data bars fade out and will notbe easy to interpret.6. The Formatting option button will appears next to the cell section in the body of yourPivotTable.File: Excel PivotTables 2019Page 44 of 5311/01/19

7. Click on the Formatting options button.8. The Conditional Formatting Data Bars will appear next to each number according to itsamount ratio.Note: The first column on a PivotTable is wider than the consecutive columns. When Data Barsare applied, the bars in the first column are not be proportional with the other column DataBars and gives a false ratio comparison.9. The following steps will illustrate how to make your first column the same width as theother PivotTable columns.File: Excel PivotTables 2019Page 45 of 5311/01/19

10. Click on the Design ribbon tab, and then click on the Report Layout button.11. Select the Show in Outline Form option.12. The first column will readjust to the same width as the other PivotTable columns. Nowthe first column Data Bars have the appropriate ratio.Note: To remove Conditional Formatting, hold down the Ctrl / Z keys.XIV. Create PivotTable MacrosYou can save the configuration of a PivotTable that you like for future use by creating a Macro.The Macro will record the steps it took to construct your desired PivotTable. Your recordedMacro will automatically run the steps to create your desired PivotTable.A.Recording a Macro1. In this exercise, you will pivot your data as shown below, and then create a Macro thatcontains the same PivotTable body configuration.File: Excel PivotTables 2019Page 46 of 5311/01/19

2. In the PivotTable Fields area, deselect the Department field.3. To create your PivotTable Marco, click on the VIEW ribbon tab.4. Click on the Macros button.5. Select the Record Macro option.6. The Record Macro window will appear.7. In the Macro name box, type your desired Macro name with no spaces(CampusBookstoreSales).8. Click on the OK button.9. At this point, you are ready to record your PivotTable by first clearing the current fields,and then reconstruct them.File: Excel PivotTables 2019Page 47 of 5311/01/19

10. Click inside any data cell in your PivotTable.11. Click on the PivotTable Analyze tab.12. In the Action group click on the Clear button, and then select the Clear All option.13. Now that the PivotTable is clear, you can reconstruct your PivotTable from scratch.14. Drag and drop the Campus field in the ROWS area.15. Drag and drop the Sales field in the VALUES area.16. Drag and drop the Campus field in the ROWS area.17. Now that you have reconstructed your PivotTable, stop recording your Macro.18. Select the VIEW ribbon tab.19. Click on the Macros button, and then select the Stop Recording option.File: Excel PivotTables 2019Page 48 of 5311/01/19

B.Running a Macro1. To run your Macro, clear your PivotTable as below.2. Deselect your PivotTable

Microsoft Excel Pivot Tables Essentials 2016 Workshop Overview Pivot Tables are one of Microsoft Excel's most powerful features. Pivot Tables are interactive tables that allow you to manage and summarize substantial amounts of data, in a concise format for easy reporting and analysis. Therefore, this manual is an introduction to Excel Pivot Tables