How to filter data based on checkbox in Excel?
Working with lists containing checkboxes in Excel is common, especially when managing tasks, attendance, or survey data. Filtering data based on whether checkboxes are checked (selected) or unchecked (unselected) can be essential for quickly reviewing only completed items, pending tasks, or filtered subsets according to your workflow requirements. However, since checkboxes are form controls and do not store values directly in cells, they cannot be filtered like typical cell content unless properly linked or processed. This article provides practical, step-by-step methods—including manual linking and automation using VBA code—to filter data based on checkbox status, helping you efficiently manage and analyze your checklist data.
Filter data based on checkbox with linking the checkbox to corresponding cell
VBA code – Automate linking checkboxes to cells and filtering based on checkbox state
Filter data based on checkbox with linking the checkbox to corresponding cell
One straightforward way to filter data by checkbox is to manually link each checkbox to its own cell. This ensures every change in the checkbox status instantly reflects as "TRUE" or "FALSE" in the linked cell, which standard Excel filters can then recognize and process. This approach is especially suitable for relatively small checklists or when you have control over the worksheet’s structure.
1. Right-click one of the checkboxes you wish to filter, and then choose Format Control from the context menu. This opens the configuration options specific to the selected checkbox. See screenshot:
2. In the Format Control dialog box, switch to the Control tab. In the Cell link box, type or select the address of the cell that you want to link this checkbox to, then click OK to confirm. For instance, linking to cell B2 means that B2 will show whether the checkbox is checked or not.
In this example, after entering cell B2 into the Cell link field, this checkbox will automatically update B2 to "TRUE" when checked, or "FALSE" when unchecked.
3. Repeat the above process for all checkboxes whose statuses you wish to filter, making sure each one is linked to its corresponding cell in the same row. This is important—if multiple checkboxes share a linked cell, only the last changed box will take effect.
4. Once linked, changing the state of any checkbox will instantly update the corresponding cell to display “TRUE” if it is selected or “FALSE” if it is cleared. This dynamic link makes it easy for Excel's filter function to identify the checkbox state. See screenshot:
5. Now, click on the header cell above the linked cells (for example, cell B1 if B2:B10 contains linked values) and go to Data > Filter. This will add the dropdown filter arrows on your header row.
6. To filter for checked checkboxes, open the dropdown filter in the linked cell column and select only TRUE. To show only unchecked checkboxes, filter for FALSE. Here’s what the filter selection looks like:
Applicable Scenarios & Tips: This method is practical when dealing with manageable lists or when setting up a new tracking worksheet. However, it can be time-consuming if you have a large number of checkboxes to link individually. Ensure that you do not accidentally link multiple checkboxes to a single cell, as this can cause incorrect filtering results. Also, when deleting rows or reorganizing your data, always verify that the checkbox-to-cell link remains valid, as moving or sorting cells may break these links.
Troubleshooting: If the filter is not showing your data as expected, double-check that all checkboxes are properly linked to the intended cells and that the linked cells are correctly referenced in the filter. If the checkboxes do not reflect the proper TRUE/FALSE status, you may need to re-link them by repeating the above steps.
If you want to insert multiple checkboxes across a selected range at once, you can utilize the Batch Insert Check Boxes feature of Kutools for Excel. For inserting multiple Option Buttons in bulk, use the Batch Insert Option Buttons utility. You can also quickly remove all checkboxes from a worksheet with the Batch Delete Check Boxes feature. These tools simplify large-scale checkbox management, significantly reducing manual effort and helping maintain data integrity, especially useful for large lists or template preparation. See below for an example:
Download and try it now! (30-day free trial)
VBA code – Automate linking checkboxes to cells and filtering based on checkbox state
For large worksheets containing dozens or even hundreds of checkboxes, manually linking each one can be tedious and time-consuming. In such cases, using VBA code is a smart solution, as it automates the process of associating every checkbox with its corresponding cell and even allows for quick filtering of data based on whether a checkbox is checked or unchecked. This approach is particularly beneficial when managing dynamic lists or maintaining regularly updated checklists.
1. Press Alt + F11 to open the Visual Basic for Applications editor. In the VBA editor, click Insert > Module to add a new code module. Then paste the following code into the module window:
Sub LinkAllCheckboxesToCells()
Dim ws As Worksheet
Dim chk As CheckBox
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
i = 2 ' Start linking from row 2 (adjust as needed)
For Each chk In ws.CheckBoxes
chk.LinkedCell = ws.Cells(i, 2).Address(False, False) ' Links to column B
i = i + 1
Next chk
End SubTip: This code automatically assigns each checkbox on the active sheet to a corresponding cell in column B, starting from row2. You can edit i =2 and Cells(i,2) to match your actual starting row and linked column.
2. Click the 
Run button or press F5 to execute the macro. All checkboxes will become linked to their respective adjacent cells, and checking or unchecking any box will update the linked cell to display TRUE or FALSE.
3. With all checkboxes now linked, apply the standard Excel filter to the linked cells column to filter data based on the checkbox status, just as described in the previous method.
Tips:
- If checkboxes are not linking correctly, confirm that the number of checkboxes matches the number of data rows you have. If you have a header row or a different starting point, adjust
i =2accordingly. - If some of your checkboxes need to be linked to different columns or rows, adjust the
Cells(i, X)parameter where X is the column number. - Always save your work before running VBA scripts. If accidental changes occur, you can revert by reopening your last saved version.
Advantages: This method significantly speeds up the setup process, improves accuracy, and helps prevent the errors that can occur when linking checkboxes one by one. It is ideal for situations where your worksheet contains a high volume of checkboxes or is subject to frequent updates.
Limitations: To use VBA, you need to save your workbook as a macro-enabled file (.xlsm) and users may need to enable macros for full functionality. Always back up your data before running VBA scripts and pay close attention to the worksheet structure when making changes.
This VBA approach is best suited for users comfortable with macros and scripting or for cases where repetitive manual linking would otherwise be impractical.
Related articles:
- How to filter data include blank rows in Excel?
- How to filter a worksheet based on a range of cells in another worksheet in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in