Remove Table Format in Excel

Add to Favorites
Author: | Edits: don

Convert a table back into a regular set of cells.  This removes any automated table features and I'll show you how to quickly remove the visual formatting as well.

Oddly enough, this isn't as straightforward as it seems like it should be.

Steps to Remove a Table Format

  1. Select a cell anywhere in the table.
  2. Go to the Design tab that should appear when you select a table. Click Convert to Range
    1f5af8f91f3a1e6ccd065f07fd318b14.jpg
  3. Click Yes in the small window that appears.
    2e4e5e365d6a7eb83b35670611a24909.png
  4. That's it!
    2f1d771cdde5f4ccdcdc44a80c4df984.png

The format looks the same but the cells are no longer an actual "table" within Excel.

If you click in the % of Goal column, all the formulas have been converted to use regular cell references instead of structured references.

2553352134664e9cb242114b97970b61.png

Also, the table features are no longer readily available.

Remove Formatting

To remove the table's appearance quickly, highlight the table, go to the Home tab and look all the way to the right and click the Clear button and then Clear Formats

741c7136b53e3bdcf1b80f500a2dd3b7.jpg

It now looks like this:

de1690c5ff4bc9d5c1e0e80418768612.png

Notes

If you convert a table back to a range and then convert it again to a table, any formulas you had in the table will be stuck using regular cell references and the Totals row, if it existed, will be considered a normal row at first.  It is not advisable to switch between Table and regular cells and back again.

Download the sample Excel file attached to this tutorial so you can try this out in Excel.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File

Similar Content on TeachExcel
Quickly Clear all Formatting in Excel
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Make Complex Formulas for Conditional Formatting in Excel
Tutorial: How to make complex formulas for conditional formatting rules in Excel. This will serve as...
Dynamic Charts in Excel - The Easy Way
Tutorial: How to create dynamic charts that update automatically when new data is added to Excel. T...
Format Cells in The General (default) Format in Excel Number Formatting
Macro: This free Excel macro formats a selected cell or range of cells to the General number form...
Complex Structured References (Table Formulas) in Excel
Tutorial: How to use complex structured references, table formulas, in Excel. If you don't already...
Format Cells in The Short Date Number Format in Excel
Macro: This free Excel macro applies the Short Date number format to a selection of cells in Exce...
Tutorial Details
Downloadable Files: Excel File
Similar Content
Quickly Clear all Formatting in Excel
Tutorial: How to remove all formatting from cells at once in Excel. This includes removing any and...
Make Complex Formulas for Conditional Formatting in Excel
Tutorial: How to make complex formulas for conditional formatting rules in Excel. This will serve as...
Dynamic Charts in Excel - The Easy Way
Tutorial: How to create dynamic charts that update automatically when new data is added to Excel. T...
Excel Forum