Build a clean spreadsheet (.xlsx)

You build Excel workbooks with Python's openpyxl on your own computer. The standard is a workbook another person can audit in five minutes: inputs typed once and labelled with units and source, calculations as live formulas (never pasted results), one consistent formula per column, clear formats, and zero formula errors after recalculation.

When to use

• "Make me a spreadsheet to track X."
• "Put this data in Excel with totals by month."
• "Build a simple budget / price calculator / commission model."
• "I need this as a Google Sheet." (build the .xlsx, then upload it through the connected Google Drive or Sheets app, or the browser)

What you need

• The purpose and the person who will use it (the owner, a teammate, a client).
• The data (CSV, export, pasted table) and the rules (definitions, targets, rates). Rates such as tax depend on country and date: get them from the owner or check with web_search and note the source in the workbook.
• Your job folder in your work space, for example /home/user/Desktop/Ada - Work space/q3-sales/.

Set up once

python3 -c "import openpyxl" 2>/dev/null || python3 -m pip install --user --break-system-packages openpyxl

(Debian refuses plain pip install --user with "externally-managed-environment"; adding --break-system-packages with --user installs only into your home folder.)

Method

1. Design the sheets before writing code. Standard layout, left to right in the order a reader thinks:

   • About: purpose, author, date, data source, how to use, version.
   • Inputs: every assumption once, with value, unit and source. Blue font on a pale fill means "typed in, change me".
   • Data: raw rows, one row per record, one column per field, as an Excel table. No totals inside the data.
   • Calc (only if needed): working steps.
   • Summary or Report: the answer, built only from formulas pointing at Inputs, Data and Calc.

2. Follow the formula rules (adapted from the FAST Standard; full list in references/workbook-standards.md):

   • No numbers typed inside formulas. =B5*Inputs!$B$3, never =B5*0.2.
   • One formula per column or row: the same formula copied all the way. A cell that breaks the pattern is where errors hide.
   • Calculate each thing once; elsewhere, link to it.
   • Short formulas. If a formula is longer than your thumb, split it into helper columns.
   • No nested IFs, no OFFSET or INDIRECT, no merged cells, nothing hidden.
   • Prefer SUMIFS, COUNTIFS, AVERAGEIFS, INDEX/MATCH, IFERROR used sparingly. These work in every Excel, LibreOffice and Google Sheets.
   • Units on every label: "Revenue (£)", "Lead time (days)".

3. Build with execute_code (it runs in a temporary folder, so use absolute paths). Tested starting point; the full version with an about sheet, validation and a chart is in references/openpyxl-recipes.md:

from pathlib import Path
from datetime import date
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.worksheet.table import Table, TableStyleInfo

OUT = Path("/home/user/Desktop/Ada - Work space/q3-sales/2026-10-05_sales-by-region_v01.xlsx")
OUT.parent.mkdir(parents=True, exist_ok=True)
GBP, PCT = '£#,##0;[Red]-£#,##0', '0.0%'
HEAD, HEAD_FILL = Font(bold=True, color="FFFFFF"), PatternFill("solid", fgColor="1F2A44")
sales = [(date(2026, 7, 3), "North", 120, 40), (date(2026, 8, 15), "West", 90, 40), (date(2026, 9, 2), "South", 150, 40)]
regions = ["North", "South", "West"]

wb = Workbook()
inp = wb.active; inp.title = "Inputs"
inp.append(["Assumption", "Value", "Unit", "Source"])
inp.append(["Q3 target per region", 12000, "£", "Agreed with owner, 1 Jul 2026"])
inp["B2"].font, inp["B2"].fill, inp["B2"].number_format = Font(color="0000FF"), PatternFill("solid", fgColor="FFF8DC"), GBP

data = wb.create_sheet("Data")
data.append(["Date", "Region", "Units", "Unit price (£)", "Revenue (£)"])
for i, (d, region, units, price) in enumerate(sales, start=2):
    data.append([d, region, units, price, f"=C{i}*D{i}"])            # same formula on every row
    data[f"A{i}"].number_format, data[f"E{i}"].number_format = "yyyy-mm-dd", GBP
last = data.max_row
tbl = Table(displayName="Sales", ref=f"A1:E{last}")
tbl.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
data.add_table(tbl)

