{"id":7893,"date":"2018-04-11T09:41:03","date_gmt":"2018-04-11T09:41:03","guid":{"rendered":"https:\/\/www.nurturetechacademy.in\/?p=7893"},"modified":"2018-04-13T12:49:55","modified_gmt":"2018-04-13T12:49:55","slug":"advance-excel-training-merge-multiple-worksheets-data-excel-power-query","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/advance-excel-training-merge-multiple-worksheets-data-excel-power-query\/","title":{"rendered":"Advance Excel Training- Merge Multiple Worksheets Data With Excel Power Query (with Video)"},"content":{"rendered":"

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.<\/p>\n

The best way to do this task is through \u201cPower Query\u201d. This option is available in Ms-Excel 2013, 2016 and office 365.<\/p>\n

(Note:- Excel 2010 and previous version users can\u2019t use this features. Excel 2013 users need download this Add ins through https:\/\/www.microsoft.com\/en-in\/download\/details.aspx?id=39379<\/a>.)<\/p>\n

Let\u2019s first understand the scenario in which we will be needing this features then we will get to the solution-<\/p>\n

The Problem-<\/strong><\/p>\n

In this snapshot below, the data is divided in to four sheets i.e. North,West,South & East Zone.<\/p>\n

\"image1\"<\/p>\n

Screen shot below showing the desire output after consolidation.<\/p>\n

\"image<\/p>\n

The solution-<\/strong><\/p>\n

Follow the below steps to get to the solution-<\/p>\n

Step-1<\/strong><\/p>\n

Select the first sheet\u2019s data and convert it into table format- Insert tab-> Table or Use short cut CTRL+T<\/strong><\/p>\n

\"image-2\"<\/p>\n

Make sure that \u201cMy table has headers\u201d option must be checked.<\/p>\n

\"image-3\"<\/p>\n

Step-2<\/strong><\/p>\n

Define a Name to your Table- Design tab -> Table Name (Type your Table name e.g North)<\/p>\n

\"image-4\"<\/p>\n

Repeat steps 1 & 2 for rest of the sheets.<\/p>\n

Step-3<\/strong><\/p>\n

Ms-Excel 2016 : Go to Data Tab->From Table\/Range<\/p>\n

Ms-Excel 2013: Go to Power query tab-> From Table\/Range<\/p>\n

\"image-5\"<\/p>\n

Step-4<\/strong><\/p>\n

Once you click on \u201cFrom Table\/Range\u201d, Excel will open Power Query window. Go to Close & Load dropdown-> Select close and load to<\/p>\n

\"image-6\"<\/p>\n

Step-5<\/strong><\/p>\n

Choose Option \u201cOnly create connection\u201d-> Ok<\/p>\n

\"image-7\"<\/p>\n

This will trigger queries and connection pane which confirms the establishment of data connection-<\/p>\n

\"image-8\"<\/p>\n

Repeat step 4 & 5 for rest of the sheets-<\/p>\n

\"image-9\"<\/p>\n

Step-6<\/strong><\/p>\n

Go to Data tab-> Get Data-> Combine Queries-> Append Queries<\/p>\n

\"image-10\"<\/p>\n

You will get two options-<\/p>\n

Option 1-Two tables- if you have only two sheets then you can select one by one from the dropdown.<\/p>\n

Option 2-Three or more tables- Use this option when you have more than three data sets.<\/p>\n

\"image-11\"<\/p>\n

\"image-11p\"<\/p>\n

Step-7<\/strong><\/p>\n

Voila!, you can see all the zone\u2019s 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<\/p>\n

\"image-12\"<\/p>\n

Click on Close & Load<\/p>\n

\"image-13\"<\/p>\n

Finally, you will get combined data in new Excel worksheet<\/p>\n

\"image<\/p>\n

You can download Dummy practice excel file?https:\/\/drive.google.com\/open?id=17tgt_fdH8OU-ZiZBI4OMAy-w2J9avLAf<\/a><\/p>\n

 <\/p>\n

Advance Excel Training- Merge Multiple Worksheets Data With Excel Power Query – Video<\/strong><\/span><\/p>\n

https:\/\/www.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Merge-Multiple-sheets-in-to-one-in-Excel-with-Power-Query.mp4<\/a><\/video><\/div>\n

 <\/p>\n

Happy Learning \ud83d\ude42<\/p>\n

 <\/p>\n

For any advance excel training in corporate or for individual query, kindly contact us<\/a>.<\/p>\n

 <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

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 <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":7914,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[241,263,243,264,57,257,260,259,249,256,248,244,250,252,245,242,253,254,246,251,255,262,258,247,261,240],"class_list":["post-7893","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-consolidate","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-merge","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses","tag-power-query"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=7893"}],"version-history":[{"count":6,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7893\/revisions"}],"predecessor-version":[{"id":7929,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7893\/revisions\/7929"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/7914"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=7893"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=7893"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=7893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}