Let Different People Edit Different Ranges

A budget template goes to several teams, and each one should fill in its own column and nothing else: sales enters sales costs, marketing enters marketing costs, and nobody overwrites the totals. Plain sheet protection cannot do that — a cell is either locked for everyone or unlocked for everyone. Excel's Allow Edit Ranges feature can: on a protected sheet, each named range has its own password, and a user who types into a range is asked for that range's password before the edit is accepted. Give each team the password for its range and the template enforces who edits what, while the formulas stay locked for all. This page builds such a template with XlsxWriter, explains exactly what it protects and what it does not, and deals with a trap: opening and saving the file with openpyxl deletes the ranges without a word. It belongs to Protecting and Sharing Excel Workbooks.

Prerequisites

pip install xlsxwriter openpyxl

Allow Edit Ranges is a feature of desktop Excel, and the behaviour described here is Excel's. Other spreadsheet applications and viewers may keep the sheet protection without honouring the per-range passwords, so test the template in the application your teams will actually use.

Step 1 — One range per team on a protected sheet

import xlsxwriter

teams = {"Sales": "sales-2026", "Marketing": "mkt-2026", "Operations": "ops-2026"}
lines = ["Salaries", "Travel", "Software", "Training", "Events", "Equipment"]

wb = xlsxwriter.Workbook("budget_2027.xlsx")
ws = wb.add_worksheet("Budget")
head = wb.add_format({"bold": True, "bg_color": "#EEF3FA", "border": 1})
money = wb.add_format({"num_format": "#,##0", "border": 1, "bg_color": "#FFF9DB"})
total = wb.add_format({"num_format": "#,##0", "bold": True, "top": 2})

ws.write_row("A1", ["Cost line", *teams, "Total"], head)
for r, line in enumerate(lines, start=1):
    ws.write(r, 0, line)
    for c in range(1, len(teams) + 1):
        ws.write_blank(r, c, None, money)                       # still locked: the ranges grant editing
    ws.write_formula(r, len(teams) + 1, f"=SUM(B{r + 1}:D{r + 1})", total)
last = len(lines) + 1
ws.write(last, 0, "Total", total)
for c in range(1, len(teams) + 2):
    col = chr(ord("A") + c)
    ws.write_formula(last, c, f"=SUM({col}2:{col}{last})", total)

for i, (team, password) in enumerate(teams.items()):
    col = chr(ord("B") + i)
    ws.unprotect_range(f"{col}2:{col}{last}", f"{team}Inputs", password)
ws.protect("budget-owner")
ws.set_column("A:A", 14)
ws.set_column("B:E", 13)
wb.close()
print("written with", len(teams), "edit ranges")

Each team's column is registered with unprotect_range(range, name, password). The input cells keep the default locked format — that matters, because an unlocked cell is editable by anyone once the sheet is protected, password or not. Locked cells inside an edit range are editable only by someone who knows that range's password. The totals, the cost line labels and the headings are locked and in no range, so they cannot be changed by anyone without the sheet password. Finally protect() switches protection on; without it, the ranges have no effect at all.

Step 2 — What is in the file

import re, zipfile

xml = zipfile.ZipFile("budget_2027.xlsx").read("xl/worksheets/sheet1.xml").decode()
print(re.search(r"<sheetProtection[^>]*/>", xml).group())
for m in re.finditer(r"<protectedRange [^>]*/>", xml):
    print(m.group())

The sheet XML holds the protection flag and one protectedRange element per team: its cells, its name and a password hash. When someone in Excel types into a cell of the Sales range, Excel shows an Unlock Range dialog asking for the password; once it is entered, that range stays editable for the rest of the session. Typing into the totals shows the usual "the cell is on a protected sheet" message. The sheet's own password — budget-owner here — stays with whoever maintains the template; with it, the owner can unprotect the sheet to change the layout, add a cost line or adjust a range, then protect it again. Teams never need it. The range names also appear in Excel's Review › Allow Edit Ranges dialog, where the sheet's owner can see and change them.

The hashes are four hexadecimal digits each. That is Excel's legacy password hash, and it is the honest answer to how strong this protection is: it stops colleagues from editing each other's figures by accident or casually, but it is not security. Any password with the same 16-bit hash is accepted, and free tools remove sheet protection in seconds. For data that one team must not see, separate files are the only real answer.

Step 3 — The openpyxl trap

from openpyxl import load_workbook

wb2 = load_workbook("budget_2027.xlsx")
wb2["Budget"]["A2"] = "Salaries and wages"          # any small edit
wb2.save("budget_2027_edited.xlsx")
xml2 = zipfile.ZipFile("budget_2027_edited.xlsx").read("xl/worksheets/sheet1.xml").decode()
print("protectedRanges after openpyxl save:", len(re.findall(r"<protectedRange ", xml2)))
print("sheet still protected:", "<sheetProtection" in xml2)

