Advance Excel Training- Merge Multiple Worksheets Data With Excel Power Query (with Video)

Sometimes the data is spread over multiple worksheets and the requirement is to consolidate them in to a single worksheet, usually either we copy and paste data individually for each sheet or consolidate option of Ms-Excel can be used but in both cases either it is time consuming or needed a lot of manual intervention.

The best way to do this task is through “Power Query”. This option is available in Ms-Excel 2013, 2016 and office 365.

(Note:- Excel 2010 and previous version users can’t use this features. Excel 2013 users need download this Add ins through https://www.microsoft.com/en-in/download/details.aspx?id=39379.)

Let’s first understand the scenario in which we will be needing this features then we will get to the solution-

The Problem-

In this snapshot below, the data is divided in to four sheets i.e. North,West,South & East Zone.

Screen shot below showing the desire output after consolidation.

The solution-

Follow the below steps to get to the solution-

Step-1

Select the first sheet’s data and convert it into table format- Insert tab-> Table or Use short cut CTRL+T

Make sure that “My table has headers” option must be checked.

Step-2

Define a Name to your Table- Design tab -> Table Name (Type your Table name e.g North)

Repeat steps 1 & 2 for rest of the sheets.

Step-3

Ms-Excel 2016 : Go to Data Tab->From Table/Range

Ms-Excel 2013: Go to Power query tab-> From Table/Range

Step-4

Once you click on “From Table/Range”, Excel will open Power Query window. Go to Close & Load dropdown-> Select close and load to

Step-5

Choose Option “Only create connection”-> Ok

This will trigger queries and connection pane which confirms the establishment of data connection-

Repeat step 4 & 5 for rest of the sheets-

Step-6

Go to Data tab-> Get Data-> Combine Queries-> Append Queries

You will get two options-

Option 1-Two tables- if you have only two sheets then you can select one by one from the dropdown.

Option 2-Three or more tables- Use this option when you have more than three data sets.

Step-7

Voila!, you can see all the zone’s data are combined in Power Query window, however by mistake if you have selected two table option then you can add more queries by going to Home tab ->Append Queries->Append Queries