Checkbox Percentage in Google Sheets: Formulas & Tips

To calculate a checkbox percentage in Google Sheets, count the checked boxes and divide by the number of items being tracked. For example, if task names are in A2:A11 and checkboxes are in B2:B11, use =IFERROR(COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11),0), then format the result as a percentage. If six of the ten tasks are checked, the result is 60%.

How checkbox percentages work

A standard Google Sheets checkbox represents a logical value: a checked box is TRUE and an unchecked box is FALSE. That makes it possible to count completed items with COUNTIF rather than counting visible check marks or typing a separate status beside every row. The percentage itself is a fraction: checked items divided by the total items in the calculation.

The formula returns a decimal value internally. A result of 0.6 means 60 percent; applying percentage formatting changes how Sheets displays the value, not the underlying calculation. This distinction matters if the result will be used in another formula or chart. Usually, do not multiply the formula by 100: doing that and then applying percentage formatting would display 6,000% instead of 60%.

The denominator is a choice, not a detail to ignore. A list of ten planned tasks has a denominator of ten even when only six are complete. But if the sheet contains unused rows, counting the whole column can make the completion figure misleading. Choose a range or a count of task labels that reflects the actual work being measured.

Set up the checkbox list

In a browser, select the cells where the checkboxes should appear, then choose Insert > Checkbox. For a simple task tracker, put task names in column A and select B2:B11 before inserting the boxes. Checking or clearing a box updates its TRUE or FALSE value, and formulas that refer to those cells recalculate automatically.

A useful layout separates the item being tracked from its status:

Column A: one task, name, or other item per row.
Column B: one checkbox per item.
A separate summary cell: the completion formula, outside the rows it counts.

Keep the summary cell outside the checkbox range. If the formula is placed inside the range it counts, the total can include itself or create a circular reference. Also make sure each item has one corresponding checkbox; mismatched ranges can produce a percentage that looks plausible but describes the wrong set of rows.

Choose the right formula and denominator

With task names in A2:A11 and their checkboxes in B2:B11, this formula divides the count of checked boxes by the number of nonempty task-name cells: =IFERROR(COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11),0). If all ten task-name cells are filled and six boxes are checked, it returns 0.6, which displays as 60% after percentage formatting. IFERROR displays zero if there are no task names, rather than showing a division error.

This approach is useful when the list may have blank rows within it: only rows with a task name contribute to the denominator. It assumes that every task-name cell in the range represents one item that should count. If a heading or note is inside A2:A11, COUNTA counts it too, so keep headings outside the data range or adjust the formula range.

If the checkbox range itself is a fixed set in which every cell is meant to represent an item, divide by the number of cells instead. With checkboxes in B2:B11, use =COUNTIF(B2:B11,TRUE)/ROWS(B2:B11). Six checked boxes out of ten cells returns 0.6, or 60% when formatted as a percentage. This version treats blank cells in the range as part of the total, so it is best when all ten positions are intended to count.

For a list that grows over time, consider a defined working range that includes the expected rows, or adjust the formula when adding items. A whole-column formula can be convenient, but it may include stray values or make the denominator difficult to reason about. The right method is the one whose numerator and denominator match the real question: “What share of the items currently on this list is complete?”

Display the result as a percentage

After entering the formula, select its cell and choose Format > Number > Percent. The value 0.6 then appears as 60%. You can also use the percentage-format button in the toolbar. To show more or fewer decimal places, use the toolbar controls for increasing or decreasing decimal places.

Rounding the display does not necessarily round the stored value. A result such as 2/3 may display as 67% with no decimal places while Sheets retains the more precise fraction for later calculations. If a report requires a particular display precision, set the number of decimal places rather than changing the formula just to make the visible number look rounded.

For an empty list, decide what a zero should mean. The IFERROR version above displays 0% when there are no task names. That can be convenient in a dashboard, but it may be clearer to show a blank or a label such as “No items” when zero might be mistaken for a real completion result. A formula can only make that distinction if the sheet’s layout and rules define what counts as an empty list.

Use custom checkbox values

Google Sheets also lets a checkbox use custom values instead of the default TRUE and FALSE. To configure one, select the cells and choose Data > Data validation; in the rule panel, set the criteria to Checkbox and enable custom cell values if that option is available in the panel. Enter the values you want used for checked and unchecked states, then save the rule. The precise labels depend on what the sheet needs, such as a status word for another workflow.

