To combine multiple Excel sheets into one, first decide whether you want to stack all records, calculate a summary, or match related data. For a repeatable master list, use Power Query’s Append command; for a simple formula-based result in current Excel, use VSTACK. Use Consolidate only when you want totals or other summaries, not every original row.
Choose the right way to combine your sheets
Suppose January, February and March sheets each contain transactions with the columns Date, Customer, Product and Amount. Stacking them creates one long transaction list. Summing Amount by region creates a summary. Adding customer details from a separate Customers sheet is a join. These are different jobs, so the right Excel tool depends on the result you need.
| What you want | Best fit | What to expect |
|---|---|---|
| Put records from several sheets underneath one another | Power Query Append | A refreshable master table |
| Stack a few similarly shaped ranges with a formula | VSTACK | A formula result that recalculates when referenced cells change |
| Combine a few small sheets once | Copy and paste | A static result; later source edits must be copied again |
| Calculate totals, averages or counts | Data > Consolidate | A summary, not a transaction-level list |
| Add fields from a related table using an ID | Power Query Merge or a lookup such as XLOOKUP | Related columns matched by a common key |
| Combine recurring workbooks saved in a folder | Power Query From Folder | A refreshable result based on files in the selected folder |
Power Query is the strongest general choice when the process will be repeated: it can append tables with different row counts and load the result to a worksheet. Microsoft says Append aligns fields by column-header name, not by column position. Microsoft’s Append guidance explains the operation.
Prepare the source sheets before combining them
Clean, consistent source data prevents many common problems. In each sheet, use one header row and keep the actual records in a simple list: avoid decorative title rows, merged cells, subtotals and blank rows within the data. Microsoft’s guidance on combining sheets recommends list-formatted data with column labels and no blank rows or columns for consolidation workflows.
#1 Best Overall
- Classic Office Apps | Includes classic desktop versions of Word, Excel, PowerPoint, and OneNote for creating documents, spreadsheets, and presentations with ease.
- Install on a Single Device | Install classic desktop Office Apps for use on a single Windows laptop, Windows desktop, MacBook, or iMac.
- Ideal for One Person | With a one-time purchase of Microsoft Office 2024, you can create, organize, and get things done.
- Consider Upgrading to Microsoft 365 | Get premium benefits with a Microsoft 365 subscription, including ongoing updates, advanced security, and access to premium versions of Word, Excel, PowerPoint, Outlook, and more, plus 1TB cloud storage per person and multi-device support for Windows, Mac, iPhone, iPad, and Android.
- Make equivalent fields use the same names, such as Customer ID everywhere—not a mix of Customer ID, CustomerID and Cust. ID.
- Keep data types consistent: dates should be dates, amounts numbers, and IDs formatted consistently.
- Remove wholly blank rows and columns from the data area.
- Decide whether the finished output should have one header row. In a normal combined table, the header appears once, not once per source sheet.
- For Power Query, convert each source range to an Excel Table: click in the range, press Ctrl+T, confirm My table has headers, then use Table Design > Table Name to give it a distinct name such as
JanuarySales.
Combine sheets with Power Query Append
When Append is the right operation
Append adds rows from one query after rows from another. Use it when each sheet contains the same kind of records and you want them in one list. It is not the same as Merge: Merge joins related tables on matching values in a common column, such as an order ID. Microsoft describes Merge as a join based on a shared column in its Merge queries guidance.
Power Query is available in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on supported platforms. Microsoft notes that Power Query is not supported in Excel 2016 or Excel 2019 for Mac; check its Power Query availability information if your edition or platform is uncertain.
Build a refreshable master table
- Convert each source range into an Excel Table and give each table a distinct name, as described above.
- Click inside a source table and choose Data > From Table/Range to open Power Query Editor.
- Choose Home > Close & Load To, then select Only Create Connection if you do not want a separate worksheet output for that source. Repeat for each table.
- Choose Data > Get Data > Combine Queries > Append, or open a query and choose Home > Append Queries as New.
- Choose Two tables or Three or more tables, add the source tables, and arrange them in the required order. Select OK.
- Review the preview. Remove any repeated header rows that were imported as records, confirm the data types, and add a source-name column if you need to trace each row back to its original sheet.
- Choose Home > Close & Load To and load the result to a new or existing worksheet.
- After changing source data, choose Data > Refresh All to update the query output.
Refresh is an action, not an automatic update after every source edit. If an appended table contains a column that another table lacks, Power Query places null values in that field for rows from the table without it. A different header name can be treated as a different field, so standardize names before appending. See Microsoft’s Append documentation for these field-matching details.
Rank #2
![Microsoft Office Home & Business 2024 | Classic Desktop Apps: Word, Excel, PowerPoint, Outlook and OneNote | One-Time Purchase for 1 PC/MAC | Instant Download [PC/Mac Online Code]](https://m.media-amazon.com/images/I/41H8B5iqO4L._SL160_.jpg)
- [Ideal for One Person] — With a one-time purchase of Microsoft Office Home & Business 2024, you can create, organize, and get things done.
- [Classic Office Apps] — Includes Word, Excel, PowerPoint, Outlook and OneNote.
- [Desktop Only & Customer Support] — To install and use on one PC or Mac, on desktop only. Microsoft 365 has your back with readily available technical support through chat or phone.
Stack ranges with VSTACK
VSTACK is a direct formula option when the ranges have compatible columns and you want a result that updates as referenced cells change. It is available in Excel for Microsoft 365 and Excel 2024, including supported Mac versions, according to Microsoft’s VSTACK documentation.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteUse one header row
If every range includes its header, the formula will repeat the header in the output. Include the header once and start the other ranges on row 2:
=VSTACK(Sheet1!A1:D1,Sheet1!A2:D50,Sheet2!A2:D50,Sheet3!A2:D50)
Rank #3
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- 1 TB Secure Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Easy Digital Download with Microsoft Account | Product delivered electronically for quick setup. Sign in with your Microsoft account, redeem your code, and download your apps instantly to your Windows, Mac, iPhone, iPad, and Android devices.
For a formula that includes each range’s first row, the basic pattern is =VSTACK(Sheet1!A1:D50,Sheet2!A1:D50,Sheet3!A1:D50), but that pattern repeats headers if row 1 is a header on every sheet. If the source ranges are Tables named JanuarySales, FebruarySales and MarchSales, structured references can make the formula easier to maintain: =VSTACK(JanuarySales,FebruarySales,MarchSales).
Handle range and spill problems
VSTACK returns a vertically appended array. If the arrays have different widths, Excel fills unmatched columns with #N/A. An IFERROR wrapper can replace errors with blanks—for example, =IFERROR(VSTACK(Sheet1!A2:D100,Sheet2!A2:F100),"")—but that can also hide real data errors. Standardize the columns first when possible.
- A
#SPILL!error means something is blocking the output area. Clear or move cells below or to the right of the formula so the result can spill. - A fixed reference such as
A1:D50will not include new records added below row 50. Use Excel Tables when you want references that expand with the table. - VSTACK does not discover newly added worksheets for you, and it is less suitable than Power Query for extensive cleaning or frequently changing sets of sheets.
Copy and paste for a one-time job
For a few small sheets that will not need ongoing updates, manual stacking is straightforward:
- Create a worksheet named Combined or Master.
- Copy the header and data from the first sheet and paste into cell
A1of the new sheet. - For each following sheet, copy only its data rows—not its header—and paste them immediately below the existing records.
- Convert the finished range to a Table with Ctrl+T, then check for blank rows, shifted columns, duplicate records and inconsistent date or number formats.
This creates a static copy. Source edits and new rows will not flow into it; repeat the copy-and-paste process when you need to update the master.
Use Consolidate for summaries, not raw records
Choose Data > Consolidate when you want to calculate a result such as a sum, average, count, maximum or minimum across comparable reports. It does not make a normal transaction-level table containing all source rows. Microsoft explains the two modes in its Consolidate guidance.
Consolidate by position
Use this when the sheets have the same layout and corresponding values occupy the same cells. Select the destination worksheet and upper-left result cell, choose Data > Consolidate, pick a function such as Sum or Average, add each source range under All references, then select OK.
Free tools Windows power users keep installed
One-click scans. No signup required.
Best Value
- Fully compatible with Microsoft Office documents, Office Suite is the number 1 affordable alternative. It is compatible with Word, Excel and PowerPoint files allowing you to create, open, edit and save all your existing documents in an easy-to-use professional office suite. Suitable for home, student, school, family, personal and business use, it includes comprehensive PDF user guides to help you get started, plus a dedicated guide for university students to help with their studies. Multilingual - English, Spanish (Español) and more languages supported.
- Professional premier office suite includes word processor, spreadsheet, presentation, graphics, database and math apps! It can open a plethora of file formats including doc, docx, odt, txt, xls, xlsx, xlsm, ppt, pptx and many more, making it the only office suite you will ever need. You can use the ‘Save as’ feature to ensure your files remain compatible with Word, Excel and PowerPoint, plus you can convert and export your documents to PDF with ease.
- Full program included that will never expire! Free for life updates with lifetime license so no yearly subscription or key code required ever again! Unlimited users allow you to install to both desktop and laptop without any additional cost, and everything you need is provided on USB; perfect for offline installation, reinstallation and to keep as a backup. Compatible with Microsoft Windows 11, 10, 8.1, 8, 7, Vista, XP (32/64-bit), Mac OS X and macOS.
- PixelClassics exclusive extras include 1500 fonts, 120 professional templates, 1000's of clip art images, PDF user guides, over 40 language packs, easy-to-use PixelClassics installation menu (PC only), email support and more! Each USB comes complete with our quick start install guide, plus a fully comprehensive PDF guide is provided on USB.
- You will receive the USB (not a disc) exactly as pictured, in protective sleeve (retail box not included). Our slimline USB is 100% compatible with ALL standard size USB ports. To ensure you receive exactly as advertised including all our exclusive extras, please choose PixelClassics. All our USBs are checked and scanned 100% virus and malware free giving you peace of mind and hassle-free installation, and all of this is backed up by PixelClassics friendly and dedicated email support.
Consolidate by category
Use this when sheets have the same labels but rows or columns are not in identical positions. In the Consolidate dialog, add each source range, then select Top row, Left column, or both under Use labels in. Labels must match consistently: for example, Average and Avg may be treated as separate labels, and unmatched labels can produce separate rows or columns. The Consolidate command may be unavailable in Excel for the web; Microsoft points to formulas or Power Query as alternatives in its overview.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Combine recurring workbooks with Power Query From Folder
If monthly or departmental workbooks arrive as separate files, put the intended files in a dedicated folder and use Power Query’s folder import. Keep their schemas consistent; the selected folder can include files in its subfolders, so unrelated files there may also be brought into the process.
- In Excel, choose Data > Get Data > From File > From Folder and select the folder.
- Check that the listed files are the ones you intend to combine.
- Choose Combine > Combine & Transform Data, then select the correct sample file and worksheet or table.
- Filter out unwanted files or worksheets and apply any needed transformations in Power Query.
- Choose Home > Close & Load. Put future files in the same folder, then use Data > Refresh All to include them.
Microsoft’s folder import instructions explain the workflow. If Power Query combines data from different sources, privacy levels—Public, Organizational or Private—can affect whether sources can be combined or how they interact. Microsoft discusses this in its Power Query Append information.
When sheets have different layouts, fix the cause first
- Different column order: Power Query Append aligns fields by header name. Manual pasting and positional formulas can misplace data if columns are reordered, so compare the headers before combining.
- Different column names: Rename equivalent fields to one standard header. Otherwise, Power Query may create separate columns for what should be one field.
- Missing columns: Power Query returns nulls for a field absent from a source. Decide whether those blanks are valid or whether the source needs correction.
- Decorative rows and merged cells: Remove or transform titles, subtotals and notes so they are not mistaken for records.
- Different data types: Convert text-formatted dates and amounts to the appropriate types in Power Query before relying on sorting, calculations or totals.
- Related rather than sequential data: If one sheet has sales and another has product categories keyed by Product ID, use Merge or a lookup to add columns; Append would put the two kinds of records on top of each other.
Validate the combined sheet
Combining does not automatically deduplicate records. Before using the master sheet for reporting or financial decisions, check the result against its sources:
- Compare the number of source records with the output row count, accounting for headers and any intentionally excluded rows.
- Compare key totals, such as the sum of Amount, before and after stacking.
- Check duplicate IDs or records. Remove duplicates only after confirming they are genuinely unwanted; if appropriate, identify a stable key first.
- Look for blank required fields and verify dates, amounts and IDs have the expected data types.
- For a query, add a new source row and confirm that Data > Refresh All brings it into the output.
Copying worksheets is not the same as combining their data
Move or Copy Sheet preserves whole worksheets as separate tabs; it does not turn their records into one unified table. Moving sheets can also affect formulas, charts or 3-D references that depend on the original workbook structure. Microsoft describes the distinction in its worksheet move and copy guidance.
For most ongoing row-stacking jobs, use Power Query Append and refresh the output when source data changes. Use VSTACK for a simpler formula result, and reserve Consolidate for summaries.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
