📗 openpyxl

📘 Start with the full guides

The long-form treatment of openpyxl, with the reasoning the shorter pages assume.

• GuideUsing openpyxl for Excel File ManipulationA runnable, step-by-step guide to creating, reading, editing, styling, and saving .xlsx files with openpyxl — workbooks, sheets, cells, formatting, and images.
• GuideApplying Conditional Formatting With openpyxlAdd Excel conditional formatting from Python with openpyxl: CellIsRule, ColorScaleRule, and FormulaRule, plus the 3.1 API quirks that trip people up.
• GuideGenerating Excel Reports from TemplatesInject data into a prepared .xlsx with openpyxl instead of rebuilding sheets from scratch — preserve branding, formulas, and print areas, then save a dated copy.
• GuideCreating Charts in Excel with openpyxlBuild native, editable Excel charts from Python with openpyxl: bind data with Reference and Series, make bar, line, and pie charts, add titles and legends, and anchor them.
• GuideInserting Images and Logos into ExcelEmbed logos and images into Excel with openpyxl: floating drawings anchored to cells, pixel resizing, header bands, and why pandas can't keep them.
• GuideStyling Excel Cells with openpyxlStyle Excel cells from Python with openpyxl: Font, PatternFill, Border, Alignment, header rows in a loop, merged titles, and reusable NamedStyles — all runnable.
• GuideWorking with Multiple Excel Sheets in PythonRead, transform, and write multi-sheet Excel workbooks with pandas and openpyxl — load all tabs into a dict, merge across sheets, and export to named sheets.

🛠️ Step-by-step how-tos

One task each, with code you can copy, run and adapt.

