x1d create pivot tables using excel 2013 3 07 2018 v1n
play

X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create - PDF document

Dec 10, 2022 •186 likes •470 views

X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create Pivot Tables using Excel 2013 1 Create Pivot Tables using Excel 2013 2 X1D: V1N X1D: V1N Creating Pivot Tables The Goal Using Excel 2013 Goal: to show the steps involved in

  1. X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create Pivot Tables using Excel 2013 1 Create Pivot Tables using Excel 2013 2 X1D: V1N X1D: V1N Creating Pivot Tables The Goal Using Excel 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 six tables starts with steps B and C (slides 3 thru 6). Step D (slide 7) is basis for each table Member: International Statistical Institute Table 1: Two-way count table (slides 8-10) US Rep: International Statistical Literacy Project Table 2: Two-way table of averages (slides 11-12) Director, W. M. Keck Statistical Literacy Project Table 3: Two-group table of statistics (slides 13-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/Excel2013-Create-Pivot-Tables-Slides.pdf Table 6: Two-way table of percentages (slides 22-23) pdf/Excel2103-Create-Pivot-Tables-Demo.pdf Create Pivot Tables using Excel 2013 3 Create Pivot Tables using Excel 2013 4 X1D: V1N X1D: V1N 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/Excel2013-Create-Pivot-Tables-Data.xlsx Create Pivot Tables using Excel 2013 5 Create Pivot Tables using Excel 2013 6 X1D: V1N X1D: V1N B: From the Insert ribbon, C: Select/Enter Range as A1:H241 Select “Pivot Table” Set ‘Location’ for each graph. . . Excel2013-Create-Pivot-Tables-Slides.pdf 1

  2. X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create Pivot Tables using Excel 2013 7 Create Pivot Tables using Excel 2013 8 X1D: V1N X1D: V1N D: Table Layout w Field List: 1a: Insert@L6. Use Q1 for Rows, Build each table from this. 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 2013 9 Create Pivot Tables using Excel 2013 10 X1D: V1N X1D: V1N 1b: Right-mouse on Data 1c. Table 1 Final Result Change “Sum” to “Count” Coding: 0=No; 1=Yes Table 1 . Any field in the dataset can be used in the body. 32 subjects answer “yes” (1) to Q1 and Q2. Create Pivot Tables using Excel 2013 11 Create Pivot Tables using Excel 2013 12 X1D: V1N X1D: V1N 2a: Insert@L19. Q1 Row; Q2 Col 2b. Table2 (Final Result) Q7 Values. Change Sum to Ave. Coding: 0=No; 1=Yes Table 2 . 65.18 is average of Q7 answers for those who said “Yes” (1) to Q2. 62.84 is the average of Q7 answers for those who said “Yes” to both Q1 and Q2.. Excel2013-Create-Pivot-Tables-Slides.pdf 2

  3. X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create Pivot Tables using Excel 2013 13 Create Pivot Tables using Excel 2013 14 X1D: V1N X1D: V1N 3a: Insert@L33. Q1 Row; Q2 Col 3b: If data spreads horizontally, Drag Q7 to Values three times ! move “Sigma Values” to Rows If problem dragging Q7 3 rd time to same place, drag to different place Create Pivot Tables using Excel 2013 15 Create Pivot Tables using Excel 2013 16 X1D: V1N X1D: V1N 3c: Change Show Values to 3d. Table 3 (Final Result) Average, Count and StdDev. Coding: 0=No; 1=Yes Right-mouse Q7; change to Average.; Right-mouse Q7_2; change to Count. Right-mouse Q7_3; change to StdDev. 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 2013 17 Create Pivot Tables using Excel 2013 18 X1D: V1N X1D: V1N 4a: Double-click on Data Field; 4b: Select “Show Values as” Select Count in ‘ Summarize by ’ Select “% of Column Total” . . Insert Table at R6 Q1 Rows Q2 Cols Q2 Values Excel2013-Create-Pivot-Tables-Slides.pdf 3

  4. X1D: Create Pivot Tables using Excel 2013 3/07/2018 V1N Create Pivot Tables using Excel 2013 19 Create Pivot Tables using Excel 2013 20 X1D: V1N X1D: V1N 4c. Table 4 (Final Result) 5a: Select “Show Values as”; Create 100% Column Table Select “% of Row Total” Table 4. . No Yes No Copy Table 4 or Yes Insert table: At R19. Q1 Rows 45.83% of all respondents said “Yes” to Q1 . Q2 Cols 47.76% of those who said Yes to Q2 said Yes to Q1 . Q2 Value/Body Create Pivot Tables using Excel 2013 21 Create Pivot Tables using Excel 2013 22 X1D: V1N X1D: V1N 5b. Table 5 (Final Result) 6a: Change Sum to Average; Create 100% Row Table; Format data as Percentages Table 5. Insert Table at R33 No Yes No Yes Q1 Rows; Q2 Cols; 27.92% of all respondents said “yes” to Q2 . Q3 Values (Body) 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. Leave “Show Values” as “No Calculation” Create Pivot Tables using Excel 2013 23 Create Pivot Tables using Excel 2013 24 X1D: V1N X1D: V1N 6b. Table 6 (Final Result) Conclusion Create two-way half table of Q3 Table 6. Pivot tables are one of the more powerful features of Excel. Knowing how to create pivot tables is a valuable skill . Yes No Knowing which is the better table is a more valuable skill No Knowing how to read, interpret and communicate the data Yes summarized in pivot tables is a most valuable skill . 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. Excel2013-Create-Pivot-Tables-Slides.pdf 4

  5. Create Pivot Tables using Excel 2013 1 X1D: V1N Creating Pivot Tables Using Excel 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/Excel2013-Create-Pivot-Tables-Slides.pdf pdf/Excel2103-Create-Pivot-Tables-Demo.pdf

  6. Create Pivot Tables using Excel 2013 2 X1D: V1N 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 six tables starts with steps B and C (slides 3 thru 6). Step D (slide 7) is basis for each table Table 1: Two-way count table (slides 8-10) Table 2: Two-way table of averages (slides 11-12) Table 3: Two-group table of statistics (slides 13-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)

  7. Create Pivot Tables using Excel 2013 3 X1D: V1N A: Open/Download Data File; Press ‘Enable Editing’ button Excel data at: www.statlit.org/XLS/Excel2013-Create-Pivot-Tables-Data.xlsx

  8. Create Pivot Tables using Excel 2013 4 X1D: V1N 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).

  9. Create Pivot Tables using Excel 2013 5 X1D: V1N B: From the Insert ribbon, Select “Pivot Table” .

  10. Create Pivot Tables using Excel 2013 6 X1D: V1N C: Select/Enter Range as A1:H241 Set ‘Location’ for each graph. .

  11. Create Pivot Tables using Excel 2013 7 X1D: V1N D: Table Layout w Field List: Build each table from this. .

  12. Create Pivot Tables using Excel 2013 8 X1D: V1N 1a: Insert@L6. Use Q1 for Rows, Q2 for columns, Q1 for Values Drag required . fields to the table layout (left side) OR to the boxes (below).

  13. Create Pivot Tables using Excel 2013 9 X1D: V1N 1b: Right-mouse on Data Change “Sum” to “Count” .

  14. Create Pivot Tables using Excel 2013 10 X1D: V1N 1c. Table 1 Final Result Coding: 0=No; 1=Yes Table 1 Any field in the dataset can be used in the body. 32 subjects answer “yes” (1) to Q1 and Q2.

  15. Create Pivot Tables using Excel 2013 11 X1D: V1N 2a: Insert@L19. Q1 Row; Q2 Col Q7 Values. Change Sum to Ave. .

  16. Create Pivot Tables using Excel 2013 12 X1D: V1N 2b. Table2 (Final Result) Coding: 0=No; 1=Yes Table 2 65.18 is average of Q7 answers for those who said “Yes” (1) to Q2. 62.84 is the average of Q7 answers for those who said “Yes” to both Q1 and Q2..

  17. Create Pivot Tables using Excel 2013 13 X1D: V1N 3a: Insert@L33. Q1 Row; Q2 Col Drag Q7 to Values three times ! If problem dragging Q7 3 rd time to same place, drag to different place

  18. Create Pivot Tables using Excel 2013 14 X1D: V1N 3b: If data spreads horizontally, move “Sigma Values” to Rows

  19. Create Pivot Tables using Excel 2013 15 X1D: V1N 3c: 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.

  20. Create Pivot Tables using Excel 2013 16 X1D: V1N 3d. Table 3 (Final Result) Coding: 0=No; 1=Yes 65.40 is average of Q7 for all respondents. 64.25 is average of Q7 for those who said Yes to Q1.

  21. Create Pivot Tables using Excel 2013 17 X1D: V1N 4a: Double-click on Data Field; Select Count in ‘ Summarize by ’ . Insert Table at R6 Q1 Rows Q2 Cols Q2 Values

  22. Create Pivot Tables using Excel 2013 18 X1D: V1N 4b: Select “Show Values as” Select “% of Column Total” .

  23. Create Pivot Tables using Excel 2013 19 X1D: V1N 4c. Table 4 (Final Result) Create 100% Column Table 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 .

