Excel – Pivot Tables

This lesson is an introduction to Pivot Tables and data mining.

Instructions:

Read through the set of learning objectives. This is what you should know when you finish the lesson.

Watch the YouTube videos before starting the lesson.

Complete every step in the assignment, in the order given. Do not skip steps.

When you have done all of the steps, fill out the Check for Understanding (CFU) form.

A completed CFU for every lesson, in-class and homework, is due to be turned in during your next class. 

The CFU forms are not to be submitted by email.

Where there are differences between software versions, the instructions for Excel 2010 are in Black, for the Mac are in green and for Excel 2013 are in blue.

Summary of Key Points:

Create a Pivot table in a new worksheet.

Use the Pivot Table Fields dialog box.

Apply and remove filters.

Access the Value Field Settings dialog box.

Use the Value Field Settings dialog box.

Group values in a date field into categories.

Change the formatting of a pivot table.

Add slicers to a pivot table.

Create a pivot table sub-report.

Create and alter a pivot chart.

Videos:

Create a Pivot Table     Time = 1:12

https://www.youtube.com/watch?v=gilhkjIGNVA

Intro Pivots

Pivot Table Tools

Slicers

Drill down into Pivot table data   4:44

Slicers    2:48

Pivot Charts

Videos - Create a Pivot Table for MACS

https://www.youtube.com/watch?v=AvgAywFMfZA

https://www.youtube.com/watch?v=l3ZhWvQUx5g

https://www.youtube.com/watch?v=eT9vqBMZmCw

https://www.youtube.com/watch?v=AvgAywFMfZA&spfreload=10

https://www.youtube.com/watch?v=peNTp5fuKFg

https://www.youtube.com/watch?v=ghtJgM5bH98

 

PivotTables in Excel

A PivotTable is a tool to work with large sets of data stored in a range in an Excel worksheet. Some of the columns in the range will contain labels. Other columns in the range will contain numerical values.  The Pivot Table is used to pull out small chunks of related data from multiple categories and analyze it, so you can look at specific subsets of information about the data. For example, you could use a pivot table to find the dollar amount of sales in the business books category of the Barnes and Nobles store in Amarillo Texas during each month of 2015.

Pivot Table Introduction video Intro Pivots

This assignment was based on www.howtogeek.com/howto/13336/working-with-pivottables-in-excel/

1.       Start Excel. Download and save this file http://bus91L.altervista.org/Data/Pivot.xlsx and then open it in Excel 2013. 

You will see a list of sales transactions for a travel agency in the Payments worksheet and a list of exam scores in the Attendance worksheet. We will start by working with the data in the Payments worksheet. Select it now.

Before creating a PivotTable we can convert the source data to a table. This will allow us to update our pivot table to include any data this is added to the data range after we create the pivot table.

2.       Click on any one single cell within the Payments list.

From the Insert tab select Table [Ctrl + T] and click on OK.

3.       With the data still selected, From the Insert tab click on the PivotTable icon [ALT  N  V].

For the MAC: From the Data Menu, click on the Pivot Table button (the label might say Summarize with Pivot Table or something similar) and select the option to create a manual pivot table.

The Create PivotTable box appears. Because we already clicked on a cell within the list, the entire list surrounding that cell is already selected for us. The PivotTable can be created in a new worksheet, or in an existing one.  In this example we will select a new one.

4.                   If the radio button (circle) next to “new sheet” is not selected (has a dot inside it) select it now.

Ignore the “analyze multiple table option “ if it is showing up in the dialog box  That option is used for complex pivot tables that draw data from multiple worksheets. Click on OK.

The new worksheet shows a PivotTable placeholder on the left. Ignore it. We will be working with the PivotTable Field List on the right.  The list of fields in this box is generated from the column headings in our data table. 

Watch this video on Pivot Table Tools

To create a pivot table you click on field names and drag field names into the Report Filter, Row Labels, Column Labels, and Values boxes in the bottom of the Pivot Table Field List. Fields that are dragged into the Values box should contain numbers to be summarized in some way (by summing, averaging, finding the maximum, minimum, etc).  Fields that are dragged into the labels boxes should contain text as their values.

5.       Click and drag the word “Amount” (it’s in the field list) into the Values box (it’s in the lower right corner of the dialog box.)

 

Notice that in Excel 2010 the boxes are labeled Report Filter, Row Labels, and Column labels.

In Excel 2013 they are labeled FILTERS, ROWS and COLUMNS.

