Connect to an Excel table or range (Power Query)

Use Excel's Get & Transform (Power Query) experience to create a dynamic connection to an existing table or range in your workbook. You can then refresh the connection when the data in table changes.

2016 2010-2013



1. Select any cell within your data range.

2. Click the Data tab, then > From Table/Range.

3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

   

4. If the table or range of data has column headers, you can check My table has headers. The header cells are used to define the column names for the query.

5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

1. Select any cell within your data range.

2. In the Power Query ribbon tab, click From Table.

   

3. If prompted, in the From Table dialog box, you can click the Range Selection button to select a specific range to use as a data source.

   

4. If the range of data has column headers, you can check My table has headers. The range header cells are used to set the column names for the query.

5. In the Query Editor, click Close & Load.

Note: If your data range has been defined as a named range, or is in an Excel table, then Power Query will automatically sense the entire range and load it into the Query Editor for you. Plain data will automatically be converted to a table when it is loaded into the Query Editor.

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The following video shows the Query Editor window appearing after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query, from the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query. The following video shows one way to display the Query Editor.

Formula Example

You can use the Query Editor to write formulas for Power Query.

= Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Power Query is known as Get & Transform in Excel 2016

Import data from external data sources