xlsx

Use openpyxl to write formula strings directly into cells, such as sheet['B10'] = '=SUM(B2:B9)'. Because openpyxl stores no cached values, recalculate the file with LibreOffice afterward so pandas and previewers can read the computed results.

Use openpyxl when you need formulas, formatting, or cell-level edits; use pandas read_excel and to_excel for bulk data movement. For a quick look at sheet contents, markitdown renders each sheet without cell coordinates.

openpyxl writes formulas as strings with no cached values, so data_only=True returns None until the file is recalculated. Running LibreOffice headless recalculation computes every formula and rewrites the file in place.

Excel-2007-era functions like SUMIFS, INDEX, MATCH, IFERROR, and SUMPRODUCT work directly. Six newer functions need an _xlfn. prefix (TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, MINIFS), while XLOOKUP, XMATCH, SORT, FILTER, UNIQUE, and SEQUENCE must be avoided entirely.

Yes, pass keep_vba=True to load_workbook so the VBA project is preserved on save. Without that flag, saving an .xlsm file strips its macros permanently.

openpyxl strips cached values from cells referencing other files, and LibreOffice then fails to resolve them, writing #NAME? and deleting the links. Copy those cells' values from the original before saving, since recalc.py refuses to run in that state without --force.