Use Google Sheets AI to Build an Attendance Dashboard

What This Does

Google Sheets' AI formula assistance helps you build an automated chronic absenteeism dashboard — tracking absence rates, flagging students above the 10% threshold, and color-coding for urgency — so you can see at a glance which students need outreach each week.

Before You Start

• You have a Google account (free)
• You have attendance data exported from your SIS (PowerSchool or Infinite Campus export as CSV)
• Your data has at minimum: student identifier (not name — use ID or anonymous), days present, and total school days

Steps

1. Set up your spreadsheet columns

Open Google Sheets and create these columns:

• Column A: Student ID (use anonymous identifier, not student name — keep PII minimal in general-purpose tools)
• Column B: Days Present
• Column C: Total School Days
• Column D: Absence Rate % (build this formula)
• Column E: Status (Chronic / At-Risk / OK)

2. Get help building the Absence Rate formula

Click on cell D2. At the bottom right of your screen, click the Explore button (star icon). In the "Ask a question about your data" box, type: "How do I calculate Column D as the percentage of days absent, where days absent = Column C minus Column B, divided by Column C?"

Or ask ChatGPT: "In Google Sheets, Column B is days present, Column C is total school days. Write a formula for Column D that calculates absence rate as a percentage. Show as a percentage with 1 decimal place."

3. Enter the formula in D2

Paste the formula. It should look like: =ROUND(((C2-B2)/C2)*100,1)

Drag the fill handle down through all rows.

4. Build the Status column (Column E)

Ask ChatGPT: "Write a Google Sheets IF formula: if D2 >= 10 show 'CHRONIC', if D2 >= 5 show 'AT-RISK', otherwise show 'OK'."

Paste the result in E2 and drag down.

5. Add conditional formatting

Select all of Column E. Click Format → Conditional formatting:

• If text contains "CHRONIC" → red background
• If text contains "AT-RISK" → yellow background
• If text contains "OK" → green background

6. Sort and filter for weekly review

Click the Data menu → Create a filter. This lets you filter by Column E to see only CHRONIC students, or sort by Column D to rank by absence rate.

7. Update weekly

Export updated attendance data from your SIS at the start of each week. Paste into Columns B and C (overwriting old data). Column D and E recalculate automatically.

Real Example

Scenario: You need to identify which students to prioritize for attendance outreach in the third week of October — chronic absenteeism reporting is due to the district next Friday.

What you do: Export SIS attendance summary (as CSV), paste into your Sheets dashboard. Filter Column E by "CHRONIC." The list shows 8 students above the 10% threshold. You have your outreach list for the week in 5 minutes.

What you get: A ranked list of students by absence rate with clear status flags — much faster than opening each student record in PowerSchool individually.

Tips

• Keep student names out of this general Google Sheet if possible — use Student ID numbers. Your SIS is the authoritative record; this sheet is an analysis tool
• Add a "Notes" column (Column F) to log when you've contacted a family, so you're not re-contacting the same household multiple times in a week
• If your SIS already has this report built in, use it instead — only build the Sheets version if your SIS doesn't surface the data the way you need it

Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.