How to Remove Table Formatting in Excel

Working with tables in Excel can be incredibly useful. Even though a spreadsheet is just one big table, creating individual tables within your spreadsheet can make it easier to see and analyze your data. You can have as many tables as you wish in your spreadsheet, to summarize different things or different parts of your data. Turning a range of cells into a table is easy to do in Excel, but when you do so, some formatting is added by default.

This formatting might not be exactly what you want; you may prefer your tables to look a little different or to have no formatting at all. The good news is that you don’t have to stick with Excel’s default formatting; you can remove it completely if you so wish. Here’s how to remove table formatting in Excel.

How to Remove Table Formatting in Excel

When you convert a range in Excel to a table, Excel applies default formatting to the table. This includes colored headers for each column and alternating colors for each row. The intent is to make the table easier to read, but if you’re not a fan of this formatting, you can remove it completely. This method will only remove the default formatting; it won’t remove any custom formatting that you’ve added to the table. The column headers and column filters will also remain untouched.

Select any cell of your table.

In the menu bar, a new Table Design menu will appear (called just Table on Mac).

Click the Table Design menu on Windows or the Table menu on Mac. On Windows, in the ribbon, click the Expand Arrow to the right of the Table Styles.

On Mac, hover over the Table Styles, and click the Down Arrow that appears.

Select Clear.

The table formatting is removed. The columns headers and filters will remain in place.

How to Clear All Formatting From a Table in Excel

If you’ve added your own formatting to a table in Excel, the method above won’t remove any of that custom formatting. Rather, it will only remove the default table formatting applied by Excel. It is possible to completely remove all the formatting from your table, including both the default formatting and any other formatting that you’ve added yourself. Once again, the column headers and column filters will still remain.

Click in any cell of your table. Press Ctrl+A on Windows or Cmd+A on Mac to select all of the data in your table.

Press Ctrl+A on Windows or Cmd+A on Mac again, to add the column headers to the selection. This step is important, as otherwise the default formatting won’t be removed.

In the Home ribbon, click the Clear tool, which looks like an eraser.

Select Clear Formats.

Excel will remove all formatting, including the default formatting and any other formatting you’ve added manually.

How to Convert a Table Back into a Range

If you’re happy with how your Excel table looks but want to remove the column filters from your headers, you can convert the table back into a range. This will still leave the column headers and colored rows, but the filters will be removed from each column.

Select any cell within your table. In the Table Design menu (or Table menu on Mac) click the Convert to Range button.

Alternatively, right-click on any call of the table, and select Table > Convert to Range.

The filters will be removed from your column headers, but the color formatting will still remain.

How to Clear Formatting from a Range in Excel

If you’ve converted a table back into a range, the cells will still have the color formatting that was applied to the table. You can remove the formatting from these cells to return them to looking like standard Excel cells. If you have multiple tables that you’ve converted back to ranges, you can search for the formatting to find all of the instances.

Select all of the cells that you want to remove the formatting from. In the Home ribbon, click the Clear button that resembles an eraser.

Select Clear Formats.

The formatting is removed from your selected cells, including any coloring as well as the column filters.

The column headers will still be present; you’ll need to delete these manually if you want to get rid of them.

How to Completely Delete a Table in Excel

If you want to get rid of your table completely, including any formatting that’s been applied to the cells, you can delete the table. This will remove not only the table data but also any formatting that the table contains, leaving you with blank cells without any formatting. This is a more drastic measure, however, as you’ll lose all of the data contained in the table.

Click any cell of your table.

Press Ctrl+A on Windows or Cmd+A on Mac. This will select all the cells in the body of the table. You don’t need to press Ctrl+A or Cmd+A again to include the table headers in the selection, as deleting the main body of your table will also remove the column headers, too. Right-click on any cell of your table and select Delete > Table Columns or Delete > Table Rows. Both options will have the same effect.

Your table, including the headers, will be deleted, along with any formatting your table may have had.

How to Change the Default Table Style in Excel

If you find yourself constantly changing the formatting of your Excel tables from the default setting, you can opt to choose your own default table style which will be applied to all new tables. You can choose from one of the other pre-set table styles, create your own table style, or opt to have no formatting at all for your default table style.

Select any cell of any table in your spreadsheet. Click the Table Design menu (Table menu on Mac).

Expand the Table Style window by clicking the Expand Arrow on Windows or hovering over the Table Style window and clicking the Down Arrow on Mac.

To select a table style as your default, right-click the style you want to use and select Set as Default.

If you want to have no formatting at all as your default table style, right-click the None style (the first in the list) and select Set as Default.

To create your own table style, expand the Table Style window, select New Table Style, and change the options to create your custom style.

You can then set your custom style as the default by right-clicking it and selecting Set as Default.

Removing Table Formatting in Excel

Removing table formatting in Excel allows you to decide what your table should look like, rather than having Excel automatically decide for you. Getting rid of all of the formatting can be more frustrating than you’d expect, but by following the methods above, you can remove all the formatting from your tables, to get them looking exactly like you want them. You can also opt to turn your tables back into ranges or to remove them completely if you wish. If you find yourself doing this a lot, you can choose your own default table style, create your own, or set the default table style to have no formatting at all.

Click to comment