Multipart article

Many to One: Converging and Merging Excel Spreadsheets and Workbooks

Smartsheet Contributor Joe Weller on Mar 07, 2018 (Last modified on Jul 21, 2021)

Does your job require you to provide business metrics? Do you need to total financial data from many Microsoft Excel files? Sometimes a simple copy and paste is the most efficient way to transfer content. But what if you have extensive data in several workbooks? You can use the Excel merge and converge functionality to keep things organized.

Excel offers more than five ways to merge spreadsheets or consolidate data rows and columns in a primary workbook. In this article, advanced Excel users discuss instances where you might want to combine data, as well as common problems and best practices to help your team get the most out of Excel. You’ll find step-by-step instructions that describe merge methods in Microsoft Excel — when steps are unique to a specific version, those steps are indicated in the instructions. Plus, you can use the downloadable demo file to practice what you learn.

 

Naming Conventions in this Article

Before we delve into the ins and outs of merging data and worksheets in Excel, it’s necessary to provide you with a working vocabulary of the terms used throughout this article:

  • Workbook: A workbook is an electronic file, like a folder, that contains one or more worksheets.
  • Spreadsheet or Worksheet: A worksheet is known in Excel as a spreadsheet. A spreadsheet contains rows and columns of cells that contain data, such as numbers, text strings, or functions. Content in cells can refer to other cells in the same spreadsheet, in several spreadsheets in the same workbook, or in a different workbook or workbooks. Spreadsheets differ from databases, which have tables.
  • Consolidating: This action combines one or more Excel lists or sets of data from different worksheets within the same workbook, or from many workbooks into a primary worksheet.
  • Merging: This concept involves merging copies or moving spreadsheets from one or more external workbooks.
  • Source Workbook or Spreadsheet: The workbook or spreadsheet you’re moving items from.
  • Target Workbook or Spreadsheet: The workbook or spreadsheet you’re moving items to.

Download Practice Files

You can download the following files and practice merging Excel files with the instructions in this article. PracticeBook1 contains spreadsheets for copying and merging within the same workbook, and PracticeBook2 contains spreadsheets for copying and merging from an external workbook.

 

Merging Demo Book1

Download Merging Demo Practice Book 1

 

 

Merging Demo Practice Book2

Download Merging Demo Practice Book 2

 

See how Smartsheet can help you be more effective

Watch the demo to see how you can more effectively manage your team, projects, and processes with real-time work management in Smartsheet.

 

Watch a free demo


Why Consolidate or Merge Data in Excel?

Merging and consolidating data in Excel can help you anytime you need an overview of large amounts of data. For example, if input comes to you from multiple sources, such as from all your account executives, it is easier to add numbers and create a report from one file. You could copy and paste, as that may be the fastest option if you have only a few cells or columns to update. But for multiple workbooks, or for hundreds or thousands of rows and columns of data, manual copying can be slow, complicated, and painful.

 

Michael Schaeffer

“Practically everyone can use Excel for merging data, especially anyone who is tasked with generating reports or analyzing data from multiple sources,” says Michael Schaeffer, an engineering project manager. “Since businesses often utilize many different database programs for cataloging information that are not designed to easily interface with each other, Excel becomes a powerful tool.”

As an example, a company may use JIRA to track and manage quality assurance results, but track project timeline data in another database. Merging information from the two sources can create a business metrics report. “[This] process is significantly easier using Excel,” Schaeffer adds.

 

Kistin Gunnis

Kistin Gunnis is a former global operations manager and the Founder of Professional Women’s Lunches. She agrees with Schaeffer: “Anyone can use merging in Excel. However, in my experience, it isn’t until you need to do some heavy lifting with lots of data that you learn these skills.” In her work, she merged Excel data to consolidate multiple data sets and to present specific data in a more readable format.

 

In What Ways Can You Merge Excel Data and Workbooks?

In Excel, data is contained in worksheets, also called spreadsheets, which reside in container files called workbooks. You can consolidate and merge data in different ways, depending on your needs:

  • Merge workbooks to include all spreadsheets in the target workbook.
  • Merge one or many spreadsheets from one or many workbooks into a single workbook.
  • Merge the data from many sheets into one worksheet.

 

Excel Merge Converge

“Merging can be as simple as taking a list of people’s first and last names and showing them in one cell with a comma,” Gunnis explains.

Merge Results Excel

But on the other hand, Gunnis explains, “It can be as complicated as collecting data from various spreadsheets and compiling them into one spreadsheet that can be used for analysis and reporting, such as for budgeting.”

Merge Excel Spreadsheets

