How to remove table formatting in Excel

When you format a range of cells as a table in Excel, the program adds filters, alternating row colors, and special formatting rules that can get in your way. To remove all of that and return to a plain grid, select any cell in the table, go to the Table Design tab (or Table tab on Mac), and click Convert to Range. Excel will strip away the table formatting but keep your data and any colors or fonts you applied manually.

The data stays exactly where it is. You are only removing the table structure itself — the dropdown filters, the automatic formatting, and the special behavior that comes with being a "table" in Excel's eyes. After you convert, you have a regular range of cells that you can format however you want without Excel trying to enforce table rules.

Key Takeaways

  • Click any cell in the table, then use the Table Design tab to find Convert to Range — this removes all table formatting at once.
  • Your actual data and any manual formatting (bold, colors, fonts) will remain; only the table structure disappears.
  • After conversion, the cells no longer auto-filter, auto-format new rows, or follow table style rules.
  • If you only want to remove the filter buttons, you can turn off AutoFilter instead of converting the whole table.

Where to find the Convert to Range button

The location depends on which version of Excel you are using. In Excel for Windows, click any cell inside the table. The ribbon at the top will show a Table Design tab (it only appears when a table is selected). On the far right of that tab, you will see a button labeled Convert to Range. Click it once.

On Excel for Mac, the tab is called Table instead of Table Design, but the Convert to Range button is in the same place — on the right side of the ribbon. In Excel Online (the web version), right-click the table and select Convert to Range from the menu that appears. The button is always there; you just have to be inside a table for it to show up.

What happens to your data and formatting

Your numbers, text, and dates do not move or change. Any color, bold, italic, or font size you applied by hand stays in place. What disappears is the table-specific behavior: the filter dropdown arrows at the top of each column, the alternating row colors that Excel applies automatically, and the special formatting rules that come with the table style.

If you had applied a table style (like "Table Style Medium 2"), that style will be removed and replaced with whatever manual formatting was underneath. If you had no manual formatting, the cells will look plain. You can always reapply colors or formatting after conversion if you want them back.

Turning off filters without removing the whole table

If you like the table structure but just want to hide the filter buttons, you do not have to convert the whole thing. Go to the Data tab in the ribbon and click AutoFilter to toggle it off. The dropdown arrows will disappear, but the table will still be a table — it will still auto-format new rows you add, and you can turn the filters back on anytime by clicking AutoFilter again.

This is useful if you want to keep the table's automatic behavior (like adding a total row or having new data automatically included in formulas) but do not want the visual clutter of the filter buttons. It is a smaller change than converting to a range, and you can reverse it just as easily.

When you might want to remove table formatting

Table formatting is helpful when you are sorting, filtering, or working with data that changes. But it can get in the way if you are building a report, sharing a spreadsheet with someone who finds the filters confusing, or trying to explore custom formatting that the table style overrides. Some people also find the alternating row colors distracting when they are trying to focus on the numbers themselves.

Another reason to convert is if you are copying data from a table into another sheet or program and the table structure is causing unexpected behavior. Once you convert to a range, the data behaves like any other cells in the spreadsheet, with no special rules attached.

How to avoid accidentally creating a table

Excel will sometimes turn a range into a table without asking. If you paste data or type a header row and then press Enter, Excel may assume you want a table and format it automatically. To prevent this, go to File > Options (or Excel > Preferences on Mac), then look for Data options. Uncheck the box that says Automatically format as table or Format as table automatically. After that, Excel will leave your ranges alone unless you explicitly ask for a table.

This setting is especially useful if you work with large datasets or paste data frequently and do not want Excel making decisions about formatting for you. Once you turn it off, you stay in control of when and how tables are created.

Frequently Asked Questions

Will I lose my data if I convert a table to a range?

No. Converting to a range removes only the table formatting and structure, not the data itself. Every number, date, and text entry stays in its cell. Any manual formatting like bold or color also stays.

Can I undo Convert to Range if I change my mind?

Yes. Press Ctrl+Z (or Cmd+Z on Mac) when ready after converting, and the table formatting will come back. If you have made other changes since then, undo will step back through those too.

What is the difference between removing a table and deleting it?

Removing (converting to a range) keeps all your data and formatting. Deleting the table removes the data entirely. Always use Convert to Range unless you actually want to erase the cells.

Do formulas that reference the table still work after conversion?

Yes, but the formula reference changes. If a formula referred to the table by name (like =SUM(Table1[Amount])), it will convert to a regular cell range reference after you convert the table. The formula still works; it just uses cell addresses instead of the table name.

Can I remove the table style but keep the table structure?

Yes. Right-click the table, select Table Styles, and choose None. This removes the colored rows and formatting but keeps the table itself, including filters and auto-formatting of new rows. It is a middle ground between keeping the full table and converting to a range.