Excel Take Data From a Table: Methods and Examples

How-to basics · Updated November 21, 2026

Yes. Excel can take data from a table by referencing its columns in a formula, filtering or looking up matching rows, summarizing values, or importing and refreshing data with Power Query. For a quick result in Microsoft 365, use a table reference or a function such as FILTER; for repeatable imports and cleanup, use Power Query.

What “take data from a table” can mean

The phrase can describe several different tasks. You might want to display a table’s entire column elsewhere, return only rows that meet a condition, find one value associated with an ID, calculate a total, or bring data from another file into the current workbook. These jobs have different best-fit tools: formulas are convenient for live worksheet results, while Power Query is designed to import, reshape, and refresh data.

Excel’s formal Table feature is more than a range with colored headings. Tables have names and named columns, expand as you add records, and use structured references in formulas. A normal cell range can also supply data to formulas or a query, but it does not provide all of those table-specific behaviors. If the source is an Excel Table, formulas can refer to columns by meaningful names such as Sales[Amount] rather than by less descriptive addresses such as D2:D500.

Make sure the source is an Excel Table

If your data is already formatted as a table, click inside it and look for the Table Design tab. The Table Name box on that tab shows its current name; you can give it a clear name such as Sales or Products. Avoid spaces in table names, and use column headings that describe the data. Each column should have a heading, and the data should not contain merged cells or blank header rows.

To convert a regular range into a table in Excel for Microsoft 365 on Windows:

  1. Select a cell in the data range.
  2. Choose Insert > Table, or press Ctrl+T.
  3. Check that the suggested range covers the data, select My table has headers if appropriate, and choose OK.
  4. On the Table Design tab, set a recognizable name in the Table Name box.

On a Mac, use Insert > Table if the keyboard shortcut differs in your Excel settings. Excel for the web also supports tables, although some ribbon details vary. If you choose that the range has headers, Excel treats the first row as column names rather than as a record. Keep one record per row and one kind of field per column; that structure makes filtering, formulas, and imports more reliable.

Return rows that meet a condition

For a live extract of matching records, FILTER is often the most direct choice in Microsoft 365. Assume the table is named Sales and has the columns Date, Region, Product, and Amount. To show every Sales row whose Region matches the value in H2, put the following formula in a blank cell outside the table. If H2 contains West, Excel returns the matching rows as a spilling result.

With a table named Sales containing Date, Region, Product, and Amount, and the desired region in H2, enter =FILTER(Sales,Sales[Region]=H2,"No matching rows") in an empty cell outside the table. If there are two matching records, the result contains those two records and their columns; if there are none, it displays No matching rows.

The result updates when the source table changes, and the structured references grow with the table. Leave enough blank cells below and to the right of the formula for the result to spill. A spill error can appear if those cells are occupied. FILTER is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual versions such as Excel 2019. If FILTER is unavailable, use the table’s filter controls and copy the visible rows, or use Advanced Filter where appropriate.

To apply more than one condition, combine logical tests. For example, multiplying two conditions in FILTER means both must be true. Make sure the compared columns have consistent data types: a text value that looks like a date may not match a real Excel date. If you need a saved, separately editable extract rather than a formula result, copy the returned cells and paste values into a new location.

Look up a related value

When you have a key such as a product code and want one corresponding field, use a lookup formula rather than returning every row. Suppose a Products table contains SKU and Price columns, and H2 contains the SKU to find. With that layout, enter =XLOOKUP(H2,Products[SKU],Products[Price],"Not found") in an empty cell. If H2 contains a code whose price is 24.5, the formula returns 24.5; if the code is absent, it returns Not found.

XLOOKUP searches the SKU column and returns the value from the same position in the Price column. It uses an exact match by default, which is usually appropriate for IDs and codes. If the SKU appears more than once, the standard formula returns the first matching result, so check whether the key should be unique. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In older Excel versions, INDEX and MATCH can perform a similar lookup, but the formula is more involved.

Summarize table data with formulas

You do not always need to copy records to “take” information from a table. A summary formula can pull a useful total while leaving the source intact. With the Sales table described above and West in H2, enter =SUMIFS(Sales[Amount],Sales[Region],H2) in a summary cell. If the West records have amounts of 120 and 50, the result is 170. SUMIFS adds values from the Amount column only for rows whose Region equals H2.

