Checklist Template In Google Sheets

In this tutorial, we’ll create a checklist template in Google Sheets.

We’ll use checkboxes, conditional formatting and a sparkline to build a checklist template like this:

Google Sheets checklist template

Checklist Template Use Case

There are many situations when a checklist comes in handy.

From simple to-do lists to project planners, from teaching lessons to tracking physical goods.

And although this simple spreadsheet checklist isn’t suitable for large, complex projects, or projects that require more robust data trails, it’s a quick and easy way to add some useful flair to your spreadsheet projects.

When I teach live workshops, I often include a front sheet in my Google Sheet that I use as a checklist for the exercise steps.

It ensures I don’t forget anything and gives the audience a visual clue as to where we’re up to in the workshop. And I get almost as many questions about how I built these checklists as for the topic of the actual workshops.

How To Create A Google Sheets Checklist Template

Checklist Template

Feel free to download the checklist template and make your own copy:

Click here to get the checklist template

Feel free to copy this sheet: File > Make a copy

I’ve set my file sharings to allow anyone with the link to view this file. You may not be able to open this file because it’s from an outside organization, and my Google Workspace domain is not whitelisted at your organization. You may be able to ask your Google Workspace administrator about this.

In the meantime, feel free to open it in an incognito window and you should be able to view it.

1. Sheet Set up

We start with the checklist title on row 1, center aligned.

Then leave a blank row.

On row 3, we put the word “Progress” in column 1. Leave the other column blank for now.

Row 4 is blank.

On row 5, put the headers for the checklist table: Status and Step

On rows 6 onwards, we put a checkbox in column 1 and the corresponding step in column 2 of that row.

To add a checkbox, go to: Insert > Checkbox

2. Add Conditional Formatting

Next, we’ll apply conditional formatting across an entire row to turn the row red when it’s marked as done.

Highlight all the rows with your checkboxes and steps, e.g. A6 to C15 in this example.

Select Conditional Formatting and add this Custom Formula Is rule:

=$A6

Set the formatting rules to light red background, dark red text, and strikethrough.

Now, whenever you check the checkboxes, the entire row will be formatted red to show it’s complete (see the GIF image at the start of this post).

We also set the table to have alternating colors, from the menu: Format > Alternating colors

3. Use A Sparkline To Create A Status Bar

Here’s the sparkline function in cell B3 that creates that dynamic status bar:

=SPARKLINE(COUNTIF(A6:A,TRUE),{"charttype","bar" ; "max",COUNTA(B6:B) ; "color1","red"})

Let’s break it down to see what’s going on.

Checkbox Count

Firstly, the COUNTIF function:

COUNTIF(A6:A, TRUE)

counts how many of the checkboxes in column A have been checked (i.e. have a TRUE value).

The output of this is a single number, between 0 and 10 in this example.

We pass that value into the SPARKLINE function.

Sparkline Bar Chart

Then we set the sparkline to be a bar chat, with the first option: 

"charttype","bar"

Max Value

Next, we need to specify a maximum value for the bar chart, so that it can compare the count of checked checkboxes (e.g. 4) against the maximum possible number (10 in this example) to get the percentage completion.

We could simply type in the max value of 10 as an option, but it’s better practice to set it with a formula so that it will update automatically if your data changes.

To do this we count the number of “steps” in the column next to the checkboxes: 

"max",COUNTA(B6:B)

Custom Color

Finally, we set a custom color for the sparkline with the final custom option: 

"color1","red"

Notes

In some European countries, sparkline formulas have a slightly different syntax and use “\” instead of “;” (read more here about syntax differences based on Google Sheets location).

Pro tip: to select or un-select multiple checkboxes at once, highlight them all and press the space bar!

4. Add A Progress Percentage

The final formula goes in cell C3 next to the sparkline to show the actual percentage complete value:

=COUNTIF(A6:A,TRUE)/COUNTA(B6:B)

This counts the checkboxes in column 1 and divides them by the total count of steps in column 2. Format it as a “%”.

24 thoughts on “Checklist Template In Google Sheets”

  1. Thank you for sharing this tip. I have always wondered about how to create a progress bar for projects and for letting people know how much was left to complete on a project or report. This will come in very handy moving forward. I also appreciate the way you have explained it very clearly. It is easy to understand and follow along.

  2. Nice and simple template! Good start for a more complex version where it would be great to add:
    – Transaction log sheet that shows who and when an item was checked or unchecked (for traceability purposes working with a team). This sheet should be “read only”
    – Add a “category” column and show progress per “category”

    A little app script magic can make this happen!

  3. Thank you. This is very good simple template and just trying to use it to increase productivity. It takes time to learned how to edit the script but I enjoy in trial and error

  4. Wonderful. I was looking for a way to help my students document their progress in their independent learning process, so that they can know where they stand, but I can also track them and see if they need help. this is perfect. Thanks a lot for posting

  5. Thank you so much for sharing this, Ben! This is amazing.
    I made a copy to edit and create my own checklist, and then added more rows to the list.

    Now the progress bar does not fill up even I kept COUNTIF(A6:A, TRUE) and “max”,COUNTA(B6:B) in the formula. Column C shows =COUNTIF(A6:A,TRUE)/COUNTA(B6:B) so it should be 100% when all the items are checked, but it isn’t. I was wondering what I did wrong?

  6. Wow this is SO helpful for me and exactly what I’ve been looking for. Thank you! I’m running into a problem though: when I have 5 items and check one, it’s displaying 17% complete instead of 20%. Not sure where that extra 3% is missing from. Have you seen this before? Thanks!

    1. Hi Emma, just change =COUNTIF(A6:A,TRUE)/COUNTA(B6:B)
      to =COUNTIF(A6:A,TRUE)/COUNTA(A6:A) at C3

      Credits to Max Makhrov
      and many thanks to Ben

  7. hi there! So glad I found this! I’m a wedding photographer and officiant and I need to remember to bring the same things each event. I’m hoping to use it multiple times a week. Do you think that could work? Do I just keep a clean version as a template and copy it over and over again each time I want to use it?/

    1. I would make a clean version an actual template in Google Sheets. That way you don’t have to copy it every time – you just open the template gallery and pick your template and it will make a new one for you.

  8. oh man this is SO cool! So much more informative than I expected when I was looking for a template. I followed steps and made my own. This is great and I Love the spacebar check boxes tip! Thank you

  9. This is great! I have created, locked protected all the boxes except the checkboxes. Shared but since live document. Everyone is using same document. I changed edit to copy to force them to make a copy to use their own. What did I miss? Help!

  10. Hi Ben,

    I have an issue that sometimes when I click the checkbox, I somehow delete it when I try to click the checkbox.

    Are you familiar with this issue? Anyway to prevent it from occurring?

Leave a Reply

Your email address will not be published. Required fields are marked *