Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've used the new Table Formatting option in Excel 2007. Now I can't remove it. I've dragged the little blue square up to the last cell on the top left, but it just won't go any further. In fact it just won't go at all.

Clear all doesn't remove it. What does? I want my table back! I'm not a beginner with Excel, but this little annoyance has made me feel like on.

Surely there must be some way to remove table format without deleting something or clearing all!

share|improve this question
up vote 13 down vote accepted

"Convert to Range" should convert it to normal ranges. Right click on a cell in table and select Table -> Convert to Range. You would need to remove the formatting manually.

share|improve this answer
Finally got around to trying this and it works. Many thanks for your insight. – RocketGoal May 10 '10 at 11:15
Alternate click path, use the Table Design tab, see – matt wilkie Apr 30 '15 at 21:05

Try selecting the entire sheet (click box with the arrow in to the left of "A", and above "1") and then try clear all again.

share|improve this answer
But I don't want to lose my data! If I clear all I imagine that's what's going to happen. – RocketGoal Apr 29 '10 at 14:21
No no, I mean the clear all formatting :). Make a copy of the file first if you think we might be talking about two different options though! – user35557 Apr 29 '10 at 14:31
Alternativley hit Ctrl+A, Ctrl+C, open a new worksheet, right click cell A1, click paste special, click "Values" and click ok. Should give you all your data with no formatting. – user35557 Apr 29 '10 at 14:34

I was able to pull this off by highlighting the entire table and then right click and choose Format Cells. Then go through and remove colors and borders and any filling and you will effectively remove the table and keep all data intact.

share|improve this answer
But you've only removed the look of the table. The actual table is still there. – RocketGoal May 7 '10 at 7:50
Did you try Right click on the table, click Table > Convert to Range as Shaji recommends? – nicorellius May 7 '10 at 14:46

If you have MATLAB:

This is really only useful if you use MATLAB often and is usually open. Make sure your Excel file isn't open when attempting this code. Nothing will happen to your data don't worry because the code won't run.

  1. Change MATLAB path to where your .xls or .xlsx file is located
  2. >> [~,~,a] = xlsread('yourfile.xls')
  3. >> xlswrite('yourfile.xls',a)

Table removed!

Notice I used 'yourfile.xls'. Change this to 'yourfile.xlsx' if your file is a .xlsx file.

share|improve this answer
  1. Select the whole table.
  2. Click in the Styles tab Format Table.
  3. Select at the button New Table Style....
  4. Select First Column Stripe.

It will create at the top of all the styles a white box Custom style. Click on this and the format you previously had will disappear.

It works for me....

share|improve this answer
  1. Click anywhere in the table.

    Tip: This displays the Table Tools, adding the Design tab.

  2. On the Design tab, in the Tools group, click Convert to Range.

Excel Ribbon Image

Note: Table features are no longer available after you convert the table back to a range. For example, the row headers no longer include the sort and filter arrows, and structured references (references that use table names) that were used in formulas turn into regular cell references.

share|improve this answer
don't know why this was downvoted, it is a correct and working answer. – matt wilkie Apr 30 '15 at 21:04

If using Excel 2010: highlight the cells in the table; go to Home / Editing / Clear / Clear Formatting. Then remove the Filter as well. enter image description here

share|improve this answer
  1. Highlight the table and copy to clipboard.
  2. Open temporary worksheet
  3. Paste as unformatted text.
  4. Recopy unformatted table back into main document.
share|improve this answer
I'm afraid this seems to be the same solution as the comment added by @user35557 to his answer back in April so I feel obliged to downvote - with apologies! - though it DOES work. – pnuts Oct 19 '12 at 23:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .