In Excel for Microsoft 365 on Windows, the usual way to query an Excel table is to select a cell in it and choose Data > From Table/Range; Power Query opens so you can filter, clean, and reshape the data, then load the result back into Excel. If you only need a formula-driven selection that updates as cells change, use a function such as FILTER instead.
What “Excel table query” can mean
The phrase can describe more than one task. In Excel, a “table” is a structured range with column headers and built-in filtering, while a “query” is a set of instructions for getting and shaping data. Power Query is Excel’s main tool for creating repeatable data-import and transformation queries. A reader may also mean a formula that selects matching table rows, or a SQL query against a database.
These approaches solve related but different problems:
- Power Query: Import, combine, clean, and reshape data, then refresh the result when the source changes.
- Excel formulas: Calculate or display matching values directly in the worksheet, often updating immediately when inputs change.
- SQL: Request data from a supported database using a query language, usually through Excel’s data-connection tools.
For recurring cleanup or combining files, Power Query is usually the best starting point. For a quick live list of rows matching a condition, a formula may be simpler. A normal Excel table’s filter arrows are useful for viewing a subset, but filtering the display is not the same as building a reusable query.
Prepare the source table
Power Query works best when the source is a well-organized rectangle: one header row, one record per row, and one kind of information per column. Avoid merged cells, blank header names, subtotal rows mixed into the records, and multiple header rows. For example, a sales list might have columns named Date, Region, Rep, and Amount, with each transaction on its own row.
To turn a range into a formal Excel table, select a cell in the data and press Ctrl+T on Windows, or Command+T on Mac. Check that “My table has headers” is selected if the first row contains field names. You can also use Insert > Table. Give the table a useful name from Table Design > Table Name, such as Sales; avoid spaces in the name. A formal table expands as new records are added directly beneath it and provides stable column names for formulas and queries.
Before querying, check that dates are real date values, amounts are numeric, and headers are unique and descriptive. A value that looks like a date but is stored as text can sort incorrectly or fail date-based filters. Fixing obvious source problems first makes later query steps easier to understand.
Create a query from an Excel table
- Click any cell inside the table you want to query.
- On Windows, choose Data > From Table/Range. If Excel asks to create a table, confirm the data range and header setting.
- In the Power Query Editor, select a column’s filter button to keep or exclude values, or use the ribbon to remove columns, change data types, sort, split, or group data.
- When the preview has the fields and records you need, choose Home > Close & Load to put the result in a worksheet. Use Home > Close & Load To if you want to select a destination or load the query to the Data Model instead.
Excel for Mac also includes Power Query in supported Microsoft 365 versions, but the available connectors and exact commands can vary by version and update channel. Look under Data > Get Data (Power Query) for the table or range import option. Excel for the web can work with some existing queries, but query creation and editing capabilities are not identical to the desktop app; use desktop Excel if the editor or connector you need is unavailable.
What Power Query does behind the scenes
Power Query records transformation steps rather than changing the original source table. A typical query might promote the first row to headers, set the Date column to a date type, remove blank records, keep only rows for a particular region, and load the cleaned result to a new worksheet. The result is separate from the source, so filtering or removing columns in the query does not delete those fields from the original table.
The Applied Steps list in the editor shows the sequence of transformations. Selecting a step lets you inspect the preview at that point; changing or deleting a step can alter everything that follows it. This step-based design is useful when monthly files follow the same layout: replace or append source data, refresh the query, and let Excel repeat the documented transformations. It is not a recording of every action in the workbook, and it does not automatically infer every business rule.
When a query is loaded to a worksheet, Excel usually creates a separate output table. Refreshing updates that output from the source and the saved steps. If you type manual notes beside or within the output, a refresh may move, replace, or overwrite query results. Keep hand-entered annotations in a separate table or worksheet and join them deliberately if they need to be part of the output.
Filter table rows with a formula instead
For a live worksheet result, FILTER can return rows from a table that meet a condition. It is available in Microsoft 365 and Excel 2021 or later. Unlike a Power Query result, the formula recalculates with worksheet changes and spills its output into neighboring cells, so leave enough empty space for the result.
Assume a table named Sales has columns Region, Rep, and Amount, with records for West, East, and other regions. Enter this formula in an empty cell outside the table:
=FILTER(Sales,Sales[Region]="West","No matching rows")
It returns all columns for rows whose Region is West; if the example data has two West records, the result is two rows. The third argument supplies a message if nothing matches. The formula does not make a permanent cleaned copy or connect to an external source: it displays a dynamic result based on the table currently in the workbook. For multi-step cleanup, repeatable imports, or combining files, Power Query is generally more suitable.
Refresh, edit, and manage queries
After source data changes, refresh the query to update the loaded result. In Excel for Windows, use Data > Refresh All to refresh workbook connections, or right-click a query output and choose Refresh to update that query. Refresh All can also update other connections in the workbook, so check which ones are included if the file contains multiple data sources.
Use Data > Queries & Connections to see and manage queries in supported desktop versions. From the pane, you can open a query for editing, inspect its status, or review connection details. To change how results load, use the query’s load settings or Home > Close & Load To in the editor. Exact pane labels can vary slightly across Excel versions.
Refresh behavior depends on the source. A query based on a table in the same workbook can read the updated table when refreshed. A query that reads a file may require that file to remain at the expected path. A database query may require a working network connection and valid credentials. Some queries can be configured to refresh when the workbook opens, but automatic refresh is not a guarantee that every source is reachable or that credentials remain valid.
Common problems and practical fixes
- “From Table/Range” is unavailable: Click inside the source range or convert it to a table first. Some Excel editions or platforms expose different Power Query features.
- Headers appear as data: In the editor, use Home or Transform > Use First Row as Headers, depending on the ribbon layout.
- Numbers or dates show errors: Check the column’s data type and regional interpretation. Text such as “1,25” may be interpreted differently depending on locale; use the appropriate locale when converting types.
- New rows do not appear: Confirm they were added inside or directly below the source table, and then refresh. If the query uses a fixed range or external file, check its source settings.
- The output overwrites nearby content: Move unrelated values away from the query’s load area or choose another destination. Query results need room to expand.
- Refresh asks for credentials or fails: Verify access to the source and review the saved connection or privacy settings. Do not place sensitive credentials in cells or share them with people who should not have access.
Power Query, filters, formulas, and SQL compared
Use the table’s ordinary filter arrows when you need to inspect a subset temporarily and do not need a separate output. Use FILTER when you want a formula-based result that reacts promptly to edits in the workbook. Choose Power Query when the work involves repeatable transformation, appending or merging datasets, importing files, or producing a clean output for analysis. Choose a database connection with SQL when the source is a relational database and you need to specify which records or fields it returns.
SQL is not a replacement name for Power Query. Power Query can connect to supported sources and transform retrieved data using its own interface and M language; a database connection may also let you provide a SQL statement, depending on the connector and permissions. For example, an organization’s database administrator may restrict which tables or queries a user can access. Ask for authorized access rather than trying to bypass those controls.
There is no separate charge for the basic table and Power Query features built into an eligible desktop Excel installation. However, accessing a source can involve separate licensing, account permissions, network access, or database costs. Those depend on the source and organization, not simply on the fact that Excel is querying a table.
Frequently Asked Questions
How do I query an Excel table in Excel?
Click inside the table and choose Data > From Table/Range on Windows. In Power Query, make the needed changes and choose Home > Close & Load to create a result in the workbook.
Is an Excel table query the same as a filter?
No. A table filter hides rows in the current view. A Power Query query stores transformation steps and can load a separate result that you refresh later.
Can I query a table with a formula?
Yes. In Microsoft 365 or Excel 2021 and later, FILTER can return matching rows from a table. Its result updates with workbook calculations, but it does not provide Power Query’s import and transformation workflow.
Does a Power Query update automatically when the source changes?
Not necessarily. A loaded result generally needs to be refreshed, though some connections can be configured to refresh on opening. The source must also be available and accessible.
Can Excel query a SQL database?
Yes, through supported database connectors in Excel’s Data import tools. The exact options depend on the Excel version, database, driver, and account permissions; some connections also allow an SQL statement.
Related
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
- Conditional Formatting in Microsoft Project: What Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- Excel MID Cell Reference: Extract Text from a CellTo extract characters from a cell in Excel, use the MID function with that cell as its first…
- Copy Formulas in Excel Across Rows: A Simple GuideTo copy a formula horizontally, select its cell and drag the small fill handle at the lower-right corner…
- Filter Excel by Highlighted CellsYes. In Excel for Microsoft 365 on Windows, open the filter arrow in the column containing the highlighted…
- Excel Insert a New Row and Keep FormulasTo insert a row in Excel and keep formulas, select the row number where the new row should…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.