Excel Xpert · Lesson 12
As much as you will need tables, there are times you need to undo one, and it is not as easy as it looks. Convert to Range takes thirty seconds to find and removes the table itself, but your data still looks exactly the same afterwards, because the formatting is a separate thing that stays behind. Eighteen seconds of video below, then everything the conversion quietly changes.
Lesson 12 · 18 seconds. The video shows how to untable a table in Excel using Convert to Range, and why the data still looks like a table afterwards. The written steps below cover the order that saves you the cleanup, what happens to formulas and pivot tables that referenced the table, and how to get filtering back once the table is gone.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
An Excel table is two separate things wearing one coat. There is the table object, which is the behavior: a name like Table1, structured references, auto expansion when you type in the row below, the filter row, the optional total row, and formulas that copy themselves down a column automatically. Then there is the table style, which is just cell formatting, the banded rows and colored header that Excel painted on when the table was created.
Convert to Range removes only the first one. The moment you confirm, the behavior is gone and the formatting is still sitting there, which is why so many people convert a table and conclude nothing happened. The banding is now ordinary fill color that Excel has no further opinion about, and it will stay until you clear it yourself.
That split explains the order in the steps below. Clearing the table style before converting takes one click, because the Table Styles gallery still exists at that point. Doing it afterwards means selecting the range and running Clear Formats, which strips your number formats, alignment and borders along with the banding, so you rebuild formatting you never wanted to lose.
The other thing worth understanding upfront is that the conversion rewrites formulas. Anything using a structured reference like Table1[Amount] gets translated into an ordinary address like D2:D50. The formulas keep working and the numbers do not change, but they are now fixed ranges. Add a row and nothing extends to include it, which is the behavior you gave up by untabling in the first place.
Six steps. Step one is out of order on purpose, and doing it first is what saves the cleanup.
Click any cell in the table, open the Table Design tab, and in the Table Styles gallery choose None, the blank swatch at the top left of the dropdown.
While the table still exists, that formatting belongs to the table, so removing the style removes all of it at once and leaves your own formatting untouched. Once you convert, the same banding becomes ordinary fill and the only blunt tool left is Clear Formats, which takes your number formats and alignment with it.
This is the whole reason people end up rebuilding a sheet's formatting after untabling. One click, thirty seconds earlier, avoids it entirely.
The Table Design tab only appears in the ribbon when your selection is inside a table, which doubles as the quickest way to check whether you are actually dealing with a table.
Plenty of data that looks like a table is really a formatted range with AutoFilter turned on. If Table Design never appears, there is no table to convert and what you want is Ctrl + Shift + L to drop the filter row plus Clear Formats for the coloring.
On the Table Design tab, click Convert to Range in the Tools group. The keyboard route is Alt, J, T, G pressed in sequence, then Enter to accept the confirmation prompt.
The prompt asks whether you want to convert the table to a normal range. Saying yes deletes the table object and its name. Your values, formulas and formatting all remain exactly where they are.
You can also right click inside the table and use Table > Convert to Range from the context menu, which is quicker if your hands are already on the mouse.
Select the range and use Home > Clear > Clear Formats. This strips the banding and header fill along with everything else, so expect to reapply your number formats, column widths' visual work, borders and bold headers afterwards.
If you only want the banding gone and your own formatting kept, it is usually faster to press Ctrl + Z back to the table, set the style to None, and convert again. Undo is the cleanest fix here, provided you have not saved and closed.
Formulas using structured references are rewritten automatically, so =SUM(Table1[Amount]) becomes =SUM(D2:D50). They still calculate correctly, but they are now fixed ranges that will not grow with the data.
Pivot tables and charts need attention. A pivot built on Table1 has its source rewritten to a cell range, so it keeps working but stops picking up new rows on refresh. Open PivotTable Analyze, Change Data Source, and confirm the range covers what you expect.
Also check named ranges, data validation lists and any Power Query steps referring to the table by name, since those refer to a name that no longer exists.
The table's filter row disappears with the table. If you still want the dropdown arrows, press Ctrl + Shift + L to switch on AutoFilter across the plain range.
Worth knowing that AutoFilter behaves slightly differently. It works on a contiguous block and stops at the first fully blank row, whereas a table always knew precisely where its own boundaries were.
| Step | What You Do | What Changes on the Sheet |
|---|---|---|
| 1. Clear style | Table Design > Table Styles > None | Banding and header fill removed, your own formatting kept |
| 2. Confirm it is a table | Click inside, look for Table Design | Tab missing means it is a formatted range, not a table |
| 3. Convert | Convert to Range, or Alt J T G then Enter | Table name, auto expansion and filter row all gone |
| 3. Same, via mouse | Right click > Table > Convert to Range | Identical result, fewer keystrokes |
| 4. Late cleanup | Home > Clear > Clear Formats | Banding gone, but number formats and borders go too |
| 4. Better late fix | Ctrl Z, set style to None, convert again | Banding gone, everything else preserved |
| 5. Formulas | Nothing, Excel rewrites them | Table1[Amount] becomes D2:D50, no longer expands |
| 5. Pivots and charts | Change Data Source, check the range | Still work, but stop catching new rows |
| 6. Filtering | Ctrl Shift L | AutoFilter dropdowns back on the plain range |
The two rows under step 4 are the same problem solved two ways, and the second is almost always better. Undo, clear the style while the table still exists, then convert again. The only time Clear Formats wins is when the file was already saved and closed, putting undo out of reach.
The option greys out for a handful of specific reasons.
Sheet protection blocks structural changes, and converting a table is structural. Review > Unprotect Sheet first, which may need the password whoever set it up used.
A table produced by Power Query or an external data connection is the output of that query, so Excel will not let you break the link by converting it. Remove the connection under Data > Queries & Connections first, or copy the values to a new sheet and untable the copy.
Legacy shared workbooks and some co-authoring sessions restrict structural edits. Turning off sharing, or working on a local copy, restores the option.
The simplest cause. If Table Design is not showing, the cursor is not in the table and there is nothing to convert.
Tables are usually the better choice, so it is worth knowing which problems actually justify removing one.
Excel tables do not permit merged cells anywhere inside them. If a report layout genuinely requires merging, untabling is the only route, though a centered-across-selection alignment often achieves the same look without merging at all.
Structured references are readable in short formulas and become long and awkward in complex ones, particularly when combined with INDIRECT or older functions that do not handle them well. Plain ranges are sometimes simply easier to work with.
Very old Excel versions and some import tools handle tables badly or ignore them. A plain range is the safest thing to hand over when you do not control what opens the file.
Excel creates a table automatically in several places, including some Power Query and Form imports. If you never asked for one, untabling is just tidying up.
Disliking the banded colors is a styling problem, solved by setting the style to None while keeping the table. Wanting to remove the filter arrows is solved by unticking Filter Button on the Table Design tab. Neither needs the table gone, and both throw away auto expansion for nothing.
Clear Formats is the only tool left at that point and it strips your number formats and borders along with the banding. Set the style to None before converting instead.
The table really is gone, the formatting just stayed. Check for the Table Design tab, which will no longer appear when you click inside the data.
A pivot built on the table keeps working but silently stops picking up new rows, because its source is now a fixed range. The failure only shows up next month when totals look low.
Deleting rows does not remove a table object and takes your column names with it. Convert to Range is the only correct route.
It creates a new table with a generic name. The old table name is gone, so any formula, chart or query elsewhere that referenced it by name stays broken.
Both are settings on the Table Design tab. Removing the table to fix a styling preference costs you auto expansion and structured references for nothing.
Converting back and forth is a sign the data outgrew the sheet.
Free 14-day trial. No credit card required.
Click any cell inside the table, go to the Table Design tab and choose Convert to Range, then confirm. The table becomes a normal range, though the formatting stays until you clear it separately.
Convert to Range removes the table object, not the cell formatting it applied. The banded rows and colored header are ordinary fill and border formatting now, and have to be cleared on their own.
Before converting, set the table style to None in the Table Styles gallery. If you already converted, select the range and use Home, Clear, Clear Formats, then reapply anything you wanted to keep.
There is no single key combination, but the ribbon sequence Alt J T G runs Convert to Range from the Table Design tab, then Enter confirms the prompt.
Structured references such as Table1[Amount] are rewritten as ordinary cell addresses like D2:D50. The formulas keep working, but they stop expanding automatically when rows are added.
No. Convert to Range keeps every value and formula. What you lose is the table behavior, meaning auto expansion, structured references, the filter row and the total row.
Usually the sheet is protected, the workbook is shared, or the selection is not inside the table. A table linked to a Power Query refresh or a data connection also blocks it until the link is removed.
Ctrl Z immediately after works. Once you save and close, converting back with Ctrl T recreates a table but does not restore the old table name or the structured references in other formulas.
The pivot keeps working but its source changes from a table name to a fixed cell range, so it no longer picks up new rows automatically. The range has to be updated manually after that.
Common reasons are merging cells, which tables do not allow, sending a file to someone on an older version, avoiding structured references in complex formulas, or preparing data for a system that expects a plain range.
Yes, the table's own filter row goes with it. If you still want filtering on the plain range, turn on AutoFilter with Ctrl Shift L afterwards.
Select any cell in the data and press Ctrl T, confirm the range and whether it has headers. Remember to rename the new table on the Table Design tab, since it will be given a generic name.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data that does not need reshaping to be useful.
Start Your Free Trial →