How to consolidate data from multiple excel sheets using Power Query

September 6, 2022

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. 

Worksheet with multiple Excel sheets

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

  • Navigate and click on the Data tab. 

Data tab in Excel

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

Get Data drop down menu in Excel

  • Select the ‘From Other Sources’ option. 

From other sources tab in Excel

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

Blank query in Excel when consolidating multiple worksheets

  • 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. 

Powerquery formula when consolidating multiple excel sheets in one worksheet

  • 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. 

Consolidating different excel sheets in one work sheet

  • 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. 

Consolidate multiple excel sheets in one worksheet

  • Click on the double-pointed arrow. 

Multiple excel sheets into one worksheet table

  • 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. 

multiple excel sheets consolidated in one excel table

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

How to combine multiple excel sheets into a single table

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. 

Consolidated table

Consolidated table edited

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. 

Consolidated table - how to make changes

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’. 

Click File

  • Select ‘Close and Load To.’ 

Turning consolidated excel sheets into one table in Excel

  • 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. 

Consolidated excel sheets in one excel table

  • Click Ok. 

Click OK

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

Consolidated table with tables from multiple excel sheets

Need further guidance? 

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

learn more

Why is PowerBI important for Accountants?

As an accountant, you may find yourself referring to multiple sources to track the performance of your client’s cash flow, profitability, business trends and even bookkeeping cycle. What if you could access those results and comparisons automatically refreshed, on-demand, from your tablet, smartphone, PC, or even wearable device? Business intelligence and data visualisation systems can provide accountants with – the critical data and organisation summary in a custom format in one (or more) visuals or a dashboard. One of these powerful business intelligence tools is PowerBI.

Power BI: What is DAX?

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.

How to connect your Xero account(s) to AccountingBi

This guide will walk you through two methods for connecting your Xero accounting software to AccountingBi

How to call your profit and loss account reports from Xero to AccountingBi

We have outlined a step-by-step process of calling your profit and loss report data from your Xero account to AccountingBi - article and video.

How to call the balance sheet reports from Xero to AccountingBi

We have outlined the steps of calling balance sheet reports from Xero to AccountingBi in a video (watch below) and an article.

How to connect Microsoft excel to Xero through AccountingBi

This video and article provide steps for connecting Xero to PowerBi through AccountingBi. It assumes that you've already pulled the Xero API from accounting BI for your various reports.
1 2 3 5
READ MORE - Business Intelligence Software
Copyright © 2023 is a brand owned and operated by Accounting SQL Limited, incorporated in the United Kingdom. ​
Terms & Conditions - GDPR