How to Merge Excel Files and Sheets
Whatsapp Pinterest
Advertisement

Need to merge multiple datasets into one spreadsheet? Here’s how.

Sometimes the Excel data you need is split across multiple sheets or even multiple files. It can be significantly more convenient to have all of this information put in the same document.

In a pinch, it’s possible to copy and paste the various cells that are required, placing them all in the same sheet. However, depending on how much data you’re working with, this might end up taking a lot of time and effort.

Instead, consider some of the smarter ways to accomplish the same task. These three methods might just allow you to skip some of the busywork 10 Essential Excel Keyboard Shortcuts for Navigating Worksheets 10 Essential Excel Keyboard Shortcuts for Navigating Worksheets If you use Excel, keyboard shortcuts can make or break your productivity. Here are 10 essential ones you should know. Read More when it comes to merging sheets or files in Excel.

How to Merge Excel Sheets

Excel makes it easy to combine more than one sheet into a new workbook.

  1. Open the sheets you want to merge.
  2. Click Home > Format > Move or Copy Sheet.
  3. Use the dropdown menu to select (new book).
  4. Click OK.

Merging Sheets Into a Single Workbook

The easiest way to merge sheets in Excel is by using the Move or Copy Sheet command. This method has its limitations, but it’s quick and straightforward.

First, open up the sheets you want to merge into the same workbook. Use the Format dropdown in the Home tab so select Move or Copy Sheet.

merge excel sheets

You should see these controls:

merge excel sheets

Use the dropdown to select (new book). This is going to serve as the master spreadsheet How to Split a Huge CSV Excel Spreadsheet Into Separate Files How to Split a Huge CSV Excel Spreadsheet Into Separate Files One of Microsoft Excel's shortcomings is the limited size of a spreadsheet. If you need to make your Excel file smaller or split a large CSV file, read on! Read More where we send all of our individual sheets. You can use the Before sheet: box to stipulate the order that the sheets are in.

Repeat this process with the rest of the sheets you want to merge. Then, save your new master document.

Merging Data Into the Same Sheet

Sometimes, you might want to take more than one dataset and present it as a single sheet. This is pretty easy to accomplish in Excel, so long as you take the time to ensure that your data is formatted properly ahead of time.

merge excel data into one sheet

Here’s the data that I’m going to use. There are two things that are very important if this process is to work correctly; the sheets that you’re consolidating need to use exactly the same layout Use Custom Views in Excel to Save Specific Worksheet Layouts Use Custom Views in Excel to Save Specific Worksheet Layouts Excel's Custom View setting makes it easy to view specific information on a crowded spreadsheet or to create different layouts for your data. Read More , with the same headers and types of data, and there can’t be any blank rows or columns.

When you’ve arranged your data to those specifications, create a new worksheet. It’s possible to run the consolidation procedure in a sheet where there’s already data, but it’s easier not to.

In this new sheet, head to the Data tab and click Consolidate.

consolidate excel data

Select Sum from the dropdown and then use the button in the References field to access your spreadsheet so you can select the data you need.

consolidate excel data

Do this for all the data sets that you want to merge. You can even draw from other workbooks by using the Browse button, otherwise known as Select in the Mac version of Excel.

consolidate excel data

Tick the box titled Create links to source data if you’re going to continue to update the data in other sheets, and want this sheet to reflect that. You can also select which labels are carried across with the tick boxes shown above. I chose to tick both boxes because I wanted both sets of labels.

Finally, click OK.

consolidate excel data

You should end up with something like the screenshot above. Unfortunately, this process isn’t suitable if you want to merge cells with text in them — it only works with numerical data. In that situation, you’ll need to use VBA The Excel VBA Programming Tutorial for Beginners The Excel VBA Programming Tutorial for Beginners VBA is a Microsoft Office power tool. You can use it to automate tasks with macros, set triggers, and lots more. We'll introduce you to Excel visual basic programming with a simple project. Read More .

Combining Workbooks With VBA

If you want to combine sheets from several workbooks in a snap, the best way is to write a simple VBA macro 5 Resources for Excel Macros to Automate Your Spreadsheets 5 Resources for Excel Macros to Automate Your Spreadsheets Searching for Excel macros? Here are five sites that have got what you're looking for. Read More . This will come in especially handy if you perform this task on a regular basis.

First, make sure that all the workbooks you want to combine are in the same folder on your computer. Then, create a new Excel spreadsheet that will bring them all together.

Head to the Developer tab and click Visual Basic.

merge excel files

Click Insert > Module and copy and paste the following code, taken from a guide by ExtendOffice:

Sub GetSheets()
Path = "C:\Users\Brad\MakeUseOf\MergingSheets\"
Filename = Dir(Path & "*.xls")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

Make sure to change the path to wherever the files are stored on your computer.

Next, save your workbook as an XLSM file so that macros are enabled How to Build a Custom Excel Toolbar of VBA Macros How to Build a Custom Excel Toolbar of VBA Macros Ready to supercharge your Microsoft Excel productivity? A custom toolbar can do just that. We'll show you how to put all your Excel macros within reach. Read More . Then run the macro and you should find that you have a single workbook that contains all the sheets from all the files in the folder.

Look Before You Leap

Merging sheets and files in Excel is rather complicated and messy. This fact should illuminate one of the most important lessons about Microsoft Excel: it’s always good to plan ahead.

Merging different data sets after the fact is always going to cause a few headaches, especially if you’re working with large spreadsheets that have been in use for a long time. Whenever you start working with a new workbook 5 Excel Document Settings You Must Never Forget to Check 5 Excel Document Settings You Must Never Forget to Check When you pick an Excel template or share a spreadsheet, it may contain custom settings that you don't want anyone to see. Always check these settings. Read More , it’s best to consider all possibilities of what the file is going to be needed for further down the line.

Excel is great at making documents that can be referred to and used for an extended period of time, but decisions made early on are likely to cause problems later.

Do you have a tip for merging different datasets? Or are you looking for assistance with the methods in this guide? Either way, why not join the conversation in the comments section below?

Explore more about: Microsoft Excel, Microsoft Office Tips, Spreadsheet.

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. nick davis
    January 4, 2018 at 4:42 pm

    Or you could use power query and save time and be more dynamic.