s = wb.create_sheet("Summary")
s.append(["Region", "Revenue (£)", "Target (£)", "vs target"])
for r, region in enumerate(regions, start=2):
    s.append([region, f"=SUMIFS(Data!$E$2:$E${last},Data!$B$2:$B${last},A{r})", "=Inputs!$B$2", f'=IF(C{r}=0,"",B{r}/C{r}-1)'])
    s[f"B{r}"].number_format = s[f"C{r}"].number_format = GBP
    s[f"D{r}"].number_format = PCT
tot = len(regions) + 2
s.append(["Total", f"=SUM(B2:B{tot - 1})", f"=SUM(C2:C{tot - 1})", f'=IF(C{tot}=0,"",B{tot}/C{tot}-1)'])
s[f"B{tot}"].number_format = s[f"C{tot}"].number_format = GBP
s[f"D{tot}"].number_format = PCT
for ws in (inp, data, s):
    for c in ws[1]:
        c.font, c.fill = HEAD, HEAD_FILL
    ws.freeze_panes = "A2"
    for col in "ABCDE":
        ws.column_dimensions[col].width = 16
wb.active = wb.index(s)                                              # opens on the answer
wb.save(OUT)
print(OUT)

4. Format for reading. Thousands separators; 0 decimals for money in summaries (2 in transactions); percentages to 1 decimal; ISO dates (yyyy-mm-dd) unless the owner prefers local style; negatives in red or brackets; numbers right-aligned; headers bold on a dark fill; freeze the header row; column widths that fit; landscape and fit-to-width when it will be printed.

5. Recalculate and check (next section). openpyxl writes formulas but cannot calculate them, so until the file is opened in Excel, Sheets or LibreOffice the formula cells have no values. Anything that reads the raw file (pandas, a script, some previewers) sees blanks.

6. Deliver. Save in the job folder, tell the owner the path, which sheet to look at, and which cells they can change. For Google Sheets, upload with the connected app (connected_apps find "upload file to Google Drive") or through the browser, and check the formulas survived.

Checking the output

• Formulas and errors. Recalculate a copy with LibreOffice and scan every formula cell for errors or blanks with the script in references/openpyxl-recipes.md (section "Recalculate and scan"). Install once: sudo apt-get install -y --no-install-recommends libreoffice-calc-nogui.
• Numbers tie out. Compute the key totals independently in Python (or pandas) from the source data and compare to the recalculated values. Row totals summed must equal column totals summed.
• Pattern check. Read each formula column back and confirm every row has the same formula shape (the script prints the patterns).
• Look. For anything the owner will print or present, convert the recalculated copy to PDF with soffice --convert-to pdf, render page 1 with pypdfium2 and look at it with vision_analyze.

Output

• YYYY-MM-DD_topic_v01.xlsx in the job folder, opening on the Summary sheet.
• Message: path, what each sheet holds, which blue cells to change, the headline numbers, and any assumption that needs the owner's confirmation. Use show_card (metrics or table) for the headline numbers.

Checks before you finish

• Zero #REF!, #DIV/0!, #NAME?, #VALUE!, #N/A after recalculation, and no formula cell left blank.
• No constants inside formulas; every input on Inputs with unit and source.
• Totals reconcile with an independent calculation.
• Headers frozen, formats applied, columns readable, workbook opens on the right sheet.
• The owner's original files untouched; your file is versioned.

Pitfalls

• Saving a workbook opened with data_only=True. That throws away every formula. Open with data_only=True only to read values, never to save.
• Editing an owner's workbook with openpyxl can drop things openpyxl does not understand (shapes, some charts, slicers, form controls) and macros unless you load .xlsm with keep_vba=True. Always save as a new file and compare sheet by sheet; for heavy edits, ask before touching a complex workbook.
• Newer functions show #NAME?. XLOOKUP, IFS, TEXTJOIN, CONCAT, MAXIFS, SWITCH must be written with an _xlfn. prefix (=_xlfn.XLOOKUP(...)), and older LibreOffice cannot calculate some of them. Dynamic-array functions (FILTER, UNIQUE, SORT) do not work reliably from openpyxl. Use INDEX/MATCH and SUMIFS instead.
• Formulas written in a local style. openpyxl stores English function names with commas between arguments, whatever the owner's Excel language.
• IDs and phone numbers as numbers. Leading zeros vanish and long IDs turn into scientific notation. Write them as text.
• Over 1,048,576 rows will not fit on a sheet. Summarise in Python and keep the raw data as CSV.
• Hard-coded totals typed as values. A total must be a formula.

See also: clean-and-analyse-data (prepare the data first), make-clear-charts, summarise-long-documents (reading someone else's workbook).