See “How to Merge Excel Spreadsheets” and “How to Concatenate (Combine) Cells” below for step-by-step instructions.

Making It Easier to Converge and Merge Excel Data

If you and your team converge data regularly, consider creating an appropriate template and sharing it with the team. Schaeffer says that creating templates can take time upfront, but that the effort pays for itself in team efficiency. In addition, Gunnis suggests taking a course to familiarize yourself with shortcuts, and formulas such as VLOOKUP, HLOOKUP, INDEX MATCH, and IF statements. You can even extend training to your team. To maximize your Excel experience, consider hiring a consultant to provide targeted training and to customize analysis and reporting capabilities.


How Do You Compare and Merge One or More Workbooks in Excel?

Before you merge data, you may want to compare files to ensure you’re not duplicating content or to verify other aspects of the data.

Compare Two Spreadsheets in the Same Workbook or Another Workbook

  1. Open the workbooks you want to compare. You cannot select to view another workbook if it is not already open.
  2. Click the View tab, in the Window group, and click the New Window button. The same Excel file opens in a different window.
  3. To view the worksheets side by side, click View Side by Side. Select the sheet you want to compare.
View Worksheets Side by Side

H3: How to Merge Excel Workbooks

Excel provides a range of methods for consolidating data. Which one you choose depends on how much data you need to move and on your facility with advanced functions like VBA, which you may choose to use if you are merging many workbooks.

Copy and Paste Cell Ranges

Simple copy and paste actions are easy for small amounts of data. However, if you are moving hundreds or thousands of cells across multiple sheets, or if you need to copy and paste the same cells often, there are more efficient actions.

To Copy and Paste Cells:

  1. In the source spreadsheet, click in the first cell you want to copy or move and drag your cursor to select all the cells you want to copy.
Copy and paste cells Excel
  1. When the cells are highlighted, right-click and click Copy on the dropdown menu.

    Copy Cells in Excel
  2. In the target spreadsheet, click in the first cell. Right-click and then click Paste from the dropdown menu.

Combine Spreadsheets in a Single Workbook Using the Move or Copy Command

You can add one or more spreadsheets to a target workbook by using the Move or Copy command in Excel. If you move a spreadsheet, only one version of the spreadsheet exists. If you copy a spreadsheet, a version will remain in the source workbook and the copy can be added to the same workbook or a different workbook.

To Move Source Spreadsheets to a Target Workbook:

  1. Open your target workbook and all source workbooks.
  2. In a source workbook, for each nonadjacent worksheet you want to copy, press CTRL and click the sheet. To select a range of files, click in the first sheet’s tab, and then press SHIFT and click the last tab. The range of tabs is selected.
     
    Select Range of Files Excel
  3. Right-click, and then click Move or Copy.
  4. In the Move or Copy dialog box, select the target workbook in the Move Selected Sheets to Book dropdown menu.
    Move Selected Sheets To Book Excel
  5. In the Before Sheet box, select the order for your sheets. You can select to place the sheets before any of the existing sheets or after the last sheet. Click the blue OK button. The sheets are moved to the target workbook. Reorder or rename the sheets as needed.

To copy a spreadsheet within the same workbook or to a target workbook:

  1. Open your workbook and select the spreadsheet you want to copy.
  2. Right-click and then click Move or Copy.
  3. In the Move or Copy dialog box, select the target workbook in the Move Selected Sheets to Book dropdown menu, whether the same or external.
  4. In the Before Sheet box, select the order for your sheets. You can select to place the sheets before any of the existing sheets or after the last sheet.
  5. Click Create a Copy. Click OK.
Create Copy of Spreadsheet Excel

Combine Workbooks By Using VBA

An advanced method for merging workbooks uses a VBA macro in Excel. We supply a script here, written by Michael Schaeffer. To use the macro, ensure that VBA is enabled for your sheet and that the Developer tab is present in the ribbon.

If you can’t see the Developer tab, you need to enable VBA.

How to Enable VBA by Adding the Developer Tab to the Ribbon

1. On the File menu, click Options, and then click Customize Ribbon.

Select Options in Excel Workbook

Note: If Options isn’t available, try the Excel menu. Click Preferences and click Ribbon and Toolbar to add the Developer tab to the ribbon.

2. In the Command pane, click Developer, and then click Add. Click OK.

Customize Ribbon Excel

To Insert a Macro into an Excel File:

  1. Open the Excel workbook you want to add a macro.
  2. Click the Developer tab.
  3. In the Code group, click the Visual Basic icon.
    Select Visual Basic Icon Excel
  4. Click VBA Project (Book1), click Microsoft Excel Objects, and right-click on ThisWorkbook.
    VBA Project Workbook
  5. Point to Insert and click Module.
     
    Select Insert Option Excel
    Select Module Excel
  6. Copy and paste the script (below) into the code window.
  7. Click Run.