Recommend

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

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

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

528 views • 28 slides
Trend Lines, Pivot Tables, and Pivot Charts Objectives Create a line chart and trendline Create

Trend Lines, Pivot Tables, and Pivot Charts Objectives Create a line chart and trendline Create

Trend Lines, Pivot Tables, and Pivot Charts Objectives Create a line chart and trendline Create a pivottable Change the layout and view of a pivottable Filter a pivottable Format a pivottable Create a pivotchart Change a pivotchart view and

319 views • 15 slides
PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1

PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1

CS1100 Computer Science and its Applications PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1 Its difficult to see the bottom line in a flat list like this, turning the list into a Pivot Table

288 views • 27 slides
PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1

PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1

CS1100 Computer Science and its Applications PIVOT TABLES AND CHARTS Leena Razzaq lrazzaq@ccs.neu.edu CS1100 Pivot tables and charts 1 Its difficult to see the bottom line in a flat list like this, turning the list into a Pivot Table will

337 views • 30 slides
Creating Confidence Intervals using Excel 2013 XL8A-V0R XL8A-V0R XL8A-V0R Create Confidence

Creating Confidence Intervals using Excel 2013 XL8A-V0R XL8A-V0R XL8A-V0R Create Confidence

Creating Confidence Intervals using Excel 2013 XL8A-V0R XL8A-V0R XL8A-V0R Create Confidence Intervals using Excel 2013 1 Create Confidence Intervals using Excel 2013 2 Create Confidence Intervals Assignment Using Excel 2013 Input data

