Click here to go back to the main course page, so that you can see the other dashboards that you can build, as well as to get your dashboard formulas cheat sheet.
In this lesson, I am going to show you how to build a student attendance dashboard in Google Sheets (Video is below). This lesson is longer than the rest of the dashboard lessons, and contains lots of important concepts / formulas. First we will build the simple version of the dashboard, and then you can add on to the existing dashboard by going through the advanced lessons.
In addition to showing the aggregated totals for the “Dashboard” tab, I will also show you how to build a tracker that shows daily attendance / time worked for each student. Also, I will teach you how to create an individual tab for each teacher, so that only the data / students for each individual teacher displays on that tab.
The basic version of the dashboard uses two reports, one that provides a list of all students, and another that shows the attendance activity for students. In the advanced version, we will add in a third report that will show activity from a completely different educational platform, where the report is in a different format than the first activity report.
After downloading the raw data that is linked below, scroll down and begin the video to start building the student attendance dashboard. Timestamps for each chapter are also shown below, so that you can easily find your place if you need to take a break.
Get the ultimate Google Sheets cheat sheet
Download the raw data
Download these CSV (Spreadsheet) files, so that they will be readily available for you to use / import when you are building the dashboards.
Activity 1 Report (Simple Version)
Activity 2 Report (Advanced Version)
View a copy of the completed dashboard
A finished dashboard example is linked below, for your reference. Use this template to help you view the formulas and formatting, while building your version as you follow along with the course.
View the completed attendance dashboard
To make your own copy of the dashboard examples, do the following:
- Click “File” on the top toolbar while the sheet is open
- Click “Make a copy”
- Rename the sheet if you want
- Click “Okay”
Your completed dashboard will look like the image below, after you complete the final “Revamp” lesson.
How to build the student attendance dashboard
Follow along with the video below to build the student attendance dashboard!
A note on date formats in varying countries
If you are located in Europe or outside of the U.S., your date format will be different than the United States, and this may affect the creation of some of the dashboards. You can change the date format by clicking File > Settings > General > Locale > United States
Timestamps for each chapter / lesson:
0:00:00 1 Attendance Dashboard Overview
0:04:29 2 Create and color the tabs
0:06:25 3 Import the data from the report(s)
0:10:54 4 Filter and Sort the Student Report
0:16:43 5 Format the attendance tab and enter the headers
0:18:41 6 Transfer data to the attendance tab_ ARRAYFORMULA
0:26:20 7 Shrink columns to make square cells
0:27:45 8 Create the timeline
0:35:33 9 Apply conditional formatting to the timeline, for the weekends
0:37:55 10 Set the attendance goal_ Custom conditional formatting
0:41:40 11 Enter the daily attendance formula
0:50:20 12 Turn the 1’s and 0’s into P’s and A’s
0:53:02 13 Calculate total attendance points per day, and per student
0:55:58 14 Calculate the average attendance points per student
0:58:53 15 Conditional formatting for daily attendance and totals
1:03:12 16 Create the Time Worked tab
1:16:08 17 Dashboard tab overview
1:17:39 18 Format the dashboard tab
1:21:42 19 Create the list of teachers
1:23:12 20 Count the number of students per teacher
1:25:30 21 Calculate the total attendance points for each teacher
1:26:52 22 Creating the attendance goals for teachers
1:29:20 23 Calculate the attendance points needed
1:32:16 24 Average attendance points per student
1:33:55 25 Percentage of attendance goal met
1:36:39 26 Create sparklines
1:40:58 27 Replicate the teacher formulas, for each program
1:45:01 28 Create a column chart for teacher attendance points
1:50:19 29 Create a column chart for program attendance points
1:53:11 30 Create stacked column charts_ Points earned vs_ points needed
2:00:47 31 Add a logo, and a recap of the dashboard tab
2:02:07 32 Create the teacher view tab
2:08:37 33 Recap and wrapping up the simple version
2:10:07 Z 1 Advanced attendance dashboard overview
2:11:38 Z2 Build the session activity 2 tab
2:17:33 Z3 Create the activity combine tab
2:18:19 Z4 Daily Activity 1 tab Create the Unique Codes
2:25:25 Z5 Create time worked column in number format (Daily Activity 1 tab)
2:27:20 Z6 Attach the platform identifier (And zoomed in formula recap)
2:32:39 Z7 Calculate time worked (Session Activity 2)
2:36:11 Z8 Optional Duration Conversions
2:39:24 Z9 Extract the student ID Using INDEX and SPLIT
2:43:47 Z10 Create the unique codes (Session Activity 2)
2:45:10 Z11 Time worked reference in number format (Session Activity 2)
2:47:05 Z12 Sum sessions to find daily times QUERY FUNCTION
2:52:13 Z13 Attach the platform identifier (Session Activity 2)
2:54:50 Z14 Build the combined activity tab (Create a stacked, unique array)
2:59:16 Z15 Sum time worked for both platforms QUERY part 2
3:01:41 Z16 Advanced attendance formula overview (Choose your method)
3:04:15 Z17 Modify the daily attendance formulas
3:10:23 Z18 Modify the daily time worked columns
3:12:56 Z19 A real world data example
3:15:29 Z20 Reset the attendance goal
3:16:45 Z21 Hide the back end tabs
3:17:24 Z22 Attendance dashboard tab revamp (Black & White version)
3:22:11 Z23 Dashboard tab revamp #2 (Blue version)
3:26:07 Z24 Attendance tab revamp
3:29:21 Z25 How to use IMPORTRANGE
