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.
Consolidate Multiple Excel Sheets By Power Query
Let us understand this step by step.
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.
Step 2: Close & Apply
Just close and apply each table after loading into Power Query Editor to apply the changes.
File > 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
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.
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.
You can learn more :
- Unpivot Data Using Excel Power Query
- Learn How to Filter Records in Excel
- Consolidate Multiple Excel Workbooks in Power Query
- Split First & Last Name Using Power Query