The combined worksheets will now appear in the Microsoft Query dialog box, as illustrated in Figure 4. Select the File menu within Microsoft Query. Select Return Data to Microsoft Excel at the bottom of the menu. An Import Data dialog box will allow you to decide how you wish to present the data. See how to merge Excel spreadsheets without copying and pasting into master worksheet in 3 min. Find more about Merge Tables wizard at https://www.ablebits.c.

Sheets

I often see people split their data into several worksheets, like one worksheet per week, per month, or per region. While this might make logical sense to the person creating the workbook, having the data spread across multiple worksheets makes it difficult to create a single pivot table to show trends across all of the data.

Combining worksheets is simple, but you would never accidentally discover the steps of this technique. I learned this trick from Australian author Matt Allington. There are a few requirements for this technique: Each worksheet must have the same columns in the same sequence. The trick will be simpler if the headings appear in row 1 of each worksheet. If you have some title cells in the top rows and the headings are in row 4 or 5, you’ll have to take the time to create a named range on each worksheet that starts at the headings and includes all rows of data.

  • The easiest method to merge Excel spreadsheets is to simply take the entire sheet and copy it from one workbook to another. To do this, start off by opening both Excel workbooks. Then, switch to the workbook that you want to copy several sheets from.
  • Using a macro to combine multiple Excel files into one When we have multiple Excel files, we can merge them in a swift manner using a VBA macro. We can easily use the macro displayed below. We will click on Alt + F11 and select Visual Basic Editor.

The technique uses Power Query, which means you can perform the steps in a blank workbook and then refresh that workbook the next time you receive the source data from elsewhere, like a coworker. The coworker’s workbook remains unchanged, but you’ll have a worksheet that consolidates all data from all of the worksheets. (Note that Power Query debuted in Windows versions of Excel 2016 and is currently only available for Windows versions of Excel, but Microsoft is working on adding it to Excel for Mac.)

Save the workbook with multiple worksheets on your local hard drive. From a blank workbook, select Data, Get Data, From File, From Workbook. Browse and select the workbook.

A Navigator dialog appears with a list of worksheets in the workbook. This is where you would normally select one sheet and choose Transform Data. But here’s the first hidden feature: Instead, right-click on the workbook name and choose Transform Data.

The Power Query Editor opens. You should have one row for each worksheet and one row for each named range. If your data always starts in row 1, you can keep the records where the Kind column says “Sheet.” If the data starts elsewhere and you used named ranges, you should filter to remove the records where the Kind column says “Sheet.” If you have a stray worksheet that doesn’t contain data but instead contains instructions or a table of contents, use the filter drop-down in the Name column to remove that worksheet from the list.

There are five columns shown in the Power Query Editor. Once you’re done filtering, you can right-click and remove the columns for Item, Kind, and Hidden. This leaves you with what appears to be a useless list of the worksheet names with the word “Table” next to each one. The column that simply repeats the word Table in each row is secretly hiding all of the data. Look for the icon in the heading row with two arrows pointing in opposite directions.

Merge All Sheets In One Sheet Excel Free

Click the arrows to open the expand dialog. Power Query provides a generic list of Column 1, Column 2, Column 3, and so on. Make sure to uncheck the box for Use Original Column Names as Prefix and then click OK.

Finally, you have a grid showing all records from all worksheets. The headings are appearing in row 1 instead of in the headings area. On the Home tab of the Power Query Editor, choose Use First Row as Headers to move the headings up from row 1.

If you had 12 worksheets that were combined, each of the worksheets likely started out with a row of headers. After moving the headings from row 1 up to the column name, you’ll still have another 11 rows spread throughout your data with the column headings appearing again.

Open the Filter drop-down on any column and choose to remove any records that have the column name. For example, you could open the Product filter and remove “Product” or open the Customer filter and remove “Customer.” This will take care of removing the remaining heading rows from the other worksheets.

Perform any final formatting steps. Filter out any blank records by removing the value (null) from any filter drop-down. Sort the data by date. Set the Data Type on the Transform tab to Date for any date columns. Rename any columns that need to be renamed.

Finally, click the Close & Load icon on the Home tab of the Power Query Editor. All of the data from the various worksheets will load into your workbook. The best part: Next week or next month when you receive a new copy of the original workbook, simply save it with the same name in the same folder. When you open your results workbook, click the Refresh icon in the Queries & Connections panel to automatically repeat all of the steps. This is another hidden step, as the Queries & Connections panel starts out too narrow for the Refresh icon to appear. Make the panel wider to see Refresh.

