How to merge multiple sheets with same headers in Excel?
Merging data from multiple Excel sheets that share the same headers is a common and often time-consuming task. For example, as shown in the screenshots below, you might have several worksheets—such as for different months, departments, or products—within one workbook, all containing information organized by identical column headers. When you need to review, analyze, or report on all this data in a consolidated manner, manually copying and pasting information is inefficient and error-prone, especially as the number of sheets grows. Fortunately, Excel offers several practical methods to help you merge such data quickly, accurately, and with minimal effort. By understanding the advantages of each approach, you can choose a solution that's best suited for your workflow, whether you want a one-time merge or a dynamic, automatically updating consolidation.
Available solutions:
Merge sheets with same headers by VBA
Consolidate excel sheets with same headers by Consolidate function
Merge or consolidate excel sheets with same headers by Kutools for Excel 
Merge data using Excel formulas (INDEX, MATCH, FILTER)
Combine and append data with Power Query (Get & Transform)
Merge sheets with same headers by VBA
If you want to merge all sheets with identical column headers into one sheet quickly and without any calculation or summarization, VBA provides an automated solution. This approach works well for users who are comfortable enabling macros and want to handle repetitive merging tasks in a single workbook with data starting from cell A1 and a consistent data structure across all sheets.
Operation steps:
1. Activate the workbook where you want to merge the sheets. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. In the VBA window, click Insert > Module. Then, paste the VBA code provided below into the code window.
VBA: Merge sheets with same headers
Sub Combine()
'UpdateByKutools20151029
Dim i As Integer
Dim xTCount As Variant
Dim xWs As Worksheet
On Error Resume Next
LInput:
xTCount = Application.InputBox("The number of title rows", "", "1")
If TypeName(xTCount) = "Boolean" Then Exit Sub
If Not IsNumeric(xTCount) Then
MsgBox "Only can enter number", , "Kutools for Excel"
GoTo LInput
End If
Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
xWs.Name = "Combined"
Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
For i = 2 To Worksheets.Count
Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
Next
End Sub3. Press F5 or click the Run button to execute the code. A prompt will appear, asking you to enter the number of title rows (for example, enter "1" if your headers occupy the first row only). See screenshot:
4. Click OK. The code will then automatically merge all data from the worksheets in the active workbook (excluding the specified header rows) into a new sheet named “Combined”.
Tips and notes:
- Your data must start from cell A1 on each sheet. If the data begins in a different location, the code will not function properly.
- All sheets to be merged should have the same structure (same headers in the same order and, ideally, in the same columns) to avoid data misalignment.
- This code merges all worksheets in the currently active workbook only. It will not merge sheets from other workbooks.
- Save your workbook before running the code. If you have unsaved work, running VBA could cause accidental loss of data if errors occur.
Advantages: Suitable for fast one-off merges directly within a workbook. No formulas or third-party add-ins required.
Limitations: Manual code execution required for each merge. Not ideal if you need to combine data from multiple workbooks, or require regular, automated updating.
Consolidate excel sheets with same headers by Consolidate function
When your goal is to merge and summarize data that shares the same column and row headers (such as totals from multiple regions or departments), the built-in Consolidate feature in Excel offers a structured solution. It allows for various types of summary calculations, such as summing, averaging, or counting values, all while using header labels to align data from different ranges.
Operation steps:
1. Open all the workbooks that contain the worksheets you plan to merge.
2. Place your cursor in a blank cell where you want the merged (consolidated) data to appear.
3. Go to the Data tab and click Consolidate.
4. In the Consolidate dialog:
- Select the calculation you need from the Function list (e.g., Sum, Average, Count).
- Click in the Reference box, then select the range you wish to consolidate from each worksheet. After selecting each range, click Add to include it in the list.
- Repeat the above step to add all ranges from all sheets you want to merge.
- Check Top row and Left column under Use labels in if your data has headers in those areas. This ensures matching and correct merging by labels.
- If you want the output to stay linked to the source data (so updates in original sheets reflect here), enable Create links to source data.
5. Click OK. Excel will summarize and align the selected data ranges by the specified function, merging them into the cell location you chose.
Practical tips:
- Use the Create links to source data option if you want consolidated results to update automatically when source data changes.
- Make sure the headers are truly identical in all ranges (including spelling and spacing), otherwise consolidation by label will not work as intended.
- For large or complex data, it is recommended to copy the original data to a backup sheet before consolidating.
Advantages: Versatile and built into all modern Excel versions. Good for summary calculations and periodic reporting.
Limitations: Not truly dynamic—if you add new source rows later, you'll need to repeat the consolidation steps. Can be confusing if labeling is inconsistent or ranges are not properly set.
Merge or consolidate excel sheets with same headers by Kutools for Excel
If your workflow involves merging sheets with identical headers or simultaneously consolidating and calculating data from multiple locations, Kutools for Excel offers a flexible and convenient solution. Kutools provides a dedicated Combine wizard, which not only supports multi-level data merging but can also handle data from both open and unopened workbooks, making the process much easier and less error-prone.
Merge excel sheets with same headers
Consolidate multiple sheets with same headers
After installing Kutools for Excel, follow these steps:（Free Download Kutools for Excel Now!)
Merge excel sheets with same headers
1. Go to the Kutools Plus tab and click Combine to start the Combine wizard. In the wizard, select Combine multiple worksheets from workbooks into one worksheet. See the screenshots below for reference:
2. Click Next >> to move to the Combine Worksheets - Step 2 of 3 wizard, then:
2) Select the workbook(s) containing the sheets you want to merge; you can select multiple files.
3) Check the worksheets you wish to include.
4) Click
to specify the data range for each worksheet. If all sheets use the same cell range, selecting it on one sheet and clicking Same range will synchronize it across all.3. Click Next >> to proceed to Combine Worksheet – Step 3 of 3, then check Combine by row option and enter 1 for the Title row number (or 0 if your data has no headers).
4. Click Finish. When prompted about saving the merge scenario for future reuse, select Yes (to save) or No (to discard).
The wizard will create a new workbook with all the specified sheets merged together, maintaining header alignment.
Consolidate multiple sheets with same headers
After installing Kutools for Excel, proceed as follows:（Free Download Kutools for Excel Now!)
1. Choose Kutools Plus > Combine to open the Combine wizard, and select Consolidate and calculate values across multiple workbooks into one worksheet. See screenshot:
2. Click Next> > to access the Combine Worksheets - Step 2 of 3 wizard. Follow these actions:
2) Tick the workbooks you wish to work with (multiple selections possible).
3) Select desired worksheets.
4) Click
to define which data ranges to consolidate. Use Same range for consistent range positions across sheets.3. Continue with Next>>, choose the function (such as sum or average) you want to use in merging the ranges, and specify which labels (top row and/or left column) apply to your merged data. See screenshot:
4. Click Finish. You will be asked whether to save your scenario for later use; choose Yes or No as needed.
The selected ranges are then consolidated and output to a new workbook.
The big advantage of Kutools' Combine function is its ability to handle complex merging needs across multiple open or closed workbooks, making it ideal for both one-time tasks and regular workflows that require data integration from disparate sheets.
Merge data using Excel formulas (INDEX, MATCH, FILTER)
For users who prefer maintaining dynamic links between original sheets and the combined data, Excel formulas provide a flexible, formula-driven solution. This method uses functions such as INDEX, MATCH, or FILTER (available in Microsoft 365 and Excel 2021+) to merge and align data across multiple worksheets with the same headers. It's especially valuable when the sheets being merged are regularly updated, as the results in the merged sheet will automatically reflect any changes in source sheets.
Applicable scenario: Best used when you want the merged data to remain up to date as the underlying sheets change, without using macros or add-ins.
Formulas Example - Simple Merge With FILTER (Excel 365/2021+)
1. On your new "Combined" sheet, select the first cell where you want to list data (for example, cell A2), and enter the following formula to stack data from Sheet1. (assuming headers are in row 1):
=FILTER(Sheet1!A2:D100,Sheet1!A2:A100<>"")This pulls all non-blank rows from Sheet1. Adjust the range A2:D100 as needed.
To merge with more sheets, use VSTACK (modern Excel):
=VSTACK(FILTER(Sheet1!A2:D100,Sheet1!A2:A100<>""),FILTER(Sheet2!A2:D100,Sheet2!A2:A100<>""),FILTER(Sheet3!A2:D100,Sheet3!A2:A100<>""))2. Press Enter, and the results will spill down, combining all selected sheets. Any updates or new rows in the original sheets will automatically appear.
Tips:
- Be sure all merged sheets have identical headers and the data ranges are sufficiently large to include all possible rows. Adjust the referenced ranges as your data grows.
- If using Excel 2019 or earlier, FILTER and VSTACK are not available. Consider using INDEX/MATCH to pull individual rows, or copy and paste as values.
- If you want to keep headers only once, manually copy the header row above the formula output.
Advantages: Merged data updates automatically with changes in source sheets. No need for VBA or third-party tools.
Limitations: Modern functions like FILTER and VSTACK are available only in the latest Excel versions. Large datasets can impact calculation performance. Formulas require careful range management as data grows.
Combine and append data with Power Query (Get & Transform)
Power Query (also known as Get & Transform) is a robust, built-in feature available in Excel 2016 and later, and in Microsoft 365. It enables you to connect, transform, and combine data from multiple sheets or workbooks without modifying the original data. This approach is ideal for recurring consolidation tasks, handling large datasets, or cleaning and shaping data before merging.
Applicable scenario: Highly recommended for advanced merging needs, especially with frequent data updates, large files, or when you need to clean/transform data during the merge.
Operation steps:
- Click anywhere inside your first data table. Go to Data > From Table/Range. If your sheet is not formatted as a Table, Excel will prompt you to create one. Click OK.
- Click Close & Load > Close & Load to load the result to a new sheet. Now you get a new Table sheet.
- Repeat the above steps for each worksheet to create a separate query for each.
- Combine Queries: In the Queries & Connecruibs pane, right click on a table and select Append from the context menu.
- In the Append dialog box, if you need to append more than two tables, select the Three or more tables option. Add each table individually to the Table to append box, then click OK.
- Finish and load: Now all data in the specified worksheets have been appended. After verifying and adjusting the order or columns as needed, click Home > Close & Load to output the combined data to a new worksheet.
Tips:
- Data does not need to be in the same workbook; Power Query can connect and append data across multiple workbooks.
- If you add new data to the original sheets or files, simply right-click the result table and select Refresh to update the merge automatically.
- You can use Power Query's transformation tools to clean, filter, and shape data during loading, which is especially helpful if some sheets have extra columns or need formatting harmonization.
Advantages: Handles large and complex merges with minimal manual intervention. Highly scalable, supports data transformation during merging, and automates future updates.
Limitations: Initial setup requires several steps and familiarity with Power Query tools. Some versions of Excel may not include full Get & Transform features.
Summary and troubleshooting tips:
- For fast, one-off merges where data starts in identical positions in each sheet, VBA or Kutools can be very efficient.
- If regular, dynamic updates and connectivity to growing datasets are crucial, consider Excel formulas or Power Query solutions.
- Always confirm that your source data shares the same headers and structure to avoid errors in merging.
- Where possible, use features like Power Query's Refresh or formula auto-updating for ongoing tasks instead of manual “copy-paste.”
- Before conducting any merge, make backups of your original data to safeguard against accidental overwrites or errors.
- If “#REF!” or “#VALUE!” errors appear, double-check your formula ranges and ensure all referenced sheets exist and contain valid data.
- If using VBA or add-ins, ensure your antivirus and macro settings allow for their operation.
- Experiment with a sample workbook before applying solutions to important or production files.
Related Articles
- How to combine multiple cells with leading zeros into a cell in Excel?
- How to generate a list of all possible4 digits combinations in Excel?
- How to save each sheet as separate text file from a workbook?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in