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-
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.
Follow the below steps to get to the solution-
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.
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.
Ms-Excel 2016 : Go to Data Tab->From Table/Range
Ms-Excel 2013: Go to Power query tab-> From Table/Range
Once you click on “From Table/Range”, Excel will open Power Query window. Go to Close & Load dropdown-> Select close and load to
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-
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.
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
Click on Close & Load
Finally, you will get combined data in new Excel worksheet
You can download Dummy practice excel file?https://drive.google.com/open?id=17tgt_fdH8OU-ZiZBI4OMAy-w2J9avLAf
Advance Excel Training- Merge Multiple Worksheets Data With Excel Power Query – Video
Happy Learning 🙂
For any advance excel training in corporate or for individual query, kindly contact us.
381 total views, 3 views today
Tag:Consolidate, corporate training courses, corporate training in delhi, corporate training programs, Excel, excel course online, excel courses online, excel online course, excel online training, excel training in delhi, excel training online, learn microsoft excel, learn ms excel online, learning excel online, learning microsoft excel, Merge, microsoft excel course, microsoft excel online training, microsoft excel training, ms excel learning, ms excel training in delhi, online corporate training, online excel course, online excel training, online excel training courses, Power Query