For a MAC the Row Labels box is called Y-Axis (Vertical) and the Column Box is called X-Axis (Horizontal).

In the rest of the lesson I’ll try to show all three names for these boxes. It’s annoying right?

In the top of the task pane the box next to the “Amount” field in the list of fields is now ticked, and “Sum of Amount” has been added to the Values box, indicating that all of the numbers in the Amount column of the range have been added up. If we examine the PivotTable itself, on the left, we indeed find the sum of all the “Amount” values from the Payments worksheet.

The sum itself is not very useful, so we want to split the data into subtotals. We can do that by dragging fields into the Row labels ROWS for 2013  (Y-Axis for MACs)  or Column Labels COLUMNS for 2013 (X-Axis for MACs)  boxes. 

6.       Click and drag the “Salesperson” field into the Row Labels ROWS for 2013  (Y-Axis for MACs) box.

We can break each salesperson’s total sales down into even smaller subsets.

7.       Click and drag the “Payment Method” heading to the Column Labels COLUMNS for 2013 (X-Axis for MACs) box.

Column labels are created and the total sales for each salesperson are subdivided into subtotals in the pivot table. A Grand Total column automatically appears at the right. Unfortunately the labels are left aligned and the numbers are right aligned, so the data in the columns doesn’t line up perfectly. The Cash column in particular is poorly aligned.

You can have more than one set of row labels or column labels.

8.       Click and drag the Package field into the Row labels ROW for 2013  (Y-Axis for MACs) box and position it below the Salesperson field. Look at the pivot table. You will see subtotals combining each salesperson with each package name.

9.       Click and drag the Package field out of the Row Labels ROW for 2013 (Y-Axis for MACs) box and back into the top area of the task pane. 

Showing and Hiding the Pivot Table Field List

If you accidentally hide the PivotTable Field List you can usually get it back just by clicking anywhere in the pivot table. (In Excel 2016 you have to use the Show Field List icon.)

To deliberately hide the PivotTable Field List ( it is turned on by default) right click at any cell of the pivot table, and select Hide Field List option from the context menu.

doc-pivottable-field-list-1

If you want to show the Field List again, right click on the pivot table, and select Show Field List.

Filters

We can use a filter to break up the data into even smaller pieces. Filters hide parts of the data, allowing us to look at a subset of the total. To use a filter you simply open the filter pulldown menu and put a checkmark next to any values you want to include in the table.

10.   Click and drag the “Package” column/heading to the Report Filter FILTER for 2013 box. The filter will appear above the pivot table in cell A1. Scroll up to see cell A1 if it is not already visible in the worksheet. You should see the word Package in cell A1 and the word (All) in cell B1. There is a pull-down arrow to the right of the word (All)

11.   Click on the pull-down arrow next to the word All.

12.   Click the checkbox next to Select Multiple Items. This turns on the filter.

Remove the check next to the word All. Now no data is selected. 

Click in the checkbox next to the word Sunseekers. This selects the rows of data with the Sunseeker package. All of the other data is hidden and will not appear in the pivot table totals.

Click Ok.

Examine the pivot table. The values in the pivot table show the sales of the Sunseekers package and exclude the rest of the data.

13.   Change the filter to show Activity Lovers. Check the pivot table to see the new values.

14.   Return to the filter and click on the black square next to the word All. This will remove the filter.

15.   Check in the top corner of the worksheet to make sure the word “All” appears in cell B1 next to the word Packages. If it doesn’t appear, you still have the filters turned on.

You can also filter the row and column labels.

16.   Click the drop down arrow above the word Cash, next to the words Column Labels. In the filter box, clear the checkmark next to “Cash.” Click Ok.  Look at the pivot table.

Is the Cash column gone? Did the Grand Totals change? You can go back and forth between the filtered and unfiltered data by pressing Control + Z to undo and then pressing Control + Y to redo. Hiding (filtering) the Cash column changed the totals. When you use a filter to hide something it subtracts the values in the hidden cells from the totals.

Notice the funnel symbol next to the words Column Labels (as shown in the picture. This might have a different label in MACs). This symbol tells you that the filter is turned on and you are not seeing all of the possible values of that field.

17.   Click on the drop-down arrow next to “Column Labels” and click in the box next to Cash to remove the filter. Click Ok

Value Field Settings

The numbers in our pivot table do not show the dollar sign. In a regular Excel worksheet we would click and drag to select the cells in the table, press Control + 1, and convert the numbers into currency style. This method does not work well with a pivot table; if you move data around in the pivot table, which we often do, the formatting doesn’t move with it.

