Sometimes, we have the information stored throughout several spreadsheets and it’ll become easier if you know How to Combine 2 Excel Sheets into 1 and you can easily merge two excel spreadsheets.
Merging this information into a single sheet can be very helpful to access and search through it.
If you have a very large amount of data in the sheets you can’t always copy and paste each & every cell into the sheet. So here are the quick ways to do the sheet completely along with the steps.
So that you can always close the file without saving it if the outcome is not appropriate for you.
How to Combine 2 Excel Sheets into 1: 3 Easy Ways
If you want to merge two Excel sheets together then Copying/Moving the data from one sheet to another can be a little messy if you are working with a large amount of data so make sure you have saved all the files before applying any of these methods.
Method 1. Copy/Move Sheet from one file to another
For convenience, I will be using the term ‘master sheet’ for the sheet in which you want to copy another sheet, ‘sample sheet’ for the sheet you want to copy into the master sheet, ‘master file’ for the file with a master sheet and sample file with the sample sheet.
Step 1. Make sure both the ‘master file’ and ‘sample file’ are open.
Step 2. In the sample file, switch to the sample sheet and select Home Tab from the top ribbon.
Step 3. In the Cells group, select the option Format > Move or Copy Sheet from the drop-down menu.
Step 4. Under the To Book: option, select the other file with the master sheet and then select the sheet before which you want to copy/move the sample sheet.
If you want to copy the sheet, check the option ‘Create a copy’. If you want to move the sheet then remain the option unchecked.
Step 5. Click OK then You’ll see the data moved/copied into a new spreadsheet.
Method 2. Combine 2 Excel Sheets into 1 Using Consolidate Function
Step 1. Click the Data tab from the top ribbon, then select Consolidate option in the workbook’s new sheet where you want to aggregate data from other sheets.
Step 2. In the Consolidate dialog, do the following.
Under Function select the operation you want to do after merging the data. Under Reference, mention the range of each sheet you want to aggregate.
Select the Add option after mentioning each reference to move all the references into the box below. Check the labels you’d use after aggregating data.
Step 3. Click OK to apply.
Method 3. Copy One or More Sheets Using Get & Transform
Step 1. Open the file in which you want to copy/import the data from other sheets.
Step 2. Switch to the Data tab from the top ribbon.
Step 3. From the group ‘Get & Transform Data’ select the option Get Data > From File > From Excel Workbook. A drop-down menu will open.
Maybe you have this ‘Get Data’ option named ‘New Query’ if you have an earlier version of excel. I am using MS Office 2019 Package.
There are several options other than From File which you can explore and try to make data importing easy.
Step 4. Browse the file from which you have to extract data and then click Open.
Step 5. Select the Sheet with the data which you want to copy/extract. You can also select multiple sheets by checking the option select multiple items.
Step 6. Click the Load option to copy.
- You can try the option Load To from the drop-down of Load for copying the data in other forms such as Table, PivotChart, and others.
- You can also try the option Transform Data near Load option to modify the data before copying it by using Power Query Editor. But this can get you a headache cause it’s complex enough.
Step 7. You’ll see the copied data in a new spreadsheet.
These are some quick ways to Combine 2 Excel Sheets into 1 and now you can copy/move/extract data from various excel sheets or files into a single file.
Read Next:
