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

(File > Make a copy)

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=TRUE

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).

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(A6:A, TRUE) function 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 by the total count of steps in column 2. Format it as a “%”.

3 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

Leave a Reply

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