fbpx

Consolidate Multiple Excel Sheets Using Power Query

If you have multiple Excel worksheets that are in the same format and their underlying differences are their values and dates (e.g. January Sales List, February Sales List, March Sales-List, etc), then we can easily consolidate all the worksheets into one. The advanced excel function like Power Query will help you to consolidate multiple excel sheets into one.

MICROSOFT POWER QUERY FOR BEGINNERS
• Course Certificate • Lifetime Access & Free Updates • Lifetime Support • Career Focused

 

Consolidate Multiple Excel Sheets By Power Query

Let us understand this step by step.

Power BI For Dummies

POWER BI FOR DUMMIES

Step 1: Load Data in Power Query

First, load each of the worksheet data individually in Power Query Editor that you want to consolidate as shown below.

LOAD DATA IN POWER QUERY

Step 2: Close & Apply

Just close and apply each table after loading into Power Query Editor to apply the changes.

File  >  Close & Apply

CLOSE & APPLY

Step 3: Append Queries

To consolidate all the worksheets into one, Power Query has an amazing feature called Append Queries. This feature can combine all your data tables into one sheet in a vertical sequence. To use this feature, go to-

Home  >  Append Queries

 APPEND QUERIES

Step 4: Add Tables to Append

After you click on the Append Queries option, the Append Queries window gets open where you can add tables to append. Click on the option ‘Three or more tables’ so that you can add as many tables you want to. Then select the sheets one by one from the list and click on the Add button. Sheets added are displayed on the right side in the window. After you are done, click on OK.

MICROSOFT POWER QUERY TRAINING
• 100+ Video Tutorials • 15 Case Studies • 2 eBooks • 10 Presentation Decks • 2 Webinar • 24*7 Dedicated Support

 

ADD TABLES TO APPEND

Step 5: Scroll down to check the Appended tables

Now, you will find all your data into one single table as shown in below picture.

APPENDED TABLES

You can learn more :