Run Sub User Form Excel

.

VBA Script by Michael Schaeffer

Below is the VBA script written by Michael Schaeffer. When copying and pasting start with the text after the ===== lines.

'===================================================================================================

'Inputs:

'Outputs:

'

'Notes: This function creates a new workbook and merges data from all Excel files contained within

'the desired folder path.

'

'It contains the most basic functionality and should be expanded upon to meet individual needs.

'===================================================================================================

Sub MergeFolderPathWorkbooks()

    Dim WorkBk As Workbook

    Dim MergedSheet As Worksheet

    Dim SourceData As Range

    Dim DestinationData As Range

    Dim LastRow As Long

    Dim CurrentRow As Long

    Dim FolderPath As String

    Dim FileNames As String
 

    Application.ScreenUpdating = False

    Application.DisplayAlerts = False

    Set MergedSheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    CurrentRow = 1

    'UPDATE folder path based on where the Excel files to be merged are located

    FolderPath = "C:\Users\Michael\Desktop\Folder\"

    FileNames = Dir(FolderPath & "*.xl*") 

    Do While FileNames <> ""

       Set WorkBk = Workbooks.Open(FolderPath & FileNames)      

       LastRow = Cells(Rows.Count, 1).End(xlUp).Row      

       'UPDATE range based on desired data to merge

       Set SourceData = WorkBk.Worksheets(1).Range("A1", "B" & LastRow)      

       Set DestinationData = MergedSheet.Range("A" & CurrentRow)

       Set DestinationData = DestinationData.Resize(SourceData.Rows.Count,SourceData.Columns.Count)         

       DestinationData.Value = SourceData.Value      

       CurrentRow = CurrentRow + DestinationData.Rows.Count      

       WorkBk.Close savechanges:=False      

       FileNames = Dir()      

    Loop   

    Application.ScreenUpdating = True

    Application.DisplayAlerts = True

End Sub

 

How to Concatenate (Combine) Cells in Excel

You can combine the contents or text strings of different cells into another cell by using the Concatenate function, which may be known as Concat in your version of Excel 2016. Combining strings is helpful if you need copy a formatted list to another file format. For example, if first names are listed in column A and last names in column B, you can concatenate them in column C.

To Concatenate Strings:

  1. In the target sheet, click in the cell where you want to add content. The sheet can be in the same sheet or workbook or in another sheet or workbook.
  2. Click the Insert Function button 
    Insert Function Button Excel
     The Insert Function window appears.
  3. In the Select a Function box, select Concatenate. If you do not see Concatenate, in the Search for a Function box, type concat, and then select Concatenate when it appears in the Select a Function box. Click OK.
     
    Concatenate Function Excel
  4. In the Function Arguments window, click in the Text1 box, and click in the first cell you want to add. Continue to add all cells, and then click OK.
     
    Function Arguements Window Excel
    The cells are combined in the target cell as PlumsPearsPeaches.
    Combined Target Cell Excel

5. In the Formula bar, make the following edits to change the syntax of your concatenated strings:

  • To add commas: In the function formula, between each cell, add “,”. Ensure that additional commas follow the previous cell and precede the next cell.
    Concatenate with commas Excel
  • To create a sentence: In the formula, add text. For example, =CONCATENATE("Favorite fruits are ", Sheet1!A2, ", ",Sheet1!A3, ", ",Sheet1!A3, ".")
  • To add ampersands between elements: =CONCATENATE(Sheet1!A2, " & ",Sheet1!A3, " & ",Sheet1!A3)

Tip: Take careful note of the placement of quotation marks, spaces, and commas around each element. In addition, ensure that you use straight quotation marks.

 

How to Merge Excel Spreadsheets

You may need to merge data from specific spreadsheets into one spreadsheet, rather than including separate spreadsheets in one workbook.

Why Consolidate Data?

Consolidating data is helpful when you need to perform the same action on data in different worksheets. For example, if you need to aggregate and then report on sales results for several account executives, it’s much easier to work with all the data in one sheet. The data you consolidate may be in different sheets in the same workbook, or in multiple sheets in one or more other workbooks.

How Consolidation Works

The Consolidate command works only for numerical values, and totals or sums common values and copies unique values. Consolidation requires a common field — in other words, that at least the left or top labels are the same. The data can be in the same order.

Consolidation Excel

