How to Combine Multiple Sheets Into One in Excel

The setup

You have a workbook with monthly sheets named Jan, Feb, Mar, … or branch sheets named North, South, East, West. Every sheet has the same column structure (same headers, same shape) and you want to combine them into one big table for analysis, pivoting, or sending to a colleague.

Excel doesn't have a one-click "combine all sheets" button. The usual options are:

1. Copy-paste manually. Sheet by sheet. If you have 12 monthly sheets it's tedious; if you have 50 branch sheets it's a nightmare.
2. Power Query. Powerful, but the setup takes a few minutes per workbook and has a learning curve. Great if you'll do this repeatedly with the same shape; overkill for a one-off.
3. A VBA macro. Fastest path for a one-off, no setup.

The free tool

Download Combine All Sheets Into One. One .xlsm file with one macro. No install, no signup, no internet connection required.

How it works:

1. Open your data workbook (the one with the sheets you want to combine) alongside the macro workbook.
2. Click into your data workbook so it's the active one.
3. Run the macro from Alt + F8.
4. When asked, click Yes if your sheets all have the same headers in row 1 (most common case). Click No if there are no headers.
5. A new sheet called Combined is created at the front of your workbook with all the data stacked. With "Yes" selected, it keeps the header row from the first sheet and skips the duplicate headers from subsequent sheets.

What it does (and doesn't) preserve

• Values: Yes, all data values are copied.
• Formulas: Copied as-is. Formulas that reference other sheets may break, so paste-as-values first if that's a concern.
• Formatting: Cell colors, fonts, borders are preserved.
• Sheet order: Sheets are processed left to right (in the tab order of your workbook).
• The original sheets: Untouched. The macro is read-only on the source data.

When this macro isn't the right tool

If your sheets have different column structures (some have a Phone column, others don't, etc.), simple stacking will misalign the data. In that case:

• Either standardize the headers across all sheets first (rename columns, add missing ones), then run the macro.
• Or use Power Query's Append Queries feature, which can map columns by name across sources.

If you need to combine sheets from multiple workbooks (not just multiple sheets in one workbook), you'd need a different approach: Power Query or a more sophisticated macro. This tool only handles sheets within a single workbook.

Going the other direction: split, not combine

If you ever need the inverse operation (break one big sheet into many sheets, one per unique value in some column), see Split Sheet by Column Value. Same workflow, opposite direction.

FAQ

Will the macro modify my source sheets? No. The source sheets are read-only. Only a new "Combined" sheet is created.

What happens if a "Combined" sheet already exists? It's deleted and replaced with the fresh result. Save your previous Combined sheet under a different name if you want to keep it.

How big a workbook can it handle? Excel's row limit is just over a million. As long as your stacked data fits, the macro works. Performance is good up to a few hundred thousand rows.

Does this work on Excel for the web? No. Excel for the web doesn't run VBA. This is a desktop-only tool (Windows or Mac).

Can I run it on a workbook that has hidden sheets? Yes. Hidden sheets are processed too. If you want to skip them, unhide them first to verify what's included, then re-hide before running.