Create Pivot Tables using Excel 2008/2013 1/26/2016 V1H Create - PDF document

Create Pivot Tables using Excel 2008/2013 1/26/2016 V1H Create Pivot Tables using Excel 2008 1 Create Pivot Tables using Excel 2008 2 V1H V1H Creating Pivot Tables The Goal Using Excel 2008, 2010 or 2013 Goal: to show the steps involved in creating six by different kinds of pivot tables from the same data set. Milo Schield Creating each of the tables starts with steps A-D (shown on the following slides). Member: International Statistical Institute Table 1: Two-way count table (slides 9-11) US Rep: International Statistical Literacy Project Table 2: Two-way table of averages (slides 12-13) Director, W. M. Keck Statistical Literacy Project Table 3: Two-group table of statistics (slides 14-16) Table 4: 100% Column Table (slides 17-19) Slides and Demo output at: www.StatLit.org/ Table 5: 100% Row Table (slides 20-21) pdf/Create-Pivot-Tables-Excel-2008-6up.pdf Table 6: Two-way table of percentages (slides 22-23) pdf/Create-Pivot-Tables-using-Excel-Demo.pdf Create Pivot Tables using Excel 2008 3 Create Pivot Tables using Excel 2008 4 V1H V1H A: Open/Download Data File; Create Excel Pivot Tables Press ‘Enable Editing’ button from this data: A1:H241 Data for Q1-Q4 (A-D) is Binary: 0=No, 1=Yes. Data for Q5-Q6 (E-F) is Ordinal (discrete): 1-5. Data for Q7-Q8 (G-H) is Quantitative (ratio). Excel data at: www.statlit.org/XLS/Create-Pivot-Tables-using-Excel-Data.xls Create Pivot Tables using Excel 2008 5 Create Pivot Tables using Excel 2008 6 V1H V1H B: From the Insert ribbon, C: In Wizard, Select Select “Pivot Table” ‘Table/Range’ and ‘Location’ . . Create-Pivot-Tables-Excel-2008-6up.pdf 1

Create Pivot Tables using Excel 2008/2013 1/26/2016 V1H Create Pivot Tables using Excel 2008 7 Create Pivot Tables using Excel 2008 8 V1H V1H D: Table Layout The Outcome shows Field List Create the following pivot tables. Study demo output. . All six tables will be indexed by using Q1 and Q2. Repeat steps A-D for each of the six pivot tables. Table 1: Two-way count table (slides 9-11) Table 2: Two-way table of averages (slides 12-13) Table 3: Two-group table of statistics (slides 14-16) Table 4: 100% Column Table (slides 17-19) Table 5: 100% Row Table (slides 20-21) Table 6: Two-way table of percentages (slides 22-23) Create Pivot Tables using Excel 2008 9 Create Pivot Tables using Excel 2008 10 V1H V1H 1: Create Table of Counts: 1a: Use Q1 for Rows, Q2 for Index by Q1 & Q2 Columns, Q1 for Values Drag required Table 1 . fields to the table layout (left side) OR Yes No to the boxes (below). No Yes Any field in the dataset can be used in the body. 32 subjects answer “yes” to Q1 and Q2. Create Pivot Tables using Excel 2008 11 Create Pivot Tables using Excel 2008 12 V1H V1H 1b: Right-mouse on Data 2: Create Table of Averages: Change “Sum” to “Count” Use Q7 Data. Index by Q1 & Q2 . Table 2. No Yes No Yes 65.18 is average of Q7 answers for those who said “Yes” to Q2. 62.84 is the average of Q7 answers for those who said “Yes” to both Q1 and Q2.. Create-Pivot-Tables-Excel-2008-6up.pdf 2

Create Pivot Tables using Excel 2008/2013 1/26/2016 V1H Create Pivot Tables using Excel 2008 13 Create Pivot Tables using Excel 2008 14 V1H V1H 2a: Drag data fields as needed; 3: Create 3 Statistics for Q7; Change “Sum” to “Average” Index by Q1 & Q2 . No Yes No Yes 65.40 is average of Q7 for all respondents. 64.25 is average of Q7 for those who said Yes to Q1. Create Pivot Tables using Excel 2008 15 Create Pivot Tables using Excel 2008 16 V1H V1H 3a: Drag Q1 to Rows; Q2 to Cols. 3b: Change Show Values to Drag Q7 three times to Values Average, Count and StdDev. Right-mouse Q7; change to Average.; Right-mouse Q7_2; change to Count. Right-mouse Q7_3; change to StdDev. If problem dragging Q7 third time to same place, drag to different place Values may stack horizontally. Cause unknown. Acceptable. Create Pivot Tables using Excel 2008 17 Create Pivot Tables using Excel 2008 18 V1H V1H 4: Create 100% Column Table; 4a: Double-click on Data Field; Index on Q1 and Q2. Select Count in ‘ Summarize by ’ Table 4. . No Yes No Yes 45.83% of all respondents said “Yes” to Q1 . 47.76% of those who said Yes to Q2 said Yes to Q1 . Create-Pivot-Tables-Excel-2008-6up.pdf 3

