Excel Delete Table but Keep Data: The Right Steps

To delete an Excel table but keep its data, convert the table to a normal range: click a cell in the table, choose Table Design > Convert to Range, then confirm. The cell values and formulas remain in place, but the table’s special features are removed.

Convert an Excel table to a normal range

In Excel for Microsoft 365 on Windows, use the table’s ribbon command rather than deleting the cells. Follow these steps:

Click any cell inside the table. Excel should display the contextual Table Design tab on the ribbon.
Open Table Design. In the Tools group, select Convert to Range.
When Excel asks whether you want to convert the table to a normal range, choose Yes.
Click outside the former table or select a cell within it to confirm that the table-specific tab and controls are gone.

This changes the table’s structure; it does not remove its cells. The existing entries stay in their worksheet locations, so surrounding formulas or references that point to those cells can generally continue to use them. Save the workbook after checking the result, especially if it contains formulas, filters, or a carefully formatted report.

On Excel for Mac, select a cell in the table and look for the contextual Table Design tab (its label can vary slightly by release), then choose Convert to Range. In Excel for the web, select the table and look for the table-related ribbon command to convert it to a range; if that command is unavailable in your version, open the workbook in the desktop app and perform the conversion there.

What changes when a table becomes a range

An Excel table is more than a colored block of cells. It has a name, built-in header filtering, structured references, and behaviors that ordinary worksheet ranges do not automatically provide. Converting it removes that table identity while leaving the underlying grid cells in place.

Values and formulas: Existing cell contents are retained. Formulas in the former table remain formulas, though table-specific formula references may be rewritten as ordinary cell references.
Automatic expansion: Typing immediately below the former table no longer automatically extends the table, because there is no table to expand.
Calculated columns: A formula entered in a table column will no longer automatically fill down as a calculated column when new rows are added.
Structured references: References such as a table name followed by a column name lose their table context. Excel generally converts formulas that use them to standard cell references during conversion.
Table tools: The table name and table-specific design controls are removed. Some table behaviors, such as convenient filtering or automatic formatting of new rows, may need to be set up separately if you still want them.

A table’s Total Row, if enabled, remains as worksheet cells rather than as a special table row. Check its formulas and labels after conversion if you rely on it for a report. In particular, a table total can use a table-aware calculation that responds to filtering; after conversion, do not assume the same behavior will continue unchanged.

Does the data keep its formatting?

Usually, yes. Converting a table to a range retains the visible cell formatting, including much of the table’s applied color and banding. This is useful when the table’s appearance is part of a report and you want to remove table behavior without rebuilding the layout.

That retained appearance can surprise people who expect conversion to strip the colors. The cells may still look like a table even though they are now a normal range. Formatting and table structure are separate: a normal range can have colored fills, bold headers, borders, and number formats.

If you want plain-looking cells as well, select the former table range and choose Home > Clear > Clear Formats. This removes formatting but keeps the values and formulas. It also removes useful formatting such as date, currency, and percentage display, so check the result before saving. If you only want to remove a table style while preserving other formatting, inspect the available options under Table Design > Table Styles before conversion; the exact style choices shown depend on the workbook and Excel version.

Choose the right action for your goal

“Delete the table” can mean several different things in Excel. Pick the action that matches what you want to keep:

Keep cells, remove table features: Choose Table Design > Convert to Range. This is the right choice when the data should stay on the sheet but no longer behave as a table.
Keep the table, remove its entries: Select the data cells and clear their contents, taking care not to remove the header or the table itself if you still need the table structure. The exact selection matters.
Remove the table and its contents: Use the table’s delete command only if you intend to remove the data too. Check the command’s wording and selection before confirming.
Keep the data but remove its appearance: Convert to a range if necessary, then clear the formats or apply the formatting you prefer.

For example, suppose a sales table has columns for date, region, and amount. If you need the records to remain but want to stop Excel from automatically extending the table when new notes are entered below it, convert it to a range. If you need to erase the sales records before entering a new set while retaining sortable table columns, do not convert the table just for that purpose; clear the appropriate data cells instead.

Check formulas and references afterward

Converting a table normally preserves formulas, but it can change how table-aware formulas are written. A formula that refers to a table column by name may become a formula using ordinary worksheet addresses. That can be perfectly valid, but it may be less readable and will not automatically adapt in the same way when rows are added outside a table.

After conversion, inspect important totals, charts, and formulas that refer to the former table. Confirm that the ranges still include every intended row. For instance, a chart that used a table as its source may not automatically include future records once the source is a fixed range. A formula elsewhere that uses a table name may also need attention if it was not adjusted as expected.

If other sheets or workbooks depend on the table, test a few representative calculations before distributing the file. Keep the original table name and column arrangement in mind: converting removes the table object, so you cannot rely on table-specific names as a permanent interface for downstream formulas. For a workbook shared with colleagues, a short note that the data is now a normal range can prevent confusion when someone expects new rows to inherit formulas or formatting automatically.

Common problems and how to fix them

The Table Design tab is missing. Click inside the table, not just near it. The contextual tab appears only when Excel recognizes the selected cell as part of a table. If it still does not appear, verify that the range is actually a table.
The cells still look like a table. That is generally retained formatting, not proof that conversion failed. Click inside the cells and check whether the contextual table tab appears. If it does not, the range is no longer a table.
Filter arrows are still visible. Filtering can also be applied to an ordinary range. If arrows remain and you do not want them, select the relevant range and use Data > Filter to turn filtering off. Avoid deleting rows or columns just to remove the arrows.
A formula or chart behaves differently. Review its source range and formula after conversion. Table references and automatic expansion are different from fixed cell references, so update the formula or chart source if future rows need to be included.
New rows do not inherit formulas or style. That is expected for a normal range. Copy the formula or formatting down manually, or keep the data as a table if automatic filling is important.

When a workbook is important, make a copy before changing a complex table. That gives you a straightforward way to compare the converted range with the original if a chart, formula, or layout does not look right.

When keeping the table is better

Conversion is not automatically an improvement. Tables are useful when the data is an ongoing list that will grow, when users need easy sorting and filtering, or when formulas should fill down consistently. They also make formulas easier to interpret by allowing references to named columns instead of fixed coordinates.

Keep the table if people will regularly add records and expect table formatting, filters, and formulas to extend. Convert it when the data is essentially fixed, when a recipient needs a plain range for a particular workflow, or when table behavior is getting in the way. If the only issue is the color scheme, changing the table style may be simpler than removing the table’s useful features.

Frequently Asked Questions

Does converting an Excel table to a range delete the data?
No. Convert to Range keeps the cell contents in place and removes the table structure. The separate delete or clear actions can remove data, so use the conversion command when preservation is the priority.

Will my formulas remain after I convert the table?
Existing formulas remain, though formulas using structured table references may be rewritten as ordinary cell references. Check important calculations and any formulas that refer to the table from elsewhere in the workbook.

Can I remove the table but keep its colors?
Yes. Convert the table to a range. The existing formatting is generally retained, so the former table can keep its appearance without table functionality.

How do I remove the table colors but keep the data?
Convert the table to a range, select the cells, and choose Home > Clear > Clear Formats. Values and formulas remain, but number formats and other formatting are removed too.

Can I undo the conversion?
Immediately after converting, use Ctrl+Z on Windows or Command+Z on Mac to undo if the action is still in the undo history. After other changes or after closing the workbook, undo may not be available; a saved copy is the safer fallback.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.