Use Excel Pivot Tables to analyze data from ASAP reports

Many ASAP reports allow for export to CSV or Excel format. Once in Excel, you can massage the data and analyze it. One quick way to get summary information from lots of detailed records is to use Excel Pivot Tables. The following is a quick instruction on creating basic Pivot Tables. There is more you can do, but this is a quick way to start using them.

  • Run the report and export to Excel
  • Open the file in Excel
  • Highlight the entire area of the data using your mouse, including the header row, making sure you have all columns and rows of the data in your highlighted area.

  • Find the Pivot Table function in Excel. In the newer releases, it is under Insert icon | Pivot Table or Data | Summarize with Pivot Table.
  • You will be asked if you want to use the Select range and where to place the table. We suggest you use the Select ranged & choose "New Worksheet". Then click OK.

  • The Pivot Table is created and now you work with the data.
  • Find the value you want in the rows and drag it to the Row Box. In my example, I wanted Ethnicity in my rows. I found Ethnicity, dragged it with my mouse and dropped it in the Row box. 
  • Find the value you want to use in your columns. In my example, I found Gender and dragged it to Columns. 
  • Then choose what you want your results to do. For numbers, you can Sum, Count, etc. I chose to Count the Gender for every Ethnicity so I dragged Gender into the "Values" box.

  • Last, choose how you want to summarize. Sum, count, etc. by clicking on the "?" and selecting "Count"

 

You can actually do multi-level analysis by having not one but two or three levels for Rows (and columns). Such as analysis of Zip Code, by Gender, so you first drag Zip Code to Rows and then drag Gender as a 2nd level grouping. This is more complex so start simple and grow from there.

My sample of data shows me how many Females and Males in each Ethnicity. Since the "0" and (blank) are the biggest, I obviously don't do a good job of recording Ethnicity on my student records!

For more information, check out Pivot Tables on the Office support site: https://support.office.com/en-IE/article/Create-a-PivotTable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

 

Have more questions? Submit a request

Comments

Powered by Zendesk