427 views • 14 slides
XL1F: Create Histogram using HISTOGRAM in Excel 2013 V0G XL1F: V0G Create Histogram using

XL1F: Create Histogram using HISTOGRAM in Excel 2013 V0G XL1F: V0G Create Histogram using

XL1F: Create Histogram using HISTOGRAM in Excel 2013 V0G XL1F: V0G Create Histogram using HISTOGRAM in Excel 2013 1 XL1F: V0G Create Histogram using HISTOGRAM in Excel 2013 2 Create Histogram using The Goal and Approach HISTOGRAM in Excel

395 views • 16 slides
23 Advanced Topics 5: Multi-lingual Models Up until now, we have assumed that in the case of

23 Advanced Topics 5: Multi-lingual Models Up until now, we have assumed that in the case of

(c) Model Pivoting (a) Result Pivoting (b) Data Pivoting src pivot pivot trg src pivot pivot trg src pivot pivot trg train train train train train train train train train train train train src src-pivot pivot-trg

179 views • 5 slides
XL1G: Create Histograms using Excel 2013 Functions V0H 3/31/2017 XL1G: 0H Create Histograms

XL1G: Create Histograms using Excel 2013 Functions V0H 3/31/2017 XL1G: 0H Create Histograms

XL1G: Create Histograms using Excel 2013 Functions V0H 3/31/2017 XL1G: 0H Create Histograms using Functions in Excel 2013 1 XL1G: 0H Create Histograms using Functions in Excel 2013 2 Create a Histogram Histogram versus using Functions in

