How To Combine Data From Multiple Tabs In Excel

The tutorial demonstrates different ways to combine sheets in Excel depending on what consequence you are later - consolidate data from multiple worksheets, combine several sheets by copying their data, or merge two Excel spreadsheets into one by the key column.

Today we will tackle a trouble that many Excel users are struggling with daily - how to merge multiple Excel sheets into ane without copying and pasting. The tutorial covers 2 nearly common scenarios: consolidating numeric information (sum, count, average, etc.) and merging sheets (i.eastward. copying data from multiple worksheets into one).

How to consolidate data in Excel
How to merge Excel sheets into one
- Combine sheets with Ultimate Suite
- Merge sheets using VBA lawmaking
- Combine data from multiple worksheets with PowerQuery
- Merge ii Excel sheets into one past the fundamental cavalcade

Consolidate information from multiple worksheets in a single worksheet

The quickest way to consolidate data in Excel (located in ane workbook or multiple workbooks) is by using the built-in Excel Consolidate feature.

Allow'south consider the following case. Supposing yous take a number of reports from your company regional offices and y'all desire to consolidate those figures into a principal worksheet and then that you accept i summary report with sales totals of all the products.

As you lot see in the screenshot beneath, the three worksheets to be consolidated have a similar data structure, simply different numbers of rows and columns:
The source worksheets to be consolidated into a summary sheet.

To consolidate the information in a unmarried worksheet, perform the following steps:

Arrange the source data properly. For the Excel Consolidate feature to work correctly, make sure that:
- Each range (data ready) yous want to consolidate resides on a split up worksheet. Don't put whatsoever information on the sail where you lot programme to output the consolidated data.
- Each canvass has the same layout, and each column has a header and contains similar data.
- In that location are no bare rows or columns inside whatever listing.
Run Excel Consolidate. In the master worksheet, click the upper-left cell where you want the consolidated data to announced, go to the Data tab and click Consolidate.

Tip. It'south is advisable to consolidate data into an empty sail. If your principal worksheet already has some data, make sure there is plenty space (bare rows and columns) to contain the merged data.
Configure the consolidation settings. The Consolidate dialog windows appears and you do the following:
- In the Function box, select one of the summary functions you want to use to consolidate your data (Count, Average, Max, Min, etc.). In this example, we select Sum.
- In the Reference box, clicking the Plummet Dialog icon and select the range on the first worksheet. And then click the Add together push button to have that range added to the All references Repeat this pace for all the ranges y'all desire to consolidate.
If one or some of the sheets reside in another workbook, click the Browse bottom to locate the workbook.
Configure the update settings. In the same Consolidate dialog window, select whatsoever of the following options:
- Bank check the Top row and/or Left cavalcade boxes under Use labels if you want the row and/or column labels of the source ranges to be copied to the consolidation.
- Select the Create links to source data box if you want the consolidated data to update automatically whenever the source data changes. In this case, Excel will create links to your source worksheets besides as an outline similar in the following screenshot.
If you expand some group (by clicking the plus outline symbol), and then click on the cell with a certain value, a link to the source data will display in the formula bar.

As you lot run across, the Excel Consolidate feature is very helpful to pull together data from several worksheets. However, it does have a few limitations. In particular, it works for numeric values only and it always summarizes those numbers in 1 way or another (sum, count, average, etc.)

If you want to merge sheets in Excel past copying their data, the consolidation option is non the manner to go. To combine just a couple of sheets, you may non need annihilation else just the good old re-create/paste. Just if you are to merge tens of sheets, errors with manual copying/pasting are inevitable. In this case, y'all may want to employ ane of the following techniques to automate the merge.

How to merge Excel sheets into 1

Overall, in that location are four ways to merge Excel worksheets into one without copying and pasting:

Combine sheets with Ultimate Suite
VB scripts
Ability Query for Excel
Merge ii Excel sheets into ane by the key column(southward)

How to combine Excel spreadsheets with Ultimate Suite

The built-in Excel Consolidate feature tin summarize data from different sheets, just it cannot combine sheets by copying their information. For this, you can employ 1 of the merge & combine tools included with our Ultimate Suite for Excel.

Combine multiple worksheets into i with Copy Sheets

Supposing you have a few spreadsheets that contain information near different products, and now you need to merge these sheets into one summary worksheet, like this:
Combine multiple Excel spreadsheets into one.

With the Copy Sheets added to your ribbon, the 3 simple steps is all it takes to merge the selected sheets into one.