If the labels are the same, the organization can also be different. Excel ignores capitalization differences on labels, but different spellings of the same word are treated as different labels. For example, pear and pears are considered different.

However, the columns Jan, Feb, Mar and Jan, Feb, Apr, Mar are recognized and summed correctly. Column and label order follow the order of the first sheet entered.

Consolidate Command Excel

Tip: Ensure your sheets contain no empty cells or empty rows or columns. Empty areas between data may cause consolidation to fail.

To Consolidate Data from One or More Spreadsheets into a Single Spreadsheet:

1. In the target sheet, click in upper left cell where you want to add content. To avoid overwriting data, ensure that you have enough free cells below and to the right.

2. Click the Data tab, and in the Data Tools group, click the Consolidate icon.

3. In the Function box, click Sum for summary function.

4. Click in the Reference box.

  • If the data you want to consolidate is in a worksheet in the same workbook, click the sheet. OR
  • If the data you want to consolidate is in another workbook, open the workbook and select the sheet you want. Click Browse and click the preferred workbook.

5. In the source sheet, select the data range. Click in the first cell and drag down and to the right until your data range is surrounded by a marquee. If you have labels in the top row and left column, include those. The range appears in the All References box. You can also type in the data range. Click Add. Repeat steps 5 and 6 for each data range on each sheet.

6. To link to the source data so that your target sheet updates whenever the source changes, click Create Links to Source Data.

7. Click OK. The data is added to the target sheet. If you selected to link to the source data, you can also view source data.

8. Click a plus sign to view all source data sheets.

9. Click a number to view data for each linked sheet.


How Do I Link Two Excel Spreadsheets?

Linking sheets means that the data will be connected and one set of data will be updated when the other is updated. There are two ways to do this:

To Create Links to a Data Source:

  1. For new data, follow the steps in the Consolidate Data procedure, then click Create Links to Source Data.

  2. To link to an existing consolidated data sheet, select all the cells in the target sheet that you want updated, and then in the Data tab, click Consolidate Data. Select the source sheet, and then click Create Links to Source Data. The cells in the target are now linked to the corresponding cells in the source sheet.  

INDIRECT Function

With the INDIRECT function, you can look up and return specific values from other spreadsheets without changing the original cells. You can return a single or locked cell value or a range of values from sheets within the same workbook or from many workbooks.


Other Options for Converging and Merging Data in Excel

Besides the commands, tools, and functions available in Excel, other tools can accelerate or streamline the processes.  

  • Microsoft Access may be used to combine files with a common identifier to create a table in Access for each spreadsheet and query the data you want.
  • Microsoft Query in Excel allows you to merge data through a connection to a SQL database.
  • SUM formulas in Excel are similar to the INDIRECT function and VLOOKUP function to total data on multiple spreadsheets and then display it in a report. PivotTables similarly aggregate data for analysis.
  • Microsoft Power Query is an advanced tool in Excel 2010, also called Get and Transform in Excel 2016. In addition, Power Query may require a significant investment in time to learn well. This is a tool for downloading multiple sheets.
  • Kutools can help you compare and combine multiple sheets for under $40. Available for Excel version 2007 - 2016 and Excel for Office 365.
  • Office Tab allows you to access multiple Microsoft Office files from tabs, as in a web browser.
  • RDBMerge is a free add-in for Excel to help you merge Excel for Windows files.
  • The Professor Excel Add-In merges Excel spreadsheet with just a few clicks and offers a free seven-day trial.
  • Ablebits Consolidate Worksheet wizard offers another alternative for easy consolidation for under $30. Excel version 2007 - 2016.

Troubleshooting: Why Can’t I Merge in Excel?

Even with the cleanest data sheets and the newest version of software, things can go wrong. Here are a few considerations before you try to merge data or sheets and if your attempts are not successful:

  • Different Data Formats: “The most common problem that I find when using data is that when the data is imported, the format of each cell may not be equal. For example, dates are not all in the same format,” explains Gunnis. She suggests importing data through CSV so that you can format data.
  • Funky File Formats and Functions: Diverging file formats and corrupted functions can also present problems, especially when spreadsheets are linked and are edited by multiple users. “It is important that everyone using the files understand saving standards to ensure that formulas don’t become corrupted,” Gunnis advises.
  • Empty Spaces: Empty cells in columns and empty columns and rows can prevent data from merging.

Make Better Decisions by Merging Data Across Your Work in Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change. 

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed. 

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.

 

 

 

Discover why over 90% of Fortune 100 companies trust Smartsheet to get work done.

Try Smartsheet for Free Get a Free Smartsheet Demo