Fixing Python xlsxwriter That Skips Data Validation Rules on Protected Sheets

You spent time building a clean, validated Excel report in Python — dropdown lists, numeric range checks, the works. Then you enabled sheet protection so users can't overwrite your formulas. You open the file and every single data validation rule is gone. No dropdowns, no error alerts, nothing.

This is a well-known xlsxwriter gotcha. The fix is straightforward once you understand what's happening under the hood, but the documentation buries the detail that most people miss.

What You'll Learn

• Why xlsxwriter silently drops data validation when sheet protection is applied in the wrong order
• The correct sequence of API calls to keep both protection and validation intact
• How to unlock specific cells so users can still interact with validated inputs
• How to apply multiple validation rules across a protected sheet without conflicts
• The most common mistakes that re-introduce this bug after you think you've fixed it

The Problem in Plain Terms

xlsxwriter writes Excel XML in a specific structure. Sheet protection and data validation live in separate XML sections of the worksheet file. When you call worksheet.protect(), xlsxwriter marks the sheet as protected and locks all cells by default. The problem is that locked cells reject data validation input in Excel's model — if a cell is locked, Excel considers it off-limits for user interaction, so it suppresses the validation UI entirely.

The secondary issue is ordering: if you call protect() after add_data_validation() without also unlocking the target cells, Excel renders the sheet but quietly ignores the validation rules on those cells. No warning, no error — just missing dropdowns.

How xlsxwriter Handles Sheet Protection

When you call worksheet.protect(), xlsxwriter sets the sheetProtection XML element and marks every cell as locked by default. This default comes from the Normal cell style in Excel, which has locked=True. Any cell that carries this attribute will be read-only when protection is active.

Data validation in the OOXML spec is stored in a <dataValidations> element. Excel processes this element and decides whether to display the validation based on whether the target cells are interactive. A locked cell on a protected sheet is not interactive, so the validation is skipped at render time — the XML is still there, but Excel doesn't expose it to the user.

Why Data Validation Disappears on Protected Sheets

Here's a minimal reproduction of the broken pattern:

import xlsxwriter

workbook = xlsxwriter.Workbook("broken.xlsx")
worksheet = workbook.add_worksheet()

# Add a dropdown list to B2:B10
worksheet.data_validation(
    "B2:B10",
    {
        "validate": "list",
        "source": ["Apple", "Banana", "Cherry"],
    },
)

# Protect the sheet — this locks ALL cells, killing the validation UI
worksheet.protect()

workbook.close()

Open broken.xlsx in Excel and click any cell in B2:B10. No dropdown appears. The validation XML is written into the file, but Excel suppresses it because the cells are locked.

The Fix: Correct Method Call Order

The solution has two parts: unlock the cells that should accept validated input, and call protect() after you've set up both the format and the validation. Order matters here.

import xlsxwriter

workbook = xlsxwriter.Workbook("fixed.xlsx")
worksheet = workbook.add_worksheet()

# 1. Create an unlocked format for validated input cells
unlocked = workbook.add_format({"locked": False})

# 2. Apply the unlocked format to the validation range FIRST
worksheet.write_blank("B2", None, unlocked)
worksheet.set_column("B:B", 20, unlocked)

# 3. Add the data validation rule
worksheet.data_validation(
    "B2:B10",
    {
        "validate": "list",
        "source": ["Apple", "Banana", "Cherry"],
    },
)

# 4. Protect the sheet LAST — locked cells stay locked, unlocked stay editable
worksheet.protect()

workbook.close()

This works because the locked: False format overrides the sheet-level default for those specific cells. When Excel evaluates the protected sheet, it sees that B2:B10 are explicitly unlocked, so it renders the data validation normally.

Note that using set_column with an unlocked format applies it to the entire column. If you only want to unlock specific rows in that column, apply the format row by row using write_blank or include it when you write your actual data.

Allowing Users to Select Locked Cells

By default, worksheet.protect() also prevents users from even selecting locked cells. That's bad UX if you want them to read values in protected cells. Pass the select_locked_cells option to allow selection without editing:

worksheet.protect(
    options={
        "select_locked_cells": True,
        "select_unlocked_cells": True,
    }
)

This keeps the sheet protected from edits but lets users click around freely — which also means they can interact with unlocked validated cells without confusion. The protect() method accepts a password as the first positional argument if you need to prevent users from removing the protection themselves: worksheet.protect("mypassword", options={...}).

Protecting Only Specific Cells While Keeping Validation

A common pattern is to lock formula cells and leave input cells free. Here's how to structure that cleanly:

import xlsxwriter

workbook = xlsxwriter.Workbook("selective.xlsx")
worksheet = workbook.add_worksheet()

locked_fmt = workbook.add_format({"locked": True, "bg_color": "#DDDDDD"})
unlocked_fmt = workbook.add_format({"locked": False})

# Column A: locked formula cells
for row in range(1, 11):
    worksheet.write_formula(row, 0, f"=B{row + 1}*1.2", locked_fmt)

# Column B: unlocked input cells with validation
for row in range(1, 11):
    worksheet.write_blank(row, 1, None, unlocked_fmt)

