Dynamic Checklist in Excel: Build One That Updates

How-to basics · Updated November 22, 2026

To make a dynamic checklist in Excel, put task names and completion checkboxes in an Excel table, then use a spill formula to show unfinished tasks automatically. With a table named Tasks and columns named Task and Done, enter this formula outside the table: =FILTER(Tasks[Task],(Tasks[Task]<>"")*(Tasks[Done]<>TRUE),"All tasks complete"). The list updates as you add tasks or check them off.

What makes an Excel checklist dynamic?

A basic checklist is a fixed range of items with a box beside each one. A dynamic checklist adjusts when its contents or completion status changes. For example, you might keep a complete list of project tasks in one place and have Excel display only the ones that remain unfinished. Add a new task to the source list, and it can appear in the active list without rebuilding the formula.

“Dynamic” can mean a few related things: the task table grows as you add rows, a progress figure recalculates when boxes are checked, or a separate view filters out completed work. These features work together, but they are not identical. A checkbox records a status; the table helps manage changing data; and a formula such as FILTER creates an automatically updated view.

This approach is useful for recurring processes, event planning, classroom tasks, project handoffs, and household lists. It is also easier to maintain than copying a separate checklist for every new project. The key is to keep the source data in a consistent layout instead of building the list from scattered cells.

Set up the task table

In Excel for Microsoft 365 on Windows, start with a sheet containing two headers: Task and Done. Enter one task per row beneath Task, leaving the Done cells ready for checkboxes. Select a cell in the range and choose Insert > Table, or press Ctrl+T, then confirm that the table has headers. On a Mac, Command+T opens the table-creation dialog. On Excel for the web, use Insert > Table.

Give the table a meaningful name so formulas are readable. Select a table cell, open the Table Design tab, and change the Table Name field to Tasks. Avoid spaces in the table name. Excel tables automatically extend when you type or paste data into the row immediately beneath them, and formulas that refer to table columns adjust to include added rows.

Keep one task per row and use the same kind of value for each status. Do not insert blank rows inside the list or merge cells in the table; either can make sorting, filtering, and later editing less predictable. If you need extra information, add columns such as Owner, Due date, or Category, rather than putting several details into the task name.

Add checkboxes or a status dropdown

In current Microsoft 365 versions that include in-cell checkboxes, select the cells in the Done column and choose Insert > Checkbox. A checked box has the value TRUE and an unchecked box has the value FALSE, which lets formulas count or filter the status. The checkbox command may not appear in older perpetual-license editions or some update channels. If it is unavailable, use a dropdown instead: select the status cells, choose Data > Data Validation, set Allow to List, and enter values such as Not started,Done in Source. Regional settings can affect the list separator.

A dropdown is a practical alternative when the checklist needs more than two states, such as Not started, In progress, and Done. The trade-off is that formulas must look for the text value Done, rather than TRUE. Checkboxes are quicker to scan and click, while dropdowns communicate a richer workflow. Choose one status method and use it consistently.

If people will add rows over time, check that a new row includes a usable status control. Table expansion adds the row to structured references, but depending on how the checkbox was inserted and the Excel version, the new cell may not automatically display a checkbox. Copy one from the row above or select the new status cells and use Insert > Checkbox. You can also prepare extra rows in advance.

Show only unfinished tasks

With task names in the Tasks[Task] table column and checkbox values in Tasks[Done], put the following formula in an empty cell outside the table. FILTER is available in Microsoft 365 and Excel 2021 or later. It returns a spill range, so leave the cells below the formula clear for the results.

=FILTER(Tasks[Task],(Tasks[Task]<>"")*(Tasks[Done]<>TRUE),"All tasks complete")

The first condition excludes empty task names. The second keeps any row that is not marked TRUE, including unchecked boxes. The asterisk combines the two conditions as an AND test. If there are no unfinished tasks, the formula displays All tasks complete instead of an error. When you add a task to the table or check off a task, the result recalculates. If Excel reports a spill error, look for existing text, merged cells, or another obstruction in the formula’s output area.

For a dropdown with values Not started and Done, assume those exact entries are in Tasks[Status] and task names are in Tasks[Task]. Use this formula to list tasks that are not done:

=FILTER(Tasks[Task],(Tasks[Task]<>"")*(Tasks[Status]<>"Done"),"All tasks complete")

In both examples, the formula creates a view; it does not move, delete, or alter the source tasks. Keep the source table as the place where people enter and update work. That separation helps prevent an accidental edit to a filtered result from being mistaken for a change to the original list.

Calculate completion and progress

A progress percentage gives a quick summary alongside the task list. Assume the table is named Tasks, with task names in Tasks[Task] and checkbox values in Tasks[Done]. In a cell outside the table, enter:

=IFERROR(COUNTIFS(Tasks[Task],"<>",Tasks[Done],TRUE)/COUNTA(Tasks[Task]),0)

Format the result as a percentage using Home > Number and the Percent Style button. For example, if the table contains five nonblank tasks and two are checked, the result is 2 divided by 5, or 40%. The formula returns 0% if the table has no task names, rather than showing a division error. If the list includes intentionally excluded items, decide whether they belong in the denominator; otherwise, the percentage may not match the progress people expect.