A better method is to assign a number format to each field of data in the Value Fields Settings dialog box. This formatting stays fixed as you move data around in the table.

You can access the Value Fields Settings box in more than one way.

18.   At the bottom of the Pivot Table Fields List task pane, find the “Sum of Amount” entry in the Values box, and click on it.  Click on Value Field Settings. A dialog box appears. We will not be using it yet, so at the bottom of the box click on Cancel. Note: If you do not see the Pivot Tables Field List at the right side of the worksheet, just click anywhere on the pivot table and it will appear.

19.   Right-click on a number in the pivot table. Click on Value Field settings. Click on Cancel.

20.   Click on a number in the pivot table. From the PivotTable Tools Options tab. click on the Active Field icon. [ALT  J  T  Z  A] for Excel 2013 use [ALT  J  T  M] For MACs this icon does not seem to be labelled. The icon is in the upper left hand corner of the toolbar.  You should see Sum of Amount as the field name. Click on the icon right below it, labeled Field Settings. For a MAC click on Settings. 

We can do three things in this dialog box. We can rename the field of data, we can change the type of mathematical operation form Sum to another choice (average or count), and we can change the format of the numbers. Sometimes we will want to show numbers as percents or as currency.

Let’s rename the data. “Sum of Amount,” the title in the “Custom Name” box, is a poor description.

21.   Click in the Custom Name box. Change the name from Sum of Amount to Package Sales.  Don’t click ok, we’re not done yet.

22.   Click the Number Format button, in the lower left corner of the value field settings dialog box. The Format Cells dialog box appears.

 

23.   From the Category list, select Currency, and change the number of decimal places to 0.  Click OK in the Format Cells dialog box

24.   Click OK in the Value Field Settings task pane to get back to the PivotTable. Note: if the values in the pivot table are still showing the pennies, go back into the Value Field Settings and change them to currency again. Sometimes Excel doesn’t work correctly when using this feature.

Working with Dates

25.   Remove the “Payment Method” from the Column Labels COLUMNS for 2013 (X-Axis for MACs) box (drag it back up to the field list), and replace it with the “Date Booked” field. This gives us one column for each date that a transaction occurred on – a very wide table!

26.   To fix this, right-click on any date in the pivot table and select Group… from the dropdown menu.

27.   The grouping box appears.  Select Months and click OK.

28.   If your pivot table disappeared, don’t worry. Your cursor is probably positioned in a cell that is too far to the right. Press Control + the Home key.

This table appears to have a gap in the column headers, the months of April through July are missing. Why is that? Because our sales data was for less than an entire year, it started in September 2009 and ended in March 2010.


 

Pivot Table #2

Sum is the default setting for a value field, but you can choose other types of calculations instead of Sum by changing the Value Field Setting box. We can even combine the Sum, Average and Count of the same field in one pivot table.

29.   Click on the Payments Worksheet tab. Click anywhere in the data table.

30.   From the Insert tab, click on the Pivot table icon [ALT  N  V]. Create the table in a New Worksheet.

31.   From the Pivot Tables Fields taskpane, Drag “Salesperson” into the Row Labels ROW for 2013 (Y-Axis for MACs) box.

32.   Drag the “Amount” field down into the Values box.

Click on the Sum of Amount field in the Values box. Click on Value Field Settings.

Change the “custom name” to “Total.” Don’t close the task pane.

Click on Number Format. Change the number format to Currency with no decimal places.

Click OK  and click OK.

33.   Drag the “Amount” field down into the Values box, below the Total field. 

Click on the Sum of Amount field in the Values box. Click on Value Field Settings.

Change the “custom name” to “Average.” Change the “Summarize Value” setting to Average.

Change the “Summarize Value” setting to Average. Don’t close the task pane

Notice that Excel ”helped us” by messing up our custom name. Go back and re-enter it as Average.  We could have avoided that problem by changing the Summarize Values to Average before creaing the custom name.

Click on Number Format. Change the number format to Currency with no decimal places.

Click OK  and click OK.

34.   Drag the “Amount” field down into the Values box, below the Average field.

Click on the Amount field in the Values box. Click on Value Field Settings.

Change the “Summarize Value” setting to Count. Change the “custom name” to “Count.”

Click OK