SF SAYS

The steps to easily combine all sheets are easy to do but particularly hard to discover.

You may also like

Excel: When “Identical” Cells Don’t MATCH

Excel: Lookup Formulas Get a Makeover with XLOOKUP

We can copy data from multiple worksheets into one by following the simple steps outlined below. In this tutorial, we will learn how to merge Excel sheets into one. We will also explore how to merge two Excel sheets, merge multiple sheets and use a VBA to combine sheets.

Figure 1 – How to merge excel documents

Merge Sheets Excel 2013

Combine Excel files into one using the Power Query tool

The Power Query tool sheets provide a quick and easy way to combine a lot of worksheets into one. When we have just a few sets of workbooks we want to merge, we can use these steps:

  • We will open all the worksheets we wish to combine

Figure 2 – Sheet 1 for merge table from different sheets

Figure 3 – Sheet 2 for merging excel sheets into one

Figure 4 – Sheet 3 for merging excel sheets into one

  • In the blank sheet, we will go to the Data Tab
  • Next, we will click on Get External Data or Get Data (depending on Excel version)

Figure 5 – How to merge spreadsheets in excel

  • In the drop-down list, we will go to FromOther Sources option. If we have Excel 2016, we will click on New Query and select Other Sources option.

Figure 6 – Merge two spreadsheets

  • Next, we will click on Blank Query to open the Power Query editor

Figure 7 – How to merge two Excel Sheets

  • In the Editor, we will enter the formula below in the formula bar:

=Excel.CurrentWorkbook()

Merge All Sheets In One Sheet Excel

Figure 8 – combine excel files

  • We will hit the Enter key to show all table names

Figure 9 – merge excel documents

  • To combine tables, we will click on the double pointed arrow in the content header cell

Figure 10 – Merge multiple excel files

  • We will select all the columns we want to combine
  • We will uncheck the Use original column name as prefix option

Figure 11 – How to merge two Excel sheets

  • We will click OK
  • Our worksheets will combine into a single table in the Power Query tab.
  • After combining Excel sheets in the Power Query, we can load it in Excel
    • We will click on the File table
    • We will click on Close and Load To

Figure 12 – Merge Excel files using Power Query

    • In the Import Data dialog box, we will select Table and New Worksheet option

Figure 13 – Combine excel files into one

    • We will click OK

Figure 14 – How to combine multiple excel files into one worksheet.

Using a macro to combine multiple Excel files into one

When we have multiple Excel files, we can merge them in a swift manner using a VBA macro. We can easily use the macro displayed below.

  • We will click on Alt + F11 and select Visual Basic Editor
  • Next, we will right-click on This Workbook and select Insert, then Modules
  • In the window that opens up, we will enter the code displayed below

Sub MergeExcelFiles()

Dim fnameList, fnameCurFile As Variant

Dim countFiles, countSheets As Integer

Dim wksCurSheet As Worksheet

Dim wbkCurBook, wbkSrcBook As Workbook

fnameList = Application.GetOpenFilename(FileFilter:='Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm', Title:='Choose Excel files to merge', MultiSelect:=True)

If (vbBoolean <> VarType(fnameList)) Then

If (UBound(fnameList) > 0) Then

countFiles = 0

countSheets = 0

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Set wbkCurBook = ActiveWorkbook

For Each fnameCurFile In fnameList

countFiles = countFiles + 1

Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)

For Each wksCurSheet In wbkSrcBook.Sheets

countSheets = countSheets + 1

wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)

Next

wbkSrcBook.Close SaveChanges:=False

Next

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

MsgBox 'Processed ' & countFiles & ' files' & vbCrLf & 'Merged ' & countSheets & ' worksheets', Title:='Merge Excel files'

End If

Else

Sheet

MsgBox 'No files selected', Title:='Merge Excel files'

End If

End Sub

  • Now to run Macro, we will click on Alt +F8 to display Macro dialog
  • Next, we will select MergeExcelFiles and tap, Run
Merge all sheets in one sheet excel online

Figure 15 – How to use a macro to combine excel files

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our liveExcelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Coments are closed

Most Viewed Posts

  • Tor Browser In Chrome
  • Urgency Impact Priority Matrix
  • Free Speedify

Scroll to top