Forum Discussion
Table will not convert to range
I am trying to convert a Table to a normal range. I right click on the table and choose "Table>Convert to Range" . After selecting "yes" the data remains unchanged in a Table format. Are there any other checks I can take?
I had a similar issue. What I found was that you need to first perform some Table action. Then you can successfully convert the Table into a Range.
Example: I first sort the table. Then I am able to successfully convert it.
I had to copy the sheet (w/o the table headings) to a new workbook, delete the table sheet, and recopy the sheet back to the original workbook.
- Is it still really a Table, or does it just still have the colors applied without the filter buttons and other table features?
JonPeltierMVP it appears it continues to behave as a Table - I tried for example deleting the headers, but they keep getting automatically renamed to "Column1" or "Column3" or whatever the value would be assigned by an actual table.
One thing to mention is that my table used to be connected to an external data source, which I'm removing in order to link it to a different external data source.
If you have any data connection, even for deleted source, you can't convert the table. All connections are to be removed first.
I can confirm this is still an issue in Version 1902.
Clinton AzoteaI had the same problem with a couple of Excel tables. I would click Convert to Range and then click Yes, I do want to convert the table to a Normal Range, and nothing would happen. The only workaround I could find was to copy and paste as Values with Number Formatting.
The problem seems to reside in certain Excel workbook files. If I copy the Excel table to a new workbook, I can convert the table to a range without any problem.
- Hi, Clinton! One thing is the table as an object and another thing is the table format. When you convert a table into a normal range, the format of the table in the cells is maintained, but the dynamic properties of the table are lost. There are at least three ways to identify if a range is a table: 1. When you stop in any cell of it, the table tools appear at the top right, on the ribbon. 2. If you go to the bottom right of the table, you see a kind of triangle, which is used to manually resize the size of the table. 3. When you are standing in a table, and you are several records down until you can not see the titles of the table, Excel column titles are replaced by the titles of the table. If you convert by the procedure that you comment a table in a normal range, you will not see any of the three named characteristics. Blessings!
