Student distance learning dashboard w/ ready-made Pivot Tables

I've been using a (secured) Google Sheet to keep track of data about students who are out of school and require short term or longer-term distance accommodations that are outside of the primary/default current mode. Most schools (should) have a student information system and/or attendance tracking system that may be capable of recording such information, and those should be continue to serve as the official 'system(s) of record'. However, these systems also may not have the kind of reports and views to provide more timely insights for those who may need information in order to make better informed decisions.

As we're at a midpoint in the school year (in the US at least) it is a good time to review and better understand the patterns and trends related to emergency accommodations (i.e. distance learning) that there simply may not be the bandwidth for while deep in the middle of what is essentially prolonged crisis response.

This link should take you to an option to make a copy of a Google Sheet which I have setup. Here are some thoughts that may help you use it better.

Someone (or a small team) needs to 'own' this and the process by which data is entered and reviewed against other systems. 

---

Don't input all data if you don't have to

I've added a bunch of columns, but you do not need to track all of the data if there is already a good system in place for it and/or you don't necessarily think it will be important (for example, you may not need to list  a student's name or the teacher names or parent contact etc.). Right click on any column to delete it.

The most important columns to use are probably:

• Notification date
• School/division
• Grade
• Status
• Reason

---

Use/add tabs

The first tab is the 'raw' data. The second and third contain pre-set pivot tables that will automatically update as you enter more raw data. You can duplicate either of the pivot table tabs and mess around with the view choices (rows, columns, and values)

---

Set up data validation drop-down lists

To make data entry easier, set up certain fields with pre-set lists of choices (select cell -> Data -> Data Validation -> List of Items)

I suggest using this for the school/division, grade, status, and reason fields. Consistency in these areas will make the pivot tables less prone to error.

Once you have one cell set up, you can highlight other cells in the column and do Edit -> Paste Special -> Paste Data Validation only. This will add the drop down criteria to the selected cells without replacing what's already in there. This is super helpful if you decide to add or change the drop-down lists over time.

---

Use filters instead of sort

You can use Filters on the 'Master' tab to quickly see all of the rows that contain a certain field (for example, all students in grade 7). This is better than constantly sorting and re-sorting the data (though this works too).

With filters, you can also utilize data in custom columns for tracking things that you may not need in a pivot table (for example, I sometimes track families I may need to follow up with in the first column).