Download How to Create Pivot Tables & Pivot Charts for WGU D547 Task 1 and more Exams Advanced Data Analysis in PDF only on Docsity!
How to Create Pivot Tables & Pivot Charts for WGU D
Task 1
Duration: ~12 minutes Instructor: [Your Name] Target Audience: WGU D547 Students
INTRO (0:00 โ 0:30)
Visual: Screen recording of Excel opening with the CDC dataset Script: "Hi everyone. In this video, I'll walk you through the exact steps to create pivot tables and pivot charts for WGU D547 Task 1 โ the Data Analysis performance assessment. We'll be using the CDC dataset 'Estimates of Emergency Department Visits in the United States from 2016 to 2021.' By the end of this video, you'll have all the pivot tables you need to complete Part A1, A2, and A3 of the task."
STEP 1: DOWNLOAD AND OPEN THE DATASET (0:30 โ 1:00)
Visual: Navigating to the course page, downloading the Excel file, opening it Script: "First, download the dataset from your course materials. The file is called 'Estimates_of_Emergency_Dept_Visits_in_the_United_States_2016-2021.xlsx.' Open it in Excel. You'll see several tabs at the bottom. The main dataset is on the first tab. We'll work exclusively on this tab."
STEP 2: PREPARE THE DATA โ ADD FILTERS (1:00 โ 1 :45)
Visual: Highlighting columns A through L, clicking Sort & Filter, dropdown arrows appearing Script: "Now, let's add filters to make it easier to select our data. Click on the top of column A โ the cell that says 'Year.' Then hold down your Shift key and click on column L. This highlights all the columns. Next, go up to the toolbar, click on 'Sort & Filter,' and select 'Filter.' You'll see small dropdown arrows appear at the top of each column. These will let us filter our data."
STEP 3: APPLY THE NECESSARY FILTERS (1:45 โ 4:00)
Visual: Clicking each filter dropdown and selecting criteria Script: "Now we need to apply the correct filters. Let me show you each one. First, click the dropdown arrow on the 'Year' column. Deselect 'Select All,' then scroll down and select only the years 2016 through 2021. Click OK. Next, click the dropdown on 'Measure Type.' Select only 'Primary Diagnosis.' Click OK. Now, click the dropdown on 'Measure' โ this is where the actual diagnosis names are. This is important: deselect 'Select All,' then scroll down and select ALL diagnoses EXCEPT 'All Diagnosis.' You want to exclude the general category. Click OK. Next, click the dropdown on 'Group.' This is where you choose what you want to analyze โ by sex, age, race, region, MSA, or payment source. For this demo, I'll select 'by sex.' Click OK. Next, click the dropdown on 'Subgroup.' Select only 'Male' and 'Female.' Deselect anything else. Click OK.
Third, drag 'Measure' into the Rows area. This will list all the primary diagnoses as rows. Fourth, drag 'Estimate' into the Values area. This is the number of visits. Now look at your pivot table. If you see 'Count of Estimate' with a bunch of ones โ that's wrong. You need to change it to 'Sum of Estimate.' Here's how: Click the little dropdown arrow next to 'Count of Estimate' in the Values area. Select 'Value Field Settings.' In the dialog box, change it from 'Count' to 'Sum.' Click OK. Now your numbers will be in the millions โ much more realistic for ED visit data. You should see totals for each diagnosis, broken down by Male and Female."
STEP 6: RENAME COLUMNS FOR CLARITY (8:30 โ 9:15)
Visual: Double-clicking column headers, typing new names Script: "To make your pivot table easier to read, let's rename a few columns. Double-click the cell that says 'Row Labels' and rename it to 'Primary Diagnosis' or 'Reason for Visit.' Double- click the cell that says 'Sum of Estimate' and rename it to 'Number of Visits.' Double-click the 'Subgroup' headers โ rename 'Male' and 'Female' if they appear as numbers instead of words. This will make your analysis much clearer when you present it to hospital leadership."
STEP 7: CREATE A PIVOT CHART (9:15 โ 10:45)
Visual: Clicking anywhere in pivot table, Insert โ PivotChart, selecting bar chart, formatting Script:
"Now let's create a bar chart to visualize our findings. Click anywhere inside your pivot table. Go to the 'Insert' menu, then click 'PivotChart.' A dialog box will appear. Select 'Bar' chart โ I recommend the clustered bar chart. Click OK. Excel will create a chart. Let me show you how to make it look professional. First, add a title. Click on the chart, go to the 'Design' tab, click 'Add Chart Element,' then 'Chart Title,' and choose 'Above Chart.' Type your title โ for example, 'Top ED Diagnoses by Sex.' Second, adjust the vertical axis. Right-click on the numbers on the vertical axis. Select 'Format Axis.' In the panel that appears, change the 'Maximum' bound to a number just above your largest bar. This eliminates empty space and makes your chart easier to read. Third, change the colors. Go to the 'Design' tab, click 'Change Colors,' and pick a professional palette โ I like the blues or teals. Your pivot chart is now ready to copy into your PowerPoint presentation."
STEP 8: CREATE ADDITIONAL PIVOT TABLES FOR
VARIATIONS (10:45 โ 12:00)
Visual: Right-clicking original pivot table, copy, paste on new sheet, changing filters Script: "For Part A2 of the task, you need three notable variations. The easiest way to create them is to copy your existing pivot table. Right-click on your pivot table sheet tab at the bottom. Select 'Move or Copy.' Check the box that says 'Create a copy.' Click OK. Now you have an identical pivot table on a new sheet. To create a variation, change something. For example: ๏ท Change the Group filter from 'by sex' to 'by age' ๏ท Change the Subgroup filter to different age ranges ๏ท Add a filter to show only a specific diagnosis
OUTRO (12:30 โ 13:00)
Script: "Thanks for watching. In summary: add filters to your dataset first, create your pivot table with Group in Filters, Subgroup in Legend, Measure in Rows, and Estimate in Values โ set to SUM. Then create your pivot chart, adjust the axis, and copy to PowerPoint. Create copies for your three variations. If you have questions, reach out to your course instructor or post in the course chatter. Good luck on Task 1"
QUICK REFERENCE CARD (Printable)
Step Action Excel Location 1 Add filters Highlight A-L โ Sort & Filter โ Filter 2 Filter Year Dropdown โ 2016- 2021 3 Filter Measure Type Dropdown โ Primary Diagnosis 4 Filter Measure Dropdown โ All EXCEPT "All Diagnosis" 5 Filter Group Dropdown โ Your choice (sex, age, etc.) 6 Filter Subgroup Dropdown โ Select specific categories 7 Filter Estimate Type Dropdown โ Visit Count 8 Filter Estimate Dropdown โ Deselect blanks 9 Insert PivotTable Insert โ PivotTable โ New Worksheet 10 Drag Group to Filters PivotTable Field List
Step Action Excel Location 11 Drag Subgroup to Legend PivotTable Field List 12 Drag Measure to Rows PivotTable Field List 13 Drag Estimate to Values PivotTable Field List 14 Change Count to SUM Value Field Settings โ Sum 15 Insert PivotChart Insert โ PivotChart โ Bar chart 16 Adjust vertical axis Right-click axis โ Format Axis โ Maximum bound 17 Add chart title Design โ Add Chart Element โ Chart Title 18 Create variations Copy pivot table โ Change filters/subgroups
COMMON ERRORS & FIXES TABLE
Error What You See Fix Wrong calculation Values are "1" or small numbers Change Value Field Settings from COUNT to SUM Chart has too many bars All diagnoses visible Add filter to Rows (Measure) โ show only to 10 Axis has too much empty space Bars are tiny at bottom of chart Right-click axis โ Format Axis โ Increase Maximum bound Missing subgroups Only "Both sexes" or "Not stated" Check Subgroup filter โ deselect unneeded options