openpyxl does not know about edit ranges. It reads the sheet protection and writes it back, but it drops the protectedRanges element entirely, and it gives no warning. The edited file is still protected, so it looks fine — but now the team columns are locked for everyone, and the teams cannot enter their figures at all. This is one more entry for the list of things openpyxl loses on a round trip, alongside the ones in What openpyxl Loses When You Save an Existing Workbook.

Step 4 — Put the ranges back after an openpyxl edit

import shutil
from openpyxl.utils.protection import hash_password

def add_edit_ranges(path, sheet_xml, ranges):
    """Insert <protectedRanges> after <sheetProtection> in one sheet of an .xlsx file."""
    items = "".join(f'<protectedRange password="{hash_password(pw)}" sqref="{ref}" name="{name}"/>'
                    for name, ref, pw in ranges)
    tmp = path + ".tmp"
    with zipfile.ZipFile(path) as src, zipfile.ZipFile(tmp, "w", zipfile.ZIP_DEFLATED) as dst:
        for item in src.infolist():
            data = src.read(item.filename)
            if item.filename == sheet_xml:
                text = data.decode()
                text = re.sub(r"(<sheetProtection[^>]*/>)", rf"\1<protectedRanges>{items}</protectedRanges>", text, count=1)
                data = text.encode()
            dst.writestr(item, data)
    shutil.move(tmp, path)

add_edit_ranges("budget_2027_edited.xlsx", "xl/worksheets/sheet1.xml",
                [(f"{t}Inputs", f"{chr(ord('B') + i)}2:{chr(ord('B') + i)}7", pw) for i, (t, pw) in enumerate(teams.items())])
xml3 = zipfile.ZipFile("budget_2027_edited.xlsx").read("xl/worksheets/sheet1.xml").decode()
print(re.findall(r'name="(\w+)"', re.search(r"<protectedRanges>.*</protectedRanges>", xml3).group()))
print("same hashes as XlsxWriter:", re.findall(r'password="(\w+)"', xml3)[1:] == re.findall(r'password="(\w+)"', xml)[1:])

When a workflow has to edit the file with openpyxl — filling in last year's actuals each month, say — the ranges can be restored afterwards by inserting the XML element back into the sheet. Its position matters: the Office Open XML schema puts protectedRanges immediately after sheetProtection, and Excel rejects files whose elements are out of order. The passwords are hashed with openpyxl's own hash_password, which implements the same legacy algorithm; the check at the end confirms the hashes are identical to the ones XlsxWriter wrote. If the workbook does not need openpyxl at all, the simpler path is to regenerate it with XlsxWriter each time. Either way, add a check like the ones above to the job that produces the file — counting protectedRange elements in the sheet XML takes a few lines and catches the problem before the template reaches the teams, rather than when the first person finds they cannot type in their column.

When to use separate files instead

Edit ranges keep everyone in one workbook, which is convenient when teams need to see each other's numbers and the totals update live. They are the wrong tool when teams must not see each other's data, when contributors use an application other than desktop Excel, or when there are dozens of contributors and passwords would have to be shared widely. A password that has been emailed to forty people is not much of a boundary, and when someone leaves a team there is no way to revoke their knowledge of it short of changing the password and re-issuing the file. In those cases, sending each team its own file and consolidating the returns is more robust — see Send Out Budget Templates and Consolidate the Returns.

Common pitfalls

• Unlocking the input cells. Unlocked cells are editable by anyone; keep them locked inside the ranges.
• Forgetting protect(). Edit ranges do nothing on an unprotected sheet.
• Editing with openpyxl. It drops the ranges on save; restore them or regenerate the file.
• Treating it as security. The legacy hash is weak; it prevents accidents, not access.
• Other applications. Range passwords are an Excel desktop feature; test where the file will be used.

Conclusion

To let each team edit only its own cells, write the template with XlsxWriter, keep the input cells locked, register one range per team with unprotect_range(range, name, password) and protect the sheet. Excel then asks for the right password when someone types into a range and refuses edits elsewhere. openpyxl deletes the ranges on save, so either regenerate the file with XlsxWriter or reinsert the protectedRanges element after every openpyxl edit.

Frequently asked questions

How do I create Allow Edit Ranges from Python? Use XlsxWriter's worksheet.unprotect_range("B2:B7", "SalesInputs", "password") for each range, then worksheet.protect().

Does openpyxl support Allow Edit Ranges? No; it neither creates nor keeps them, and saving a workbook with openpyxl removes any that exist.

Should the cells in an edit range be locked or unlocked? Locked; unlocked cells are editable by everyone on a protected sheet, while locked cells in a range require that range's password.

Are range passwords secure? No; they use Excel's weak legacy hash and are easy to remove, so they only prevent accidental edits.

Related

• Up one level: Protecting and Sharing Excel Workbooks — protection and sharing.
• Lock Cells and Protect a Sheet with openpyxl — plain sheet protection.
• Define Names and Protect Sheets with XlsxWriter — XlsxWriter protection options.
• Remove Sheet Protection with openpyxl — how easily it comes off.