Excel Power Query From the Same Workbook: How It Works

Yes. In Excel for Microsoft 365 on Windows, Power Query can read data stored in the same workbook: turn the source range into an Excel table, then choose Data > From Table/Range. You can also use the Excel.CurrentWorkbook() Power Query function to find tables and named ranges in the open workbook.

What “from the same workbook” means

Power Query is Excel’s tool for importing, cleaning, combining, and reshaping data. A query does not have to connect to a separate file or database. It can take a table or named range from the workbook that contains the query, apply transformation steps, and load the result back into that workbook or keep it as a connection.

There is an important distinction: Power Query reads workbook objects, not arbitrary worksheet tabs as complete data sources. The function Excel.CurrentWorkbook() returns tables, named ranges, and supported dynamic-array ranges in the current workbook; it does not return a list of ordinary worksheets. If your source is simply a block of cells on a sheet, make it a table or define a name for the range first.

This arrangement is useful when raw data and reports belong together. For example, a workbook could contain a table named SalesInput, a query that standardizes its dates and product names, and a separate output table used by a summary sheet. The query can be refreshed after someone adds rows to SalesInput.

How Power Query reads workbook data

When you choose Data > From Table/Range, Excel opens the Power Query Editor and creates a query based on the selected table or range. If the selected cells are not already a table, Excel asks whether to create one. A table is usually the better source because it has a defined name, expands when new rows are added, and makes the source easier to identify.

Behind the interface, Power Query uses the M language. The M expression Excel.CurrentWorkbook() returns a table describing the available workbook objects, including a name and a nested Content value for each object. It does not automatically clean or combine every object; you choose which object to open and what transformations to apply.

For a blank query, the Windows desktop route is Data > Get Data > From Other Sources > Blank Query. In Power Query Editor, use Home > Advanced Editor to enter M code, or use the formula bar to enter an expression. A simple query can begin with let Source = Excel.CurrentWorkbook() in Source. The resulting list helps you inspect the workbook objects and select a source. The exact labels can vary slightly between Excel builds.

Two practical ways to create the query

Choose the method that fits the job. The table-first route is direct and usually easiest for a single source; a blank query gives more control when you want to inspect available objects or build a more customized query.

From a selected table: click a cell in the source table and choose Data > From Table/Range. This opens the editor with that table as the source.
From a named range or another workbook object: create a blank query, call Excel.CurrentWorkbook(), and select the relevant object from the returned list. Named ranges can be useful when the source is a small fixed block or a parameter rather than a growing dataset.
From an existing query: in Power Query Editor, right-click a query and choose Reference to create another query based on its result. This is often preferable to loading that result to a sheet and then reading the sheet output back into a new query.

For a table, select a cell and check its name on the Table Design tab. Give it a clear name such as SalesInput rather than leaving an unclear default name. A descriptive name makes it easier to recognize the source later, especially when the workbook contains several tables.

A simple same-workbook example

Suppose a worksheet contains a monthly sales list with columns for OrderDate, Region, Product, and Amount. Convert the list to a table and name it SalesInput. Then select a cell in the table and choose Data > From Table/Range. In the editor, check that the date and amount columns have appropriate data types, remove blank rows if needed, and filter out any records that should not appear in the report.

After making the transformations, choose Home > Close & Load, or use the Close & Load options to select a destination. Loading to a worksheet creates an output table; choosing connection-only keeps the query available without placing its result in cells. The exact options shown depend on the query and Excel build. For a simple report, loading to a new worksheet is easy to inspect. For a query that only feeds another query, connection-only may keep the workbook tidier.

When new sales rows are entered directly beneath the source table, Excel normally expands the table. Refreshing the query then reads the expanded source and applies the same transformation steps. A query does not necessarily update the output immediately after a cell is edited; refresh it with Data > Refresh All, or refresh the individual query from the Queries & Connections pane.

Refresh behavior and query dependencies

Creating a same-workbook query does not create a live link that recalculates after every edit. Power Query runs when you refresh it, and its output represents the source data as processed at that time. This separation lets you review and clean data without changing the original input, but it also means a report can be out of date until refreshed.

