This will show you all the table names in the entire workbook (it will also show you the named ranges and/or connections in case it exists in the workbook). Note that the Power Query formulas are case sensitive, so you need to use the exact formula as mentioned (else you will get an error). In the Query editor, type the following formula in the formula bar: =Excel.CurrentWorkbook ().In the Get & Transform Data group, click on the ‘Get Data’ option.Here are the steps to combine multiple worksheets with Excel Tables using Power Query: I have given the tables the following names: East_Data, West_Data, North_Data, and South_Data. This kind of data is extremely easy to combine using Power Query (which works really well with data in Excel Table).įor this technique to work best, it’s better to have names for your Excel Tables (work without it too, but it’s easier to use when the tables are named). Suppose you have four different sheets – East, West, North, and South.Įach of these worksheets has the data in an Excel Table, and the structure of the table is consistent (i.e., the headers are same).Ĭlick here to download the data and follow along. If the data is not in an Excel Table, the method shown here would not work.

When combining data from different sheets using Power Query, it’s required to have the data in an Excel Table (or at least in named ranges). Combine Data from Multiple Worksheets Using Power Query Based on your version, some images may look different (image captures used in this tutorial are from Excel 2016). Note: Power Query can be used as an add-in in Excel 20, and is an inbuilt feature from Excel 2016 onwards. So I decided to write this tutorial and show the exact steps to combine multiple sheets into one single table using Power Query.īelow a video where I show how to combine data from multiple sheets/tables using Power Query:īelow are written instructions on how to combine multiple sheets (in case you prefer written text over video). I asked him to use Power Query to combine different sheets, but then I realized that for someone new to Power Query, doing this can be tough. I recently got a question from a reader about combining multiple worksheets in the same workbook into one single worksheet.

Stacking excel drop downs excel 2016