When checkboxes use custom values, a formula looking for TRUE will not count them if the checked value is different. Change the COUNTIF criterion to match the actual checked value. For example, if the checked state stores the text Done, count Done values rather than TRUE. The denominator still needs to represent the full set of items, and the labels should be applied consistently throughout the range.

Custom values can make a sheet easier for people to interpret, but they are not necessary for calculating a percentage. Default TRUE/FALSE values are simple and work well with logical formulas. Before changing an existing tracker, check whether other formulas, filters, or connected processes expect the original checkbox values.

Build a useful progress summary

A percentage is most informative when readers can see what it measures. Label the summary cell, for example “Tasks complete,” and make the denominator clear with a nearby count such as “6 of 10.” Two lists can both show 60% while representing very different amounts of work; the count gives the percentage context. If the task list changes frequently, ensure that the formula’s denominator changes in step with it.

You can make separate completion percentages for distinct groups by giving each group its own checkbox range and formula. For instance, a project sheet might have sections for planning, review, and delivery. Keep each formula limited to the rows in its section, and avoid including section titles or explanatory notes in the count. This produces summaries that answer specific questions instead of one broad figure that hides differences.

For a shared sheet, consider protecting formula cells from accidental edits while leaving checkbox cells available to collaborators. Google Sheets permissions and protected ranges can help control who may edit parts of a sheet, but protection is not a substitute for choosing the correct sharing permissions. A percentage formula itself does not verify that a person completed a task; it only reports the values recorded in the checkbox cells.

Common problems and how to fix them

The result is a decimal. The formula is probably working. Select the result and use Format > Number > Percent.
The result is 0% even though boxes look checked. Confirm that the formula points to the checkbox cells and that the checked value is TRUE. If the boxes use custom values, count the custom checked value instead.
The percentage is too low. Check whether the denominator includes blank rows, headings, notes, or unused checkbox cells. Narrow the range or count only the task labels that should qualify.
The percentage is too high. Make sure the formula does not count a summary cell, a duplicate row, or checkboxes outside the intended group.
The formula shows a division error. The denominator may be empty or zero. IFERROR can display a chosen fallback, but it is still worth checking why there are no qualifying items.

Also check that boxes and labels stay aligned after sorting or inserting rows. If someone sorts only one column, task names can become separated from their checkboxes, leaving the formula mathematically correct but the underlying records wrong. Sort the full table range so each task and its status move together.

Google Sheets compared with Excel

The basic idea is the same in Excel and Google Sheets: count checked logical values and divide by the intended total. However, this answer uses Google Sheets in a web browser, where checkboxes can be added through Insert > Checkbox and the formula uses the cell values created by those checkboxes. Excel’s checkbox options and insertion workflow vary by version and by whether the sheet uses in-cell checkboxes or form controls. A form-control checkbox may need to be linked to a cell before a formula can count its state.

Do not assume that a copied checkbox design will behave identically in both applications. When moving a tracker between Sheets and Excel, check whether the boxes remain interactive, whether their values are stored in the cells the formula references, and whether custom values are preserved. The displayed percentage is only dependable when the underlying checkbox values and ranges are dependable.

Frequently Asked Questions

What formula counts checked boxes in Google Sheets?
Use COUNTIF with TRUE as the criterion. For checkboxes in B2:B11, =COUNTIF(B2:B11,TRUE) returns the number that are checked. To calculate a percentage, divide that count by the appropriate number of items.

How do I calculate the percentage of checked boxes?
Divide checked boxes by the total items. With task names in A2:A11 and checkboxes in B2:B11, use =IFERROR(COUNTIF(B2:B11,TRUE)/COUNTA(A2:A11),0), then format the formula cell as Percent.

Why does my checkbox formula show 0%?
Check that the formula refers to the cells containing the boxes and that the checked value is TRUE. If you configured custom checkbox values, use the custom checked value as the COUNTIF criterion instead.

Should blank rows be included in the total?
Usually not if they are simply unused space. Count task labels with COUNTA when each nonblank label is an item, or use a fixed checkbox range when every position in that range is intended to count. The denominator should match the set you want the percentage to describe.

Can I show a checkbox percentage with decimal places?
Yes. Apply Format > Number > Percent, then adjust the displayed decimal places with the toolbar controls. For example, a fraction close to one-third can display as 33.3% without changing the underlying formula.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.