Create Pivot Tables using Excel 2008/2013 1/26/2016 V1H Create Pivot Tables using Excel 2008 19 Create Pivot Tables using Excel 2008 20 V1H V1H 4b: Select “Show Values as” 5: Create 100% Row Table; Select “% of Column Total” Index on Q1 and Q2. . Table 5. No Yes No Yes 27.92% of all respondents said “yes” to Q2 . 29.09% of those saying yes to Q1 said Yes to Q1 . The first step for Table 5 is the same as 4a for Table 4. Create Pivot Tables using Excel 2008 21 Create Pivot Tables using Excel 2008 22 V1H V1H 5a: Select “Show Values as”; 6: Create two-way table of Q3; Select “% of Row Total” Index by Q1 and Q2. . Table 6. Yes No No Yes 59% of respondents said Yes to Q3 . 36% of those who said Yes to Q1 said Yes to Q3 . Of those who said Yes to Q1, 36% said Yes to Q3. Create Pivot Tables using Excel 2008 23 Create Pivot Tables using Excel 2008 24 V1H V1H 6a: Change Sum to Average; Conclusion Format data as Percentages Pivot tables are one of the more powerful features of Excel. Knowing how to create pivot tables is a valuable skill . Knowing which is the better table is a more valuable skill Knowing how to read, interpret and communicate the data summarized in pivot tables is a most valuable skill . Create-Pivot-Tables-Excel-2008-6up.pdf 4

Create Pivot Tables using Excel 2008 1 V1H Creating Pivot Tables Using Excel 2008, 2010 or 2013 by Milo Schield Member: International Statistical Institute US Rep: International Statistical Literacy Project Director, W. M. Keck Statistical Literacy Project Slides and Demo output at: www.StatLit.org/ pdf/Create-Pivot-Tables-Excel-2008-6up.pdf pdf/Create-Pivot-Tables-using-Excel-Demo.pdf

Create Pivot Tables using Excel 2008 2 V1H The Goal Goal: to show the steps involved in creating six different kinds of pivot tables from the same data set. Creating each of the tables starts with steps A-D (shown on the following slides). Table 1: Two-way count table (slides 9-11) Table 2: Two-way table of averages (slides 12-13) Table 3: Two-group table of statistics (slides 14-16) Table 4: 100% Column Table (slides 17-19) Table 5: 100% Row Table (slides 20-21) Table 6: Two-way table of percentages (slides 22-23)

Create Pivot Tables using Excel 2008 3 V1H A: Open/Download Data File; Press ‘Enable Editing’ button Excel data at: www.statlit.org/XLS/Create-Pivot-Tables-using-Excel-Data.xls

Create Pivot Tables using Excel 2008 4 V1H Create Excel Pivot Tables from this data: A1:H241 Data for Q1-Q4 (A-D) is Binary: 0=No, 1=Yes. Data for Q5-Q6 (E-F) is Ordinal (discrete): 1-5. Data for Q7-Q8 (G-H) is Quantitative (ratio).

Create Pivot Tables using Excel 2008 5 V1H B: From the Insert ribbon, Select “Pivot Table” .

Create Pivot Tables using Excel 2008 6 V1H C: In Wizard, Select ‘Table/Range’ and ‘Location’ .

Create Pivot Tables using Excel 2008 7 V1H D: Table Layout shows Field List .

Create Pivot Tables using Excel 2008 8 V1H The Outcome Create the following pivot tables. Study demo output. All six tables will be indexed by using Q1 and Q2. Repeat steps A-D for each of the six pivot tables. Table 1: Two-way count table (slides 9-11) Table 2: Two-way table of averages (slides 12-13) Table 3: Two-group table of statistics (slides 14-16) Table 4: 100% Column Table (slides 17-19) Table 5: 100% Row Table (slides 20-21) Table 6: Two-way table of percentages (slides 22-23)

Create Pivot Tables using Excel 2008 9 V1H 1: Create Table of Counts: Index by Q1 & Q2 Table 1 Yes No No Yes Any field in the dataset can be used in the body. 32 subjects answer “yes” to Q1 and Q2.

Create Pivot Tables using Excel 2008 10 V1H 1a: Use Q1 for Rows, Q2 for Columns, Q1 for Values Drag required . fields to the table layout (left side) OR to the boxes (below).

Create Pivot Tables using Excel 2008 11 V1H 1b: Right-mouse on Data Change “Sum” to “Count” .

Create Pivot Tables using Excel 2008 12 V1H 2: Create Table of Averages: Use Q7 Data. Index by Q1 & Q2 Table 2. No Yes No Yes 65.18 is average of Q7 answers for those who said “Yes” to Q2. 62.84 is the average of Q7 answers for those who said “Yes” to both Q1 and Q2..

Create Pivot Tables using Excel 2008 13 V1H 2a: Drag data fields as needed; Change “Sum” to “Average” .

Create Pivot Tables using Excel 2008 14 V1H 3: Create 3 Statistics for Q7; Index by Q1 & Q2 No Yes No Yes 65.40 is average of Q7 for all respondents. 64.25 is average of Q7 for those who said Yes to Q1.

Create Pivot Tables using Excel 2008 15 V1H 3a: Drag Q1 to Rows; Q2 to Cols. Drag Q7 three times to Values If problem dragging Q7 third time to same place, drag to different place Values may stack horizontally. Cause unknown. Acceptable.

Create Pivot Tables using Excel 2008 16 V1H 3b: Change Show Values to Average, Count and StdDev. Right-mouse Q7; change to Average.; Right-mouse Q7_2; change to Count. Right-mouse Q7_3; change to StdDev.