• How-toClear Cell Contents and Formatting with openpyxlReproduce Excel's Clear menu in Python: clear values but keep formatting, reset formatting but keep values, remove comments and hyperlinks, or clear everything. Covers clearing a template's input area while keeping its formulas, and why merged cells make a simple clearing loop fail.
• How-toConvert Excel Column Letters and Numbers in PythonTurn 28 into "AB" and "XFD" into 16384 with openpyxl's get_column_letter and column_index_from_string, parse cell and range addresses, and map DataFrame columns to letters for formulas — plus why naive base-26 code breaks at Z and the 0-based/1-based traps between pandas, openpyxl and xlsxwriter.
• How-toDelete Empty Rows and Columns with openpyxlRemove blank rows and columns from a worksheet in place while keeping its formatting: detect empty rows without creating cells, delete them in runs from the bottom up, handle empty columns, repair row heights, merged cells and formulas that delete_rows leaves behind, and switch to a copy-based rebuild when the sheet is large.
• How-toHow openpyxl Stores Python Values: Types, Dates and NoneWhat happens to an int, a bool, a date, a Decimal, a NumPy value or a timezone-aware datetime when you assign it to an openpyxl cell — the cell type it becomes, the number format it gets, what comes back when the file is reopened, and which values fail only when you save.
• How-toMerge Repeated Group Labels into Single Cells with openpyxlTurn a report where the region name repeats on every row into one merged, vertically centred label per group: find runs of equal values, merge them with openpyxl, handle nested groups so an inner label never spans two outer groups, keep borders tidy, and know when to hide repeats with conditional formatting instead so the sheet stays sortable.
• How-toMove a Range of Cells with openpyxl move_rangeShift a block of cells to a new position with ws.move_range: values and styles move, translate=True rewrites formulas inside the block, and everything else — formulas that point at the block, merged cells, conditional formats — stays where it was. Learn the rules and a safe way to move a table.
• How-toRead Excel Rows into Dictionaries with openpyxlTurn each row of a sheet into a dictionary keyed by the header — the csv.DictReader pattern for .xlsx files. Stream rows with iter_rows(values_only=True), skip blank rows, clean up missing and duplicate headers, build lookups keyed by ID, and load rows into dataclasses without pandas.
• How-toTranspose a Range of Cells with openpyxlSwap rows and columns in a worksheet from Python: transpose values with zip, carry fonts, fills, borders and number formats across, handle merged cells and column widths, transpose with pandas when styles do not matter, and write a live TRANSPOSE array formula instead of a copy.
• How-toUse Theme Colours and Tints in openpyxlColour Excel cells with the workbook theme instead of fixed RGB values using openpyxl: theme colour indexes and the swapped order of the first four, tints that lighten and darken like Excel's palette, resolving theme colours to hex for checks and HTML, and rebranding a whole report by changing the theme's accent colours — verified against a rendered copy.
• How-toAdd Data Bars and Colour Scales with openpyxlIn-cell visuals that survive edits: DataBarRule and ColorScaleRule, percentile versus fixed endpoints, negative values, icon sets, and how to pick between a bar, a scale and a plain threshold.
• How-toAdd Icon Sets to Excel Cells with openpyxlAdd traffic lights, arrows, flags and rating icons to an Excel report with openpyxl's IconSetRule — choose the icon set, set thresholds as numbers, percents, percentiles or cells the reader can edit, control inclusive boundaries, show icons alone, and drive status icons from a calculated column.
• How-toApply a Colour Scale per Row or per Column with openpyxlColour scales compare every cell in their range with every other cell, so one big product drowns out all the small ones. Apply a separate colour scale to each row or each column with openpyxl so every item is coloured against its own range, choose between min-max and percentile scaling, keep the number of rules manageable, and check the rendered result.
• How-toControl Conditional Formatting Rule Order and Stop If TrueWhen several conditional-formatting rules match the same cell, their priority decides what the reader sees — and Excel and LibreOffice resolve overlaps differently. See how openpyxl numbers rules, set priority explicitly, use stopIfTrue so the first matching rule wins everywhere, design self-contained rules, and check the order in a saved file and a rendered page.
• How-toHighlight an Entire Row Based on a Cell Value with openpyxlColour a whole row when its Status is Overdue or its amount crosses a limit, using a formula-based conditional format with the right mix of absolute and relative references — so the colour follows the data after edits and sorts.
• How-toHighlight Blank Cells in Excel with openpyxlMake missing values impossible to overlook: highlight empty required fields, whole rows with a gap, or blanks only inside the data. See why ISBLANK misses cells that look empty, how LEN(TRIM()) catches spaces and formulas returning "", and how to stop the rule from colouring every empty row below the table.
• How-toHighlight Cells Above a Threshold with openpyxlDraw the eye to the numbers that matter — CellIsRule for fixed thresholds, FormulaRule for a threshold that lives in a cell, whole-row highlighting, and rule priority.
• How-toHighlight Cells Containing Specific Text with openpyxlColour every status that mentions "late", every reference that begins with INV, or every note containing any word from a list. Write containsText, beginsWith and formula rules with openpyxl, choose between case-insensitive SEARCH and case-sensitive FIND, match whole words so "late" does not match "calculate", and drive the keyword from a cell.
• How-toHighlight Differences Between Two Columns with openpyxlMark every row where system A and system B disagree, or where this month's value differs from last month's sheet. Write the comparison as a conditional format with openpyxl, and decide deliberately how case, blanks versus zero, trailing spaces and rounding differences should count.
• How-toHighlight Duplicate Values in Excel with openpyxlFlag repeated values in a worksheet: the built-in duplicateValues rule, formula rules for duplicates across columns, whole-row highlighting, and a pandas alternative.
• How-toHighlight Overdue Dates in Excel with openpyxlMark overdue and due-soon rows in an Excel tracker with conditional formatting that uses TODAY(), so the colours stay correct every day the file is opened — ignore blank and completed rows, order rules with stopIfTrue, add a days-overdue column and avoid the text-date trap.
• How-toHighlight Top and Bottom Values in Excel with openpyxlMark the top three sales, the bottom 10%, everything above average, or the best rep in each region with conditional formatting written from Python. Covers the top10 and aboveAverage rules, how ties behave, highlighting whole rows, top-N per group with COUNTIFS, and checking the result without Excel.
• How-toHighlight Values Outside a Tolerance of Target with openpyxlColour each actual figure by how far it is from its own target — green within tolerance, amber close to the limit, red outside it — with openpyxl conditional formatting: percentage and absolute tolerances, a tolerance setting readers can change, per-row tolerances, targets of zero, and a recalculated check that the rules flag exactly the rows pandas expects.
• How-toList and Remove Conditional Formatting with openpyxlSee every conditional formatting rule in a workbook — range, type, priority, formula and fill — as a table, then remove rules by range, by type or all at once, collapse duplicates that pile up when scripts rerun, and renumber priorities, all with openpyxl.
• How-toApply Conditional Formatting to a Range in openpyxlAttach a conditional formatting rule to a cell range in openpyxl with conditional_formatting.add(), covering range syntax, CellIsRule, and the FormulaRule fallback.
• How-toUse Conditional Formatting Rules That Reference Another SheetHighlight cells based on lists and settings kept on other sheets with openpyxl: flag orders from customers on a blocked list with COUNTIF across sheets, colour values above a threshold stored on a Settings sheet, use defined names so rules read clearly and work in older Excel versions, anchor references correctly, and verify the rule logic by recalculating a helper formula.
• How-toExtract Hyperlink URLs from Excel Cells with openpyxlpandas reads the text a link displays, never the address behind it. Get the URLs out of Excel cells with openpyxl — inserted links, mailto links, links to other sheets and HYPERLINK formulas — and handle workbooks too large for normal mode.
• How-toExtract Report Header Fields from an Excel Sheet with PythonMany exported reports put the branch, period, currency and run date in label-value pairs above the table. Read those fields with openpyxl wherever they sit, find where the table starts, parse the period into real dates and attach the fields to every row before combining files.
• How-toRead Cell Colours and Font Styles as Data with openpyxlWhen a spreadsheet uses green for done and red for blocked, the colour is the data. Read fill colours, bold and strikethrough with openpyxl and turn them into real columns.
• How-toRead Dropdown Lists and Validation Rules from an Existing WorkbookFind out what a template's dropdowns allow — the regions, statuses and priority codes behind each data validation rule — and check the data people entered against them. Read validation rules with openpyxl, resolve inline lists, range references and named ranges to real values, and report every cell that breaks its rule.
• How-toSkip Hidden and Filtered Rows When Reading Excelpandas reads every row, including the ones hidden by a filter or collapsed in a group. Use openpyxl to find hidden rows, columns and sheets, and read only what the user can see.
• How-toAdd Dropdown Data Validation to Excel with openpyxlWrite Excel dropdown lists from Python with openpyxl DataValidation — inline lists, lists from a lookup sheet or named range, custom error messages, and the 255-character limit.
• How-toCreate Dependent Dropdown Lists in Excel with PythonBuild cascading dropdowns — pick a category, then only its subcategories are offered — from a mapping table in Python: a hidden lookup sheet, one named range per category with safe names, a data validation list for the first column and an INDIRECT-based list for the second, error messages, and a Python check that existing rows still match.
• How-toHighlight Invalid Cells in Excel with PythonMark the exact cells that failed validation with openpyxl — a red fill, a comment explaining the rule, a legend and an issues sheet — so a submitter can fix the file in minutes.
• How-toValidate Excel Rows with pydantic ModelsDescribe what a valid row looks like once, as a pydantic model, and check every row of a sheet against it. Map Excel headers to fields with aliases, constrain values, normalise text before checking, add rules that span columns, and turn validation errors into a report that names the Excel row, column and value.
• How-toHandle the 1904 Date System in Excel Files with PythonSome workbooks count dates from 1 January 1904 instead of 1900, so the same serial number means a date four years and one day later. Detect which system a file uses with openpyxl, convert raw serial numbers with the right origin, combine files that use different systems, and write workbooks in either system.
• How-toCount Rows in an Excel File Without Loading ItFind out how many rows a large workbook has before deciding how to process it — in milliseconds, without reading it into pandas. Read the size the file declares with openpyxl, spot when that size is missing or inflated by formatted empty rows, and count real rows with calamine or by streaming the sheet's XML.
• How-toFind Out Why an Excel File Is Slow to OpenDiagnose a sluggish workbook from Python without opening it in Excel: which parts of the xlsx package take the space, sheets whose used range stretches far beyond the data, thousands of cell styles, piles of conditional-formatting rules and data validations, volatile functions such as INDIRECT and OFFSET, external links, and a one-page diagnosis sheet that ranks what to fix first.
• How-toReduce the File Size of an Excel Workbook with PythonFind out what makes a workbook big by measuring its parts, then shrink it where it counts: downscale oversized images, delete formatted-but-empty rows that inflate the used range, remove archive sheets, and know which popular fixes barely help. Includes measured before-and-after sizes, an openpyxl bug that breaks saving twice, and habits that keep generated reports small.
• How-toSpeed Up openpyxl with read_only ModeHow openpyxl's read_only mode streams a worksheet instead of building it, what values_only=True saves, the limits you accept in exchange, and the file handle you must close.
• How-toStream Excel Rows into a Database with openpyxlLoad a large Excel sheet into a database without ever holding it in memory — read rows with openpyxl's read-only mode, convert and validate each row, insert in batches inside a transaction, divert bad rows to a rejects table and make the load safe to rerun.
• How-toLink Dashboard Cells to Data Sheets with FormulasBuild an Excel dashboard whose figures are live formulas pointing at the data sheets — SUMIFS and COUNTIFS over a table, a named report-month cell readers can change, quoted sheet names, errors handled with IFERROR — so the dashboard updates when the data does, and check the results without Excel.
• How-toFill an Excel Template with Python and openpyxlLoad a prepared .xlsx template with openpyxl, set named cells for title, date and totals, fill a repeating data table from a list or DataFrame, and save a new file.
• How-toGenerate Invoices from an Excel Template with PythonProduce one invoice workbook per customer from a designed Excel template — named cells instead of hard-coded addresses, money calculated with Decimal and a stated VAT rounding rule, invoice numbers that never repeat or skip, a fixed block of line rows with an overflow check, a register of everything issued and a PDF for each invoice.
• How-toRepeat a Template Block for Each Record in an Excel ReportStamp a designed block of rows — a customer statement, a card, a mini-report — once per record down a single sheet with openpyxl, copying styles, merged cells, row heights and conditional formatting, translating formulas to their new rows, growing the block for records with more line items and starting each record on a new printed page.
• How-toCompare Excel Output to a Golden File in TestsTest a whole generated workbook at once by comparing it with an approved "golden" snapshot — not the .xlsx bytes, which change on every save, but a readable text description of every cell's value, number format and style. Includes masking volatile cells, a clear diff when something changes and a --update-golden option for intended changes.
• How-toCreate Excel Test Fixtures in Memory for pytestStop committing sample spreadsheets to the test folder. Build each test workbook in memory with openpyxl — including the awkward shapes real exports have, such as title rows above the header, dates and numbers stored as text, error cells, formulas without results and formatted empty rows — and run one test across all of them with pytest.mark.parametrize.
• How-toProfile a Slow Excel Report Script in PythonFind out where a slow report actually spends its time before changing anything. Time each stage with a small context manager, drill into the slowest one with cProfile and pstats, read the output correctly, and fix the usual culprits — per-cell styling in openpyxl, slow reads, row-by-row writes — measuring the gain each time.
• How-toFormat Numbers in Thousands and Millions in Excel with PythonShow 1,234,567 as 1,235K or 1.2M in an Excel report without changing the stored value — scaling commas in number formats, units in headers or suffixes, auto-switching K and M with conditional sections, negatives and small values, and totals that seem not to add up.
• How-toRead Cell Values as Displayed in Excel with Pythonopenpyxl and pandas return the stored value — 1234.5 — not the text Excel shows, such as (1,235) or 05/03/2026. Get the displayed text two ways — exported by LibreOffice, or formatted in Python by a small function that handles common number and date codes — and check them against each other.
• How-toShow Negative Numbers in Red with Parentheses in ExcelFormat negatives the way finance readers expect — (1,235) in red, aligned with the positives above them — from Python with openpyxl or XlsxWriter. Covers the _) alignment trick, zero as a dash, currency and accounting layouts, reversed colours for costs, the "(0)" trap and parsing parentheses back into numbers.
• How-toAdd a Combo Chart with a Secondary Axis in openpyxlPlot revenue as bars and margin as a line on their own axis — combining chart objects, the y_axis.axId and crosses settings, and when a second axis misleads.
• How-toAdd a Data Table Below an Excel ChartShow the numbers behind a chart in a grid directly underneath it — Excel's chart data table — from Python: switch it on with openpyxl's DataTable or XlsxWriter's set_table, choose borders and legend keys, drop the separate legend, carry the cells' number formats into the table, and check the rendered result. Includes when a data table helps and when data labels or a separate table are better.
• How-toAdd a Trendline to an Excel Chart with PythonAdd linear, moving-average, polynomial and exponential trendlines to Excel line and column charts with openpyxl — forecast periods ahead, show the equation and R², style the line, and put the same fitted numbers into cells with SLOPE, INTERCEPT and RSQ so the trend can be read by people and by code.
• How-toAdd a Line Chart to an Excel Report with PythonAdd an editable line chart to an Excel report with openpyxl: write time-series data, plot multiple series, set a date x-axis, add markers, label axes, and anchor it.
• How-toCreate a Box and Whisker Chart in Excel with PythonExcel's built-in box-and-whisker chart cannot be written by openpyxl or XlsxWriter, but a native, editable box plot can be built from a stock chart: compute quartiles and whisker ends with pandas, map them to open, high, low and close, let up-down bars draw the boxes and high-low lines the whiskers, add a median marker, and list outliers beside the chart.
• How-toCreate a Bubble Chart in Excel with openpyxlPlot three measures at once — margin across, growth up, revenue as bubble size — to see a product portfolio or customer list at a glance. Build a native Excel bubble chart with openpyxl, add one series per group, size bubbles by area, scale them, label each bubble with its name, and keep axis labels clear of negative values.
• How-toCreate a Bullet Chart in Excel with PythonBuild a bullet chart — qualitative bands, an actual-value bar and a target marker — as a native Excel chart from Python: normalise KPIs with different units, draw the bands as stacked columns, draw the actual as a thick error bar and the target as a dash marker on the same axis, tidy the legend and axis, and check the rendered result. Includes why the common secondary-axis recipe is fragile.
• How-toCreate a Butterfly Chart in Excel with PythonBuild a butterfly (tornado, population-pyramid) chart as a native Excel bar chart with openpyxl: plot one side as negative values, overlap the two series on the same rows, hide the minus signs with a two-part number format, move the category labels to the edge, show positive data labels, colour each side, and check the rendered result.
• How-toCreate a Doughnut Chart with openpyxlBuild an Excel doughnut chart from Python: data and categories from a sheet, the hole size, one colour per slice, percentage labels that are readable on dark slices, a start angle, two rings to compare periods, the total shown in the title, and when a bar chart would say it better.
• How-toCreate a Dumbbell Chart in Excel with PythonBuild a dumbbell chart — two dots per category joined by a line, showing a before and after value — as a native Excel chart with openpyxl: a line chart with the lines switched off and markers on, the connecting bars drawn as custom error bars from a hidden helper series, categories sorted by the size of the change, the helper hidden from the legend, and the result rendered to check it.
• How-toCreate a Funnel Chart in Excel with PythonDraw a conversion funnel — visitors to orders, leads to deals, applicants to hires — as an Excel chart from Python: step and overall conversion rates with pandas, a centred stacked bar chart with an invisible spacer series in openpyxl, labels that carry counts and rates, stages top to bottom, and why the native funnel chart type is out of reach.
• How-toCreate a Gantt Chart in Excel with PythonDraw a project or month-end close timeline as a native Excel Gantt chart from a task list. Build it from a stacked bar chart with an invisible start series, put the first task at the top and the dates along the bottom, fix the date range, shade progress, and — as an alternative — paint a Gantt grid with conditional formatting.
• How-toCreate a Gauge Chart in Excel with PythonBuild a speedometer-style KPI gauge from Python with openpyxl: a half-circle doughnut made by hiding half of each ring, coloured red, amber and green bands from your thresholds, a value arc that moves when the KPI cell changes, a helper table of formulas behind it, several gauges on one dashboard, and a render check.
• How-toCreate a Pie Chart in Excel with openpyxlPieChart, data labels that show percentages, exploding a slice, a doughnut variant — and the honest guidance on when a bar chart tells the same story better.
• How-toCreate a Radar Chart with openpyxlCompare several options across the same criteria — vendors, products, candidates, regions — with an Excel radar (spider) chart built from Python: series from a scoring table, a fixed 0–5 scale, visible axes, marker and filled styles, one colour per option, and a weighted score table next to it so the chart is not the only basis for a decision.
• How-toCreate a Scatter Chart in Excel with openpyxlPlot one numeric column against another with openpyxl: ScatterChart and Series, markers without lines, axis titles and scaling, a trendline, and multiple series.
• How-toCreate a Slope Chart in Excel with PythonBuild a slope chart in Excel with openpyxl to compare each item between two periods: lay the data out with one series per item, draw a line chart with only two category points, colour rising and falling items differently, label each line at its ends instead of using a legend, tidy the axes, and check the rendered chart.
• How-toCreate a Stacked Column Chart with openpyxlBuild stacked and 100% stacked column charts in Excel with openpyxl — grouping and overlap settings, series colours, value labels inside each segment, a total label above every stack using an invisible line series, hiding its legend entry, and horizontal stacked bars.
• How-toCreate a Step Chart in Excel with PythonDraw values that change at specific moments and then hold — prices, interest rates, headcount, stock levels — as a step chart in Excel from Python: why a normal line chart misleads with diagonal ramps, how to double up the change points with pandas, build the chart as an openpyxl scatter chart on a real date axis, carry the last value to the end of the period, and compare several series.
• How-toCreate a Stock Chart with openpyxlDraw an open-high-low-close candlestick chart in Excel from Python: the column order Excel requires, hiding the series lines, high-low lines, up and down bars in your own colours, a value axis that does not start at zero, trading days without weekend gaps, and a volume chart underneath.
• How-toCreate a Waffle or Progress Chart in ExcelShow progress towards 100% — share of a target reached, budget used, survey completion — as a waffle chart of 100 cells or as a progress bar, built from worksheet cells and conditional formatting with openpyxl rather than a chart object: number the grid so it fills from the bottom, colour cells with one formula rule driven by the percentage cell, hide the numbers, add data-bar progress bars with fixed 0–100% limits, and render the sheet to check.
• How-toCreate an Area Chart in Excel with openpyxlShow how a total builds up over time — revenue by region, hours by project, stock by warehouse — with a native Excel area chart. Build standard, stacked and 100% stacked area charts with openpyxl, make overlapping areas see-through, keep axes visible in Excel, order series so small ones are not hidden, and handle gaps in the data.
• How-toCreate a Bar Chart in Excel with openpyxlBuild a native, editable Excel bar chart from Python with openpyxl: write sample data, bind it with Reference, set categories and titles, pick a style, and anchor it.
• How-toFormat Chart Axes with openpyxlMake the axes of an openpyxl chart say what you mean: keep them visible in Excel, title them, fix the scale and the step between labels, show thousands as "k", switch gridlines on or off, use a logarithmic scale for fast growth, rotate crowded category labels, and move labels clear of negative bars.
• How-toPlot a Chart from Non-Adjacent ColumnsChart only some of a sheet's columns — dates from column A, actual from D and forecast from G — without copying data around: add each series from its own Reference with openpyxl or its own add_series call in XlsxWriter, take series names from the header cells, find columns by heading instead of letter, keep a chart working when columns in between are hidden, and check the series references in the chart XML.
• How-toPut a Chart on Its Own Chartsheet with openpyxlGive a chart a whole sheet to itself — a full-screen, full-page chart tab that opens first in the workbook, prints on one page and cannot be dragged out of place. Create chartsheets with openpyxl, size the chart so every application shows it properly, make one per region in a loop, colour and order the tabs, protect them and set landscape printing.
• How-toShade a Target Band Behind a Line ChartShow a target range — on-time delivery between 95% and 98%, stock cover between two and four weeks — as a shaded band behind a line in a native Excel chart built with openpyxl: a stacked area chart with an invisible lower series and a coloured band series, a line chart combined on the same axes, band limits driven by two input cells, the helper series removed from the legend, and the weeks outside the band listed next to the chart.
• How-toShow Negative Bars in a Different ColourColour the negative bars of a variance or profit chart red and the positive ones green, from Python: split the values into two helper series that update with the data, colour individual data points when the values are fixed, or use XlsxWriter's invert-if-negative colour for Excel 2010 and later — with what each approach writes into the file, how to check it, and why openpyxl's invertIfNegative on its own turns negative bars white.
• How-toStyle Chart Colours and Data Labels with openpyxlReplace Excel's default chart styling from Python — series fill and outline colours, one highlighted bar, dashed target lines, subtle gridlines, bold coloured data labels, label number formats that survive every viewer, and a single palette function so every chart in a report matches.
• How-toUse Multi-Level Category Labels in an Excel ChartLabel a chart's category axis in two tiers — quarters grouped under years, months under quarters, products under regions — with openpyxl: write the outer label only on the first row of each group, point the categories at both columns as a multi-level string reference, and see why repeated outer labels break the grouping. Rendered to check, with the XlsxWriter equivalent.
• How-toAdd a Total Row to an Excel Table with PythonGive an Excel table a real totals row from Python — the one readers can switch between Sum, Average and Count in Excel — with openpyxl's totalsRowCount and table column settings, SUBTOTAL formulas that respect filters, custom totals, the matching XlsxWriter options and how to keep the totals row out of pandas.
• How-toAdd an Autofilter to an Excel Sheet with openpyxlSet ws.auto_filter.ref so every data row is covered, freeze the header, apply it after pandas writes the sheet, and understand why recorded filter criteria hide nothing.
• How-toAppend Rows to an Excel Table and Resize It with openpyxlAdd new records to an existing Excel table so it grows the way it does when someone types below it: extend the table's range and filter, fill calculated columns with the correct structured-reference formula, carry number formats down, move the totals row, and extend dropdowns — so pivots, charts and formulas that use the table pick the rows up.
• How-toApply Autofilter Criteria and Hide the Filtered Rows with openpyxlopenpyxl can record autofilter criteria, but it never hides a row — the file opens showing everything under a filter that claims otherwise. Write the criteria and hide the non-matching rows yourself so the workbook opens already filtered, with SUBTOTAL totals that match what readers see.
• How-toConvert an Excel Table Back to a Normal RangeDo from Python what Excel's "Convert to Range" does: remove a table object with openpyxl, rewrite the structured references that would otherwise break into ordinary A1 references, keep the banded look by writing it into the cells, keep an AutoFilter if one is wanted, and check with a recalculation that every formula still gives the same answer.
• How-toConvert Several Ranges into Excel Tables at OnceTurn every block of data in a workbook into a proper Excel table in one pass with openpyxl: find the separate rectangular blocks on each sheet, check and repair their header rows so Excel will accept them, give each table a unique valid name from its sheet and heading, skip blocks that are too small or already tables, and verify that nothing overlaps.
• How-toCreate an Excel Table with PythonBuild a real Excel table with openpyxl — Table and TableStyleInfo, header rules that prevent the repair dialog, structured references, extending the range, and reading existing tables.
• How-toGroup and Outline Rows in Excel with openpyxlAdd collapsible groups to a generated report: row and column outlines, nested levels, collapsed-by-default state, summary rows below or above, and subtotal formulas.
• How-toRead an Excel Table by Name into pandaspandas has no option to read an Excel table by its name, but openpyxl knows every table's exact range. Find a table anywhere in the workbook, skip its totals row, get formula results, and load it into a DataFrame.
• How-toRename and Restyle Existing Excel TablesGive the Table1, Table2 and Table3 in an existing workbook meaningful names with openpyxl without breaking anything: rename the table object and its key, rewrite every structured reference to it in cell formulas, defined names, data validation and conditional formatting, refuse names that clash, then apply one table style across the workbook — checked by recalculating the formulas in LibreOffice before and after.
• How-toAdd a Logo Image to an Excel Report with openpyxlStep-by-step openpyxl guide to add a logo to an Excel report: create the image, anchor it at A1, size it to a header band, add a merged title, and save.
• How-toInsert an Image from Memory into Excel with PythonPut images into a workbook straight from bytes — a logo downloaded over HTTP, a base64 string from an API, a photo stored in a database, a picture resized with Pillow — without writing temporary files, using openpyxl or XlsxWriter, and avoid the closed-buffer and format errors that only appear at save time.
• How-toResize and Position Images in Excel with openpyxlPlace a logo or chart image exactly where you want it — pixel sizing, aspect ratio, cell and offset anchors, and why an image moves when a column is resized.
• How-toAdd Comments and Notes to Excel Cells with PythonAnnotate generated Excel reports from Python: attach openpyxl comments, size them, read existing notes back, and use document properties to record where the numbers came from.
• How-toHide Sheets, Rows and Columns with openpyxlTidy a generated Excel report from Python: hide working sheets, use veryHidden, collapse rows and columns, add outline grouping, and avoid the last-visible-sheet corruption.
• How-toLet Different People Edit Different RangesBuild a shared budget workbook in which each team can edit only its own column: Excel's Allow Edit Ranges written with XlsxWriter's unprotect_range, one range and password per team on a protected sheet, what users see when they type in someone else's range, why openpyxl silently deletes these ranges when it saves the file, and a small function that puts them back.
• How-toLock Cells and Protect a Sheet with openpyxlStop readers overwriting formulas in a generated report: unlock the input cells, enable sheet protection, choose which operations stay allowed, and verify it on the way out.
• How-toMake an Excel File Open Read-Only RecommendedStop colleagues from accidentally editing a published report: set the read-only recommended flag with xlsxwriter, add it to openpyxl workbooks with a small post-save patch, mark a workbook as final, keep the flag from being dropped when the file is re-saved, and understand what these settings do and do not protect.
• How-toProtect Workbook Structure with openpyxlStop readers adding, deleting, renaming, moving, hiding or unhiding sheets in a generated report by locking the workbook structure with openpyxl — combined with very hidden lookup sheets and sheet protection — and understand how weak the stored password is and what the lock does not cover.
• How-toRemove Hidden Data Before Sharing an Excel FileA workbook sent outside the team often carries more than the visible numbers: hidden and very hidden sheets, hidden rows and columns, comments with names in them, formulas that point at a salary sheet, named ranges and author details. Audit a workbook for all of it with openpyxl, then build a clean copy with values only, and check the copy before it goes out.
• How-toRemove Sheet Protection from a Workbook You Own with openpyxlUpdate your own protected report templates from Python — turn off sheet and workbook structure protection, make changes, and put the original protection back exactly as it was, including Excel's SHA-512 password hashes. Covers what openpyxl keeps, what "unprotecting" does and does not change, and batch maintenance of many files.
• How-toAdd Alternating Row Colors with openpyxlBand the rows of an Excel report so long lists are easy to follow — with static fills, a MOD(ROW()) conditional format that survives sorting, a SUBTOTAL-based rule that keeps alternating when rows are filtered, table stripes, and banding by group instead of by row.
• How-toAdd Borders to a Range with openpyxlopenpyxl sets borders per cell, and every assignment replaces the whole border. Build helpers that draw a light grid, an outline around a table, a heavy line under the header and a double line above totals — without wiping edges set earlier — and handle merged cells and immutable style objects correctly.
• How-toAdd Hyperlinks to Excel Cells with PythonAttach web, sheet, email and file links with openpyxl and xlsxwriter — including the style line that makes them look clickable, and when the HYPERLINK formula is the better choice.
• How-toApply a Reusable Style Theme Across an Excel ReportStop scattering fonts and fills through your script — define a theme once with NamedStyle, apply it by role, and keep every generated report visually consistent.
• How-toApply Built-In Cell Styles Like Good, Bad and NeutralUse Excel's own built-in cell styles from Python with openpyxl — Good, Bad and Neutral, Title and the headline styles, Input, Calculation, Total, Note and the accent styles: the exact names openpyxl expects, applying them by value, what happens when you add direct formatting on top, how they appear in Excel's Cell Styles gallery, and when conditional formatting is the better tool.
• How-toApply Gradient and Pattern Fills with openpyxlFill cells with solid colours, hatching patterns and two-colour gradients from Python: which colour a solid PatternFill really uses (and why a bgColor-only fill turns black), the pattern types and their foreground/background colours, linear and path GradientFill, fills in conditional formats, removing fills, and a rendered check of what each one looks like.
• How-toChange the Default Font of an Excel Workbook with PythonMake a generated workbook use your house font everywhere — not just in the cells you styled: set XlsxWriter's default_format_properties (also through pandas), understand why changing the Normal style in openpyxl does nothing to existing cells, use a template so openpyxl inherits the right default, keep bold and coloured cells in the same family, and verify the fonts that actually render.
• How-toFreeze the Header Row in Excel with openpyxlFreeze the header row in Excel from Python with openpyxl using ws.freeze_panes. Freeze rows, columns, or both, understand the anchor cell, and unfreeze — runnable.
• How-toHide Gridlines and Set the Zoom Level with openpyxlControl how a generated workbook looks the moment it opens: hide gridlines and headings, set the zoom, choose the selected cell and the first visible row, open in Page Layout view, hide zeros, set right-to-left sheets, and avoid the grouped-sheets trap when changing the active sheet.
• How-toIndent Cells to Show a Hierarchy with openpyxlLay out a chart of accounts, an organisation or a product tree the way people read hierarchies: children indented under their parent, parents in bold with rolled-up totals, and rows grouped so each branch can be collapsed. Work out each row's depth from a parent–child table, set Alignment(indent=...), roll values up the tree and add outline levels with openpyxl.
• How-toMerge Cells and Centre a Report Title with openpyxlAdd a merged, centred title banner to a generated report — merge_cells, styling the anchor cell, the MergedCell read-only trap, and why centre-across-selection is often better.
• How-toRotate Text in Excel Cells with openpyxlAngle header labels so a wide table of months, products or dates fits on screen, turn a side label vertical beside a block of rows, or stack letters top to bottom. Set textRotation with openpyxl — and understand its odd 0–180 and 255 values — size the header row so rotated text is not clipped, and do the same in xlsxwriter.
• How-toSet Column Width and Row Height in openpyxlSet Excel column widths and row heights from Python with openpyxl, build an auto-fit pass by measuring content, hide columns, and set sensible defaults — runnable.
• How-toSet the Print Area and Page Setup with openpyxlMake a generated workbook print properly: print area, landscape orientation, fit to width, repeated header rows, margins, headers and footers, and page breaks.
• How-toStyle Whole Columns and Rows Efficiently with openpyxlFormat entire columns and rows in large openpyxl workbooks without slowing the script down: what column and row styles actually do (and why they do not restyle cells that already hold data), reusing one Font object instead of creating thousands, named styles for the fastest path, styling only the used range, and checking the rendered result and the file's style count.
• How-toWrap Text and Align Cells with openpyxlMake long text readable in a generated Excel report — wrap text inside cells, set row heights that fit the wrapped lines, align text to the top so multi-line rows read cleanly, indent hierarchies, rotate narrow headers, centre a title across columns without merging and shrink text as a last resort.
• How-toWrite Rich Text in a Cell with openpyxlPut more than one format in a single cell — a red, bold OVERDUE inside a status line, a highlighted keyword in a comment, H₂O and m² with real subscripts and superscripts. Build CellRichText from TextBlock and InlineFont objects, generate it from data, read existing rich text back, and avoid the load-and-save that silently flattens it.
• How-toTranslate Common VBA Macros to PythonA phrasebook for moving VBA to Python: the eight patterns that make up most Excel macros — finding the last row, looping over rows, deleting rows that match, lookups with a dictionary, filtering and copying, looping over a folder of files, saving a copy with a date, and error handling — each shown in VBA and in pandas or openpyxl.
• How-toSave a Workbook as an Excel Template with PythonCreate a real .xltx template with openpyxl — styled headers, input cells, dropdowns, formulas and protection — by setting workbook.template = True, check the content type that makes Excel treat it as a template, and avoid the trap of loading an .xltx and saving it as .xlsx without switching the flag back, which produces a file Excel refuses to open.
• How-toWork with Macro-Enabled .xlsm Files in openpyxlEdit .xlsm workbooks from Python without destroying the VBA project: the keep_vba flag, why the filename matters, what openpyxl still drops, and how to verify macros survived.
• How-toRead a Cell Range Like B3:F20 into a pandas DataFramePull exactly one block of cells out of a worksheet — a table that starts at B3 and ends at F20, with other content around it. Do it with read_excel's usecols, skiprows and nrows, or with openpyxl and an A1 range string, and turn it into one reusable function.
• How-toRead a Named Range into pandasLoad the cells behind an Excel named range into a DataFrame: look the name up with openpyxl, resolve workbook- and sheet-scoped names, handle ranges that span several areas or point at #REF!, and read named ranges from files you cannot edit.
• How-toCopy Cell Styles from One Cell to Another with openpyxlCopy fonts, fills, borders, alignment and number formats between cells with openpyxl — within a sheet, across sheets and between workbooks — plus column widths, row heights and whole-row formatting for new data.
• How-toFind and Replace Text in Excel with openpyxlReplace a company name, a product code or a year across every sheet of a workbook without losing formatting: plain and regex replacement, formulas left intact, rich text preserved, and a log of every change.
• How-toFind the Last Row with Data in openpyxlws.max_row counts formatted empty cells, stale dimensions and cells you merely looked at. Find the real last row with data — for the whole sheet or one column — and append in the right place.
• How-toInsert and Delete Rows and Columns with openpyxlAdd and remove rows and columns in an existing Excel file with openpyxl — insert_rows, delete_cols, why formulas and merges do not move, and safe deletion in reverse order.
• How-toIterate over Rows and Columns with openpyxlWalk an Excel sheet cell by cell in Python — iter_rows and iter_cols, values_only, bounded ranges, the max_row trap, and building dicts keyed by header name.
• How-toopenpyxl: Append Data to an Existing Excel SheetAdd new rows to an existing .xlsx file with openpyxl — using ws.append(), explicit row indexing, dict-based sparse appends, type-safe values, and atomic saves.
• How-toRead a Cell Value From Excel With openpyxlRead Excel cell values with openpyxl: load_workbook, ws['B2'].value, ws.cell(), iter_rows, ranges, and data_only for cached formula results instead of formula text.
• How-toSet the Active Sheet, Selected Cell and Tab Colours with openpyxlDecide what a reader sees when a generated workbook opens: which sheet is in front, which cell is selected, where the view is scrolled to, and which tabs are coloured. Includes the trap that leaves two sheets grouped after wb.active changes, and the xlsxwriter equivalents.
• How-toSet Workbook Properties Like Author and Title with PythonGive generated workbooks a proper title, author, company, keywords and custom properties such as the data source or row count. See what openpyxl and xlsxwriter can set, which fields each one overwrites on save, and how to read the properties back for auditing.
• How-toUnmerge Cells and Fill the Values with openpyxlTurn merged cells into a proper table — find every merged range, unmerge it, copy the top-left value into each cell, keep the formatting, and handle the read-only and pandas cases where merge information is missing.
• How-toWhat openpyxl Loses When You Save an Existing Workbookopenpyxl rewrites the whole file when it saves, and features it does not model disappear — sparklines, shapes and text boxes, data-bar and validation extensions, macros, slicers. Tested list, a pre-save risk scan, and safer alternatives.
• How-toAdd SUM and AVERAGE Formulas with PythonGenerate SUM, AVERAGE, SUBTOTAL and conditional aggregate formulas from Python so a total row always covers the data you just wrote, however many rows there are.
• How-toAudit Every Formula in a Workbook with PythonBuild an inventory of every formula in a workbook — which functions it uses, which sheets and external files it depends on, which numbers are hard-coded in it — then find the inconsistent formula in a column of identical ones and the value someone typed over a formula. Output the audit as its own sheet.
• How-toConvert Excel Formulas to Values with PythonFreeze a workbook's formulas into static values with openpyxl while keeping every style, plus the LibreOffice recalculation step for files Excel has never opened.
• How-toCopy a Formula Down a Column in openpyxlFill a formula down every data row from Python — generating references per row, translating an existing formula with openpyxl's Translator, and copying the source cell's style.
• How-toCreate a Named Range in Excel with openpyxlAdd workbook-wide and sheet-local defined names with openpyxl, point them at ranges or single cells, use them in formulas and charts, and list or delete them safely.
• How-toFind External Links and Broken References in a Workbook with PythonAudit a workbook for links to other files and for #REF! errors — in cell formulas, defined names, data validation and conditional formatting — with openpyxl and the zip file itself, then report them or replace them with values.
• How-toMap Formula Dependencies in a Workbook with PythonTrace precedents and dependents across every sheet of a workbook without Excel: parse formulas with openpyxl's Tokenizer, resolve cross-sheet references and named ranges, build a dependency graph, list what feeds a result and what a changed input affects, find unused inputs and circular references, and write the map to a sheet.
• How-toRead Formula Results with openpyxl data_onlyWhy openpyxl returns None for formula cells with data_only=True, how cached values really work, and four reliable ways to get the numbers a workbook is supposed to contain.
• How-toUse Structured Table References in Excel Formulas from PythonWrite formulas that refer to Excel table columns by name — =SUM(Sales[Total]), calculated columns, SUMIFS over table columns — from openpyxl and xlsxwriter, including the [@Column] shorthand that must be written out in full.
• How-toWrite Dynamic Array Formulas like FILTER and UNIQUE from PythonWrite spilling Excel formulas — FILTER, UNIQUE, SORT, SEQUENCE, XLOOKUP — from Python so they work when the file opens. Why openpyxl output shows #NAME? or an @, what the _xlfn prefix and array metadata are, and how xlsxwriter handles both.
• How-toWrite a Formula to an Excel Cell with openpyxlAssign a formula string to a cell with openpyxl, avoid the Text-format trap that shows the formula instead of a result, and verify what actually landed in the file.
• How-toWrite Formulas That Reference Other Sheets with openpyxlLink a summary sheet to its data sheets with formulas like =SUM('Sales Data'!B2:B50). Learn the quoting rules for sheet names with spaces and apostrophes, build references with a helper instead of f-strings, and avoid the openpyxl trap where renaming a sheet silently breaks every formula that pointed at it.
• How-toWrite Legacy Array Formulas (Ctrl+Shift+Enter) with openpyxlSome formulas only work as array formulas — SUM over a product of two ranges, MAX(IF(...)) with a condition — and written as ordinary formulas they silently return the first row's answer. Write them with openpyxl's ArrayFormula for one cell or a block, read them back, write them with XlsxWriter, verify the results with LibreOffice, and know when a dynamic array formula is the better choice.
• How-toWrite LET and LAMBDA Formulas from PythonLET and LAMBDA written from openpyxl or xlsxwriter show #NAME? unless they are stored the way Excel stores them: _xlfn. before the function and _xlpm. before every parameter name. A helper that builds the stored form, named LAMBDA functions as defined names, and how to check the result.
• How-toWrite Lookup Formulas (XLOOKUP, VLOOKUP, INDEX/MATCH) with openpyxlGenerate live lookup formulas that pull prices, names or rates from another sheet: VLOOKUP with an exact match, INDEX/MATCH, and XLOOKUP — which must be written with the _xlfn prefix or it shows #NAME? — plus absolute ranges that survive copying, not-found handling, tiered approximate matches, a named lookup range, and a LibreOffice check that every formula returns what pandas expects.
• How-toApply the Same Change to Every Sheet in a WorkbookMake one change across every worksheet of a workbook — or of a folder of workbooks — with openpyxl: style the header row, freeze it, set number formats by column heading, add a footer and a print setup, while skipping chart sheets, hidden sheets and sheets that do not have the expected layout, logging what was changed on each sheet and making the run safe to repeat.
• How-toCopy a Sheet Between Excel Workbooks with Pythonopenpyxl's copy_worksheet only works inside one workbook — here is what actually happens when you copy across files, how to carry values and styles over, and when to copy the file instead.
• How-toList Every Sheet with Its Size and VisibilityProduce an inventory of a workbook, or a folder of workbooks, with one row per sheet: visible, hidden or very hidden, chart sheet or worksheet, the used range Excel reports against the range that actually holds data, counts of formulas, tables, charts, images, merged cells and validation rules, protection, and the space each sheet takes inside the file — written to an Excel report.
• How-toMerge Workbooks into One File with a Sheet per SourceCollect a folder of workbooks into a single file where each source keeps its own tab: turn file names into valid, unique sheet names under the 31-character limit, copy values quickly with pandas or copy values, styles, widths and merged cells with openpyxl, handle source files with several sheets, and add an index sheet that links to every tab.
• How-toRename, Reorder and Delete Excel Sheets with openpyxlSheet housekeeping that does not break the workbook: the 31-character title rules, move_sheet and the index list, safe deletion, hiding instead of removing, and which tab opens first.
• How-toSave Each Sheet of a Workbook as a Separate FileSplit a workbook into one .xlsx file per sheet while keeping each sheet's formatting: remove the other sheets with openpyxl, freeze formulas that point at sheets that will no longer exist, drop defined names that would dangle, make hidden sheets visible, build safe file names, and know when a values-only split with pandas or a full-fidelity split through Excel is the better tool.
• How-toSearch Every Sheet and Workbook in a Folder for a ValueFind which workbook, sheet and cell contain an invoice number, a customer name or a pattern across a whole folder of Excel files. Match exactly, by substring or by regular expression, treat 1042 and "1042" as the same, skip lock files and broken files, and search thousands of workbooks quickly in read-only mode.
• How-toSum the Same Cell Across Many Sheets with 3D References from PythonBuild a summary sheet that totals the same cell on every monthly or regional tab with Excel 3D references like =SUM(Jan:Dec!B2), written from Python with openpyxl — bookend sheets, quoting rules, inserting new tabs in the right place, and which functions support 3D ranges.
• How-toWrite a List of Dictionaries to Excel Without pandasWrite records from an API, a database cursor or your own code straight to .xlsx with openpyxl: a header from the union of keys, missing keys left blank, nested values flattened, dates and decimals kept as real Excel types, write-only mode for large lists, and a formatted table at the end.

