Excel: Where Is the Data for Your Table?

In Excel, the data for a table is the range of worksheet cells containing the records and column headings. When you create a table, the box labeled Where is the data for your table? shows the cell range Excel plans to use; for an existing table, select a cell in it and choose Table Design > Resize Table to see or change that range.

What Excel means by the table data

An Excel table is a structured way to manage data already stored in worksheet cells. The table does not normally hold a separate copy of the information somewhere hidden: it organizes a rectangular range on a sheet. For example, if a sales list has headings in A1:D1 and records through row 25, its table range is A1:D25. The headings define the fields, and each following row is a record.

The wording in the creation dialog can be confusing because it sounds as though Excel is asking for a file or database. In the usual Insert Table process, it is asking which cells on the current worksheet should become a table. The range may be entered as an address such as $A$1:$D$25, or Excel may fill it in after detecting a nearby block of data.

That answer applies to an ordinary Excel table, sometimes called a structured table or list. A PivotTable, a what-if analysis Data Table, and a table loaded from an external source have different source details. Those distinctions matter if you are trying to find where values came from rather than simply locate the cells.

Where to see the range when creating a table

In Excel for Microsoft 365 on Windows, select a cell inside the data block, then choose Insert > Table. The Create Table dialog opens with a range in the field labeled Where is the data for your table? Review that range before confirming. If the first row contains labels such as Date, Product, and Amount, leave My table has headers selected. If it does not, clear that box so Excel treats the first row as data and adds generic headings.

Check that the proposed range includes every intended column and row, including the header row.
Make sure it does not include unrelated notes, blank spacer columns, or totals that should remain outside the records.
Confirm the header setting, then select OK to create the table.

Excel usually detects a contiguous block around the active cell. This is convenient, but it is not a guarantee that the selected boundary matches your intention. A completely blank row or column can interrupt detection, while a nearby note or extra data field can sometimes be swept into the selection. Reading the address in the dialog is safer than assuming the automatic choice is right.

You can also select the cells first and then choose Insert > Table; the selected range should appear in the dialog. On Excel for Mac, the same general approach applies: select the data and use Insert > Table, then verify the displayed range and header option. Ribbon labels and layout can vary slightly by update, but the dialog’s range is the key detail.

How to find the source range of an existing table

To inspect a table already on a worksheet, click any cell inside it. Excel displays the contextual Table Design tab on the ribbon. Choose Table Design > Resize Table. The dialog shows the range currently used by that table; you can inspect it and, if needed, enter or select a different range. Cancel if you only wanted to look and do not want to make a change.

The Name box, just left of the formula bar, is another way to identify a selected table’s location. Click a table cell and look at the cell address shown there, but remember that this is only the active cell, not the full table range. Resize Table is the more direct way to see the complete boundary. You can also use the Name box’s drop-down list to jump to a named table, then inspect its range.

For a quick visual check, select the table and note where its shaded formatting and filter arrows end. Visual formatting can be customized, however, so it is less reliable than the Resize Table dialog. A table name, such as Table1 or SalesData, identifies the table for formulas and other workbook features; it is not itself a worksheet address and does not tell you which rows are included.

What happens when the table grows or changes

Excel tables can expand as you add records directly below the last row or add a field beside the last column. In many ordinary cases, Excel extends the table formatting and table behavior automatically. For example, entering a new record in the row immediately below a table often incorporates that row. If the new data is separated by a blank row, placed far away, or otherwise not recognized as an extension, use Table Design > Resize Table to include it deliberately.

Resizing changes which worksheet cells belong to the table; it does not move the underlying values to a separate location. When you expand a range, check that each included column has a useful, nonblank heading and that the new rows follow the same layout as the existing records. When you shrink it, cells outside the new boundary remain on the worksheet, even though they no longer receive that table’s structured formatting and features.

Sorting or filtering a table changes the displayed order or visible records, not the table’s underlying range. A filter may hide rows from view, which can make it look as if the table contains fewer records. Clear the filters before counting visible entries or checking the full list. Deleting a row from the worksheet is different: it removes the row’s contents and, if it was part of the table, reduces the records in the table.

