How to Calculate Progress in Excel Using Check Boxes

You can create an easy-to-understand Excel worksheet to track KPI progress toward company goals or daily task completion by using Excel check boxes and the KPI and COUNTIF functions.

How to Use Excel Check Boxes

Excel check boxes are one of Excel’s easy-to-use features. We will look at two methods: using standard Excel and using free Excel.

Standard Excel Check Boxes (Progress)

We will create a table like the one below to indicate whether daily tasks have been completed.

There are two main ways to track progress.

1. Record completion by using “O” and “X.” (The first method in the image below.)
2. Use Excel check boxes to easily record completion. (The second method in the image below.)
   

How to Automatically Link Excel Check Boxes to Cells

We will look at how to enable the Excel Developer tab, insert a check box into a cell, assign a value to a check box, and automatically assign check box values using a macro.

How to Enable the Excel Developer Tab

To place a check box in an Excel cell, first enable the “Developer” tab.

How to Add Excel Features

There are several ways to add the Developer tab to the Excel ribbon so you can use the check box feature.

1. Right-click an empty area at the top of Excel and select [Customize Ribbon].
2. Select [File], [Options], and [Customize Ribbon].
3. Click the three-dot […] menu at the top of the ribbon and select [Customize Ribbon].

Next, let’s look at how MacBook Excel users can add the Excel menu.

First, click the three-dot […] menu. Then click [more commands…] on the screen below.

The [Ribbon] and [Quick Access Toolbar] options are enabled as shown below. Select [Ribbon] to add the main menu.

Select Developer under [Main Tabs] to use Excel check boxes.

How to Insert a Check Box in an Excel Cell

Select the Developer tab, then select Check Box. Left-click where you want to place it to create a check box as shown below.

To insert the check box into an Excel cell, delete unnecessary text and resize it with the mouse so it fits inside the cell.

After placing a check box in a cell, copy and paste that cell to extend the check boxes as needed.

How to Calculate Progress in Excel

The key to calculating progress in Excel is to “link check boxes to cells.” There are two methods: manually linking each check box to an Excel cell or using an Excel macro.

Link Excel Check Boxes to Cells (Manual)

To calculate progress, you need to link the displayed check box to a cell to return a value. Right-click the check box as shown below and select [Format Control].

Enter the desired Excel cell so that a value is returned based on whether the check box is selected.

After entering the cell location to link to the check box, the linked cell displays [TRUE or FALSE] depending on the check box selection.

To improve the readability of the Excel progress data, set the font color to match the cell background color.

Repeat the same process to link each additional check box to a cell.

Once all check boxes are linked to cells, you can check progress based on the selected check boxes as shown below.

Another important feature for calculating progress in Excel is the [COUNTIFS] function.

The Excel formula used is as follows.

L5 Cell =COUNTIF(C5:K5,TRUE)/COUNTA(C5:K5)

Link Excel Check Boxes to Cells (Excel Macro)

Above, we looked at manually linking check boxes to cells. This time, we will look at a method that uses Excel macros for situations where manually linking them as shown below would be less productive.

First, change the formula in [L column] to the following.

L5 Cell =COUNTIF(C5:K5,TRUE)/9

In the formula above, 9 is the number of check boxes.

Select the following option to use Excel macros.

When setting an Excel macro name, enter the name without spaces.

Enter the following in Excel VBA.

Using the Excel VBA statements below automatically links the check boxes to Excel cells.

Sub auto()
Dim chk As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each chk In Ws.CheckBoxes
With chk
.LinkedCell = _
.TopLeftCell.Address
End With
Next chk
End Sub

After setting up the Excel macro, use the Developer tab button to insert a button that runs the macro.

Assign the macro [auto] created above to the button you insert.

Insert and run the macro button as shown below. All check boxes are automatically linked to their respective cells, allowing you to calculate progress for all columns.

Using Free Excel (Linking Check Boxes to Cells)

Use [Google Sheets], a way to use Excel for free, to automatically link check boxes to cells.

With Google Sheets, none of the steps above are necessary.

This is because when you insert a check box, it automatically fits the cell and is automatically linked to that cell.

Let’s look at how to use it.

On the Google Sheets Insert tab, select Check box.

Insert and select check boxes in the desired cells as shown below to view Excel progress as follows.

Conclusion

With advances in AI, including ChatGPT, Google Gemini, and DeepSeek, there are now many ways to use programs even without knowing all their features. However, companies still cannot use AI technology 100%, and analysis and visualization can vary based on individual capabilities, so I believe studying is necessary.