Pivot Tables 101
Analyzing A-F Accountability Data
Please Answer The Following Questions:
1. I use Excel PivotTables regularly and feel comfortable using them.Yes No
2. I use Excel PivotTables once in a while and could use a refresher.Yes No
3. PivotTables represent a new learning opportunity for me.Yes No
4. PivotTables are the cool new ride at the State Fair!True False
PivotTable Self-Assessment
MEET YOUR TRAINERFOUNDER / CEO
Educator- 24 years
Researcher- 20 years
Leadership-18 years
District Administrator- 7 years
Arizona Department of Education, Scottsdale
Unified School District, Arizona Charter Schools
Association & Center for Student Achievement
ILDI LACZKO-KERR
ILDI LACZKO-KERR
Core Principles Guiding Our Work Today
LEARNING OBJECTIVESP A R T I C I P A N T S W I L L :
• Identify potential uses for PivotTables and PivotCharts
• Create a PivotTable and PivotChart using A-F student level data
• Learn how to use PivotTables and PivotCharts to save time and energy by quickly, efficiently and effectively analyze data
SET YOUR INTENTIONS
WHAT ARE YOU HOPING TO
ACHIEVE TODAY?
Why PivotTables?
• PivotTables help summarize, analyze, explore, and present summary data
• You can use a PivotChart report to visualize that summary data in a PivotTable report, and to easily see comparisons, patterns, and trends
• Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data
A PivotTable Report Is Especially Designed:
• Querying large amounts of data in many user-friendly ways
• Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas
• Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you
A PivotTable Report Is Especially Designed:
• Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data
• Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want
• Presenting concise, attractive, and annotated online or printed reports
Possible uses for PivotTables and PivotCharts
• Any data that can be contained in an Excel spreadsheet can be analyzed using a PivotTable
• Types of data that you already have access to:• Student achievement (AzMERIT, SGP, benchmarks) • Budgets• Attendance • Discipline
Other Possible Uses?
Working with your shoulder partner to identify at least two other types of data that you could analyze using a PivotTable:1. _____________________________________2. _____________________________________3. _____________________________________4. _____________________________________
Be prepared to report out to the large group.
A-F Static Data FileThe static file merges assessment data provided by statewide assessment
vendors with AzEDS enrollment information to help schools understand performance based on various accountability-related business rules (e.g., FAY
status, test participation status, proficiency level, etc.).
Analyzing A-F Data
2018 A-F Static File
Getting Started!Step 1: Open you’re A-F Static student level data file. Step 2: Select the Insert tab to access the PivotTable functionStep 3: Select the PivotTable icon
• PivotTable command creates tables without charts• PivotChart command creates tables and charts automatically
Step 4: Choose PivotTable. • Follow the Create PivotTable Wizard.• Notice the hash lines around your data, this lets you know
that your PivotTable will use all of the data in your file.
Step 4 (cont): The wizard creates a blank PivotTable in a new worksheet.
Blank PivotTable
PivotTable Field List
• Move data from your field list into the columns, rows and values sections to create your PivotTable.
• Moving data is as easy as dragging and dropping it into the boxes below!
Guided Practice: Creating a PivotTable
Step 1: Open your Excel file and follow Steps 1-4 to start a blank PivotTable.
Step 2: What’s your question?“How many students are in the low, typical and high growth categories in each grade?”
Step 3: Determine the data that you need to select in your PivotTable to answer this question:•Test subject•Grade•SGP CY and SGP Category
Step 4: Start your PivotTable!
Step 5: Determine how you want to display your data. • Drag the Subject field and
place it in the filter box, the Grade and SGP Categoryfields into the Row Labeland/or Column Label boxes.
• SGP Category and Gradecan be in either the column or the row depending on your preference
Step 6: Select the SGPCY field and drag into the Values box. Notice this results in the Count of SGP scores
Step 7: To duplicate a PivotTable, copy the entire table and paste below, make changes to the filters or fields.
Step 8: Double check your work.• Do the results make sense? Are you able to answer your
question?• Is your PivotTable is reporting the number of students by SGP
level (low, typical and high), grade and subject.
Step 9: Formatting your results. Filter out data that you don’t want displayed. • Select the Column Labels drop down arrow. Remove the
checkmark next to the grade levels you do not want to display. • Select the Row Labels drop down arrow. Remove the
checkmarks next to the growth levels you don’t want to display.
Step 10: Label your PivotTable tab• Right click the tab, select Rename.• When the tab turn black, name your PivotTable i.e., SGP
summary.• If you don’t name as you go- you’ll loose track of your
work.
RESULTS!
You should have a PivotTable the summarizes the number of students in each growth category for students in your school by grade and content area. • Sample:
• Take a moment to interpret your results.• Share one Ah-Ha with the person next to you.
Now What?
Use these results in your PivotTable to identify students who need intervention and enrichment.
Step 1: Double click any number in a cell to see which students’ data is use to create it• Excel will open a new worksheet that has the individual
students’ selected.• The new worksheet copies all of the data from the original
data sheet but ONLY for those students you selected.Step 2: Evaluate these students’ results by digging deeper into their
areas of weakness/strength.
Create PivotCharts from PivotTables
• A PivotChart is a chart derived from a PivotTable
• The advantage of a PivotChart over a regular chart is that you can drag fields to different locations in the chart layout to display different levels of detail or different views of the data
• PivotCharts are linked to the data in the PivotTable so when you make changes to the data in the PivotTable you automatically change the PivotChart
• The easiest way to create a PivotChart is to create a PivotTable
Step 1: Select any cell in the PivotTable.Step 2: Select the Insert tab to choose the type of chart.
Step 3: Formatting your chart.• The Design tab allows you to change the layout of your
chart, add titles, label your x and y axis, etc.
Using Data to Improve Learning
Work with your shoulder partner to identify at least two potential ways that you can use Pivot Tables to support student
achievement and/or your school improvement initiatives.
BE PREPARED TO REPORT OUT TO THE LARGE GROUP
Q U E S T I O N S
Reflect:
Appreciation, Apology or Aha!
SURVEY & FEEDBACKComplete the online survey:
1. Use the QR Code2. Go to lotustcg.com and
select Survey Link from the menu bar
Follow Up/FeedbackEmail: [email protected]
L O T U ST R A I N I N G & C O N S U L T I N G G R O U P