data analysis tips and tricks - charter school institute · tips and tricks 2018 colorado charter...
TRANSCRIPT
![Page 1: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/1.jpg)
Data Analysis Tips and Tricks2018 Colorado Charter Schools ConferencePresented by Aislinn Walsh, CSI School Performance Analyst
![Page 2: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/2.jpg)
Presentation Outline
Looking at state files in Microsoft ExcelApplying a filter Navigating filters/columnsFinding counts and averagesEntering formulasUsing a VLookup formula to add in additional student informationUsing Pivot Tables to summarize data
![Page 3: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/3.jpg)
Mirror my ScreenPlease open the Excel file called “Sample State Data File for Data Analysis Presentation”.
![Page 4: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/4.jpg)
Tabs, Columns, & Rows
Excel Vocab Review
Tabs: Different windows in an Excel file Columns: Always
letters of the alphabet Rows: Always a
number
What are we using today?
Tabs: CMAS Growth CMAS Results NWEA Results VLookup Example Teacher Names Pivot Example
“Cell A1” = Column A, Row 1
![Page 5: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/5.jpg)
Scenario #1I am a School Leader. I’m interested in a certain population of students in my school. How can I quickly see how they’ve performed?
![Page 6: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/6.jpg)
Applying & Navigating Filters
![Page 7: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/7.jpg)
Finding Counts and Averages
![Page 8: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/8.jpg)
Scenario #2I’ve seen how students performed on CMAS, now I want to compare that to their interim performance. How can I pull in percentile ranks from their NWEA MAP data?
![Page 9: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/9.jpg)
Using VLookupAs long as you have a matching value in two different files, you can pull anything you want from one file to another using VLookup.
Why SASIDs? • Always consistent,
always unique
Why Percentile Rank? • Comparison value
available with NWEA
Where do I begin?• Cell C3• Type “=VLOOKUP(“ into
the cell
![Page 10: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/10.jpg)
Scenario #3I want to look at student level results by teacher.
![Page 11: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/11.jpg)
Using VLookupPart 2VLookup lets me pull values from other files. I can use it here.
We’re going to start by inserting a column. • Right click on Column AB
in the CMAS Growth tab and click “Insert”.
![Page 12: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/12.jpg)
Scenario #4I want to display some of the information I learned. What can I do?
![Page 13: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/13.jpg)
Using Pivot TablesPivot Tables are easy ways to create data dashboards.
Sample Pivot Table/Pivot Chart/Slicers
• Pivot Table: Takes a larger data file and compresses it into a table, using filters much like we did earlier.
• Pivot Chart: Can only be built from a Pivot Table, will change data automatically based on the results of the Pivot Table.
• Slicers: Can only be built from a Pivot Table/Pivot Chart, functions a lot like filters.
![Page 14: Data Analysis Tips and Tricks - Charter School Institute · Tips and Tricks 2018 Colorado Charter Schools Conference Presented by Aislinn Walsh, CSI School Performance Analyst. Presentation](https://reader035.vdocuments.us/reader035/viewer/2022070706/5e9c746b4a8136610a249e38/html5/thumbnails/14.jpg)
Finding Comparison Data
CDE Resources (some examples): Schoolview Data Center School/District Dashboard Education Statistics (Excel Flatfiles)