Google Sheets - Template for a Senior Course Audit

This week I presented at the Michigan School Counselors Association fall conference.  One of the sessions I co-led with CACC Counselor Terri Tchorzynski on being more efficient when using Google Apps.  One of the examples we provided was how a senior course audit template could be utilized in place of a paper-pencil audit (which a majority of counselors in our state still use...).

Several thought this should be shared wider, but before I do I need to give a few caveats:

1. Credit has to be given to @waffleta (yes, my husband) - he created the initial senior audit I started with, this is it refined and generalized...along with directions.  
2. If this is something you find worthwhile, you may find out if your SIS can do something similar!  Many of them can, and you can save yourself even MORE time as the course catalog is already in there :-)
3. Finally, if you need to add rows for additional courses, you will need to understand how to set up data validation that uses data from another sheet.  Not hard, but it may be unfamiliar to you...

Here is a screen shot of the audit sheet:

Here is a screen shot of part of the summary it creates:

Here is how to use a provided Google Sheet template for audits -

Here are some general directions for modifying and using the credit audit template.

Making a Copy of the Template

1. Click on this Template Preview link for the Credit Audit Template spreadsheet
2. Click on the blue USE TEMPLATE button in the upper-right corner to make a copy of it in your own Google Drive account
3. Click on the name of the new document in the upper left and rename the document for your use (replacing the 2 - Credit Audit Template)

Customizing The Template For Your District

1. Open the template Google Sheet you created (if it currently isn’t)
2. Go to the sheet titled Course Sheet
3. In the gray boxes under the appropriate headings, replace the sample list of courses with the courses found in your school’s course catalog - you should include any courses that can meet the requirement for that category in the list. (You can leave empty gray boxes - they will be ignored on the audit sheet.)
4. Add/copy any courses that can satisfy elective course credit requirements in the red box under Elective Courses (most courses will be available here as well as under the course category).
5. Go to Print (File -> Print, or Print icon, or control+P)
6. Click on Headers & footers option
7. Select EDIT CUSTOM FIELDS
8. Add desired information that will appear on all copies of this template (ie. school district name, school building name, your name, date type for current date)
9. Click CONFIRM
10. Click NEXT
11. Click Cancel on the next screen (this will save your headers/footers)
12. Go to the Audit Sheet & scroll to bottom
13. Enter your School District Name in the appropriate box
14. Go to the Summary Sheet
15. In cell G38 put in the number of course opportunities students have
16. Fill in the appropriate number of Required Credits for each category (yellow boxes

Using the Audit Sheet

1. Make a copy of your customized template sheet
• There are many ways to do this, here are a few options you can explore in more depth:
1. Make your template an Organizational Template to use in Google Drive
2. Modify the URL for your template by changing the “edit…” ending to “template/preview” (that is what I did with mine for you to copy)
3. Use the Make a copy option under the File Menu
2. Set the name of the document (probably you would include the student name in the title)
3. Fill in the 3 yellow boxes on the Summary sheet
4. Go to the Audit Sheet
5. Fill in the student’s coursework, adding notes as appropriate to the right
6. Use an x to mark if the student is currently enrolled in the course, failed the course, or earned credit for the course
• Boxes will turn green as you fill these in - YOU MUST USE AN x
7. Mark the Online Experience and Senior Math Experience with Yes as appropriate.

Summary Sheet

All information should display on the summary sheet from the Audit Sheet, along with calculations at the bottom. You may need to set custom page breaks when printing to fit it to one page, if a pdf or paper copy is desired.

Let me know if you find it useful!