Home

Table will not convert to range

%3CLINGO-SUB%20id%3D%22lingo-sub-194186%22%20slang%3D%22en-US%22%3ETable%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-194186%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20convert%20a%20Table%20to%20a%20normal%20range.%26nbsp%3B%20I%20right%20click%20on%20the%20table%20and%20choose%20%22Table%26gt%3BConvert%20to%20Range%22%20.%20After%20selecting%20%22yes%22%20the%20data%20remains%20unchanged%20in%20a%20Table%20format.%26nbsp%3B%20Are%20there%20any%20other%20checks%20I%20can%20take%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-194186%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%202016%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-194255%22%20slang%3D%22en-US%22%3ERE%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-194255%22%20slang%3D%22en-US%22%3EHi%2C%20Clinton!%20One%20thing%20is%20the%20table%20as%20an%20object%20and%20another%20thing%20is%20the%20table%20format.%20When%20you%20convert%20a%20table%20into%20a%20normal%20range%2C%20the%20format%20of%20the%20table%20in%20the%20cells%20is%20maintained%2C%20but%20the%20dynamic%20properties%20of%20the%20table%20are%20lost.%20There%20are%20at%20least%20three%20ways%20to%20identify%20if%20a%20range%20is%20a%20table%3A%201.%20When%20you%20stop%20in%20any%20cell%20of%20it%2C%20the%20table%20tools%20appear%20at%20the%20top%20right%2C%20on%20the%20ribbon.%202.%20If%20you%20go%20to%20the%20bottom%20right%20of%20the%20table%2C%20you%20see%20a%20kind%20of%20triangle%2C%20which%20is%20used%20to%20manually%20resize%20the%20size%20of%20the%20table.%203.%20When%20you%20are%20standing%20in%20a%20table%2C%20and%20you%20are%20several%20records%20down%20until%20you%20can%20not%20see%20the%20titles%20of%20the%20table%2C%20Excel%20column%20titles%20are%20replaced%20by%20the%20titles%20of%20the%20table.%20If%20you%20convert%20by%20the%20procedure%20that%20you%20comment%20a%20table%20in%20a%20normal%20range%2C%20you%20will%20not%20see%20any%20of%20the%20three%20named%20characteristics.%20Blessings!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659833%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F147336%22%20target%3D%22_blank%22%3E%40Clinton%20Azotea%3C%2FA%3EI%20had%20the%20same%20problem%20with%20a%20couple%20of%20Excel%20tables.%20I%20would%20click%20Convert%20to%20Range%20and%20then%20click%20Yes%2C%20I%20do%20want%20to%20convert%20the%20table%20to%20a%20Normal%20Range%2C%20and%20nothing%20would%20happen.%20The%20only%20workaround%20I%20could%20find%20was%20to%20copy%20and%20paste%20as%20Values%20with%20Number%20Formatting.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3BThe%20problem%20seems%20to%20reside%20in%20certain%20Excel%20workbook%20files.%20If%20I%20copy%20the%20Excel%20table%20to%20a%20new%20workbook%2C%20I%20can%20convert%20the%20table%20to%20a%20range%20without%20any%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-993406%22%20slang%3D%22en-US%22%3ERe%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-993406%22%20slang%3D%22en-US%22%3E%3CP%3EI%20can%20confirm%20this%20is%20still%20an%20issue%20in%20Version%201902.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-993488%22%20slang%3D%22en-US%22%3ERE%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-993488%22%20slang%3D%22en-US%22%3EIs%20it%20still%20really%20a%20Table%2C%20or%20does%20it%20just%20still%20have%20the%20colors%20applied%20without%20the%20filter%20buttons%20and%20other%20table%20features%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024594%22%20slang%3D%22en-US%22%3ERE%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F25101%22%20target%3D%22_blank%22%3E%40Jon%20Peltier%3C%2FA%3E%26nbsp%3Bit%20appears%20it%20continues%20to%20behave%20as%20a%20Table%20-%20I%20tried%20for%20example%20deleting%20the%20headers%2C%20but%20they%20keep%20getting%20automatically%20renamed%20to%20%22Column1%22%20or%20%22Column3%22%20or%20whatever%20the%20value%20would%20be%20assigned%20by%20an%20actual%20table.%3C%2FP%3E%3CP%3EOne%20thing%20to%20mention%20is%20that%20my%20table%20used%20to%20be%20connected%20to%20an%20external%20data%20source%2C%20which%20I'm%20removing%20in%20order%20to%20link%20it%20to%20a%20different%20external%20data%20source.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1024889%22%20slang%3D%22en-US%22%3ERE%3A%20Table%20will%20not%20convert%20to%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1024889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F464744%22%20target%3D%22_blank%22%3E%40iromero1305%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20any%20data%20connection%2C%20even%20for%20deleted%20source%2C%20you%20can't%20convert%20the%20table.%20All%20connections%20are%20to%20be%20removed%20first.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Clinton Azotea
Occasional Visitor

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?  

6 Replies
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!

@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.

I can confirm this is still an issue in Version 1902.

Is it still really a Table, or does it just still have the colors applied without the filter buttons and other table features?

@Jon Peltier 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.

@iromero1305 

If you have any data connection, even for deleted source, you can't convert the table. All connections are to be removed first.

Related Conversations
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Counting Days
Tim Hunter in SQL Server on
2 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
How do I group rows in an excel table?
Marisa Medrano in Excel on
1 Replies
Delete row/column including table row/column
Johan van Wensen in Excel on
4 Replies