The simplest way to combine Excel sheets

The fastest method is to copy all the data from each sheet and paste it into a single sheet in the same workbook. Open your Excel file, click the sheet tab you want to copy from, select all the data (Ctrl+A on Windows, Command+A on Mac), copy it (Ctrl+C or Command+C), then click the destination sheet tab and paste (Ctrl+V or Command+V). Repeat this for each sheet you want to combine.

This works best when all your sheets have the same column structure — the same headers in the same order. If your sheets have different layouts or you need to keep track of which data came from which sheet, you will need a different approach. The manual copy method also means you are not updating automatically if the original sheets change later.

If you have many sheets or large amounts of data, this becomes tedious and error-prone. The next sections cover methods that are faster or that handle more complex situations.

Key Takeaways

  • straightforward copy-and-paste works when all sheets have identical column headers and you only need to do this once.
  • Power Query (in Excel) or pivot tables can combine sheets automatically and update when source data changes.
  • If sheets have different structures, you may need to add a column identifying which sheet each row came from.
  • Google Sheets users can use the IMPORTRANGE function to pull data from multiple sheets into one without manual copying.

Using Power Query to combine sheets automatically

Power Query is a built-in Excel tool (called Get & Transform Data on Windows) that can combine multiple sheets with a few clicks and update automatically when data changes. Open Excel, go to the Data tab, and click Get Data (or New Query). Select From Other Sources, then Blank Query. In the formula bar, you can write a formula that tells Power Query which sheets to combine.

The advantage is that Power Query remembers your instructions. If someone updates the original sheets next week, you can refresh the combined sheet and it will pull the new data automatically. This saves time if you do this task regularly. The downside is that Power Query has a learning curve — the formula syntax is not intuitive for beginners.

A simpler Power Query approach: manually select each sheet one at a time through the Get Data menu, let Power Query load each one, then use Append Queries to stack them together. This is slower than a formula but easier to understand if you have never used Power Query before.

Combining sheets with different column structures

If your sheets do not have the same columns in the same order, combining them is trickier. You have two choices: rearrange the columns in each sheet to match before combining, or add a helper column that identifies which sheet each row came from.

The helper column method works like this: before you copy data from Sheet1, add a new column called "Source" and fill it with "Sheet1" for every row. Do the same for Sheet2 (fill the Source column with "Sheet2"), then copy all the data from both sheets into your combined sheet. Now you can sort or filter by the Source column if you need to see which data came from where.

If you have five or more sheets with mismatched columns, this becomes a lot of manual work. In that case, Power Query is worth learning because it can handle the column matching for you.

Using Google Sheets to combine data from multiple sheets

Google Sheets has a function called IMPORTRANGE that pulls data from one sheet into another without copying and pasting. In your destination sheet, click a cell and type =IMPORTRANGE("spreadsheet_url", "Sheet1!A:Z"), replacing the URL with your actual file and the range with the columns you want. Google Sheets will ask permission the first time, then automatically import that data.

You can use multiple IMPORTRANGE formulas in the same sheet to pull from different sheets or even different files. If the source data changes, the imported data updates automatically. This is cleaner than copy-paste because you are not duplicating data — you are referencing it.

The limitation is that IMPORTRANGE works best when you want to combine sheets that are already in the same Google Sheets file. If your sheets are in separate files, IMPORTRANGE still works but requires you to share those files or have access to them.

Combining sheets with a pivot table

A pivot table is designed to summarize data, but it can also combine multiple sheets if you set it up correctly. First, make sure all your sheets have the same column headers. Then create a new sheet, go to Insert > Pivot Table, and select all the data from your first sheet. In the pivot table editor, add all your columns to the Rows section — this will list every row from your data without summarizing it.

The pivot table approach is slower than copy-paste or Power Query for straightforward combining, but it is useful if you also want to analyze the combined data — count totals, sum amounts, or break down by category. Once you have the pivot table set up, refreshing it to include new data is one click.

What to do if sheets have different numbers of columns

When sheets have different numbers of columns, combining them creates blank cells. This is usually fine — your combined sheet will have all the columns from all the sheets, and rows from sheets that do not have certain columns will show empty cells in those columns.

Before combining, check whether the column names match across sheets. If Sheet1 has a column called "Date" and Sheet2 has a column called "Date Submitted", Power Query or manual sorting will treat them as different columns. Rename them to match before combining, or you will end up with two separate columns in your final sheet.

If you are combining sheets manually, paste data from Sheet1 first, then paste data from Sheet2 starting in the row right below Sheet1's data. This keeps everything in order and makes it straightforward to see where one sheet's data ends and another begins.

Frequently Asked Questions

Can I combine sheets from different Excel files?

Yes. Open both files, then copy from one and paste into the other. You can also use Power Query to pull data from a closed file — go to Get Data > From File > From Excel, select the file, and choose which sheets to combine. This is useful if you need to combine data from files that are stored on a shared drive.

Will combining sheets remove duplicates automatically?

No. If the same row appears in multiple sheets, it will appear multiple times in your combined sheet. After combining, you can remove duplicates by selecting all data, going to Data > Remove Duplicates, and choosing which columns to check. Excel will delete rows that are identical in those columns.

What if I accidentally paste data in the wrong order?

Sort your combined sheet by a column that identifies the source (like the "Source" helper column mentioned earlier), or by date if your data has timestamps. This will group the data back together by sheet. If you used Power Query, you can undo and re-run the query — the instructions are saved.

Can I combine sheets without opening Excel?

If you use Google Sheets, yes — you can use IMPORTRANGE from any device with a web browser. If you use Excel, you need the desktop app or the web version of Excel (which has limited Power Query support). The web version of Excel can handle copy-paste but not all advanced features.