For a dropdown workflow, use Tasks[Status] instead of the checkbox column. Assuming task names are in Tasks[Task] and statuses are in Tasks[Status], this counts completed tasks as a share of all named tasks:

=IFERROR(COUNTIFS(Tasks[Task],"<>",Tasks[Status],"Done")/COUNTA(Tasks[Task]),0)

You can add visual cues with Home > Conditional Formatting, such as a data bar for the percentage or a rule that changes the appearance of completed rows. For a formula-based row rule, assume task names begin in A2 and checkbox values are in B2, with the rule applied to the task rows. The formula is =$B2=TRUE. Use Home > Conditional Formatting > New Rule, choose the formula-based rule option, and set the format. The dollar sign fixes the status column while the row number adjusts for each row.

Make the checklist easier to maintain

Keep the entry table and the unfinished-task view in separate areas or on separate sheets. A useful layout is a Tasks sheet for the table and a Today or Open items sheet for the FILTER results and progress percentage. This gives users a clear place to update the checklist and a clean view for reviewing work. Do not put a spill formula inside the source table; spilled arrays cannot expand within a table.

  • Use clear, unique task wording. “Confirm venue access” is easier to verify than “Venue.”
  • Add an owner or due date only when it helps. Extra columns are useful for coordination, but unnecessary fields add maintenance.
  • Sort and filter the source table when needed. Table headers provide filter controls without changing the underlying records.
  • Protect formulas if many people edit the file. Worksheet protection can help prevent accidental edits, but it is not a substitute for access control or a secure backup.
  • Test with a small example. Add a task, check it, and confirm both the unfinished list and percentage respond as expected.

When a checklist is shared, explain which cells are meant to be edited and whether checked tasks should remain visible in the source table. A dynamic unfinished list is a view, not an archive. If completed work matters for reporting, keep it in the source table or move it through a deliberate process rather than deleting it just to shorten the current view.

Common problems and their fixes

If the formula shows #NAME?, check that the table name and column headers match the formula exactly, and confirm that your Excel edition supports FILTER. Structured references are not ordinary cell ranges: Tasks[Task] means the whole Task column in the table named Tasks. If your table still has its default name, either rename it or update the formula to use the actual name.

If you see #SPILL!, clear cells below and beside the formula where results need to appear. A spill range cannot overwrite existing content, and merged cells can also block it. If the formula returns no task names even though some boxes are unchecked, verify that unchecked boxes are actually FALSE and that the formula points to the correct status column. A text dropdown containing No will not behave like a checkbox value unless the formula checks for that text.

Some installations use semicolons rather than commas between formula arguments because of regional settings. If a correctly copied formula produces a syntax error, use the separator your Excel installation expects. Do not replace the comma inside a text string or change the formula’s logic. Also check for extra spaces in dropdown entries: Done and Done are different text values.

When to use another approach

A table plus checkboxes and FILTER is a good fit for a compact checklist that needs a live list of open items. For a checklist with approvals, dependencies, notifications, or multiple people changing the same record, a spreadsheet may become difficult to coordinate. A shared task-management service or a structured Microsoft Lists setup may offer clearer ownership and activity history. Choose based on the workflow and collaboration requirements, not simply on the number of checkboxes.

If FILTER is not available, the table can still function as a checklist: turn on its header filters and filter the status column to show unchecked items. You can also use a normal range with formulas, but you will need to adjust the referenced ranges as the list grows. For basic completion tracking, a status dropdown or checkboxes plus Excel’s built-in table filtering may be enough; a separate dynamic formula view is optional, not a requirement.

Frequently Asked Questions

Can an Excel checklist add new tasks automatically?
Yes, if the task list is an Excel table, adding a task in the next table row expands the table and its structured references. A FILTER formula that refers to the table can then include the new task. Make sure the new row also has a checkbox or the appropriate dropdown status.

Can Excel show only unchecked items?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER can return only rows whose checkbox value is not TRUE. Alternatively, use the table’s built-in filter on the status column. The formula approach creates a separate live view, while the table filter temporarily changes what is visible in the source list.

Do Excel checkboxes contain TRUE or FALSE?
In current in-cell checkbox versions, a checked box evaluates to TRUE and an unchecked box to FALSE. This makes them usable in formulas such as COUNTIFS and FILTER. If a checkbox feature is unavailable, a dropdown can provide text statuses instead, but the formulas must test those text values.

Why does my dynamic checklist show a spill error?
A spill error usually means Excel cannot place all returned items in the cells below the formula. Clear obstructing contents, remove merged cells from the output area, and keep the formula outside an Excel table. The source task table can remain a table; it is the formula’s output range that needs room to expand.

Can I build this in older Excel without FILTER?
Yes. Use an Excel table and filter its status column to show unchecked items, or use a fixed-range formula approach that suits the older version. The table remains useful even without dynamic arrays, but a separate list that automatically spills only unfinished tasks requires a compatible function or another method.

Related

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.