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:
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 (requires you to be logged in to a Google account):
Click here to get the checklist template
Feel free to copy this sheet with the “USE TEMPLATE” button.
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 “%”.
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.
Thank you for sharing this me
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!
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
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
thanks for sharing this Ben! much appreciated!
You’re welcome! Hope you find it useful. Cheers!
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?
I just discovered this template. Love it. But wondering the same thing. Did you ever figure this out?
Thank you very much for this clear and structured explanation! It helped me a lot!
Thanks for the ready document!
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!
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
…and for B3, do the same modification
change B6:B to A6:A
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?/
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.
Great Ben,
So Glad.
This helps a lot and gives a new vision.
Thanks a lot.
Dhiraj Shah
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
Hi, lovely, clean idea with the sparkline. But hey, I need to clear a form that will have SOME checkboxes ticked.
How do I do that?
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!
You’re awesome! Thank you
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?
Great idea! This template will come in handy when I need to create a checklist for any project. Thank you for sharing!
You’re welcome!