362 views • 21 slides
Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, Surveillance Epidemiologist

Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, Surveillance Epidemiologist

Pivot Table Demonstration Tools for LBOHs May 27, 2020 cott Troppy, Surveillance Epidemiologist Bureau of Infectious Disease and Laboratory Sciences MA Department of Public Health 1 Pivot Table Learning Objectives Use pivot tables to turn

330 views • 32 slides
Introductory Statistics Day 6 Probability and Pivot Tables Definition (Probability) The

Introductory Statistics Day 6 Probability and Pivot Tables Definition (Probability) The

Introductory Statistics Day 6 Probability and Pivot Tables Definition (Probability) The probability of an outcome is the proportion of times the outcome would occur if we observed the random process an infinite number of times. Definition

405 views • 13 slides
Creating Confidence Intervals using Excel 2010 5/08/2015 V0M V0M V0M Create Confidence

Creating Confidence Intervals using Excel 2010 5/08/2015 V0M V0M V0M Create Confidence

Creating Confidence Intervals using Excel 2010 5/08/2015 V0M V0M V0M Create Confidence Intervals using Excel 2010 1 Create Confidence Intervals using Excel 2010 2 Create Confidence Intervals Assignment Using Excel 2010 Input data

274 views • 14 slides
Excel Power Query ry: Useful for Investment Banking and Advisory ry Roles? The Magic of Text

Excel Power Query ry: Useful for Investment Banking and Advisory ry Roles? The Magic of Text

