How to merge every Excel table into one sheet
By the Analistable team · Updated · 5 min read
To collect every table in a workbook onto one sheet without listing them, create a blank Power Query with =Excel.CurrentWorkbook(), filter out the result table itself, and expand the Content column. Tables you add later are picked up on refresh, and the Name column tells you which table each row came from.
Part of our guide: How to combine Excel sheets into one
When this technique helps
Some workbooks get a new table every month or for every client. Listing each one in VSTACK or in an Append step means editing the query every time. Excel.CurrentWorkbook() returns every table (and named range) in the current file, so one query covers all of them, now and later.
Step by step
- Make sure each block of data is an Excel table (Ctrl+T) with the same column headers.
- Choose Data → Get Data → From Other Sources → Blank Query.
- In the formula bar, enter
= Excel.CurrentWorkbook()and press Enter. You see one row per table, with Name and Content columns. - Filter the Name column to keep only the tables you want (for example, names that begin with “Sales_”).
- Click the expand icon on Content, untick Use original column name as prefix, and click OK.
- Rename the query (for example
AllSales) and Close & Load.
The full query
let
Source = Excel.CurrentWorkbook(),
SalesTables = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_")),
Combined = Table.ExpandTableColumn(
SalesTables, "Content",
Table.ColumnNames(SalesTables{0}[Content])
)
in
CombinedFiltering by a name prefix is important: when you load the result back into the workbook as a table, Excel.CurrentWorkbook() would otherwise include that table too and double every row on the next refresh.
If the tables' columns differ, replace the expand step with Table.Combine(SalesTables[Content]) to keep every column. See combining files with different columns.
Result
| Name | Order | Product | Amount |
|---|---|---|---|
| Sales_Jan | 1001 | Desk | 420 |
| Sales_Jan | 1002 | Lamp | 65 |
| Sales_Feb | 1003 | Chair | 180 |
| Sales_Mar | 1004 | Desk | 420 |
Alternatives
- A fixed set of tables:
=VSTACK(Sales_Jan, Sales_Feb, Sales_Mar)— see combining Excel tables. - Tables in separate files: use From Folder — see combining Excel files into one spreadsheet.
- No Power Query: the sheet consolidator stacks every sheet of a workbook in your browser.
Keep the tables consistent
- Use one naming pattern for every table (Sales_Jan, Sales_Feb) so the filter catches new ones.
- Keep the same headers in every table; rename columns before they're added, not after.
- Set column types in the combined query (dates, decimals) rather than relying on each table's formatting.
Add the table name as a column
Excel.CurrentWorkbook() returns a Name column alongside each table's Content. Keep it when you expand Content and every row carries the name of the table it came from — useful when the tables are named Sales_Jan, Sales_Feb and so on. You can then split the name into a month with Transform → Split Column → By Delimiter, using the underscore.
Only some tables
If the workbook also contains lookup tables you don't want to stack, filter the Name column before expanding. A naming convention makes this simple: start every table to combine with the same prefix and filter with Text Filters → Begins With. In M the step looks like this:
Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], "Sales_"))The filter also stops the query from picking up its own output table, which is the usual cause of rows doubling on refresh.
Worked example: four tables, one sheet
A workbook holds Sales_Jan (120 rows), Sales_Feb (98 rows), Sales_Mar (134 rows) and a Rates table. Filtering to names beginning with Sales_ and expanding gives 120 + 98 + 134 = 352 rows. After the first refresh, the output table, called something like Query1 or the query's name, also appears in Excel.CurrentWorkbook(); because it does not start with Sales_, the filter keeps it out and the result stays at 352 rows.
Tables with different columns
When you click the expand button, Power Query lists the columns of the tables it sampled. If a later table has an extra column, it may be left out of the list. To make sure every column is included, replace the fixed column list in the expand step with one built from all the tables:
Columns = List.Union(List.Transform(Filtered[Content], Table.ColumnNames)),
Expanded = Table.ExpandTableColumn(Filtered, "Content", Columns)Missing values become null. Clean them with Transform → Replace Values if you need zeros instead.
Troubleshooting
| Symptom | Cause | Fix |
|---|---|---|
| Rows double on every refresh | The query reads its own output table | Filter the Name column to the source tables |
| A new table isn't included | Its name doesn't match the filter | Rename it to the agreed prefix |
| A column is missing | Expand used a fixed column list | Build the list with List.Union |
| Numbers arrive as text | Data type set to Any after the expand | Set types in a step after the expand |
| Excel.CurrentWorkbook not available | Ranges are not formatted as tables | Press Ctrl+T on each range and name it |
For tables spread over several files rather than one workbook, see combining Excel files into one spreadsheet.
Common mistakes
- Loading the result to a table whose name matches the filter prefix, for example naming the output Sales_All. It is then read back in on the next refresh.
- Setting column types before the expand step; types have to be applied to the expanded columns.
- Converting a range to a table but leaving the default name Table3, so it can't be recognised by prefix.
- Editing the output table by hand. Typed changes are lost the next time the query refreshes, so make corrections in the source tables.
To repeat the process monthly, add the new month as another table with the agreed prefix and press Data → Refresh All; nothing in the query needs editing.
Frequently asked questions
- How do I combine all tables in a workbook?
- Use a blank Power Query with =Excel.CurrentWorkbook(), filter the table names you want and expand the Content column.
- Why are my rows doubled after refresh?
- The query is reading its own output table. Filter Excel.CurrentWorkbook() by a name prefix so the result table is excluded.
- Does Excel.CurrentWorkbook include named ranges?
- Yes, it returns tables and workbook-level named ranges. Filter by name to keep only what you need.