The easiest way to combine Excel spreadsheets depends on how many files you have: for a one-time, small job, copy and paste the matching rows; for several files or repeat updates, use Power Query. In Excel for Microsoft 365 on Windows, Power Query can append tables into one list and refresh it later without repeating the combination by hand.
Choose the right kind of combining
“Combine” can mean different things in Excel. Most people want to stack records from multiple sheets or workbooks into one longer table—for example, putting January, February, and March sales under the same headings. This is called appending. If you instead need to bring a detail, such as a customer’s region, beside a matching customer ID, that is a lookup or a query merge. The steps differ, so decide which result you need before importing anything.
For a quick decision, use these guidelines:
- A few small ranges, one time: copy the data rows and paste them beneath the first set of rows.
- Several workbooks with the same columns: use Power Query to append them.
- A recurring monthly or weekly task: keep the source files in a consistent folder and use Power Query’s folder connector, then refresh.
- Data that must match by an ID rather than stack: use a lookup or merge operation, not append.
Before combining, make a backup copy of important files. Combining data can expose duplicates, inconsistent labels, or missing values; it does not automatically decide which record is correct.
Fastest for a small one-time job: copy and paste
When there are only two or three short lists and their columns already match, manual copying is usually the simplest approach. In the destination sheet, retain one header row, then copy only the data rows from each source and paste them directly below the existing records. For instance, if each monthly sheet has columns for Date, Product, and Amount, copy the February and March records without their header rows beneath January’s data.
On Windows, select the range and press Ctrl+C, then select the destination cell and press Ctrl+V. On Mac, use Command+C and Command+V. To avoid bringing unwanted formatting along, use Home > Paste > Values after copying, or choose the Values paste option from the paste menu. Check that dates, numbers, and identifiers still have the expected formats.
This approach is easy but not especially reliable for repeated work. If a source list changes, you must copy it again, and it is easy to omit a row or paste a header halfway down the combined data. Manual paste also does not keep a live connection to the source workbook. Choose it for a simple, verifiable one-off task, not for a process that will happen every month.
Use Power Query to append workbooks
Power Query is Excel’s built-in tool for importing and reshaping data. It is useful when several workbooks contain similar tables because it can combine the rows and save the steps as a query. These directions are for Microsoft Excel for Microsoft 365 on Windows.
- Open a new or destination workbook and select Data > Get Data > From File > From Workbook.
- Choose the first source workbook. In the Navigator, select the relevant Excel table or sheet, then choose Transform Data to open the Power Query Editor.
- Repeat the import for the other workbooks, or use the folder method below if there are many files.
- In Power Query Editor, select Home > Append Queries > Append Queries as New. Choose Two tables or Three or more tables, select the tables to stack, and confirm.
- Review the combined preview, then select Home > Close & Load to return the result to Excel.
Appending puts records from one table beneath records from another. Power Query matches columns by their header names, not simply by their left-to-right positions. That helps when one workbook has columns in a different order, but it also means inconsistent headings can create separate columns. If one file calls a field “Customer ID” and another calls it “Cust ID,” rename one heading before the final load so the values land in the same column.
Combine many files from a folder
For recurring files with a consistent layout, the folder method can save the most time. Put the source workbooks in one folder, and avoid placing unrelated workbooks or the final output file there. In Excel on Windows, choose Data > Get Data > From File > From Folder, browse to the folder, and select Combine & Transform Data. Excel presents a sample-file process; confirm the sheet or table to use and inspect the Power Query preview before loading.
The query applies the same transformation to the files it finds in the folder. When another compatible file is added, select Data > Refresh All to update the combined output. This is not the same as merging files into one permanent source workbook: Power Query reads the inputs and produces a result table. Keep the original files in place if the query depends on that folder path.
Folder imports work best when every file has the same table structure: one header row, consistent column names, and data in the same kind of table or sheet. Files with extra title rows, different sheet names, or unrelated content may need cleanup or a more deliberate query setup. Test with a small sample folder before relying on the process for a large set.
Prepare columns and headers before combining
A little preparation prevents many common errors. Convert each source range into an Excel table by selecting a cell in the range and choosing Insert > Table, then confirm whether the table has headers. Tables are easier to identify in the Navigator and typically expand as new rows are added. Use clear, unique column names and remove blank title lines above the headers.
Check that corresponding columns use compatible data types. A date stored as a real Excel date in one file but as text in another may not sort or calculate consistently. Likewise, product codes with leading zeros should generally be treated as text; otherwise a code such as 00427 can become 427. In Power Query, inspect the type icon in each column heading and set appropriate types after the data is combined.
Also agree on basic conventions: whether blank cells mean “unknown” or “not applicable,” how dates are represented, and whether each source has a unique record ID. A combined table can contain repeated records if source files overlap. Appending does not automatically deduplicate rows or reconcile conflicting versions of the same record.
Append versus merge: a common point of confusion
Use Append Queries when the tables have the same kind of records and you want more rows. For example, append three regional order tables if each has Order ID, Date, and Amount. Use Merge Queries when you want to match rows using a shared field and bring columns from one table into another. For example, merge an orders table with a customer table using Customer ID to add the customer’s region.
A merge needs a suitable matching key. If Customer ID is duplicated in the customer table, one order may match multiple customer records, producing more rows than expected. Before merging, check whether the key is unique in the table that is supposed to contain one record per customer. Excel’s Power Query Editor offers Home > Merge Queries; choose the matching columns in both previews and select the appropriate join kind. This differs from merging cells with the Merge & Center button, which changes worksheet layout and is not a way to combine datasets.
Formula option for a small, changing set of sheets
If the data is already in the same workbook and you want a live stacked result, Microsoft 365 and Excel 2024 include the VSTACK function. With Sheet1!A1:C3 containing headers and two data rows, and Sheet2!A1:C3 containing the same headers and two more rows, enter this in the destination sheet’s A1 cell:
=VSTACK(Sheet1!A1:C3,Sheet2!A2:C3)
The result is five rows by three columns: one header row followed by the four records, with the second sheet’s header deliberately excluded. VSTACK spills the result into neighboring cells, so keep the spill area clear. This is convenient for a couple of fixed ranges, but you must update the formula when adding another sheet or extending a range. For a larger changing collection of files, Power Query is usually easier to maintain.
Refresh, troubleshoot, and keep the result dependable
If a Power Query result does not show a new file or changed source data, first select Data > Refresh All. Then check that the source workbook was saved, the file is still in the expected folder, and the query points to the correct location. If a new column is missing, verify its heading and whether the query’s applied steps or sample-file setup need adjustment.
Common warning signs include blank columns, errors in a type-conversion step, and unexpectedly duplicated rows. Blank columns often point to inconsistent headers or spacing in names. Conversion errors can occur when one file contains text in a column that Power Query treats as numeric. Duplicates may be legitimate repeated transactions, overlapping exports, or accidental copies; compare IDs and source files rather than deleting rows blindly.
For a dependable workflow, keep untouched source files, save the query workbook separately, and document what files belong in the input folder. After loading, compare the combined row count with the source totals and spot-check several records from each file. If other people will use the workbook, explain how to add files and refresh without changing the column structure.
Mac and Excel for the web differences
The steps above describe Excel for Microsoft 365 on Windows. Power Query is available in Microsoft 365 for Mac, but available connectors and commands can differ by Excel version and update channel. If you do not see the same From Workbook or From Folder choices, check the Data tab on your Mac build and its current Power Query support; for a folder-based workflow with the Windows connector, use Excel on Windows or choose a manual method that your Mac version supports.
Excel for the web also has a different feature set and interface from the Windows desktop app. Do not assume that every desktop import connector or query-editing command is available in the browser. If the workbook already contains a query, web refresh support can depend on the data source and account or workbook setup. For a critical recurring process, create and verify the query in the desktop version intended to maintain it.
Frequently Asked Questions
How do I combine multiple Excel files into one sheet?
For a few files, copy each file’s data rows below one header row. For repeatable work, import the files with Data > Get Data and append them in Power Query, or use From Folder when the files share a consistent structure.
Can Excel combine spreadsheets automatically?
Power Query can combine compatible source tables and refresh the result. It does not automatically fix mismatched headings, decide which duplicate is correct, or include files that are outside the query’s selected source location.
Should I append or merge tables?
Append when you want to stack similar records as additional rows. Merge when you need to match records using a shared key and add columns from another table.
Will combining spreadsheets remove duplicate rows?
No. Appending adds rows and can preserve duplicates. Review repeated records using a reliable ID or other fields, and remove duplicates only when you know which records should be considered identical.
Can I combine sheets with different column orders?
Power Query append matches fields by column name, so differing order is usually manageable. Make the header names consistent first; columns with different names may be treated as separate fields rather than combined.
Related
- Convert an Excel Binary Worksheet (.XLSB) to Excel
- Content Calendar Template Excel: Create and Use One
- Ctrl+G in Excel: Open Go To and Select Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Emergency Light Checklist Template in Excel: What to IncludeYes. An emergency light checklist template in Excel can track each fixture’s location, inspection dates, test results, defects,…
- Google Sheets Merge Cells Shortcut and Menu OptionsGoogle Sheets does not have a dedicated keyboard shortcut that directly merges selected cells. In a browser, open…
- How to Indent a New Line in an Excel Cell: Easy StepsTo indent just one new line in an Excel cell, edit the cell, insert a line break with…
- How to Autofit a Column in Excel to Fit Its ContentsTo AutoFit a column in Excel for Microsoft 365 on Windows, point to the right edge of its…
- Excel vs CSV Row Limits: How Many Rows Can Each Hold?Excel allows up to 1,048,576 rows per worksheet. CSV has no fixed row limit in its format, so…
- Excel Paste Text Without Text to Columns?Yes. To paste tab- or line-break-separated text into a single Excel cell without splitting it across columns or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.