In this article, you will find the steps for both Excel and Google Sheets on how to run a Pivot table from the passes report and target those students who create passes the most.
Video Walkthrough - Excel
Note: Although this video was recorded using Excel for Mac, the instructions and process are the same for Windows users as well.
To find frequent flyers (students who are frequently using passes), you’ll first need to export the passes report. If you would like to know how to do this, please refer to the Administrators - Pass Search article.
Using Pass Data in Excel
Once you have exported the CSV file for the destination/origin room you would like to get the data from, you need to follow these next steps:
Save your CSV as an Excel File or open with Google Sheets
Select the entire data set on your CSV file
Click 'Insert' and select 'Table'
Select 'My Table has Headers' and click OK
Once it is set as a table, make sure not to have any empty rows
Click 'Insert' and select 'Pivot table'
Once you have the pivot table open, drag 'Students names' to the 'Rows' pivot fields
Drag the 'Duration to the 'Values' Pivot Field
Change ‘Duration’ Field setting to be ‘Count’
Select the entire pivot table
Click ‘Home’
Click ‘Conditional Formatting’
Select ‘Top/Bottom Rules’
Select ‘Above Average’
Design the coloring to your preferences
Click OK
Your pivot table should now show a list of the total number of passes that every student in the data set has had along with the above average students’ total counts highlighted for you.
Using Pass Data in Google Sheets
Once you have exported the CSV file for the destination/origin room you would like to get the data from, you need to follow these next steps:
Upload your CSV file to your Google Drive and open it with Google Sheets
Click 'Insert' and select 'Pivot table'
Once you have the pivot table open, drag 'Students names' to the 'Rows' pivot fields
Uncheck the totals option
Drag the 'Duration to the 'Values' Pivot Field
Change ‘Duration’ summarize by setting to be ‘Count’
Click ‘Format’
Click ‘Conditional Formatting'
Select ‘Color Scale’ in the right pop-up menu
Click 'Apply to Range' and highlight all of the rows in column B that show the total passes for each student
Use Preview to select your coloring design
We suggest using a white to color scale so your more frequent flyers are highlighted in a darker color.
Change 'Midpoint' to 'Number' and type in the formula below and adjust for your number of rows
=average(B$2:B$9)
Make sure you change the last number to match the last row number in your data set. If you have 100 students, your formula would be (B$2:B$101)
Click 'Done'
Your pivot table should now show a list of the total number of passes that every student in the data set has had along with color shading noting the above average students’ total pass counts.
Here is a quick video walkthrough of this process:
This is just one of many ways to use pivot tables and conditional formatting to analyze your pass data.
💡Feel free to reach out to your Customer Success Manager for help or check out our SmartPass Academy webinars for sessions on using your SmartPass Data to understand student movement.
FAQ
Are there any plans for a report like this in SmartPass?
Yes! Coming soon to SmartPass you will have access to Summary Reports, where you will be able to find this data and more. Here’s a quick read about Summary Reports: Summary Reports