Structured references make the formula easier to read and maintain than fixed ranges. If new records are added to Sales, the table expands and the formula includes them. For grouped summaries across several categories, an Excel PivotTable may be more useful than a set of formulas: select a cell in the table, choose Insert > PivotTable, then place fields in the Rows, Columns, Values, and Filters areas. A PivotTable is a summary, not a live copy of the underlying rows; use Refresh when its source data changes.

Import data and refresh it with Power Query

Use Power Query when the source is another workbook, a text file, or another supported connection, especially if you must repeat cleanup steps. To import a separate workbook in Excel for Microsoft 365 on Windows, choose Data > Get Data > From File > From Workbook, select the file, choose the relevant sheet or table in the Navigator, and select Transform Data to inspect or modify it. Choose Close & Load to put the result into the workbook. Available connectors can vary by Excel edition and platform.

For data already in a worksheet range, select a cell in that range and choose Data > From Table/Range. Excel opens the Power Query editor; when you finish shaping the data, use Home > Close & Load to load the result. You can filter rows, remove columns, change data types, and rename fields in the editor. Power Query records those steps so they can be applied again when the source is refreshed.

A query result is a separate output from its source, not a continuously updating mirror. After the source file changes, choose Data > Refresh All to rerun connections and queries. Check the query’s load destination and refresh settings if the output is not where expected. Excel for the web and Mac may offer different Power Query authoring features and connectors from Windows desktop Excel, so confirm that the required source and transformation options are available in your version.

Choose the right method for the job

  • Use structured references when a formula needs a table column or a value from the current row.
  • Use FILTER when you need a changing set of rows that meet one or more conditions.
  • Use XLOOKUP when a key should return one related value.
  • Use SUMIFS or a PivotTable when the goal is a total or grouped summary rather than copied records.
  • Use Power Query when data must be imported, cleaned in repeatable steps, or refreshed from a supported source.

Consider whether the result should update automatically, whether it must be editable independently, and how often the source changes. A formula result stays linked to the workbook’s current data, while copied values do not update automatically. A query can be refreshed, but it generally reflects the source only after a refresh. For a one-time extract, filtering and copying may be enough; for a recurring report, a formula or query usually reduces repeated manual work.

Common problems and how to fix them

  • The formula shows an error or an unexpected result: check the table name and column headings, and confirm that the formula refers to the intended field. Structured references can change if a heading is renamed.
  • FILTER shows a spill error: clear cells in the area where the results need to appear. Dynamic-array results cannot spill into occupied cells, and they cannot spill from inside an Excel Table.
  • A lookup says Not found: check for leading or trailing spaces, different number-versus-text formats, and spelling differences. Confirm that the lookup value really exists in the key column.
  • New rows are missing: confirm they were added within or directly adjacent to the table so Excel expands it. For imported data, use Refresh All; for a PivotTable, refresh the PivotTable as well.
  • The query output looks different from the source: review its applied steps and data types in Power Query. A conversion to date or number can change how values display, and removed or filtered rows may be intentional steps.

One common misconception is that every formula referring to a table creates an independent copy. It does not: a formula calculates a result from its references, and the result updates as those references change. Likewise, a Power Query output is refreshable but is not necessarily synchronized at the moment its source changes. Choose the method based on the update behavior you need, and keep a copy of the original data before making extensive changes.

Frequently Asked Questions

Can Excel automatically pull new rows from a table?
Yes. A structured-reference formula or a dynamic-array formula such as FILTER can include rows added to the source table. Power Query can also include them when refreshed, provided the source range or table includes the new rows.

Can I pull data from a table in another workbook?
Yes. You can use an external workbook reference for certain formula-based tasks, or import the workbook with Data > Get Data > From File > From Workbook. Power Query is often easier to maintain for repeatable imports. An external reference may depend on the source file’s path and availability.

Can I use a formula to return an entire table column?
Yes. In Microsoft 365 or Excel 2021 and later, a structured reference such as =Sales[Amount] entered in a blank cell outside the table can return the column’s data as a spilled array. Leave space for the results, and note that the reference returns data rows rather than the table heading.

Why can’t I put a FILTER formula inside a table?
Dynamic-array formulas need to spill into neighboring worksheet cells, while table cells are managed as part of the table’s calculated-column structure. Put the FILTER formula in a blank area outside the table and make sure its output area is clear.

Does Excel update imported data as soon as the source changes?
Usually not. A Power Query result updates when you refresh it, manually with Data > Refresh All or according to configured refresh options. A formula linked to another workbook may also depend on the workbook being accessible and its link values being updated.

Related

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.