Excel Combine Sheets with Same Name into One File

Updated 7 November 2026 · Standard formula, shown below

Yes, you can combine multiple Excel sheets that have the same name into one file, but Excel does not have a built-in one-click command for it. You can do it manually by copying and pasting, or more efficiently with Power Query, a free tool included in modern Excel versions. This article explains both methods, when to use each, and how to avoid common pitfalls.

Why You Might Need to Combine Same-Named Sheets

You often end up with several workbooks that share the same sheet name, like “Sales”, “Data”, or “Report”. For example, you might receive one file per month from different regions, each containing a sheet named “Data”. Combining them into a single workbook lets you analyze all the data together, create a single chart, or build a consolidated report. Without combining, you would have to open each file separately and manually merge the contents, which is time-consuming and error-prone.

Manual Method: Copy and Paste

The simplest way to combine same-named sheets is to copy the data from each sheet and paste it into a new sheet in one workbook. Here are the steps:

Open the first workbook and go to the sheet you want to copy.
Select all the data (Ctrl+A or Cmd+A) and copy it (Ctrl+C or Cmd+C).
Open the destination workbook, create a new sheet, and paste the data (Ctrl+V or Cmd+V).
Repeat for each additional workbook, pasting the data below the existing data on the same sheet.

This works fine for small datasets that you only need to do once. However, it becomes tedious if you have many files or if the data changes regularly, because you would have to repeat the process every time.

Power Query: The Better Way

Power Query (called “Get & Transform Data” in Excel) is a powerful tool that can combine multiple files with the same structure automatically. It is available in Excel 2016 and later, and in Microsoft 365. Here is how to use it to combine sheets with the same name from multiple workbooks:

In Excel, go to the Data tab and click Get Data > From File > From Folder.
Browse to the folder that contains all your workbooks and click OK.
In the dialog that appears, click Combine & Transform Data.
Excel will show a preview of the files. Select the sheet name you want to combine (for example, “Data”) from the drop-down list.
Click OK. Power Query will load all the data from that sheet across all files into a single preview.
Click Close & Load to put the combined data into a new worksheet.

Power Query automatically refreshes the data if you add new files to the folder, as long as you refresh the query (Data > Refresh All). This makes it ideal for recurring reports.

Using VBA to Automate the Task

If you are comfortable with macros, you can use VBA (Visual Basic for Applications) to combine sheets with the same name. This is useful when you need to combine sheets from a specific list of workbooks, not necessarily all files in a folder. A simple macro can loop through each file, copy the sheet, and paste it into the active workbook. However, VBA requires some programming knowledge and can be risky if you are not careful, as it can modify files permanently. Always test on a copy of your data first.

Common Problems and How to Avoid Them

When combining sheets, you may run into a few issues:

Headers repeated: If each sheet has a header row, you will end up with multiple header rows in the combined data. In Power Query, you can promote the first row as headers and then filter out the repeated headers.
Different column orders: If the sheets have the same name but different column layouts, Power Query will still combine them, but the columns may not align properly. Check the preview carefully.
Data types: If a column is numeric in one file and text in another, Power Query may convert everything to text. You can change the data type in the query editor.
Large files: Combining many large files can slow down Excel. Consider using Power Query’s “Load to Data Model” option to keep the data compressed.

Comparing Manual, Power Query, and VBA

Here is a quick comparison to help you choose the right method:

Manual copy-paste: Best for one-time, small datasets. No special skills needed.
Power Query: Best for repeated use, many files, or when you want a refreshable connection. Requires Excel 2016 or later.
VBA: Best for complex logic, such as combining only certain sheets or applying transformations. Requires programming knowledge.

Frequently Asked Questions

Can I combine sheets with different names?

Yes, Power Query can combine sheets with different names, but you would need to use the “Combine & Transform Data” option and select each sheet name manually, or use a custom function. The process is similar, but you may need to adjust the sheet selection step.

Will Power Query work with .xls files?

Power Query can read .xls files, but you may need to enable the appropriate data provider. In most cases, it works fine, but for older files, you might need to convert them to .xlsx first if you encounter issues.

How do I refresh the combined data when I add new files?

If you used Power Query, simply go to the Data tab and click Refresh All. The query will re-read the folder and include any new files that match the pattern.

Can I combine sheets from different folders?

Yes, you can use Power Query to combine files from multiple folders by using the “From Folder” option on each folder and then appending the queries. Alternatively, you can copy all files into one folder for simplicity.

What if the sheets have different headers?

Power Query will combine them by position, not by header name. If the headers differ, you may end up with columns that have different names. You can rename columns in the query editor to align them.

Is there a way to combine sheets without losing formatting?

Manual copy-paste preserves formatting. Power Query is designed for data, not formatting, so it will not carry over colors, fonts, or column widths. If you need formatting, use the manual method or VBA.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.