For repeatable, file-based Excel work—such as cleaning a column, updating cells, or processing several worksheets—a Python script using openpyxl can apply the same rule consistently. The basic pattern is to load a workbook, select a sheet, loop over the relevant cells, make changes, and save to a separate file. Test the exact workflow on a copy: openpyxl does not calculate formulas, and saving can affect workbook features it does not support.
When openpyxl is the right tool
openpyxl is a Python library for working with Excel workbook files. It is a good fit when you want a deterministic operation repeated across rows, worksheets, or recurring files—for example, trimming whitespace, updating values, or creating a workbook from structured data. The official openpyxl 3.1.3 tutorial covers installation with pip, loading workbooks, worksheet operations, and saving.
As an Amazon Associate I earn from qualifying purchases.
It is not a replacement for Excel’s calculation engine. If a task depends on recalculating formulas, or on Excel-specific workbook behavior, account for that before choosing the library. If the goal is to analyze data inside an eligible Microsoft 365 workbook rather than process files externally, Python in Excel is a separate option with its own availability and data constraints.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsA safe starter pattern
This example trims whitespace from text values in column A on a worksheet named Sheet1, starting below the header. It leaves blank cells and non-text values unchanged. It is a pattern to adapt, not a tested script for an unknown workbook.
#1 Best Overall
from pathlib import Path
from openpyxl import load_workbook
source = Path("input.xlsx")
target = Path("output.xlsx")
wb = load_workbook(source)
ws = wb["Sheet1"]
for row in ws.iter_rows(min_row=2, min_col=1, max_col=1):
cell = row[0]
if isinstance(cell.value, str):
cell.value = cell.value.strip()
wb.save(target)Install the library in the Python environment you plan to use with pip install openpyxl, as shown in the official tutorial. Pillow is only needed to include images in a workbook; it is not required for ordinary cell-value edits.
Build a repeatable workflow
- Inventory the workbook. Note its file type and sheet names, and check whether it contains formulas, macros, charts, images, data validation, external links, or other features the task depends on.
- Test a copy. Run the intended load-and-save operation on a representative workbook before applying it to important files. Inspect the result in Excel or the spreadsheet application used by your team.
- Target cells explicitly. Select the intended worksheet by name and use a bounded range, known column, or header lookup so the script’s target is clear.
iter_rows()is useful for processing a defined range. - Make the transformation predictable. Handle expected blanks and data types. Where practical, make the change idempotent: running the script again should not keep adding or altering content unintentionally.
- Save to a new path while developing. The openpyxl tutorial warns that
Workbook.save()overwrites an existing file without warning, so avoid pointing the output at the original until your process is deliberate. - Verify the output. Compare expected row counts, representative values, formulas, formatting, and any workbook features relevant to the task. A script finishing without an error does not establish that the result is correct.
For a recurring task, put the transformation in a function and make the input and output paths configurable. You can also record which rows or cells changed, making it easier to audit a run and diagnose unexpected results.
Rank #2
- Language: english
- Book - automate the boring stuff with python, 2nd edition: practical programming for total beginners
- It is made up of premium quality material.
Formulas: stored text is not a recalculated result
A cell containing a formula and the value last calculated for that formula are distinct. The openpyxl 3.0.10 usage guide explains that loading with data_only=True returns the value cached the last time a spreadsheet application read the sheet. It does not cause openpyxl to calculate the formula; the cached value may be stale or unavailable.
If your automation changes inputs and depends on updated formula results, plan for a spreadsheet application to recalculate the workbook, then verify the results there. Do not treat a cached value read by Python as proof that the formula reflects the latest inputs.
Protect macros and other workbook features
Saving an existing workbook can affect features beyond the cells you edit. The openpyxl 3.1.3 tutorial says it does not read every possible item in an Excel file and warns that shapes may be lost when a workbook is opened and saved. The older 3.0.10 usage guide also warns about images and charts. These warnings make a copy-based test especially important for workbooks that rely on drawings, charts, connections, or complex Excel features.
For macro-enabled files, the tutorial documents the keep_vba load option for preserving VBA elements. Preservation does not make the macros editable through openpyxl, and the macro-enabled file extension must remain consistent when saving. Test the saved file in Excel before relying on it.
Python scripts versus Python in Excel
| Consideration | External Python with openpyxl | Python in Excel |
|---|---|---|
| Where it runs | In a Python environment outside Excel, working with workbook files. | In eligible Excel for Microsoft 365 workbooks, using Python formulas and Excel’s calculation workflow. |
| Best suited to | Repeatable file processing and edits to supported workbook content. | In-workbook analysis using worksheet data and supported sources such as Power Query. |
| Important constraint | It does not recalculate formulas, and saving may affect unsupported features. | Microsoft says common external-data functions such as pandas.read_csv and pandas.read_excel are not compatible in this environment. |
| Availability | Requires a Python installation and the library. | Microsoft documents it for Excel for Microsoft 365 and Excel for Microsoft 365 for Mac; check current availability for your locale and account. |
For current product details and eligibility, consult Microsoft’s Python in Excel guide and its linked availability information. Choose based on whether you need an external repeatable file operation or analysis within an eligible Excel workbook.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