Excel Power Query ry: Useful for Investment Banking and Advisory ry Roles? The Magic of Text Parsing and Automatic Updates from Websites Common Question About Certain Excel Features I noticed that you now cover [Power Pivot / Power

259 views • 8 slides
Scripting for Multimedia LECTURE 9: WORKING WITH TABLES Tables in HTML A table displays a

Scripting for Multimedia LECTURE 9: WORKING WITH TABLES Tables in HTML A table displays a

Scripting for Multimedia LECTURE 9: WORKING WITH TABLES Tables in HTML A table displays a two-dimensional grid of data Use <tr> to create rows and <td> to create table details Do not use <table> to create a page

772 views • 13 slides
Create Sampling Distributions from Single Die V0G 11/16/2016 V0G Create Sampling Distribution

Create Sampling Distributions from Single Die V0G 11/16/2016 V0G Create Sampling Distribution

Create Sampling Distributions from Single Die V0G 11/16/2016 V0G Create Sampling Distribution of a Single Die using COUNTIF in Excel 2013 1 V0G Create Sampling Distribution of a Single Die using COUNTIF in Excel 2013 2 Create Sampling

805 views • 28 slides
Excel Tutorial 4 Working with Charts and Graphics COMPREHENSIVE Objectives XP XP Create

Excel Tutorial 4 Working with Charts and Graphics COMPREHENSIVE Objectives XP XP Create

Excel Tutorial 4 Working with Charts and Graphics COMPREHENSIVE Objectives XP XP Create an embedded chart Work with chart titles and legends Create a pie, 3D, column, and line charts Use custom formatting with chart axes

819 views • 24 slides
General Investor Presentation March 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and

General Investor Presentation March 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and

1 General Investor Presentation March 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and Metrics Excel files with the Companys quarterly financial results and metrics from the current period dating back to the full year 2008 are

482 views • 45 slides
Tables in TEX \eTD \bTD overhead \eTD \eTR overhead so much \eTABLE \eTD \eTR \bTABLE even

Tables in TEX \eTD \bTD overhead \eTD \eTR overhead so much \eTABLE \eTD \eTR \bTABLE even

Tables in TEX \eTD \bTD overhead \eTD \eTR overhead so much \eTABLE \eTD \eTR \bTABLE even once you master the syntax, it's still an overkill for simple tables and keep cheating every time you want to create a new table to create a table in

344 views • 19 slides
5.2 Microsoft Excel Microsoft Excel Microsoft Excel is the spreadsheet component of the

5.2 Microsoft Excel Microsoft Excel Microsoft Excel is the spreadsheet component of the

5.2 Microsoft Excel Microsoft Excel Microsoft Excel is the spreadsheet component of the Microsoft Office Suite. It is used primarily to enter, edit, format, sort, perform mathematical computations, save, retrieve and print numeric

717 views • 32 slides
Traveling The PIVOT FOOT is what matters!!! If the pivot foot is lifted the ball MUST be passed

Traveling The PIVOT FOOT is what matters!!! If the pivot foot is lifted the ball MUST be passed

Traveling The PIVOT FOOT is what matters!!! If the pivot foot is lifted the ball MUST be passed or shot before it returns to the floor. 4-44 How to establish a pivot foot Traveling is moving a foot or feet in any direction in excess of

551 views • 6 slides
Earnings Presentation First Quarter 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and

Earnings Presentation First Quarter 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and

1 Earnings Presentation First Quarter 2018 DISCLAIMER 2 2 Notes Regarding Financial Tables and Metrics Excel files with the Companys quarterly financial results and metrics from the current period are accessible in the financial results

356 views • 31 slides
Create Centered Stacked Bar Charts V0A 12/11/2016 for Even-Choice Ordinal Data using Excel 2013

Create Centered Stacked Bar Charts V0A 12/11/2016 for Even-Choice Ordinal Data using Excel 2013

Create Centered Stacked Bar Charts V0A 12/11/2016 for Even-Choice Ordinal Data using Excel 2013 V0A Excel2013: Create Centered Stacked 100% Bar Charts for Even-Choice Ordinal Data 1 V0A Excel2013: Create Centered Stacked 100% Bar Charts for

273 views • 14 slides
Create Centered Stacked Bar Charts V0A 12/11/2016 for Odd-Choice Ordinal Data using Excel 2013

Create Centered Stacked Bar Charts V0A 12/11/2016 for Odd-Choice Ordinal Data using Excel 2013

Create Centered Stacked Bar Charts V0A 12/11/2016 for Odd-Choice Ordinal Data using Excel 2013 V0A Excel2013: Create Centered Stacked 100% Bar Charts for Odd-Choice Ordinal Data 1 V0A Excel2013: Create Centered Stacked 100% Bar Charts for

153 views • 14 slides
Macros and VBA in Excel: Part 2 American Society for Engineering Education November 11 th 2013

Macros and VBA in Excel: Part 2 American Society for Engineering Education November 11 th 2013

Macros and VBA in Excel: Part 2 American Society for Engineering Education November 11 th 2013 Download this File umich.edu/~pruchser ASEE Example Spreadsheet Once open in Excel Enable Editing Intro Philipp Ruchser Grad

426 views • 20 slides
Excel Create a Budget By Martha Nelson Digital Learning Specialist 1. I am neither a Certified

Excel Create a Budget By Martha Nelson Digital Learning Specialist 1. I am neither a Certified

Excel Create a Budget By Martha Nelson Digital Learning Specialist 1. I am neither a Certified Public Accountant, nor a Financial Advisor. 2. This is a conversation. Please talk to your neighbor or me. 3. Sometimes I make a mistake

433 views • 31 slides

More recommend