Reading & writing workbooks
Many sheets & many files
Combine a folder of workbooks, split one by value, copy and reorder sheets.
Start with the full guides
The long-form treatment of many sheets & many files, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toConsolidate Timesheets from Many Workbooks with PythonEveryone fills in the same timesheet template and sends it back; you need hours by project and person for the week. Read the name and week from each file's header cells, unpivot the day grid into rows, spot who has not submitted, catch unknown project codes, impossible days and short weeks, and write one consolidated workbook.
- 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-toCombine Multiple Excel Files into One in PythonMerge many .xlsx files into one workbook with pandas — glob the folder, concat with name-based column alignment, track the source file, and export the result.
- 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-toCreate a Sheet for Each Group in a DataFrameSplit one DataFrame into one worksheet per region, customer or month with groupby and ExcelWriter — with safe sheet names, a contents sheet, consistent formatting, and the silent overwrite trap when two groups map to the same name.
- 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-toRead All Sheets from an Excel File into DataFramessheet_name=None returns a dictionary of every sheet — how to use it, why one ExcelFile beats repeated read_excel calls, how to filter and concatenate sheets, and what it costs on a large workbook.
- 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-toSplit One Excel Sheet into Multiple Files by Column ValueBreak a combined export into one workbook per region, customer or month — groupby, safe filenames, sheets vs files, and keeping formatting on every split output.
- 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-toCombine Sheets with Different Layouts into One TableStack sheets that hold the same kind of data in different layouts — headers on different rows, columns in a different order and under different names, extra columns, total rows — into one clean table with pandas: find each sheet's header row, map column synonyms to standard names, drop totals and notes, record the source, and report columns that could not be mapped instead of losing them.
- How-toAppend New Rows to a Master Excel File Without DuplicatesWeekly extracts overlap — the same invoices appear in this week's file and last week's — and appending them blindly fills the master workbook with duplicates. Find genuinely new rows with a key-based anti-join, decide what to do with rows that changed, append with openpyxl so the master's formatting and table survive, keep reruns idempotent, and log every run.
- How-toConcatenate Excel Sheets with Different ColumnsStack monthly exports whose columns drift — align headers, map renamed fields, keep a source column, and report what each file was missing before you combine them.
- How-toMerge Excel Files with Different Column Names in pandasTwo systems export the same customers under different headers — "Customer No" and "cust_id", "Email" and "email_address" — with keys formatted differently. Map both files to one schema, normalise the keys, merge on several columns and combine the overlapping fields with a clear rule for which source wins.
- How-toMerge More Than Two Excel Files on a Key ColumnCombine several workbooks that describe the same records — HR, payroll, IT and training exports keyed by employee ID — into one wide table: chain merges with functools.reduce, keep keys that appear in only some files, avoid _x/_y column collisions, record which files each key came from, catch duplicate keys before they multiply rows, and write the result with a coverage summary.
- How-toRead Multiple Excel Sheets with PolarsRead the month sheets of a workbook into one Polars DataFrame — list sheet names without loading data, pick sheets by pattern, force consistent column types across sheets, stack them with the right concat mode, turn sheet names into dates and repeat across many workbooks.
- How-toProcess Multiple Excel Files in Parallel with PythonUse every core on a folder of workbooks with ProcessPoolExecutor — why threads do not help, how to return small results, error isolation per file, and when parallelism makes things worse.
- How-toSplit a Large Excel File into Files of N RowsBreak a big workbook into smaller ones for an upload limit, an import tool or an email attachment: the simple pandas version, a streaming version with openpyxl read-only and write-only modes that keeps memory flat, the header repeated in every part, dates kept as dates, numbered file names, and a check that no row was lost or duplicated.
- How-toConsolidate Employee Expense Claims into a ReportTurn a folder of employee expense claim forms into one checked report with pandas and openpyxl: read header cells and the claim table from each form, convert foreign-currency lines, apply policy rules such as category limits, missing receipts and duplicate claims, summarise by employee and cost centre, and write a workbook with an exceptions sheet for the approver.
- How-toWrite Multiple DataFrames to One Excel FileWrite several pandas DataFrames into one .xlsx: separate sheets via one ExcelWriter, stacking on a single sheet with startrow, and appending with mode='a'.
- How-toIgnore Excel Lock Files When Processing a FolderStop a folder-processing job from crashing on the files Excel and LibreOffice leave next to open workbooks: recognise ~$ owner files, .~lock files and temporary save files, skip half-copied workbooks that are not yet valid zip files, notice when a workbook is open in someone's Excel, and report what was skipped and why.
- How-toGenerate One Excel Report per Region in a LoopSplit one dataset into many workbooks safely: a filename that cannot collide or break a filesystem, one failure that does not stop the batch, atomic writes, an empty-group decision, and a manifest of what was produced.
- How-toWatch a Folder and Process New Excel Files with PythonProcess each workbook as soon as it lands in a drop folder. Watch the folder with watchdog, ignore Excel's lock and temporary files, wait until a file has finished copying, hand files to a single worker through a queue, move them to processed or failed folders, pick up anything that arrived while the watcher was down, and poll instead on network shares.
- How-toCreate One Chart per Group with openpyxlTurn a long table — sales by region and month — into a grid of small charts, one per region, generated in a loop: write each group as its own block, build a chart that references only that block, lay the charts out in a grid with computed anchors, give every chart the same axis scale so they can be compared, or put each group on its own sheet with its chart.
- How-toRead Excel Files Inside a ZIP Archive with PythonRead workbooks straight out of a .zip without unpacking it: list the members, open one with zipfile and pass it to pd.read_excel, loop over every workbook while skipping __MACOSX and lock-file junk, read archives downloaded into memory, handle password-protected ZIPs, and understand the errors pandas gives when handed the archive itself.
- How-toRead Excel Sheets Whose Names Change with pandasFind the right sheet when its name changes every month, differs in case or spacing, moves position or hides behind a hidden tab — by pattern, by date in the name, by visibility or by the columns it contains.
- 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-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.
When it breaks
The errors this topic produces, with the cause explained rather than just the fix.
- FixFix Invalid Worksheet Name Errors When Writing ExcelSheet names built from data — regions, customers, dates — break Excel's rules: 31 characters at most, none of [ ] : * ? / \, no leading or trailing apostrophe, unique regardless of case. See what openpyxl, xlsxwriter and pandas do with each rule and write one function that makes any name safe.
- 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.
Follow it through a track
Prefer an ordered route? These tracks cover many sheets & many files 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.
Many sheets & many files is part of Reading & writing workbooks.