How to find Excel progress rate, use checkbox (selection box)

To achieve company goals using Excel KPI If you need to calculate the progress or percentage of daily work performance, use the Excel checkbox and COUNTIF Functions allow you to create highly visible progress tracking files.

Let's learn how to get the progress rate in Excel by using checkbox cell connection, countifs, and counta functions.

CONTENTS

How to use Excel checkboxes

Excel checkbox is one of the easy-to-use Excel functions. Let's look at two ways: using basic Excel and using free Excel.

Basic Excel checkbox (progress rate)

I'm trying to create a table that displays whether daily tasks have been accomplished, as shown below.

This is an example of using the Excel checkbox.

There are two main ways to check progress.

This is a way to record achievement using “O” and “X”. (First method in the photo below)
This is an easy way to record achievement using Excel check boxes. (Second method in the photo below)

There are two ways to display progress in Excel.

How to automatically connect cells with checkbox in Excel

Let's learn how to set up the Excel development tool, how to insert a checkbox into a cell, how to specify the value of the checkbox, and how to automatically specify the value of the checkbox using a macro.

How to set up Excel development tools

To insert a checkbox into an Excel cell, you must first activate “Development Tools = Developer”.

This is how to add Excel development tool functions.

How to add Excel functions

There are several ways to add development tools to the Excel ribbon menu to use the checkbox function in Excel.

You can place the mouse cursor in an empty space at the top of Excel, right-click, and select [Customize Ribbon Menu].
You can select [File], [Options], and [Customize Ribbon].
[…] ] You can click the top of the ribbon menu with three dots and select [Customize Ribbon Menu].

This time, we will learn how to add Excel menus for MacBook Excel users.

first of all [… ] Click on the three dots. Afterwards, on the screen below, [more commands…] ] Click .

This is how to add the main function to Excel on MacBook.

As shown below, the functions of [Ribbon] and [Quick Access Toolbar] are activated, and select [Ribbon] to add the main menu.

This is the MacBook Excel ribbon menu and quick function settings screen.

If you select development tools in the [Main tabs], you can use the Excel checkbox.

Select Development Tools from the Excel main tab.

How to insert a checkbox into an Excel cell

Select Excel development tool and check the checkbox. Afterwards, if you left-click on the desired location, a check box will be created as shown below.

To insert a checkbox into an Excel cell, delete unnecessary text and then resize it with the mouse so that the checkbox can fit inside the cell.

Use the mouse to resize the checkbox and insert it into the cell.

You can expand the checkbox as much as you want by placing a checkbox in a cell and then copying and pasting the cell.

After inserting a checkbox into a cell, use copy and paste to enlarge the checkbox as much as you want.

How to find progress rate in Excel

The key to calculating progress in Excel is to “link checkboxes to cells.” There are two ways to do this: manually linking each checkbox to an Excel cell and using an Excel macro.

Excel checkbox cell connection (manual)

To calculate the progress rate, you must connect the displayed boxes and cells to derive the value. Right-click the checkbox as shown below and select [Format control].

This is how to manually link an Excel checkbox to a cell.

Enter the desired Excel cell so that the value appears according to the checkbox selection.

Connect the checkboxes by entering the location of the desired cell.

If you enter the location of the cell to connect to the checkbox as shown above, the value is connected as [TRUR or FALSE] depending on the cell selection as shown below.

The cell value connected to the checkbox is divided into true and false.

To increase the visibility of Excel progress data, set the font color to be the same as the cell's background color.

Unify colors to hide unnecessary values.

Afterwards, repeat the above process to connect the next checkbox to the cell.

To manually associate checkboxes with cells, set each of the following checkboxes.

If you connect all checkboxes to cells, you can check the progress rate based on the boxes shown below.

Excel progress rate was automatically calculated using check boxes.

This is how to use checkboxes connected to Excel cells.

Another important function to calculate Excel progress rate is [COUNTIFS] This is the use of functions.

The Excel function formula used is as follows.

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

The Excel progress rate formula is a formula that utilizes the countifs and counta functions.

Excel checkbox cell connection (Excel macro)

Above, we learned how to manually connect checkboxes and cells. This time, we will look at how to use the Excel macro function in cases where it is not productive to connect manually as shown below.

First, change [L column] to the formula below.

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

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

Let's check how to connect multiple Excel checkbox cells.

To use Excel macros, select the options below.

You can use the macro function in the Excel View tab.

The way to set an Excel macro name is to set the name without spaces.

If there is a space in the macro name, setting is not possible.

Excel VBA Enter the following:

This is an Excel checkbox cell auto-connection macro.

If you use the Excel VBA statement below, the checkbox and Excel cell are automatically connected.

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

Once the Excel macro settings are complete, insert a button to run the macro using the button in the development tool.

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

If you insert and run the macro execution button as shown below, all checkboxes will be automatically connected to each cell, allowing you to calculate the progress rate of all columns.

Using an Excel macro, many checkboxes were connected to each cell at once.

Use free Excel (link checkbox cells)

How to use Excel for free [Google Sheets] Use to automatically connect check boxes and Excel cells.

If you use Google Sheets, all of the above steps are not necessary.

The reason is that when a checkbox is inserted, it is automatically aligned to the cell, and the checkbox is automatically set to be connected to the cell.

Let’s find out how to use it.

Select the checkbox in the Insert tab of Google Sheets.

If you insert and mark the checkbox in the desired cell as shown below, you can check the Excel progress rate as shown below.

conclusion

If you want to use the above functions based on big data, we recommend using Excel Office. If you want to check only the progress rate without big data, we recommend using Google Sheets.