Attendance Spreadsheet

We have been using a spreadsheet to track attendance in my classes for years. I’ve decided to make this tool available to everyone!

We use Google Sheets. But I will include both the Google Sheets link and an Excel (.xlsx) file. I have not fired up Excel on this machine in a long while, so I have not tested what works well or looks good in Excel.

This is a very advanced spreadsheet. It does amazing things and looks great. It is very easy to use to take attendance by non-experts with a phone in a classroom. However, in order to customize it, fix it when weird situations occur or make changes to it requires an expert. I am not your expert 🙂 I wish I could be there for all of you but I simply do not have the time. Find a local expert, share the sheet with them and tell them what you’d like to do with it. Please do not ask me to help you with it. I offer this file freely and as-is.

All of the data in this sheet is fictional. I just stuffed it with random names and numbers to show off what the sheet can do. When you are ready to do so with your own copy, you’ll want to clear all of this example data and start from scratch.

To use the file as we do, click the link listed below. It will open the Google Sheet in a new tab that you can only view (not edit). To get your own copy, simply go to the File menu and select Make a Copy. You may be prompted to select the Google Drive in which to save the sheet.

https://docs.google.com/spreadsheets/d/1XbfEz1UMLl9GXB_bmTXNN6DJqmE4OMW9d25RpF-sQvs/edit?usp=sharing

If you want the untested Excel version, click the “Free Download” button, below. You don’t need to do that if you are using the link above.

I will explain what each tab does.

1. Settings tab: This sheet normally stays hidden to keep it out of the way. I simply put a few ‘variables’ that are used in the sheet. Take a look and edit what you need to. Then Hide the tab.
2. Lists tab: This is a place where I keep several Lists of things that I use throughout the sheet in various drop-down picklists. Edit to match your situation.
3. Roster tab: The Roster tab is a growing list of all your people. Whether you have workers, visitors, children (in general), children from various bus routes or other ministries – any people you want to track are entered here the first time they arrive. So you add names in any new blank row at the bottom, select what kind of person they are (whatever is relevant to you and that is in your Lists tab). We then track the bus route #. You check the box to show that person was in attendance that day.
4. Builder tab: Each Sunday, when we check many boxes for people that showed up that day (in the Roster tab), this sheet automatically fills with an alphabetized list of people from the class and some basic numbers. We use the numbers to do a quick head-count to double-check our Roster work. This tab is to summarize and clean-up a given week’s attendance data so we can copy-paste easily.
5. Database tab: Each week after church I copy all attendance cells from the Builder tab to new rows in the Database tab. Sort as you see fit.
6. Summarized tab: This is where we run a bunch of formulas on the Database tab’s content and prepare it for dashboard use. We put things in tables, summarized by week (in the left-most columns) and the items in the right-most sections is merely different snapshots of this or that time-period or the various columns of data we want to report on, such as overall Attendance, Worker counts, total Children in attendance, Decisions, etc. This is a great tab to share with your Paster or Sunday School superintendent or whomever keeps track of your church’s records. They can simply look at the row for a given week and enter it in whatever software or record-keeping system they use.
7. Dashboard tab: This is where the glitz is. I wanted to quickly summarize all the work in the other tabs. The summarizing is done in the previously mentioned tab, but the charts and ‘scorecards’ in this tab show it off visually. You have 2 picklists to vary as you want. You select which Metric you want to chart in the big, central chart in B16, Then in B23 you can further select a data range to focus on. The Date selector will only filter the data for the scorecards, not all the charts. You’ll see as you play with it.