In Excel: click inside the table and choose Table Design ▸ Convert to Range to keep the data as a normal range, or select the whole table and press Delete (or Home ▸ Clear ▸ Clear All) to remove the data too.
Click any cell inside the table so the contextual Table Design tab appears.
To keep the data, choose Table Design ▸ Convert to Range (or right-click ▸ Table ▸ Convert to Range) and confirm.
The table style stays as static formatting; remove it with Home ▸ Clear ▸ Clear Formats if you want plain cells.
To delete everything instead, press Ctrl+A twice inside the table to select it including headers, then Home ▸ Clear ▸ Clear All.
Check formulas afterwards: converting rewrites Table1[Sales] to cell references, while deleting the table turns them into #REF! errors.
What this does
Removing a table means one of two different things, and Excel has a separate path for each. Convert to Range keeps every value and formula but strips the table behavior: filter buttons, auto-expansion and the table name disappear, and structured references such as =SUM(Table1[Sales]) are rewritten to plain cell references like =SUM(B2:B6). Deleting the table removes the data as well: select it and press Delete to clear the contents, or use Home ▸ Clear ▸ Clear All to wipe values and formatting in one step. Note that Convert to Range leaves the banded table style behind as static formatting. The same idea underpins a lot of everyday Excel work, so the few minutes spent getting it right here pay back across every sheet you build afterwards. Treat it as a pattern, not a one-off, and it stops being something you look up and starts being something you reach for. The difference between a quick fix and a sheet you can trust is the extra minute you spend validating “remove table in excel but keep data”. Start on a copy or a tiny sample, keep the affected cells visible, and compare the result with the tool above before you touch the real workbook. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. The point is a workflow that saves repeating the same clicks every week, but the practical win is that someone else can open the file and understand what happened without asking you.
A worked example
Table1 sits in A1:B6 with headers Month and Sales, and E1 contains =SUM(Table1[Sales]), showing 4700. Click any cell in the table, open Table Design ▸ Convert to Range and confirm. The filter arrows vanish, E1 now reads =SUM(B2:B6) and still shows 4700, and the striped rows remain until you select A1:B6 and choose Home ▸ Clear ▸ Clear Formats. Tables add filter buttons, auto-expansion and structured references you may not want when handing over a simple sheet, pasting into another system, or building layouts a table would fight against. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.
In Google Sheets
Everything above works in Google Sheets too. Excel and Sheets share the formula syntax used here; only the surrounding menus are arranged differently. That portability is deliberate — learn it once and it follows you between the two tools and across Windows and Mac. Nothing on this page is behind a login: the tool runs entirely in your browser, the formula is shown in full with one-click copy, and the steps work the same on Windows and Mac. That is the whole promise here — the exact answer, a way to prove it on your own numbers, and just enough context to make it stick. Treat “remove table in excel but keep data” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.
Common mistakes
- Expecting Convert to Range to remove the striped formatting — the style stays until you clear formats.
- Pressing Delete on the contents and assuming the table is gone; the empty table object and its name still exist.
- Looking for the Table Design tab without first clicking inside the table — it only appears for the active table.
- Deleting a table that other formulas reference by name, which leaves
#REF!errors instead of converted references. - Confusing deleting a table with deleting the worksheet tab that contains it.
Frequently asked questions
Does Convert to Range delete my data?
No. All values and formulas stay in place; only the table behavior (filters, name, structured references, auto-expansion) is removed.
What happens to =Table1[Sales] formulas?
Convert to Range rewrites them to ordinary references such as =SUM(B2:B6). Deleting the table instead breaks them with #REF!.
How do I remove a table in Google Sheets?
For the newer Sheets tables, open the menu next to the table name and convert it to an unformatted range. If it is just banded formatting, use Format ▸ Alternating colors ▸ Remove.
Other ways people ask this
People reach this page typing “excel remove table keep data”, “excel delete table but keep data”, “excel remove table formatting keep data” and “remove table from excel but keep data”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “excel remove table keep data”, “excel delete table but keep data”, “excel remove table formatting keep data” all point at the one operation explained on this page, which is why they all lead here.