Function Problems

How to Create a Dynamic List of Unchecked Names in Excel

WPS Content Manager Sep 30, 2026 868 views

Question details

The user wants to generate a dynamically updating list of employee names that have not been checked off, with support for filtering by department.

How to Create a Dynamic List of Unchecked Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking employee tasks, attendance, or training completion using a checklist, and needing a clean summary of individuals who still need to take action.
Observed behavior: The user needs column F to dynamically display names from column B whenever their corresponding checkbox in column C remains unselected, while retaining the ability to sort or filter by department.

Before you start

Ensure that your checkboxes are individually linked to underlying cells so they output TRUE when checked and FALSE when unchecked. Your spreadsheet software must also support dynamic array functions like FILTER.

Solution 1Recommended

Use the FILTER Function to Extract Unchecked Data

Leverage the dynamic FILTER function to instantly return all rows where the checkbox value is FALSE.

The most robust way to create a self-updating list in modern Excel is using the FILTER function. It reads the TRUE/FALSE status generated by your form control checkboxes and outputs the corresponding names.

Link Checkboxes to Cells

Right-click each checkbox in column C, select 'Format Control', and set the 'Cell link' to the exact cell it sits on (e.g., C2). Unchecked boxes will now represent a FALSE value.

Organize the Data Table

Ensure your data is well-structured. For example: Column A (Department), Column B (Name), and Column C (Checked Status).

Apply the FILTER Formula

Click the first cell in column F (or your desired output area) and enter the formula: =FILTER(A2:B100, C2:C100=FALSE, "All checked").

Filter by Specific Department (Optional)

To show unchecked names for a specific department like Finance, use multiple criteria by multiplying conditions: =FILTER(B2:B100, (C2:C100=FALSE)*(A2:A100="Finance")).

Use the FILTER Function to Extract Unchecked Data

Dynamic Updates: As you click and unclick the checkboxes in column C, the generated list in column F will instantly expand or shrink without requiring manual refreshes or VBA macros.

Smart Data Processing with WPS

Create Dynamic Checklists Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides full support for dynamic array formulas like FILTER, allowing you to instantly build responsive checklists and trackers. Handle your complex data organization with a clean, user-friendly interface.

1. Insert Checkboxes: Open your file in WPS Spreadsheet, go to the 'Developer' tab, select 'Insert', and choose 'Check Box' to place next to your names.
2. Link the Checkboxes: Right-click the checkbox, choose 'Format Object', and link it to its host cell so it outputs FALSE when unchecked.
3. Enter the FILTER Formula: Select your output cell and type =FILTER(B2:B50, C2:C50=FALSE, "Complete").
4. View Dynamic Results: Press Enter. The list will automatically populate and update in real-time as you interact with the checkboxes.

100% compatible with Microsoft Excel formulas, formatting, and form controlsNative support for dynamic array functions like FILTER and SORTFree to download and use with a lightweight installationBuilt-in developer tools to easily insert and link checkboxes

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FILTER formula returning a #CALC! error?

The #CALC! error occurs when the FILTER function finds no results matching your criteria (e.g., when all checkboxes are checked). You can prevent this by adding the optional 'if_empty' argument to your formula, such as =FILTER(B2:B10, C2:C10=FALSE, "Everyone is checked").

How do I link multiple checkboxes to cells quickly?

Currently, form control checkboxes must be linked to cells one by one through the 'Format Control' menu. To speed up the process, you can link the first checkbox, copy the cell (not just the object), paste it down the column, and quickly adjust the 'Cell link' reference for each.

Can I sort the dynamically generated list of unchecked names alphabetically?

Yes. You can wrap your FILTER formula inside a SORT function. For example, use =SORT(FILTER(B2:B100, C2:C100=FALSE)) to ensure the output list of unchecked names is always alphabetized.

Excel & Data Problems