Kickoff the Re-create Sheets Sorcerer.
On the Excel ribbon, become to the Ablebits tab, Merge group, click Copy Sheets, and choose one of the following options:
- Copy sheets in each workbook to 1 sheet and put the resulting sheets to one workbook.
- Merge the identically named sheets to one.
- Re-create the selected sheets to one workbook.
- Combine data from the selected sheets to i sheet.
Since we are looking to combine several sheets by copying their information, we selection the last option:
Select worksheets and, optionally, ranges to merge.
The Copy Sheets sorcerer displays a list of all the sheets in all open workbooks. Select the worksheets yous want to combine and click Next.

If you lot don't want to re-create the entire content of a sure worksheet, make use of the Collapse Dialog icon to select the desired range as shown in the screenshot below.

In this example, nosotros are merging the first iii sheets:

Tip. If the worksheets you want to merge reside in some other workbook that is currently closed, click the Add together files... push to browse for that workbook.
Choose how to merge sheets.
In this step, you lot are to configure additional settings so that your worksheets will be combined exactly the way you desire.

How to paste the data:
- Paste all - re-create all the data (values and formulas). In nearly cases, it is the selection to choose.
- Paste values only - if yous don't desire formulas from the original sheets to be pasted into the summary worksheet, select this option.
- Create links to source data - this volition inset formulas linking the merged data to the source data. Select this option if you desire the merged data to update automatically whenever any of the source information changes. It works similarly to the Create links to source information option of Excel Consolidate.
How to conform the data:
- Place copied ranges one nether another - conform the copied ranges vertically.
- Identify copied ranges side past side - conform the copied ranges horizontally.
How to re-create the data:
- Preserve formatting - cocky-explanatory and very convenient.
- Separate the copied ranges by a blank row - select this option if y'all want to add an empty row between information copied from different worksheets.
- Copy tables with their headers. Cheque this selection if you want the tabular array headers to exist included in the resulting sail.
The screenshot below shows the default settings that work only fine for the states:

Click the Copy button, and you lot will accept the information from iii different sheets merged into 1 summary worksheet like shown in the beginning of this example.

Other ways to combine sheets in Excel

Autonomously from the Copy Sheets magician, the Ultimate Suite for Excel provides a few more merging tools to handle more specific scenarios.

Case 1. Merge Excel sheets with a different club of columns

When you are dealing with the sheets created past different users, the order of columns is often different. How practise y'all handle this? Will you be copying the sheets manually or moving columns in each canvas? Neither! Commit the job to our Combine Sheets wizard:
Combine Sheets for Excel

And the data will be combined perfectly past column headers:
The data from different sheets are combined by column headers.

Example 2. Merge specific columns from multiple sheets

If you have actually large sheets with tons of different columns, you may want to merge only the most important ones to a summary table. Run the Combine Worksheets sorcerer and select the relevant columns. Yep, information technology's that easy!
Merge specific columns from multiple sheets.

As the upshot, but the data from the columns that you selected get into the summary canvas:
Only selected columns from multiple sheets are merged.

These examples have demonstrated but a couple of our merge tools, only there is much more to information technology! After experimenting a fleck, you will see how useful all the features are. The fully functional evaluation version of the Ultimate Suite is bachelor for download at the finish of this post.

Merge sheets in Excel using VBA lawmaking

If you are a power Excel user and feel comfortable with macros and VBA, you can combine multiple Excel sheets into i by using some VBA script, for instance this 1.

Delight continue in heed that for the VBA code to work correctly, all of the source worksheets must take the same structure, the same column headings and aforementioned cavalcade gild.

Combine data from multiple worksheets with Power Query

Power Query is a very powerful engineering science to combine and refine data in Excel. At that, it'south rather complex and requires a long learning bend. The following tutorial explains the common uses in detail: Combine data from multiple data sources (Power Query).

How to merge ii Excel sheets into one by the key cavalcade(s)

If you are looking for a quick fashion to match and merge information from two worksheets, then you tin either utilise the Excel VLOOKUP function or embrace the Merge Tables Wizard. The latter is a visual user-friendly tool that lets you compare 2 Excel spreadsheets by a common column(s) and pull matching data from the lookup table. The following screenshot demonstrates one of possible results.
Merging two Excel sheets into one by the key column

The Merge Tables wizard is also included with the Ultimate Suite for Excel.

This is how y'all consolidate data and merge sheets in Excel. I hope you volition notice the information in this curt tutorial helpful. Anyhow, I thank you for reading and wait forward to seeing you on this blog adjacent week!

Available downloads

Ultimate Suite 14-day fully-functional version (.cypher file)

You lot may also exist interested in

97 comments to "Consolidate data in Excel and merge multiple sheets into one worksheet"