Many to One: Converging and Merging Excel Spreadsheets and Workbooks

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.

‌ Download Merging Demo Practice Book 1

‌ Download Merging Demo Practice Book 2

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.

“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 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 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.

“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.

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.”

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.

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.

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.

2. When the cells are highlighted, right-click and click Copy on the dropdown menu.

   

3. 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.
    
   
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.
   
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.

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.
​

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.

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.
   
4. Click VBA Project (Book1), click Microsoft Excel Objects, and right-click on ThisWorkbook.
   
5. Point to Insert and click Module.
    
   
   
6. Copy and paste the script (below) into the code window.
7. Click Run.

.

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

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 
   
    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.
    
   
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.
    
   
   The cells are combined in the target cell as PlumsPearsPeaches.
   

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.
  
• 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.

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.

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.

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.

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.

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.

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.

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.