35.   In the Column Labels or COLUMNS for 2013 (Y-Axis for MACs) box you will see an  Description: http://cdn.howtogeek.com/wp-content/uploads/2010/03/sigmavalues.pngfield. This feature doesn’t seem to be included in a MAC. Excel automatically created this field to show the totals of each field in the Values box.

36.    You can change the appearance of a pivot table by moving fields up and down inside a box, or by moving them from one box into another. Drag  the Salesperson field from the Row Labels ROWS for 2013 (X-Axis for MACs)  box into the Column Labels COLUMNS for 2013  (Y-Axis for MACs)  box. Look at the pivot table. This format is more difficult to see and understand. Drag the Salesperson field back into the Row Labels ROWS for 2013  (X-Axis for MACs) box.

Format the entire PivotTable

37.   On the PivotTable Tools/Design tab click on the pull-down arrow in the bottom-right of the PivotTable Styles list In 2013 use [ALT J Y S]. Click on one of the styles to format the pivot table.

38.   Click on the Grand Totals icon [ALT J Y G]. You can use this icon to show and hide the row and column totals. Click on Off for Rows and Columns.

39.   On the PivotTable Tools/Options tab, click on the Field Headers icon [ALT J T H]. This will remove the names for the columns and rows. Click on it again [ALT J T H] to show the names of the columns and rows.


Slicers

Slicers are a new tool to visually display filters on the screen. Here is a video about Slicers

Note: Slicers are not available in Office 2007 or Office 2011 for the MAC. You will have to do this section of the assignment, steps 55 to 65, on a PC with Office 2010 or 2013 installed.

We will also be working with slicers in the Dashboards assignment.

 

40.   Continue working in Pivot Table 2  In the Pivot Table field List box, drag Payment Method and Package into the FILTERS field.

41.   On the PivotTable Tools/Options tab, click on the Slicers icon [ALT J T S L]. In Excel 2013 the slicer icon is found on the PivotTable Tools/Analyze tab [ALT  J  T  S  F].

Note: If the icon is not on the tab, your file is probably open in an older version of Excel. Click on the File tab and save the file. Close the file and then open Excel 2013. Reopen the file.

 

42.   Select the Payment Method and Package fields.

43.   Click Ok.

44.   Click and drag to move the slicer boxes below the pivot table.

45.   Click in the title area of  the Package slicer box. From the Slicer Tools Options tab change the Shape Style [ALT J   O  Q] to red. Change the Shape style of the Payment Method slicer box to blue.

46.   Click on one of the names on the package slicer - Sunseekers.

The data for that package is shown in the table and the data from the other two packages are hidden. Notice that all of the payment methods are dark blue. This tells you that each of these methods was used to pay for at least one Sunseekers package.

47.   Click on the filters icon at the top right of the Package slicer box to clear the filter.

48.   Click on the Visa payment method. All of the Packages are showing in red. This tells you that at least one of each package was paid for by Visa.

49.   With the Visa filter still selected, click on Sunseekers in the package slicer. Notice that only two salespeople sold a Sunseeker package that was paid for by Visa.

50.   Click on the filter icon at the top of each slicer box to remove the filters from both slicers.

Pivot Charts

You can also create a PivotChart report after you create a PivotTable by clicking on the PivotTable, then on the Insert tab, in the Charts group, click a chart type.

You can use any chart type except an xy (scatter), bubble, or stock chart.

Deleting the source Pivot table converts the interactive Pivot Table Chart to a static chart ( it will not automatically update itself as the data changes).

In the Sort dialog box you have an option to click and drag to change the order manually.

Pivot Chart video Pivot Charts

  1. Click in cell A2 in the Attendance worksheet to selects the data we will use to build the table. This worksheet can be found in the http://bus91L.altervista.org/Data/PivotTable.xlsx file.

52.   In the Insert section of the ribbon Click on the Pivot Table icon [ALT N V] and select Pivot Table.

53.  In the Create Pivot Table dialog box your Table/Range should be showing and the New Worksheet option selected. Click on OK. A new worksheet should open, with the Field List Pivot Table Fields in Excel 2013 Task Pane open on the right. Rename this worksheet as Scores.

54.  Inside the task pane, click and drag Attendance into the Row Labels ROWS for 2013 (X-Axis for MACs) box and Exam Score into the Values box. You should see the pivot table on the left.

Next we would like to create a Scatter Plot chart to accompany the pivot table. Unfortunately, Scatter Plot charts can’t be created from a pivot table, so we will do the next best thing and create a Line chart.

55.  Click in the pivot table to display the two PivotTable Tools tabs in the ribbon

