Combine all sheets into one in Excel

The procedure described below is available in the free version of !SEMTools (Lite and Pro).

If you have dozens of sheets with tables of the same structure, such as CRM exports or reports by store, region or month, you need to combine them for analysis. Doing it by hand is tedious and error-prone.

How to combine all sheets into one

The algorithm is simple:

• go through all sheets of the current workbook in order;
• copy all non-empty cells from each sheet (values, without formulas);
• paste the data one under another on a new summary sheet, leaving the source sheets unchanged;
• ideally, add a column with the sheet name next to the data, so you know where each row came from.

With a formula in Excel 365

In Excel 365, VSTACK with a 3D reference stacks the same range from several sheets. The sheets must be next to each other, from Jan to Dec in this example:

=VSTACK('Jan:Dec'!A2:D100)

Empty rows of the ranges come along too; wrap the formula in FILTER to drop them. The result is a dynamic formula, not values: if you delete the source sheets, it breaks. And it doesn’t add the sheet names.

With a VBA macro

This macro creates a “Combined” sheet, copies the used range of every sheet as values one under another and writes the sheet name in column A:

Sub CombineAllSheets()
    Dim ws As Worksheet, target As Worksheet, src As Range, nextRow As Long
    Set target = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    target.Name = "Combined"
    nextRow = 1
    For Each ws In Worksheets
        If ws.Name <> target.Name And Application.CountA(ws.Cells) > 0 Then
            Set src = ws.UsedRange
            src.Copy
            target.Cells(nextRow, 2).PasteSpecial xlPasteValues
            target.Cells(nextRow, 1).Resize(src.Rows.Count, 1).Value = ws.Name
            nextRow = nextRow + src.Rows.Count
        End If
    Next ws
    Application.CutCopyMode = False
End Sub

If all sheets have a header row, the headers will repeat; filter them out afterwards. Store the code in the Personal Macro Workbook to use it in any file.

With !SEMTools in one click

The “Combine sheets of the active workbook into one” tool in !SEMTools does all of the above in one action. Adding the column with sheet names is optional: a dialog asks whether you want it. The result is a clean summary table where each block is the content of one sheet. You can move it to a new workbook or keep it in the current one and delete the other sheets. Formatting is kept, and formulas are converted to values.

Use cases

• preparing data for pivot tables and dashboards;
• processing many CSV exports with the same structure;
• combining regional or periodic reports of the same kind;
• building a single dataset for filtering and analysis.

Scenario 1: CSV exports

Say you have many CSV files of the same kind, daily or regional reports. Open them all in Excel (each becomes a workbook with one sheet), use Merge all open workbooks into one to collect all sheets into one workbook, and then run “All sheets into one” to get a single table. If you need a single CSV file instead, see how to merge CSV files.

Scenario 2: several workbooks with different sheets

You have several workbooks open, each with several sheets, and not all of them are needed. For example, you want to combine only sheets named “Data”, “Sales” or “Report”. Open all the workbooks, use the procedure that deletes sheets of open workbooks by condition to keep only the sheets you need, then merge the open workbooks, and finally run “All sheets into one”.

Summary

I hope you found what you were looking for on this page and were able to combine your sheets. VSTACK with a 3D reference works in Excel 365 for adjacent sheets, a macro gives you values with sheet names, and the free !SEMTools procedure does it in one click without code. You may also be interested in how to combine all columns into one.