Formulas & Excel logic
Formulas in workbooks
Writing formulas with openpyxl, reading cached results, named ranges and recalculation.
Start with the full guides
The long-form treatment of formulas in workbooks, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toStore Formula Results with xlsxwriter So Every Viewer Shows Valuesxlsxwriter writes formulas with a cached result of 0, so pandas, openpyxl, previewers and even LibreOffice's command-line conversion show zeros until Excel recalculates. Compute the results in Python and store them with the formula, keep them consistent, and stop text that starts with "=" from becoming a formula.
- 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-toHandle Circular References and Iterative Calculation in PythonFinancial models often contain deliberate circular references — interest on an average cash balance, a bonus calculated on profit after the bonus. Detect cycles in a workbook from Python, enable Excel's iterative calculation with openpyxl (iterate, iterateCount, iterateDelta), see what happens without it, and solve the same circularity in Python by fixed-point iteration or algebra so reports do not depend on iteration settings.
- 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-toRecalculate Excel Formulas Without Excel in PythonGet formula results when openpyxl returns None: recalculate with the formulas library or LibreOffice headless, or restructure so the values are computed in Python.
- How-toRun What-If Scenarios on an Excel Model with PythonKeep the Excel model as the single source of truth and let Python drive it: define scenarios as input values, write one copy of the model per scenario with openpyxl, recalculate them all in one headless LibreOffice call, read the outputs back, build a sensitivity table and write a scenario comparison workbook.
- How-toTurn an Excel Model into a Python FunctionKeep a profit model in Excel where finance owns it, and call it from Python like a function: compile the workbook's formulas with the formulas library into a callable that takes the input cells and returns the output cells, check its answers against LibreOffice, run two thousand Monte Carlo scenarios in a few seconds, and catch functions the library cannot evaluate before trusting the results.
- 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-toGoal Seek in Python Instead of ExcelReplace Excel's Goal Seek with scipy.optimize: find the price that hits a profit target, the interest rate behind a loan payment, a break-even value for every row of a DataFrame, and even goal-seek an Excel model itself by recalculating it in LibreOffice — with brackets, tolerances and failure checks.
- How-toCheck That Totals in a Report Add UpAutomatically check a finished Excel report before it is sent: every total row equals the sum of the rows above it, every total column equals the sum across, the grand total cross-foots both ways, the summary sheet agrees with the detail sheet, and differences within a rounding tolerance are accepted — with each failure reported by sheet and cell so it can be fixed.
- How-toCall Excel Worksheet Functions Through Microsoft GraphUse Excel's own calculation engine from Python without Excel installed — call worksheet functions such as PMT and VLOOKUP on a workbook in OneDrive or SharePoint through Microsoft Graph, pass values and ranges as arguments, read results and Excel errors, and reuse a session for many calls.
- How-toRecalculate Excel Online Workbooks with Graph SessionsUse workbook sessions to make multi-call Graph jobs fast and consistent, force a recalculation before reading results, run what-if scenarios that are never saved, and handle slow session creation.
- How-toBuild a Break-Even Analysis in Excel with PythonBuild a live break-even workbook from Python: contribution margin, break-even units and revenue and margin of safety as Excel formulas driven by input cells, a multi-product break-even based on the sales mix, a price-by-cost sensitivity grid, and a cost-volume-profit chart, all checked against the same figures computed in pandas.
- How-toBuild a Loan Covenant Compliance ReportTest bank loan covenants every quarter with pandas: last-twelve-month EBITDA and interest from monthly accounts, net debt at the quarter end, leverage, interest cover and minimum liquidity against their limits, the headroom on each covenant expressed as how far EBITDA or cash could fall before a breach, a forward look using the forecast, and a compliance workbook that shows its formulas.
- How-toAdd a Dropdown Filter That Drives an Excel DashboardGive an Excel dashboard a slicer-like control from Python — a data validation dropdown fed by a hidden list, an "All" option, SUMIFS formulas and a chart that follow the selection, and a second filter combined with the first — because native slicers cannot be written by openpyxl or XlsxWriter.
- How-toBuild a Rolling 12-Month Chart on an Excel DashboardGive an Excel dashboard a chart that always shows the latest twelve months, built from Python with openpyxl: a helper block of EDATE and SUMIFS formulas anchored on the newest month in the data, an optional override cell for looking back, a prior-year comparison line, and checks with LibreOffice that the window moves when new months arrive.
- 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-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 Calculated Columns to an Excel Table with PythonAdd a calculated column to an existing Excel table with openpyxl: find the table, widen its range and autofilter, register the new column with a calculated-column formula so Excel fills it into rows added later, write the formula into every row with portable structured references, add a total, and verify the results by recalculating.
- How-toRefresh Excel Data Connections and Recalculate with win32comRefreshAll returns before the data arrives. Make refreshes synchronous, recalculate with the right level, refresh pivot caches in order, and verify nothing went stale.
- 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-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-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-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 Excel Formulas from a pandas DataFramePut live formulas into a sheet written with to_excel: build formula strings per row, look up column letters by name so the formulas survive column changes, account for the header, index and startrow offsets, add a totals row, understand why the results read back as 0 or NaN until the file is recalculated, and turn the formula conversion off for text that merely starts with "=".
Compare the options
Which tool or approach to pick, and why.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- 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.
- FixHighlight Cells with Formula Errors Using Conditional FormattingMake #DIV/0!, #N/A, #VALUE! and #REF! results stand out in a generated workbook with openpyxl conditional formatting: a rule that colours any error, a separate softer rule for expected #N/A lookups, a whole-row highlight driven by an error in one column, and a Python check that lists every error cell after recalculation so problems are found before the report is sent.
- FixFix Formula Cells Reading as NaN (or 0) in pandaspd.read_excel returns NaN — or a suspicious 0 — for every formula cell in a workbook generated by a script, while the same file shows the right numbers in Excel. The cause is a missing cached result, not a bad read. Detect it, then fix it by recalculating with LibreOffice, computing the values in pandas, or writing cached results when the file is created.
- FixFix Formulas Showing as Text in Excel Output from PythonA workbook written by Python shows "=SUM(B2:B9)" in the cell instead of the result. Check whether the cell was stored as a formula or as text, then fix the cause: a leading space or apostrophe, a writer told not to convert strings to formulas, text written with write_string, or a sheet saved with Show Formulas switched on.
- 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.
Follow it through a track
Prefer an ordered route? These tracks cover formulas in workbooks along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Formulas in workbooks is part of Formulas & Excel logic.