Insert Checkboxes in Excel with xlsxwriter

A checklist is one of the most common things people build in Excel: month-end close steps, audit sign-offs, onboarding tasks, a review column in a list of exceptions. For years the options were a TRUE/FALSE dropdown or a fiddly form control floating over the cell. Microsoft 365 now has cell checkboxes — a boolean value in a cell, displayed as a box the reader can tick — and xlsxwriter can create them with insert_checkbox. This page, part of Building Excel Reports with xlsxwriter, builds a month-end close checklist with checkboxes, a progress figure and finished rows struck through, and covers what happens in programs that do not support the feature.

Prerequisites

pip install "xlsxwriter>=3.2.1" pandas openpyxl

close_steps.xlsx lists six month-end close steps with an owner, a due date and whether each is done. Displaying checkboxes needs a current Microsoft 365 version of Excel; everywhere else the same cells show TRUE and FALSE.

Step 1 — Insert a checkbox

A checkbox is a boolean cell with a special format. insert_checkbox(row, col, value) writes the boolean and applies that format in one call:

import xlsxwriter

book = xlsxwriter.Workbook("checkbox_basics.xlsx")
sheet = book.add_worksheet("Tasks")
sheet.write_row(0, 0, ["Task", "Done"], book.add_format({"bold": True}))
sheet.write(1, 0, "Send invoices");   sheet.insert_checkbox(1, 1, True)
sheet.write(2, 0, "Chase INV-2150");  sheet.insert_checkbox(2, 1, False)
sheet.set_column(0, 0, 20)
book.close()

from openpyxl import load_workbook
print([(c.value, c.data_type) for c in load_workbook("checkbox_basics.xlsx").active["B"][1:]])

The cells hold True and False with type b, boolean. Clicking a box in Excel flips the value, and anything that reads the cell — a formula, pandas, openpyxl — sees a plain boolean. Rows and columns are zero-based, like every xlsxwriter method; insert_checkbox("B2", True) with an A1 reference works too.

Step 2 — Write the checklist from a DataFrame

pandas writes a boolean column as TRUE/FALSE cells. Write the table with pandas, then overwrite the Done column with checkboxes through the same xlsxwriter worksheet:

import pandas as pd

steps = pd.read_excel("close_steps.xlsx")
done_col = steps.columns.get_loc("Done")
with pd.ExcelWriter("close_checklist.xlsx", engine="xlsxwriter", datetime_format="dd mmm") as writer:
    steps.to_excel(writer, sheet_name="Close", index=False, startrow=2)
    ws = writer.sheets["Close"]
    for i, done in enumerate(steps["Done"], start=3):
        ws.insert_checkbox(i, done_col, bool(done))
    ws.set_column("A:A", 24)
    ws.set_column("B:C", 10)
    ws.set_column("D:D", 7)
    ws.write("A1", "Month-end close — September 2026", writer.book.add_format({"bold": True, "font_size": 14}))
print(steps["Done"].sum(), "of", len(steps), "done")

bool(done) converts NumPy booleans to Python ones, which xlsxwriter expects. Starting the table at row 3 leaves room for a title and the progress line added next. The owners can now tick their steps directly in the shared workbook.

Step 3 — Show progress with formulas

A ticked box is TRUE, so COUNTIF(range, TRUE) counts them. xlsxwriter cannot calculate formulas and stores 0 as their result unless you pass one; pass the value you already know from pandas, so viewers that do not recalculate — previews, pandas, openpyxl — see the right figure:

first, last = 4, 3 + len(steps)
done_n = int(steps["Done"].sum())
with pd.ExcelWriter("close_checklist.xlsx", engine="xlsxwriter", datetime_format="dd mmm") as writer:
    steps.to_excel(writer, sheet_name="Close", index=False, startrow=2)
    wb, ws = writer.book, writer.sheets["Close"]
    for i, done in enumerate(steps["Done"], start=3):
        ws.insert_checkbox(i, done_col, bool(done))
    ws.write("A1", "Month-end close — September 2026", wb.add_format({"bold": True, "font_size": 14}))
    ws.write_formula("A2", f'=COUNTIF(D{first}:D{last},TRUE)&" of "&ROWS(D{first}:D{last})&" steps done"',
                     None, f"{done_n} of {len(steps)} steps done")
    ws.write_formula("C2", f"=COUNTIF(D{first}:D{last},TRUE)/ROWS(D{first}:D{last})",
                     wb.add_format({"num_format": "0%", "bold": True}), done_n / len(steps))
    ws.set_column("A:A", 24); ws.set_column("B:C", 10); ws.set_column("D:D", 7)
    ws.conditional_format(f"A{first}:D{last}", {"type": "formula", "criteria": f"=$D{first}=TRUE",
                          "format": wb.add_format({"font_strikeout": True, "font_color": "#808080"})})

print(load_workbook("close_checklist.xlsx").active["A2"].value)
print(pd.read_excel("close_checklist.xlsx", header=None, nrows=2).iloc[1, [0, 2]].tolist())

openpyxl shows the formula; pandas reads the stored results — "3 of 6 steps done" and 0.5 — because they were written with the formula. When someone ticks another box, Excel recalculates and the line updates to "4 of 6". The conditional format uses a formula anchored on column D, =$D4=TRUE, so the whole row of a finished step turns grey and struck through; unticking the box restores it.