When the source is not a normal cell range

Some tables are connected to information outside the worksheet. For example, data may be imported with Power Query, linked to a database, or loaded from a file. The visible result still occupies cells in the workbook, but refreshing the connection can replace or update those values from the original source. If the cells appear to be controlled by a connection, look at the Data tab for queries and connections, or select the table and check the available table or query properties. Do not assume that editing a displayed value will change the original database or file.

A PivotTable is also different from an ordinary table. It summarizes a source range, another table, or a connection; its displayed cells are a report, not necessarily the original records. Select the PivotTable and use PivotTable Analyze > Change Data Source to inspect or change its source when that command is available for its source type. For a PivotTable based on the workbook Data Model or an external connection, the source may be managed through the model or connection rather than a simple worksheet address. Refreshing a PivotTable updates its summary from its source; it does not turn the summary into the source data.

Excel’s what-if analysis Data Table is another separate feature. It calculates results for one or two input values in a sensitivity analysis. It is not the same thing as a structured Excel table created with Insert > Table, so its input cells and calculation setup will not be revealed by Table Design > Resize Table.

Common range problems and how to fix them

The first record became a heading: The header option may have been selected even though the range had no header row. Undo the creation if practical, or correct the table headings and verify that the first data record is present.
A heading says Column1 or Column2: Excel may not have recognized the first row as headers, or the original heading cell was blank. Give every column a clear, unique heading.
Some records are missing: Open Table Design > Resize Table and compare the displayed range with the last intended row. A blank line can cause Excel’s automatic detection to stop early.
Unrelated cells were included: Resize the table to end at the correct row and column. Check the boundary before confirming so notes or totals do not become ordinary records.
New records do not sort or filter with the rest: They may be outside the table. Extend the table range or add the records directly beneath it, then confirm they are included.
The visible list looks incomplete: A filter may be hiding rows. Clear the filter to see all records; this does not change the source range.

Before resizing, consider whether formulas, charts, PivotTables, or other workbook features depend on the table. Table references often adjust when a table changes, but a separate chart or imported-data setup may have its own source settings. If the workbook is important, save a copy before making structural changes, particularly when you are unsure which cells other reports rely on.

Choosing the right range for a reliable table

A good table range is one continuous rectangle: one heading row followed by records, with one type of information per column. Keep explanatory notes, report titles, and standalone totals outside that rectangle. Avoid merged cells in the data area, since they interfere with the consistent row-and-column structure that sorting, filtering, and structured references expect.

For example, a product list might have columns for SKU, Product, Category, and Unit Price. Each row should describe one product, and each heading should name one field. Do not put two products in a single cell or use a blank row to separate categories; use a Category column instead. This shape makes it easier to filter for one category, add records, and refer to columns by their table headings.

When your goal is simply to format a small range, a table may be more structure than you need. When you expect to sort, filter, append records, or use structured references in formulas, an Excel table is generally a better fit than a range with manually applied colors. In either case, the underlying data remains on the worksheet unless a connection or other feature supplies it from elsewhere.

Frequently Asked Questions

Where is the data for your table in Excel?
The Create Table dialog’s range field identifies the worksheet cells Excel will use. For a table that already exists, select a cell in it and choose Table Design > Resize Table to view the full range.

How do I change the data range for an Excel table?
Select a cell in the table, choose Table Design > Resize Table, and enter or select the new rectangular range. Check the heading row and boundaries before confirming, especially if the range includes blank rows or unrelated cells.

Does an Excel table store a second copy of my data?
A normal Excel table organizes the worksheet cells in its range; it is not usually a separate copy. A query or external connection can refresh values from another source, so check the workbook’s connection details if the data is imported.

Why is Excel not including my new rows?
The rows may not be directly beside the existing table, or a blank row may be interrupting the range. Add the records immediately below the table when appropriate, or use Resize Table to extend the boundary.

Is a PivotTable’s data source the same as its displayed cells?
No. A PivotTable displays a summary generated from a source range, table, model, or connection. Use PivotTable Analyze > Change Data Source where applicable to inspect the source; the displayed summary is not necessarily the original records.

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.