Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Define Names and Protect Sheets with XlsxWriter

Build an input template with XlsxWriter in which formulas use defined names instead of cell addresses and only the input cells can be edited: workbook and sheet-scoped names for ranges and constants, a dropdown fed by a named list on a hidden sheet, unlocked input cells, hidden formulas, protect() options that still allow sorting and filtering, and a check of the result with openpyxl and LibreOffice.
xlsxwriterProtection & sharing

A workbook that other people fill in needs two things from the code that builds it. Its formulas should be readable — =Amount*VAT_Rate says what it does, =C5*Lists!$D$2 does not — and its structure should be safe from accidents, so that someone typing figures cannot overwrite a formula or delete the list behind a dropdown. XlsxWriter handles both: define_name creates named ranges and named constants, and protect locks a sheet so only the cells you have explicitly unlocked can be edited. This page builds a small expense claim template that uses both, then reads the file back to check that the names, locks and hidden formulas are what was intended. It belongs to Building Excel Reports with XlsxWriter.

Prerequisites

Bash
pip install xlsxwriter openpyxl

openpyxl is used only to inspect the finished file. XlsxWriter writes new workbooks; it cannot open and modify an existing one.

Step 1 — Names for a list, a constant and a sheet-only range

Python
import xlsxwriter

wb = xlsxwriter.Workbook("expense_claim.xlsx")
inp = wb.add_worksheet("Claim")
lists = wb.add_worksheet("Lists")

categories = ["Travel", "Accommodation", "Meals", "Equipment", "Training"]
lists.write_column("A1", ["Category"] + categories)
lists.hide()

wb.define_name("Categories", f"=Lists!$A$2:$A${len(categories) + 1}")    # a workbook-wide range
wb.define_name("VAT_Rate", "=0.2")                                      # a named constant
wb.define_name("Claim!Amounts", "=Claim!$C$5:$C$24")                     # scoped to the Claim sheet only

define_name takes a name and what it refers to, written as a formula. Three kinds of name are useful in templates. A range name such as Categories points at cells, here the list of expense categories on a separate sheet. A constant such as VAT_Rate points at a value rather than a cell, so it does not live anywhere a user can edit it; changing the rate means changing the code that builds the template, which is usually what you want for a rate set by policy. A sheet-scoped name is written with the sheet name in front — Claim!Amounts — and is visible only on that sheet, which avoids clashes when several sheets each need their own Amounts. Names must start with a letter or underscore, contain no spaces, and must not look like a cell address: VAT2026 is fine, VAT1 is not, because it is the address of a cell in column VAT.

The list sheet is hidden. Hiding is not security — anyone can unhide a sheet unless the workbook structure is protected — but it keeps the template tidy and stops casual edits.

The names in the template Categories refers to five cells on the hidden Lists sheet and is visible everywhere. VAT_Rate is a constant that lives in no cell. Amounts is scoped to the Claim sheet and refers to the amount column. A B C 1 Name Refers to Scope 2 Categories =Lists!$A$2:$A$6 workbook 3 VAT_Rate =0.2 workbook 4 Amounts =Claim!$C$5:$C$24 Claim sheet only

Step 2 — Input cells, dropdowns and formulas that use the names

Python
title = wb.add_format({"bold": True, "font_size": 14})
head = wb.add_format({"bold": True, "bottom": 1, "bg_color": "#EEF3FA"})
unlocked = wb.add_format({"locked": False, "bg_color": "#FFF9DB", "border": 1, "border_color": "#D9D9D9"})
unlocked_money = wb.add_format({"locked": False, "bg_color": "#FFF9DB", "num_format": "#,##0.00", "border": 1, "border_color": "#D9D9D9"})
calc_money = wb.add_format({"num_format": "#,##0.00", "hidden": True})
total_fmt = wb.add_format({"bold": True, "num_format": "#,##0.00", "top": 1, "hidden": True})

inp.write("A1", "Expense claim", title)
inp.write("A2", "Name")
inp.write("B2", None, unlocked)
inp.write_row("A4", ["Date", "Category", "Amount (gross)", "VAT", "Net"], head)
for r in range(4, 24):                       # rows 5–24 in Excel terms
    inp.write_blank(r, 0, None, unlocked)
    inp.write_blank(r, 1, None, unlocked)
    inp.write_blank(r, 2, None, unlocked_money)
    inp.write_formula(r, 3, f"=IF(C{r + 1}=\"\",\"\",ROUND(C{r + 1}*VAT_Rate/(1+VAT_Rate),2))", calc_money)
    inp.write_formula(r, 4, f"=IF(C{r + 1}=\"\",\"\",C{r + 1}-D{r + 1})", calc_money)
inp.data_validation("B5:B24", {"validate": "list", "source": "=Categories"})
inp.data_validation("C5:C24", {"validate": "decimal", "criteria": ">=", "value": 0,
                               "error_message": "Enter the amount as a positive number."})
inp.write("B26", "Total")
inp.write_formula("C26", "=SUM(Amounts)", total_fmt)
inp.set_column("A:A", 12, wb.add_format({"num_format": "dd/mm/yyyy"}))
inp.set_column("B:B", 16)
inp.set_column("C:E", 15)

Every cell in a new sheet is locked by default, but locking does nothing until the sheet is protected. So the template marks the cells people may edit — name, date, category, amount — with a format that has "locked": False, and gives them a pale yellow fill so users can see where to type. The VAT and net columns are formulas that refer to VAT_Rate by name, and the total is =SUM(Amounts), which is easier to check than a range address. The category dropdown takes its options from =Categories, so adding a category means extending the list and the name, not editing every validation rule. The formula cells use "hidden": True, which hides the formula from the formula bar once the sheet is protected — users see the result, not the calculation. That is a convenience, not secrecy: the formulas are still in the file.