56.  From the PivotTable Tools tab click on the Pivot Charts icon [ALT J  T C].  The Pivot Charts icon is not available in the MAC. Select a chart from the Insert Charts options.

57.  In EXCEL 2013 the Pivot Charts icon is found on the Insert tab. [ALT  N  S  Z  C].

58.  The chart selection dialog box will open. Choose the fourth Line chart, “Line with Markers.” In Excel 2013 click on Line in the list of chart options, and then click on the Line with Makers icon. It’s the fourth one.

59.  The chart will appear in the worksheet. Click and drag it to place the chart next to your data.

60.  Right-click on the legend, it should be on the right, and choose Delete.

61.  Click in the chart title box. Change the title to “Average Exam Score.”

62.  Click on the Pivot Chart to display the four (three in Excel 2013) PivotChart Tools tabs in the ribbon.

63.  In Excel 2010, from the PivotChart Tools Layout Tab, click on the Axis Titles icon [ALT J  A  I]. Select the primary Horizontal Axis option and then select title below axis. Name the Axis “Classes Attended.”

In Excel 2013, from the PivotChart Tools Design Tab, click on the Add Chart Element icon, select Axis Titles and select primary Horizontal Axis [ALT  J  C  A  A  H]. Click in the Axis title box. Change the title to “Classes Attended.”

This is a picture of what the  Scatter Plot pivot chart should look like when combined with a pivot table.

Pivot Table Sub-Reports

You can double click on any number in a pivot table to create a sub-report showing the details behind that number. We want to create a list of students who attended eight classes.

64.  with the Field List Pivot Table Fields in Excel 2013 Task Pane open on the right., lick and drag Exam Score into the Values box two more times. Your pivot table should show columns labeled Sum of Exam Score, Sum of Exam Score2, and Sum of Exam Score3.

65.  Select any cell inside the Sum of Exam Score2 column. Right click. Choose Value Field Setting. In the dialog box, change the “Summarize Value Field By” setting from Sum to Count. Change the “Custom Name” to Count of Exam Scores. Click OK.

66.  Select any cell inside the Sum of Exam Score3 column. Right click. Choose Value Field Setting. In the dialog box, change the “Summarize Value Field By” setting from Sum to Average. Change the “Custom Name” to “Average of Exam Scores”. DO NOT PRESS OK! YOU ARE NOT FINISHED! Click on the Number Format button to open a second dialog box. Change the format setting to Number with 2 decimal places. Click OK to close this dialog box. Click Ok to close the Value Field Setting dialog box.

67.  In the Scores worksheet find the number 11 in the Attendance = 8 row and in the Count of Exam Scores column of the Attendance pivot table. That should be cell C6 in the pivot table.

68.   Double click on that cell. A new worksheet will appear, listing all of the exam scores. Click in any cell that is not part of the pivot table so you can see what the sub-report looks like. Rename this worksheet as Count.

69.   Copy and paste the range of data from the Count worksheet into the area below the Attendance pivot table in the Scores worksheet.

  1. Right click on the Count work-sheet tab and select Delete. Ignore the warning message and choose Delete in the popup box.

This is a picture of what the Pivot table and its subsidiary table should look like.

 

Review – Fine Points

Video     Create a Pivot Table     Time   =  4:34

Video     Doing More with Pivot Tables     Time  =  3:29

Instructions for the Check for Understanding (CFU) form.

A CFU form is turned in for every assignment.

Starting with the line for your name, copy and paste the CFU form into a Word document.

Fill in your name, date, section number

Circle (or indicate by highlighting) a Y or N response for each statement. I do not subtract points for No answers.

Below the list of Y/N statements, insert ONE screenshot of any part of your work. I just need to see something to prove that you did the assignment. I don’t need to see all of the assignment.  Resize the screenshot, if necessary, so both the form and graphic can fit on one page.

A copy of the CFU is due to be turned in during the next class session.  I do not accept CFU’s through email. 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Pivot Tables

Y/N I can create a Pivot table in a new worksheet. Steps 1-4, 32-35

Y/N I can use the Pivot Table Fields dialog box.

Y/N I can apply and remove filters.

Y/N I can access the Value Field Settings dialog box. 

Y/N I can use the Value Field Settings dialog box.

Y/N I can group values in a date field into categories.

Y/N I can change the formatting of a pivot table.

Y/N I can add slicers to a pivot table.

Y/N I can create a pivot table sub-report.

Y/N I can create and alter a pivot chart.