How to create a progress bar based on the number of checklist completed in Google Sheets

In this post, I am going to show you a step by step guide to create a progress bar that is based on the number of checklist that is completed in Google Sheets.

Create a progress bar based on the number of checklist completed in Google Sheets

We will split this into 2 parts. The 1st part is:

Adding Checklists

  1. To insert checkboxes, go to Insert -> Tickbox
  2. And add your task list beside it.

Adding Progress Bar

  1. To add the progress in %, use this formula:
    =COUNTIF(B3:B,true)/CountA(B3:B).
    • COUNTIF(B3:B,true) – this will count the number of checkboxes that are checked.
    • CountA(B3:B) – this will count the number of checkboxes available.
    • Be sure to format the cell to show in percentage by going to Format -> Number -> Percentage.
  2. To add progress bar, use this formula:
    =SPARKLINE({Countif(B3:B,true),Countif(B3:B,false)},
    {"charttype","bar" ; "max",COUNTA(B3:B) ; "color1","#1aa260"; "color2", "#D3D3D3"})
    • The syntax for using sparkline is: SPARKLINE(data, [options]).
    • {Countif(B3:B,true),Countif(B3:B,false)} – this is the data that is used to determine the progress bar. In this example, I’m using the number of checked boxes and the number of unchecked boxes.
    • "charttype","bar" – determines the type of chart to use. In our example, we are using a stacked bar chart
    • "max",COUNTA(B3:B) – refers to the max number of checkboxes
    • "color1","#1aa260"; "color2", "#D3D3D3" – this sets the color of the bar. In this example, because I am using 2 data (number of checked boxes, number of unchecked boxes), I can set 2 different colors for each of this data.