worksheet.data_validation(
    "B2:B11",
    {
        "validate": "integer",
        "criteria": "between",
        "minimum": 1,
        "maximum": 1000,
        "error_message": "Enter a whole number between 1 and 1000.",
    },
)

worksheet.protect("secret123")
workbook.close()

The locked column A cells display their formula results and can't be edited. Column B is unlocked and enforces the integer validation rule. Both behaviors coexist correctly because the cell-level format takes priority over the sheet-level protection default.

If you're building more complex reports with multiple worksheets, the same principle applies per sheet. Check out the patterns discussed in the article on fixing freeze_panes across multiple sheets in xlsxwriter — the per-worksheet format approach is consistent across these features.

Combining Protection With Multiple Validation Rules

Real workbooks often need several validation rules across different ranges. Apply each rule independently and make sure every target range has the unlocked format before calling protect().

import xlsxwriter

workbook = xlsxwriter.Workbook("multi_validation.xlsx")
worksheet = workbook.add_worksheet()

unlocked = workbook.add_format({"locked": False})

# Mark all input ranges as unlocked
worksheet.set_column("B:B", 15, unlocked)  # Category dropdown
worksheet.set_column("C:C", 12, unlocked)  # Quantity integer
worksheet.set_column("D:D", 12, unlocked)  # Date range

# Validation rule 1: dropdown list
worksheet.data_validation(
    "B2:B20",
    {
        "validate": "list",
        "source": ["Hardware", "Software", "Services"],
    },
)

# Validation rule 2: integer range
worksheet.data_validation(
    "C2:C20",
    {
        "validate": "integer",
        "criteria": ">",
        "value": 0,
        "error_title": "Invalid quantity",
        "error_message": "Quantity must be a positive whole number.",
    },
)

# Validation rule 3: date must be in the future
worksheet.data_validation(
    "D2:D20",
    {
        "validate": "date",
        "criteria": ">",
        "value": "2024-01-01",
        "error_message": "Please enter a date after 2024-01-01.",
    },
)

# Protect sheet AFTER all validation and format setup
worksheet.protect()

workbook.close()

Each column gets the unlocked format via set_column, which covers the entire column range. Then each validation rule is applied independently. Protection comes last. All three validation rules will be active and visible in Excel.

If you're also dealing with named ranges in these workbooks, the ordering constraint is similar — the article on fixing lost named ranges in xlsxwriter walks through why close-time operations can silently drop configuration.

Common Pitfalls and Gotchas

Calling protect() before data_validation()

This is the second most common mistake. Even if your cells are unlocked, calling protect() before data_validation() can cause the XML to be written in an order that some Excel versions misinterpret. Always set up formats and validations first, then protect.

Using set_column to set formats but forgetting rows already written

If you've already called worksheet.write() on specific cells without the unlocked format, set_column won't retroactively change those cells' lock state. Apply the unlocked format at write time, not as an afterthought via set_column alone.

Validation source lists with more than 255 characters

Excel has a 255-character limit for inline dropdown source lists. If your combined list string (including commas) exceeds that, use a cell range reference instead of a Python list: "source": "=$F$1:$F$20". This is especially easy to hit on protected sheets where you might have long category names.

Confusing input_message with error_message

xlsxwriter supports both input_message (shown when the cell is selected) and error_message (shown on invalid input). On a protected sheet, the input message tooltip still won't display on locked cells even if the validation rule is present. Another reason to double-check your unlock setup.

Password encoding edge cases

xlsxwriter uses a legacy XOR-based password hash for sheet protection, which is the same algorithm Excel uses. This means protection is easy to remove with any modern Excel utility — it's a deterrent, not true security. Don't rely on xlsxwriter sheet protection to guard sensitive data.

For reference, if you've run into similar silent-skip behavior in other areas of xlsxwriter, the same careful ordering pattern solves conditional formatting that disappears on filtered rows — the root cause is analogous.

LibreOffice behaves differently

LibreOffice Calc handles the interaction between sheet protection and data validation differently from Excel. A file that displays validation correctly in Excel may still suppress dropdowns in LibreOffice even with the unlocked format applied. If you need cross-application compatibility, test in both environments before shipping.

It's also worth noting that cell format inheritance can produce surprising results when you're working with merged cells — the xlsxwriter merged cell format issue covers why merged ranges need special handling that standard cell writes don't.

Wrapping Up

The pattern to remember is simple: unlock before you protect. xlsxwriter's sheet protection locks all cells by default, and Excel silently ignores data validation on locked cells. Applying a locked: False format to your input ranges before calling worksheet.protect() keeps both features working correctly.

Here are four concrete steps to take right now:

1. Create a dedicated unlocked format with workbook.add_format({"locked": False}) and apply it to every cell range that will have a validation rule.
2. Move all data_validation() calls to before your worksheet.protect() call and keep that order consistent.
3. Open the generated file and click a validated cell — if the dropdown or error alert appears, the fix is working. If not, verify the cell-level format was actually applied (not just set_column on a range you already wrote).
4. Add a password to protect() only if you need to prevent users from removing protection, and document that it's a deterrent, not encryption.
5. Test in your target Excel version and, if needed, LibreOffice before declaring the workbook production-ready.