Physical Address

304 North Cardinal St.
Dorchester Center, MA 02124

How To Remove Table Formatting In Excel?

Excel Table is a fantastic tool that significantly simplifies the process of working with data. And because more sophisticated tools like Power Query and Power Pivot now rely on Excel tables as the data source, the significance of Excel tables is only likely to increase in the coming years.

However, the focus of this article is not on the greatness of Excel Tables; rather, it is on the formatting, which is an aspect of Excel that a large number of users find to be particularly bothersome.

Excel will automatically apply certain formatting to a data range that has been converted into a table when you use the Convert Data Range to Table command (you can, of course, customise or remove the table formatting). In addition, the formatting of an Excel table is preserved even after it has been converted back to a range in Excel.

In this walkthrough, I will demonstrate how to clear table formatting in Excel, in addition to a few other customization options available to you with that programme. So let’s get started!

How to Remove Excel Table Formatting (while keeping the Table)

Suppose I have the dataset as shown below.

Using the keyboard shortcut Control + T, I was able to convert these numbers into an Excel table, and the resulting table is seen below.

How To Remove Table Formatting In Excel?

Excel has obviously gone ahead and formatted the table, as shown by the fact that it is now available to be seen (apart from adding filters). The most of the time, I don’t like the formatting that Excel applies automatically, therefore I need to make some adjustments to it.

Either I can entirely delete the formatting from the table, or I can edit it so that it appears in the manner that I want it to. Allow me to demonstrate both of these processes to you.

Remove Formatting from the Excel Table

The procedures necessary to remove the formatting from an Excel table are as follows:

Choose any cell you’d like from the Excel table. Choose the Design menu option. (since this is a contextual tab, it won’t show up till you click a cell in the table first)

When you are in Table Styles, choose the More icon (it is the one that is at the very bottom of the little scrollbar).

Simply choose the Clear option from the menu.

The Excel Table formatting will be removed after completing the procedures above, but the table will maintain its status as a table. The filters that are automatically applied will still be visible to you; the formatting that was previously present has just been removed.

You now have the option to manually format it, if you so choose.

Change the Formatting of the Excel Table

If the format that is applied by default to an Excel Table is not to your liking, you have the option to change it by selecting a different preset from the available options.

Imagine that you have the Excel table shown below and that you wish to make some changes to the formatting of this data.

This may be accomplished by following the steps below:

  • Choose any cell you’d like from the Excel table.
  • Choose the Design menu option (this is a contextual tab and only appears when you click any cell in the table)
  • When you are in Table Styles, choose the More icon (it is the one that is at the very bottom of the little scrollbar).
  • You may choose one of the many available patterns.

When you move the mouse over any of the designs, you will get a real-time preview of how the corresponding formatting will appear in the table that you have created in Excel. After you have completed all of the necessary steps to get the desired formatting, just click on it.

If none of the pre-existing Excel table styles appeal to you, you can always make your own by selecting the ‘New Table Styles’ button and following the on-screen prompts to do so. This will open a dialogue box for you to make the necessary formatting adjustments in.

Remove Excel Table (Convert to Range) & the Formatting

It is straightforward to transform tabular data into an Excel table, and it is just as straightforward to transform an Excel table into the standard range.

The formatting, however, is not preserved when you convert an Excel table to a range, which is one of the things that may be a little aggravating about the process.

And now you are going to have to manually clear the formatting of the Excel table.

Let’s say you have the Excel table displayed below at your disposal:

The procedures necessary to convert this Excel table to a range are as follows:

  • Use the right mouse button to choose any cell in the Excel table.
  • Choose the option to get to the Table.
  • Click the “Convert to Range” button.

The outcome of this for you may be seen in the table below (where the table has been deleted but the formatting remains).

You now have the option to manually adjust the formatting, or you may completely wipe all of the formatting.

Follow the instructions below in order to get rid of all of the formatting:

  • Choose the complete portion of the range that will be formatted.
  • Select the Home tab in the menu.
  • Click the Clear button located in the Editing group.
  • Select Clear Formats from the list of available alternatives that appears.

If you did this, you would be left with simply the data, and all of the formatting would be eliminated.

It’s also possible to accomplish this goal by first removing all of the formatting from the Excel Table itself (using the manner described in the previous section), and then deleting the table. This is just one of many possible approaches (Convert to Range).

Delete the Table

The following steps should be followed in order to get rid of the table completely:

  • Choose the whole of the table.
  • Hit the Delete key

This will erase the table in Excel, as well as remove any formatting the table may have had (except the formatting that you have applied manually).

In the event that you have certain manually applied formatting that you also want to delete when removing the table, follow the procedures that are listed below:

  • Select the button labelled Clear (in Editing group)
  • To get rid of everything, choose Clear All.

To clear everything at once in Excel Windows, press ALT + H + E + A on your keyboard (press these keys one after the other in succession).

Therefore, the following are some examples of situations in which you may delete table formatting in Excel.

I really hope that you found this lesson to be helpful.

Video Guide

Leave a Reply

Your email address will not be published. Required fields are marked *