The fastest way depends on how many sheets you have and whether the data is identical in structure

If all your sheets have the same columns in the same order, you can combine them in Excel itself without leaving the program. If the sheets have different structures or you need to do this regularly, a formula-based approach or a pivot table works better. The method you choose also depends on whether you want to preserve the original sheets or delete them after merging.

Most people can finish a merge in under five minutes using one of the three methods below. The manual copy-paste approach works for two or three sheets. The formula method scales to dozens of sheets. The pivot table method is best when you need to summarize data across sheets rather than just stack it.

Key Takeaways

  • Copy and paste works fastest for two or three sheets with identical column layouts, and takes less than five minutes.
  • A formula using INDIRECT and CONCATENATE can pull data from multiple named sheets automatically, even if you add new sheets later.
  • Pivot tables let you combine sheets and summarize the data at the same time, which is useful when you need totals or counts across all sheets.
  • Power Query (in Excel 2016 and later) can append multiple sheets in one step and is the most reliable method for large numbers of sheets.
  • Always keep a backup copy of your original file before merging, in case you need to refer back to the separate sheets.

Copy and paste method for a few sheets

Open the file that contains all your sheets. Click the tab of the first sheet you want to merge. Select all the data in that sheet by clicking the top-left corner cell selector (the box above row 1 and to the left of column A), or press Ctrl+A. Copy the selection with Ctrl+C.

Create a new sheet by right-clicking any sheet tab and selecting "Insert Sheet", or click the plus icon next to the sheet tabs. Name it something like "Combined" or "Master". Click on cell A1 in the new sheet and paste with Ctrl+V. Now go back to the second sheet you want to merge. Select all its data the same way, copy it, then click on the first empty row in your combined sheet and paste.

Repeat this for each additional sheet. If the sheets have headers (column titles), delete the duplicate headers from the second and later pastes — keep only the headers from the first sheet. When you are finished, you can delete the original sheets by right-clicking their tabs and selecting "Delete Sheet", or leave them in place if you want to keep a record.

Formula method for sheets with consistent structure

This method works when you have many sheets with identical columns and you want the merge to update automatically if you add new data. First, create a new sheet called "Combined". In cell A1, type the headers from one of your source sheets.

In cell A2, enter this formula: =IFERROR(INDIRECT("Sheet1!"&ADDRESS(ROW()-1,COLUMN())),"")&IFERROR(INDIRECT("Sheet2!"&ADDRESS(ROW()-1,COLUMN())),"")&IFERROR(INDIRECT("Sheet3!"&ADDRESS(ROW()-1,COLUMN())),"")&"". Replace "Sheet1", "Sheet2", and "Sheet3" with your actual sheet names. This formula pulls data from the same cell position in each sheet and stacks it vertically.

Copy this formula down to cover all the rows you expect to have data. The formula will skip empty cells and stop when it runs out of data. If you add new sheets later, you can edit the formula to include them. This method is more work to set up but requires no manual updates once it is running.

Power Query method for large numbers of sheets

If you have Excel 2016 or later, Power Query is the most reliable way to merge many sheets at once. Open your file and go to the Data tab in the ribbon. Click Get Data (or New Query in older versions), then select From File and choose From Workbook.

Select your own file. A window will open showing all the sheets in your workbook. Hold Ctrl and click each sheet you want to combine. Click Load. Power Query will open a preview showing all the selected sheets stacked together. Click Close & Load to create a new sheet with the combined data.

Power Query automatically handles headers and mismatched column names. If some sheets have extra columns that others do not, Power Query will include all columns and fill missing cells with blanks. This method is the fastest for 10 or more sheets and the least error-prone.

Pivot table method to combine and summarize at once

Use a pivot table when you want to merge sheets and also count, sum, or group the data by category. First, combine your sheets using one of the methods above — the pivot table will work on the combined data. Then select all the combined data including headers.

Go to the Insert tab and click Pivot Table. Choose to create the pivot table in a new sheet. Drag the fields you want to analyze into the Rows, Columns, and Values areas. For example, if each sheet contains sales by region, you can drag Region to Rows and Sales Amount to Values to see totals by region across all sheets at once.

Pivot tables are especially useful when the original sheets have the same structure but you want a summary view rather than a line-by-line list. You can refresh the pivot table if the source data changes, and create multiple pivot tables from the same combined data to view it different ways.

What to do if sheets have different column layouts

If your sheets do not have the same columns in the same order, you cannot use the straightforward copy-paste or formula methods. Instead, use Power Query, which can match columns by name even if they appear in different positions. Power Query will create a combined sheet with all unique column names and fill in blanks where a sheet does not have data for that column.

Alternatively, manually rearrange the columns in each sheet to match before merging, or create a new sheet and use formulas to pull specific columns from each source sheet in the order you want. This takes longer but gives you complete control over which columns appear in the final result and in what order.

Keeping your file organized after the merge

After merging, decide whether to keep the original sheets or delete them. If you keep them, move them to the end of the sheet tabs so the combined sheet is the first thing someone sees when they open the file. You can also hide the original sheets by right-clicking the tab and selecting "Hide" — they stay in the file but do not appear unless someone unhides them.

Name your combined sheet clearly: "Master", "All Data", or "Combined" are all better than "Sheet4". If you created a pivot table, put it on its own sheet and name that sheet too. Add a note at the top of the combined sheet explaining when it was created and which sheets it contains, so anyone using the file later understands what they are looking at.

Frequently Asked Questions

Can I merge sheets from different Excel files?

Yes. Open all the files you need, then use Power Query to pull data from multiple workbooks at once. Go to Data > Get Data > From File > From Workbook, select the first file, then repeat the process for additional files. Power Query will combine them into a single sheet.

What happens to formulas when I copy and paste sheets together?

Formulas copy over and adjust automatically if they reference cells within the same sheet. Formulas that reference other sheets will still point to those sheets. If you delete the original sheets after merging, those formulas will break and show #REF! errors. Use the formula method or Power Query instead if your data contains cross-sheet references.

How do I merge sheets if they have different numbers of columns?

Power Query handles this automatically — it will include all columns from all sheets and fill missing cells with blanks. If you use copy-paste, manually add empty columns to the sheets with fewer columns first, so all sheets have the same structure before you merge.

Can I undo a merge if I make a mistake?

Yes, as long as you have not saved the file. Press Ctrl+Z to undo the last action, or press it multiple times to undo back to before the merge started. If you have already saved, you can close the file without saving the changes and reopen the original version.

Is there a way to merge sheets automatically every time I open the file?

Power Query queries refresh automatically when you open the file if you set them to do so. Right-click the combined sheet, select "Refresh", and Power Query will pull the latest data from all source sheets. You can also set up a macro using VBA to merge sheets on demand, but that requires programming knowledge.