How to consolidate data from multiple excel sheets using Power Query

Power Query is a powerful data transformation tool that allows you to combine data from multiple excel sheets into one table. 

It is a stock feature in Office 365 and Microsoft Excel 2016 and onward versions. You can also download Power Query as an add-in if you have Excel 2010 and 2013. 

How to combine data from multiple worksheets in one table using Power Query 

If you want to consolidate data from multiple sheets using Power Query, the data must be in an Excel Table or named ranges. The structure of the table should be consistent for each worksheet (ensure the headers are the same). 

For example, in our case, we have three worksheets – March Sales, April Sales and May Sales. Each worksheet has the same header – as seen in the attached screenshot. 

To combine your worksheets with Excel Tables using Power Query, follow the following steps: 

• Navigate and click on the Data tab. 

• Click the ‘Get Data’ drop-down menu. 

• Select the ‘From Other Sources’ option. 

• Choose ‘Blank Query’ and allow Power Query some seconds to open the Power Query editor. 

• Navigate to the Query editor and type: =Excel.CurrentWorkbook() formula in the formula bar. It is crucial to type in the exact formula because Power Query formulas are case-sensitive. 

• Once you click Enter on your keyboard, you will see all the table names in the workbook. You will also see any named ranges and connections present in the workbook. 

• If you wish to consolidate specific Excel Tables, click the drop-down icon in the name header to choose the tables that you want to combine. However, in this example, we want to combine all the tables. 

• Click on the double-pointed arrow. 

• Choose the columns you want to combine – if you don't wish to combine all columns. If otherwise, check the tiny box next to ‘Select All Columns’ if you want to combine all columns. 

• Untick the ‘Use original column name as prefix’ option. Then click Ok.

Once you are done with the steps, data from your worksheet tables will be combined into one table. You can edit and rename this table according to your preference before you load it in Excel as a new table in a new worksheet. 

Note: Everything you edit will be recorded on the right side of the screen as shown. You can undo your change by pressing on the X. 

How to load consolidated tables in Excel as a new table in a new worksheet 

Once you have combined your tables, follow the following steps to add them as a new table in a new worksheet: 

• Click ‘File’. 

• Select ‘Close and Load To.’ 

• Choose how you want to view your data in the Import Data dialogue box that appears. In this case, we will select the Table and New worksheet options. 

• Click Ok. 

You will see all your data combined in one table on your worksheet. 

Need further guidance? 

Feel free to contact us if you have any questions on Excel, PowerBi and AccountingBi.