Step 4 — Read the ticks back

The point of a checklist is usually to collect the answers. Because checkboxes are plain booleans, reading them needs nothing special — pandas gives a boolean column:

back = pd.read_excel("close_checklist.xlsx", skiprows=2)
print(back[["Step", "Owner", "Done"]].to_string(index=False))
print("outstanding:", back.loc[~back["Done"], "Owner"].value_counts().to_dict())

The outstanding steps group by owner, ready for a reminder email. When collecting from many copies of the same checklist, read each one this way and concatenate; Consolidate Timesheets from Many Workbooks shows the pattern.

Let people tick boxes and nothing else

A shared checklist works best when the ticks are the only thing people can change: the steps, owners and formulas stay as they were written. Pass an unlocked format as the fourth argument of insert_checkbox and protect the sheet — every other cell is locked by default:

book = xlsxwriter.Workbook("close_protected.xlsx")
ws = book.add_worksheet("Close")
unlocked = book.add_format({"locked": False})
ws.write_row(0, 0, ["Step", "Owner", "Done"], book.add_format({"bold": True}))
for i, r in enumerate(steps.itertuples(index=False), start=1):
    ws.write(i, 0, r.Step)
    ws.write(i, 1, r.Owner)
    ws.insert_checkbox(i, 2, bool(r.Done), unlocked)
ws.set_column(0, 0, 24)
ws.protect()
book.close()

sheet = load_workbook("close_protected.xlsx").active
print("sheet protected:", sheet.protection.sheet, "| C2 locked:", sheet["C2"].protection.locked, "| A2 locked:", sheet["A2"].protection.locked)

The checkbox cells remain clickable on a protected sheet, while the step names and owners cannot be edited. Protection without a password is a guard against accidents, not a security measure; Lock Cells and Protect a Sheet with openpyxl explains the difference. Ticks also drive more than counts: =SUMIFS(E4:E9, D4:D9, TRUE) totals an amount column for ticked rows only, which turns a list of invoices with an "approved" checkbox into a running approved total.

Where checkboxes do not appear

Cell checkboxes are a new part of the file format, and support varies:

Because the value is always kept, formulas and conditional formats behave the same everywhere; only the clickable box is missing. If the audience uses older Excel, add a data validation list of TRUE,FALSE to the column so people can still pick a value from a dropdown — xlsxwriter's data_validation with {"validate": "list", "source": ["TRUE", "FALSE"]} — and the checkboxes appear for those who can see them. Avoid post-processing the file with openpyxl: it reads the ticks fine, but saving drops the checkbox formatting.

A checkbox suits a genuine yes/no. When a step can be in more than two states — not started, in progress, done, not applicable — a dropdown of those words with a colour per value tells readers more than a box can, and works in every spreadsheet program; Add Dropdown Data Validation to Excel with openpyxl shows how to build one.

Common pitfalls

• An old xlsxwriter. insert_checkbox needs version 3.2.1 or later.
• NumPy booleans. Convert with bool() before passing values in.
• Formulas showing 0. Pass the known result as the value argument of write_formula.
• Relative references in the conditional format. Anchor the column ($D4) so each row checks its own box.
• Saving with openpyxl afterwards. The checkbox formatting is removed; make xlsxwriter the last step.
• Assuming everyone sees boxes. Older Excel and other programs show TRUE/FALSE; label the column clearly.

Performance notes

insert_checkbox writes one cell, so thousands of rows are no slower than writing booleans. The conditional format is a single rule over the whole range, not one per row.

Conclusion

Write checklist tables with pandas and replace the boolean column with insert_checkbox cells through the same xlsxwriter worksheet. Count ticks with COUNTIF(range, TRUE), passing the result you already know so non-recalculating readers see it, and grey out finished rows with a conditional format anchored on the checkbox column. Read the answers back as ordinary booleans, and remember that only current Microsoft 365 shows the boxes — everywhere else sees TRUE and FALSE.

Frequently asked questions

How do I add a checkbox to an Excel cell with Python? With xlsxwriter 3.2.1 or later, call worksheet.insert_checkbox(row, col, True or False). The cell stores a boolean and, in current Microsoft 365 versions of Excel, displays it as a checkbox that the reader can click.

Why do my checkboxes show as TRUE and FALSE? Cell checkboxes are a recent Microsoft 365 feature. Older Excel versions, LibreOffice and most other spreadsheet programs do not support it and show the underlying boolean instead. The value is the same, so formulas that use it still work.

How do I count ticked checkboxes in Excel? A ticked checkbox is TRUE, so =COUNTIF(D2:D7,TRUE) counts them and =COUNTIF(D2:D7,TRUE)/ROWS(D2:D7) gives the share complete. When writing the formula with xlsxwriter, pass the expected result as the value argument so readers that do not recalculate see it.

Can openpyxl create or keep checkboxes? openpyxl reads the ticks as True and False, but it does not create checkboxes, and saving a workbook with openpyxl drops the checkbox formatting, leaving TRUE and FALSE values. Create checkbox workbooks with xlsxwriter as the final step.

Related

• Up one level: Building Excel Reports with xlsxwriter — formatted reports from scratch.
• Store Formula Results with xlsxwriter — cached formula values.
• Apply Conditional Formatting with xlsxwriter — formula-based rules.
• Read Boolean Columns from Excel with pandas — reading the ticks.