To get data from a worksheet table or cell range into Power Query in Excel for Microsoft 365 on Windows, select a cell in the data and choose Data > From Table/Range. Excel opens the Power Query Editor, where you can clean or reshape the data before choosing Home > Close & Load to return it to a worksheet.
What “From Table/Range” Does
From Table/Range connects a worksheet’s data to Power Query, Excel’s tool for importing and transforming data. It does not simply copy the selected cells. Instead, it creates a query that records the source and the transformation steps you apply. You can load the resulting data to a worksheet or, when appropriate, create a connection for use elsewhere in the workbook.
The command works with an existing Excel table or a regular rectangular range. If you select a range that is not already a table, Excel asks whether it should create one. A table has a name, column headers, and a defined set of records; those features make it easier for a query to identify and refresh its source as the data changes.
This is useful when you need to repeatedly clean an export, remove unwanted columns, filter rows, or combine data from a consistent source. A query can preserve those steps, so you do not have to repeat the same manual edits after each new batch of data.
Prepare the Source Data
Power Query works best when the source is a simple, consistent block of data. Before importing, check that each column has one clear heading and that every row represents one record. For example, a sales list might have columns named Date, Customer, Region, and Amount, with one sale per row.
- Use one header row, with a distinct name for each column.
- Avoid completely blank rows or columns inside the data block; they can make Excel detect a smaller range than intended.
- Keep one kind of information in each column. Mixing dates, notes, and amounts in a single column can lead to inconsistent types.
- Remove merged cells and decorative title rows from the data itself. Put report titles above the table, not among its records.
- If the source will grow, format it as a table with Home > Format as Table or Insert > Table.
When Excel asks whether the table has headers, confirm the setting that matches the first row. If a decorative title or blank row sits above the actual headings, select just the data block first; otherwise Excel may treat the wrong row as field names.
Import a Range with Power Query
- Click a cell inside the data you want to use. If it is an ordinary range, check that it is contiguous and has a header row.
- On the ribbon, choose Data > From Table/Range. The command is in the Get & Transform Data area of the Data tab.
- If Excel displays a Create Table dialog, confirm the detected range and set or clear My table has headers as needed, then select OK.
- In the Power Query Editor, inspect the preview. Choose transformations such as removing columns, filtering rows, or correcting data types from the ribbon or a column’s context menu.
- Choose Home > Close & Load to load the query result to a worksheet. For placement or connection options, use the Close & Load dropdown and select Close & Load To.
Excel generally loads the result to a new worksheet by default. With Close & Load To, you can choose a location or a connection-only result, depending on the available options and workbook. Loading to a worksheet creates an output table; it is separate from the source table, even when both tables are in the same workbook.
Transform and Load the Data
The Power Query Editor shows a preview of the rows and columns. Selecting a column lets you change its data type, filter its values, or remove it. The Applied Steps pane records transformations in order. Selecting an earlier step shows the data at that stage; deleting a step removes that transformation and may change later results.
For example, suppose a monthly sales export includes an internal notes column and rows for several regions. In the editor, you can remove Notes, filter Region to retain only the regions needed for a report, and set Date and Amount to the correct data types. Each action becomes part of the query, rather than an edit to the original source cells.
Data types deserve attention. Dates imported as text may not sort or group as dates, while amounts stored as text may not total correctly downstream. Use the type icon in a column heading or the data-type controls on the ribbon to set an appropriate type. If a date is interpreted incorrectly because the source uses another regional format, review the locale-related type options rather than assuming the displayed values are valid.
Refresh and Keep the Query Current
Once loaded, a query can be refreshed to rerun its recorded steps against the source. Select a cell in the output table and choose Data > Refresh, or use Data > Refresh All to refresh workbook connections and queries together. You can also right-click the loaded table and choose Refresh.
If the source is an Excel table, adding records directly below it normally expands the table, and a refresh can include those records. If the source is a plain range, newly added rows outside the original range may not be included automatically. Turning the source into a named table before creating the query is often the more reliable choice for data that grows over time.
Refresh does not mean that Power Query edits the source cells. It reads the source, repeats the query steps, and updates the output. If someone changes the source values, those changes can appear after refresh; if someone manually edits cells in the query output, a later refresh can replace those edits. Make corrections in the source or in the query steps, not in output cells you expect the query to maintain.
Table, Range, and Structured References
A worksheet range is simply a group of cells, such as A1:D50. An Excel table is a named object with headers and built-in expansion behavior. Power Query can start from either, but a table is usually easier to manage when rows are added, and it is easier to recognize in formulas and the workbook’s Name Manager.
Tables also support structured references in worksheet formulas. For example, if a table is named Sales and has an Amount column, a formula to total that column is =SUM(Sales[Amount]). With values of 120, 80, and 50 in Sales[Amount], the result is 250. This formula calculates from the table; it is not a Power Query import command. The distinction matters: use a formula for calculations in the sheet, and use Power Query when you want a repeatable import or transformation process.
To rename a table, click inside it and use the Table Design tab’s Table Name box. Use a clear name without spaces, such as SalesData. Renaming a table after building a query is possible, but changing or deleting its source can break the query, so update the source deliberately and check the query after structural changes.
Common Problems and Fixes
- The command is unavailable: Click inside the source data and check that you are using desktop Excel with the Data tab visible. Some older or non-desktop editions have different Power Query capabilities.
- Only part of the data appears: Check for blank rows or columns splitting the range. Convert the complete source to a table, then confirm the query’s source step points to that table.
- Headers appear as data: In the editor, use Home > Use First Row as Headers when the first preview row contains field names. If the wrong row was included, correct the source selection as well.
- New rows do not show up: Confirm that they were added inside or immediately below the source table, then refresh. A query based on a fixed cell range may need its source range updated.
- Values change type or show errors: Inspect the type-conversion step and the source values. Mixed formats, regional date conventions, and nonnumeric text in a number column can cause conversion errors.
- Refresh reports a source error: Check whether the source table or worksheet was renamed, deleted, or moved, and whether the workbook still has access to any external file the query uses.
For a query that starts from a range in the same workbook, keep the source table intact and avoid renaming its columns casually. Renaming a field can affect later query steps that refer to that column. If a refresh error appears after a change, open the query and inspect the Applied Steps pane to find the first step that no longer works.
Power Query Compared with Other Options
Choose the method based on what “get data” means in your task. Power Query is a good fit for repeatable cleanup and combining sources. It keeps the source separate from the transformed result and can rerun steps on refresh. It is not necessary for every small calculation.
- Use a cell reference or formula when you need a value or calculation that updates as cells change, such as a total or lookup result.
- Use copy and paste for a one-time transfer that does not need a repeatable refresh process.
- Use Power Query when the same import, filtering, type correction, or reshaping needs to be repeated.
- Use an external-data command when the source is a file, database, or online service rather than a table already in the workbook.
For a one-off copy of only part of a table, filtering or copying visible cells may be quicker. For a monthly report that receives a new export each period, a query is often more maintainable because the steps are saved. The best choice depends on whether you need a live calculation, a static copy, or a refreshable data-preparation workflow.
Excel for Mac and Excel for the Web
The steps above describe Microsoft 365 Excel on Windows. Power Query is also available in Microsoft 365 Excel for Mac, but the set of data sources and some editing or refresh capabilities can differ by Excel build. Look on the Mac Data tab for the Power Query or From Table/Range command; if the label or options differ, use the commands shown in that installed version rather than assuming the Windows ribbon layout is identical.
Excel for the web has different capabilities from the Windows desktop app, and support can depend on the query and its source. If you need to create or substantially edit a query, use the desktop application when the required controls are not available in the browser. A workbook created on desktop may still have query refresh options online, but do not assume every connector or transformation can be managed there.
Frequently Asked Questions
How do I get data from a table in Excel?
Select a cell in the table, choose Data > From Table/Range, make any needed changes in Power Query Editor, then choose Home > Close & Load. Excel creates a query and loads its result.
Does From Table/Range change my original data?
No. Power Query reads the source and loads a separate result. Transformations in the query do not rewrite the original table, although refreshing can replace manually edited cells in the query output.
How do I add new rows to the data that Power Query imports?
Add them to the source Excel table, then refresh the query. If the query uses a fixed range instead of a table, check or revise the source range so it includes the new rows.
Can I get only certain columns or rows?
Yes. In Power Query Editor, remove columns you do not need and use column filters to keep the required rows. Close and load the query when the preview reflects the result you want.
What is the difference between a table and a range?
A range is a set of cells. A table is a named Excel object with headers and features that help it expand as records are added. Both can be used as a Power Query source, but tables are generally more convenient for growing data.
Why does Refresh not include my newest records?
The new records may be outside the query’s original range, or they may not be part of the source table. Check the source step and table boundaries, add the records to the source, and refresh again.
Related
- Excel Data Validation: Can a Dropdown Hold Multiple Values?
- Excel Data Validation: How to Circle Invalid Data Cells
- Excel Data Validation: Add Criteria and Rules
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Custom Cover Page in Word: Create and Add Your OwnTo add a custom cover page in Word, place a new page at the start of your document,…
- Google Sheets: Conditional Formatting for Tick BoxesYes. In Google Sheets, you can use conditional formatting to change a checkbox’s appearance or highlight other cells…
- Excel Cell Bullet Points: Add and Format Lists in CellsYes. In Excel for Microsoft 365 on Windows, edit a cell and press Alt+0149 on the numeric keypad…
- Excel Workbook vs Spreadsheet: What’s the Difference?An Excel workbook is the complete Excel file; a spreadsheet is the broader term for a grid-based document…
- How to Begin Track Changes in Microsoft Word for ReviewsTo begin tracking edits in Word for Microsoft 365 on Windows, open your document, select Review > Track…
- Rate Quotation Format in Excel: A Practical GuideA rate quotation in Excel is an itemized document that shows what you will charge for specified goods…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.