WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Worksheet Merge

Excel Worksheet Merge

Merge Worksheet in Excel

Merging multiple sheets into one worksheet one by one is a tough task, but thankfully we have a feature called “Consolidate” in excel, and also from Excel 2010 onwards, we can use “Power Query” as a worksheet merger. In this article, we will show you how to merge worksheets into one.

Getting the data in multiple worksheets is quite common but combining all the worksheet data into once is the job of the person who receives the data in different sheets.

Merger Worksheet Using Consolidate Option

The easiest and quickest way to merge multiple worksheets data into one is by using the built-in feature of excel “Consolidate.” For example, look at the below data in excel sheets.

Eastwest

SouthNorth

In the above image, we have four worksheets which comprise of four different regions product-wise sales numbers across months.

From the above four sheets, we need to create one single sheet to show all the summary results. Follow the below steps to consolidate worksheets.

Step 1: Create a new worksheet and name it as a “Consolidated Sheet.”

merge worksheet Step 1

Step 2: Place your cursor in the first cell of the worksheet, go to the DATA tab, and click on the “Consolidate” option.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Data - Consolidate

Step 3: This will open up below the “CONSOLIDATE” window.

merge worksheet Step 3

Step 4: Since we are consolidating all the 4 region data, choose the option of “SUM” under the function drop-down list in excel.

merge worksheet Step 4

Step 5: Next, we need to choose the reference range from the first sheet to the last sheet. Place your cursor inside the reference box, go to the EAST sheet, and choose the data.

merge worksheet Step 5

Step 6: Click on the “ADD” button to add the first reference area.

merge worksheet Step 6 - Add

Now, this is added to the reference list.

merge worksheet Step 6 - Added

Step 7: Next, you just have to go to the “South” sheet, and the reference range would have selected automatically.

merge worksheet Step 7

Step 8: Click on “ADD” again, and the second sheet reference is added to the list. Like this, repeat the same for all the sheets.

merge worksheet Step 8

Now we have added all the 4 sheets of references. One more thing we need to do finally is while selecting each region range we have selected, including the row header and column header of the data table, so to bring the same into the consolidated sheet, check the boxes of “Top Row” and “Left Column” and also check the box of “Create links to Source Data.”

merge worksheet Step 9

Ok, click on “Ok,” and we will have a summary table like the below.

merge worksheet Step 9 - Consolidated data

As you can see above, we have two grouped sheet numbers as 1 & 2. If you click on 1, it will show all the region’s consolidated table, and if you click on 2, it will show the breakup of each zone.

merge worksheet Step 9 (group 2 view)

Looks fine, but this is not the kind of merging of worksheets, so actually merging is combining all the worksheets into one without any calculations; for this, we need to use the Power Query option.

Merge Worksheets by Using Power Query

Power Query is an add-in for Excel 2010 & 2013 versions, and also it’s a built-in feature for Excel 2016 onwards versions.

Go to the Data tab and choose to “Get data” from that, choose “From File” then, From Excel Workbook.

merge worksheet (Get Data)

Select the sheet then transform it into a power query editor.

 

For this, we need to convert all the data tables into Excel Tables. We have converted each data table into Excel Table and name by their region names as East, South, West, and North.

First, go to any of the sheets, under the Power Query, click on “Append.”

merge worksheets (Append Queries)

Now this will open up the “Append” window.

merge worksheets (Append window)

Here we need to merge more than one tables, so choose the “Three or more tables” option.

Select table

Select the table “East” and click on the “Add>>” button.

Add East

Do the same for other region tables as well.

merge worksheets (added all Tables)

After this, just click on “Ok,” it will open the “Power Query Editor” window.

merge worksheets (Table editor)

Click on the “Close & Load” option.

Close & load

This will merge all the sheets into one in a new worksheet of the same workbook.

merge worksheets (loaded table)

Things to Remember

  • Power Query in excel is available for Excel 2010 & 2013 as Add-in, and from Excel 2016 onwards, this is a built-in tab.
  • Consolidate is used to consolidate different worksheets into one based on arithmetic calculations.
  • For Power Query merge, we need to convert the data into excel table format.

Recommended Articles

This has been a guide to Excel Worksheet Merge. Here we discuss how to merge worksheets into one using the consolidate and power query option along with practical examples. You may learn more about excel from the following articles –

  • Power Query Tutorial
  • Column Merge Excel
  • Examples of Combine Cells in Excel
  • Merge Tables Excel
  • Unmerge Cells Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More