Use Data > Refresh All when several queries feed the same report. To inspect or manage queries, choose Data > Queries & Connections. Right-clicking a query in the pane provides options such as refreshing it and opening its properties; available options can vary by query type. Query properties can include refresh-related settings, but these settings do not turn Power Query into automatic, cell-by-cell recalculation.

If one query depends on another, build the dependency in Power Query with Reference rather than repeatedly importing an intermediate output table. This keeps the steps explicit and can avoid needless worksheet loading. A practical structure is one source query for SalesInput, a cleaned query that references it, and a final query that groups or filters the cleaned data for a report.

Common problems and how to avoid them

The sheet does not appear in the source list: this is expected if it is just a worksheet. Convert the data to a table, or define a named range, then inspect the objects returned by Excel.CurrentWorkbook().
New rows are missing: check whether they were added inside the table or directly below it. If they are outside the table boundary, extend the table or add the rows to it, then refresh.
The query reads its own output: if a query loads a result table and another query reads that table as its source, the workbook can become difficult to refresh reliably. More seriously, a query that reads its own output can create a circular dependency. Use a distinct input table and query references instead of feeding a query its own result.
Column names or types change: transformations often refer to specific column names and data types. Renaming a source column, changing its type, or removing it can break a later step. Keep source headers stable, and review the applied steps if an error appears.
The output appears unchanged: confirm that the source cells changed, the source range is part of the table, and the correct query was refreshed. Also check whether the query is set to load to a connection rather than a visible worksheet table.

Power Query generally leaves its source data in place; its transformations produce a separate result. That is one reason to keep a clear distinction between input tables and query output tables. It also makes it easier to spot which data a refresh will read and which data it will replace.

Where query results can go

A query can load results to a worksheet table, remain as a connection, or in supported cases be added to the Data Model. A worksheet table is convenient when people need to inspect or reference the cleaned rows. A connection-only query is useful as an intermediate step when another query consumes it. The Data Model is intended for analysis across larger or related datasets, but it is not necessary for every workbook.

Consider the destination before loading a large result. Worksheets have a limit of 1,048,576 rows, so a result that exceeds that limit cannot fit as a normal sheet table. A Data Model or a smaller filtered result may be more appropriate, depending on the analysis and Excel edition. Loading a query to a sheet also takes workbook space and can make a file harder to navigate if many intermediate results are visible.

Excel desktop, Mac, and web differences

The steps above are for Excel for Microsoft 365 on Windows. Excel for Mac includes Power Query capabilities, but the available data sources, authoring features, and ribbon labels can differ by Excel version and update channel. If a path or command is missing on Mac, check the current Data tab and Microsoft’s support information for that specific build rather than assuming every Windows option is available.

Excel for the web does not provide the same complete Power Query authoring experience as Excel desktop. Some query refresh scenarios may be available depending on the workbook and source, but creating or editing a query can require the desktop app. For reliable setup and the menu paths described here, use the current desktop version of Excel.

Frequently Asked Questions

Can Power Query use data from another sheet in the same workbook?
Yes, if the sheet’s data is in an Excel table or a named range. Power Query does not treat a plain worksheet tab as a source through Excel.CurrentWorkbook(), so convert the relevant cells to a table or name the range first.

Will a query update automatically when I edit the source table?
Not in the same way as a worksheet formula. Power Query processes the source when refreshed. Use Data > Refresh All or refresh the relevant query; some refresh settings may offer additional scheduling behavior, but an ordinary edit does not guarantee an immediate query refresh.

Can I load the query result back onto the same sheet as the source?
It is possible to choose a worksheet destination, but keep the input and output areas separate. Do not place the result where it overwrites source cells, and avoid designing a query to read the table that it produces. A separate output sheet is usually clearer and safer.

Can I use a named range instead of an Excel table?
Yes. A named range is available as a workbook object to Excel.CurrentWorkbook(). It can suit a fixed block or a small parameter, while an Excel table is usually more convenient for records that grow over time.

Is a Power Query from the current workbook the same as a formula?
No. A query applies saved transformation steps when it runs, while a worksheet formula recalculates according to Excel’s calculation settings. Use a query for repeatable data preparation and import; use formulas when you need cell-level calculations that respond as worksheet values change.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.