Automation

How to Automate Excel Tasks With Python

For `.xlsx` files, `openpyxl` can read and write cells without opening Excel. Use a copy of any important workbook and inspect formulas, formatting and macros before changing it.

Step by step

  1. Install openpyxl in a virtual environment: `python -m pip install openpyxl`.
  2. Create or load a workbook, selecting the intended worksheet.
  3. Write values and save to a new filename.
  4. Open the result in Excel to inspect formulas and formatting.

Run this example

Use Python 3 with openpyxl installed. Copy the code into a file or interactive Python environment.

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Item", "Units"])
ws.append(["Widget", 3])
print(ws["A2"].value, ws["B2"].value)
# Save only after reviewing: wb.save("sales-copy.xlsx")

Expected output

Widget 3

Common issue

Do not overwrite macro-enabled files casually; openpyxl may not preserve every advanced Excel feature.

Try it yourself

Add another sales row and calculate the total units in Python.

Create, calculate and verify a real workbook

Use a project virtual environment: python -m venv .venv, activate it, then run python -m pip install openpyxl. openpyxl works with .xlsx workbooks; it does not read legacy .xls files. The example below creates and reloads a real workbook in a temporary folder, leaving your existing files alone.

Build a sales report

Rows contain item, units and unit price. Python computes each row total and the grand total. Reloading the file verifies saved values rather than just checking the in-memory workbook.

from pathlib import Path
from tempfile import TemporaryDirectory
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font

with TemporaryDirectory() as folder:
    destination = Path(folder) / "sales-report.xlsx"
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "Sales"
    sheet.append(["Item", "Units", "Unit price", "Total"])
    for item, units, price in [("Notebook", 3, 2.5), ("Pen", 2, 1.25)]:
        sheet.append([item, units, price, units * price])
    total = sum(sheet.cell(row, 4).value for row in range(2, sheet.max_row + 1))
    sheet.append(["Grand total", None, None, total])
    for cell in sheet[1]:
        cell.font = Font(bold=True)
    sheet.freeze_panes = "A2"
    sheet.column_dimensions["A"].width = 20
    for row in sheet.iter_rows(min_row=2, min_col=3, max_col=4):
        for cell in row:
            cell.number_format = "0.00"
    workbook.save(destination)
    saved = load_workbook(destination)
    assert saved['Sales']['D4'].value == 10
    assert saved['Sales']["A1"].font.bold
    print(f"Saved: {destination.name}")
    print(f"Total: {saved['Sales']['D4'].value:.2f}")
    saved.close()

Expected output

Saved: sales-report.xlsx
Total: 10.00

Apply the workflow to your workbook

  1. Copy the input file and load the copy with load_workbook.
  2. Select a worksheet by name and verify required headers before processing rows.
  3. Validate numbers, calculate results, and write to a new output filename.
  4. Reload the result to check values, then inspect its layout in Excel or LibreOffice.

To retain the sample report, replace the temporary folder with a project output folder and choose a new filename. Check for an existing file before saving: save can overwrite it.

Formulas and advanced workbook features

openpyxl writes formulas but does not calculate them. Loading with data_only=True reads cached results from a spreadsheet application's previous calculation; those results may be absent or stale. This example calculates in Python so its checks do not depend on Excel. Macro preservation and advanced features require separate testing; do not treat loading and saving as proof of fidelity.

Exercise and troubleshooting

Add Pencil,4,0.50 before calculating the grand total. The total should become 12.00 and move to D5. If importing openpyxl fails, install with the same interpreter that runs the script. If saving fails while Excel has the output open, close it or choose a different filename. A KeyError selecting a sheet usually indicates a name mismatch.

Workbook loading, saving and formula caveats in the openpyxl tutorial.

Keep learning

Read the relevant documentation · Free foundational resources

Related guides: How to Read and Write CSV Files With Python and Python Functions Explained Through Practical Projects.

Interested in applying these skills to a product? Explore the optional Python to Profit course. It focuses on product and business building, not replacing foundational Python study.