Step 3 — Protect the sheet, but leave useful actions allowed

Python
inp.protect("", {"sort": False, "autofilter": False, "format_columns": True, "select_locked_cells": True})
inp.activate()
wb.close()
print("template written")

protect() switches the locks on. Its options decide what users may still do on a protected sheet: here they can resize columns so long category names fit, and select locked cells to copy values, but cannot insert or delete rows, change formats or edit locked cells. An empty password, as here, protects against accidents only — anyone can unprotect the sheet from the Review tab — which is often the right choice for an internal template where the goal is to stop mistakes, not people. A password adds a prompt, but sheet passwords in the Excel format are stored as a weak hash and are easily removed, so they are not a security control either; see Lock Cells and Protect a Sheet with openpyxl for the same caveat.

There is one gap. XlsxWriter cannot protect the workbook structure — the setting that stops users unhiding, renaming, adding or deleting sheets — so the hidden Lists sheet can be unhidden. If that matters, open the finished file with openpyxl and set wb.security.lockStructure = True, or set the list sheet to very hidden with lists.very_hidden(), which removes it from Excel's Unhide dialog altogether.

What protection does and does not do Sheet protection stops edits to locked cells, hides formulas marked hidden and limits actions such as inserting rows. It does not encrypt the file, does not stop the hidden list sheet being unhidden, and its password is easy to remove. Sheet protection does block edits to locked cells hide formulas marked hidden limit inserting and deleting rows allow chosen actions like resizing It does not encrypt or hide the data stop sheets being unhidden resist a determined user protect workbook structure in XlsxWriter Building the protected template The code first defines the names, then writes the input cells with an unlocked format and the formula cells with a hidden format, adds dropdowns that use the named list, and finally protects the sheet with the options users still need. define_name list, constant, scoped range Input cells locked: False Formula cells hidden: True Dropdown source =Categories protect() with allowed actions

The order matters in one respect only: formats are fixed when a cell is written, so the unlocked and hidden formats must be applied as the cells are written, not afterwards. Protection itself can be switched on at any point before the workbook is closed.

Step 4 — Check the finished file

Python
from openpyxl import load_workbook

wb2 = load_workbook("expense_claim.xlsx")
print("workbook names:", {n: d.attr_text for n, d in wb2.defined_names.items()})
ws = wb2["Claim"]
print("sheet names on Claim:", {n: d.attr_text for n, d in ws.defined_names.items()})
print("Claim protected:", ws.protection.sheet, "| Lists state:", wb2["Lists"].sheet_state)
for ref in ["B2", "C5", "D5", "C26"]:
    c = ws[ref]
    print(f"{ref}: locked={c.protection.locked} hidden={c.protection.hidden} value={c.value!r}")

Reading the file back confirms each decision: the three workbook names and the one scoped to Claim, the protection flag on the sheet, the hidden list sheet, and the lock state of an input cell (unlocked), a formula cell (locked and hidden) and the total. This kind of check is worth keeping as a test, because a template that silently lost its protection or a name would not show any error until someone overwrote a formula.

Bash
python - <<'EOF'
from openpyxl import load_workbook
wb = load_workbook("expense_claim.xlsx"); ws = wb["Claim"]
ws["B2"] = "A. Tester"; ws["A5"] = "2026-09-14"; ws["B5"] = "Travel"; ws["C5"] = 120
ws["B6"] = "Meals"; ws["C6"] = 36.6
wb.save("claim_filled.xlsx")
EOF
soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' claim_filled.xlsx > /dev/null 2>&1
sed -n '4,6p;26p' claim_filled.csv

Filling in two lines and recalculating in LibreOffice shows the names working in formulas: VAT of 20.00 on a gross 120.00 and 6.10 on 36.60, net amounts of 100.00 and 30.50, and a total of 156.60 from =SUM(Amounts). The fill-in script uses openpyxl, which writes cells without asking about protection — sheet protection is enforced by the spreadsheet application, not by the file format, which is one more reason to see it as a guard against accidents.

Common pitfalls

  • Unlocked formats forgotten. Without "locked": False on input cells, nobody can type anything once protected.
  • Names that look like cell addresses. VAT1 is a cell; use VAT_Rate.
  • Hidden sheets treated as secure. Users can unhide them; use very_hidden() or structure protection.
  • Passwords as security. Sheet protection prevents accidents, not determined users.
  • Hard-coded rates in formulas. Put them in named constants so they change in one place.

Conclusion

In XlsxWriter, define_name gives formulas readable names for ranges and constants — workbook-wide or scoped to one sheet with Sheet!Name — and a named list makes dropdowns easy to maintain. Mark input cells with an unlocked format, hide formulas where you want only results visible, and call protect() with the options users still need. XlsxWriter cannot lock workbook structure, so use very_hidden() or openpyxl for that, and read the file back in a test to confirm the result.

Frequently asked questions

How do I create a named range with XlsxWriter? Call workbook.define_name("Name", "=Sheet1!$A$1:$A$10"); prefix the name with a sheet name, as in "Sheet1!Name", to scope it to that sheet.

Can a defined name hold a constant instead of a range? Yes: workbook.define_name("VAT_Rate", "=0.2") creates a name that formulas can use like a cell.

How do I let users edit only some cells in XlsxWriter? Give those cells a format with "locked": False, then call worksheet.protect().

Can XlsxWriter protect the workbook structure? No; it protects sheets only. Use very_hidden() for sheets that must not be unhidden, or set workbook structure protection with openpyxl afterwards.

More on xlsxwriter

More on xlsxwriter →