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.
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.”
Step 2: Place your cursor in the first cell of the worksheet, go to the DATA tab, and click on the “Consolidate” option.
Step 3: This will open up below the “CONSOLIDATE” window.
Step 4: Since we are consolidating all the 4 region data, choose the option of “SUM” under the function drop-down list in excel.
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.
Step 6: Click on the “ADD” button to add the first reference area.
Now, this is added to the reference list.
Step 7: Next, you just have to go to the “South” sheet, and the reference range would have selected automatically.
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.
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 headerRow HeaderExcel Row Header is the grey column on the left side of column 1 in the worksheet that contains the numbers (1, 2, 3, etc.). To hide or reveal row and column headers, press ALT + W + V + H. 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.”
Ok, click on “Ok,” and we will have a summary table like the below.
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.
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 ExcelAdd-in For ExcelAdd-ins are different Excel extensions that can be found in the options section of the file tab. The first box displays the system's enabled add-ins, and if the user wishes to enable more, they must click on manage add-ins. 2010 & 2013 versions, and also it’s a built-in feature for Excel 2016 onwards versions.
Follow the steps to merge worksheets using power query in excel.
- Go to the Data tab and choose to Get data from that, choose “From File” then, From Excel Workbook.
- Select the sheet then transform it into a power query editor.
For this, we need to convert all the data tables into Excel TablesExcel TablesIn excel, tables are a range with data in rows and columns, and they expand when new data is inserted in the range in any new row or column in the table. To use a table, click on the table and select the data range.. 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”.
- Now this will open up the “Append” window.
- Here we need to merge more than one tables, so choose the “Three or more tables” option.
- Select the table “East” and click on the “Add” button.
Do the same for other region tables as well.
- After this, just click on “Ok,” it will open the “Power Query Editor” window.
- Click on the “Close and Load” option.
This will merge all the sheets into one in a new worksheet of the same workbook.
Things to Remember
- Power Query in excelPower Query In ExcelPower Query in Excel is a case-sensitive tool that helps the users search data sources, associate with data sources, and then shape the database according to their requirement. The users can even share their findings and create multiple reports using more query tools. 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 formatExcel Table FormatExcel comes with a number of table styles that you may quickly apply to a table format. In Excel, you can design and use a new custom table style of your choice. .
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 –