⚖️ Compare the options

Which tool or approach to pick, and why.

• Comparisonopenpyxl vs pandas for Excel Automationpandas reads Excel through openpyxl, so they are not rivals. See where the line falls — rows versus cells — and how to hand off between them in one script.
• ComparisonWhen to Use xlwings Instead of openpyxlxlwings drives a real copy of Excel; openpyxl edits the file. Four jobs justify the cost — recalculation, objects openpyxl drops, live workbooks and native output.
• ComparisonPython in Excel vs openpyxl and xlwingsPython in Excel runs code inside a cell; openpyxl and pandas write files from a script; xlwings drives the Excel application. Compare where each runs, what it can reach and which jobs it suits.
• Comparisonopenpyxl vs xlsxwriter vs pandas.ExcelWriterUnderstand how pandas.ExcelWriter wraps the openpyxl and xlsxwriter engines, when each one wins, and write the same DataFrame three ways with a clear decision table.

🔧 When it breaks

The errors this topic produces, with the cause explained rather than just the fix.

• FixAdd Error Bars to an Excel Chart with openpyxlShow uncertainty and ranges on Excel charts written from Python: compute confidence intervals from raw data with pandas and SciPy, attach custom error bars that read their lengths from cells, draw asymmetric min/max ranges, use fixed and percentage error bars, style the bars, and check the result by rendering the workbook.
• FixFix "At least one sheet must be visible" in openpyxl and pandasopenpyxl refuses to save a workbook in which no sheet is visible: "IndexError: At least one sheet must be visible", or for a single sheet "The only worksheet of a workbook cannot be hidden". Both usually come from a pandas ExcelWriter that wrote no DataFrames, or from hiding data sheets. Find which case you have, and guard against empty output.
• FixFix "ValueError: Cannot convert to Excel" in openpyxlopenpyxl accepts only values Excel can store — numbers, text, booleans, dates, None. Lists, dicts, sets, UUIDs and pandas' NA raise "Cannot convert … to Excel". Find the offending values, convert them deliberately, and write DataFrames without surprises.
• FixFix the "Data Validation extension is not supported" Warning in openpyxlWhat openpyxl's "Data Validation extension is not supported and will be removed" warning means, when it is harmless, how to silence it narrowly, and how to rebuild the dropdowns it drops before you save the workbook.
• FixFix "Excel Found Unreadable Content" After Writing with PythonYour script saved a workbook and Excel offers to repair it. The rules Excel enforces — sheet names, table names, defined names, timezones — and how to satisfy them.
• FixFix IllegalCharacterError When Writing Excel with openpyxlopenpyxl refuses to write text containing control characters such as \x00 or a vertical tab. Find which cells hold them, decide what each character should become, and clean the data so the write succeeds.
• FixFix "MergedCell object attribute value is read-only" in openpyxlopenpyxl raises this AttributeError when code writes to a cell that sits inside a merged range. Find the merge, write to its top-left cell, and watch for the less obvious cause — insert_rows and delete_rows move cells but leave merged ranges behind.
• FixFix #NAME? in Formulas Written from Python (XLOOKUP, TEXTJOIN, IFS)Formulas that work when typed into Excel show #NAME? when openpyxl writes them. Newer functions must be stored with an _xlfn. prefix, and misspelled or localised names, missing defined names and unquoted text cause the same error. Lint every formula before saving.
• FixFix NoneType Errors When Reading Cells with openpyxlWhy openpyxl hands your code None — empty cells, formulas that were never calculated, merged ranges, trailing formatted rows — and how to fix each one: find which cell is None, skip or default empty values deliberately, get formula results with data_only after a recalculation, and read merged ranges from their top-left cell.
• FixFix "openpyxl does not support the old .xls file format"Why openpyxl refuses .xls files, how to read them with xlrd or calamine, how to convert a whole folder to .xlsx, and how to spot a mislabelled file before it fails.
• FixFix openpyxl max_row Counting Empty Rowsws.max_row reports 2,000 on a sheet with six rows of data, and ws.append() writes far below the table. Learn the three things that inflate max_row — formatted cells, cells created by reading, and stale dimension tags — and how to find and fix the real last row.
• FixFix openpyxl "TypeError: expected <class ...>"openpyxl checks the type of every style, chart and dimension attribute, and raises "TypeError: expected <class 'float'>" or "expected <class 'openpyxl.styles.fills.Fill'>" when it gets the wrong kind of value. Read the class name, find the assignment, and pass the right object — including the case where the error only appears at save time, far from the line that caused it.
• FixFix openpyxl "Value must be one of" and "Max value is" Errorsopenpyxl validates style and settings values against the names Excel uses internally, so Side(style="bold"), Alignment(horizontal="middle"), PatternFill("Solid") and orientation="Landscape" all fail with ValueError: Value must be one of {...}. Translate the familiar names to the accepted ones, use openpyxl's constants, handle "Max value is" and colour errors, and watch for values that are only checked at save time — after half a file has been written.
• FixFix ReadOnlyWorksheet Has No Attribute Errors in openpyxlSwitching to load_workbook(read_only=True) for speed breaks code that used iter_cols, merged_cells, column_dimensions, comments, hyperlinks or writes — and EmptyCell objects have no coordinate. See exactly what read-only mode supports, rewrite column access and gap handling for streaming, get the missing metadata from a second normal load, and know when read-only is the wrong mode.
• FixFix "Workbook contains no default style" Warning in openpyxlopenpyxl warns "Workbook contains no default style, apply openpyxl's default" when a file's stylesheet lacks a Normal style. The data is fine — but the related "no stylesheet" warning can turn dates into numbers. Tell them apart, silence the harmless one precisely and repair the files.
• FixFix "Worksheet Does Not Exist" KeyError in openpyxlWhy openpyxl raises KeyError on a sheet you can see in Excel: hidden whitespace, case, renamed tabs and index confusion — plus a resilient sheet lookup helper.
• FixFind #REF!, #DIV/0! and Other Formula Errors in a Workbookpandas turns formula errors into NaN, so a broken workbook can look merely incomplete. Scan every sheet with openpyxl for cells whose cached value is an error, catch #REF! inside formula text even without a recalculation, and fail a report job before it sends a workbook full of errors.

🧭 Follow it through a track

Prefer an ordered route? These tracks cover openpyxl along the way, in the order that builds on itself.

• 🎨Formatting & charting75 of this topic's pages
• 🌱Getting started71 of this topic's pages
• 🧹Data transformation32 of this topic's pages
• 🚀Reporting workflows8 of this topic's pages

🔗 Related topics

Subjects that keep turning up on the same pages as this one, most overlap first.