Format of "data range" area not as a table

[tomas_manca]

Hi,
I have trouble with format of the data area from external data import.

I do properly the connection to the external data and I proceed up to regular point (pasting the data into excel sheet), where I can choose to format the data as a table or pivot table or pivot chart.
But I do not want any of these options, but to have it formatted as regular data area.

If teoretically I choose the table format and then I try to convert it to range, I can do, but this during this operation excel removes the connection to external data.


I have older excel files from somebody, where the external connection to the area exists and it's not formatted as a table. Now I need to create some new ones like this, but I can't find out how to do it.


I can see also, that in the menu - Data - Connection - Add I can set a new connection, but there's no option to adress excel, where to show this data. (It's true, in the older files I can see excel addressing the first cell, but this is not editable in this part of menu as well.)

Do you know how to do it?
thank you much, Tomas

 

[Yongle]

try this

select any cell in table \ DESIGN tab\ click on TABLE STYLE dropdown \ go to bottom \ click CLEAR

 

[jkpieterse]

By design you cannot create new connections without their results being pulled into a table. I applaude this behaviour because tables have many advantages over non-table ranges, such as automatic expansion of forumlas pointing to the table, automatic expansion of source ranges of charts and pivottables and etcetera. Also you can add formulas in your table (in any column) and the data connection will continue to work and refresh the correct columns.

 

[tomas_manca]

Hello,
thank you for your time and comments.

@ Yongle: Unfortunately this "\ DESIGN tab\ click on TABLE STYLE dropdown \ go to bottom \ click CLEAR" doesn't remove table formatting, just the erases the predefined design.
@ jkpieterse: Tables have advantages, no doubt, but not each time (we e.g. have large files with many data queries and switched off automatic calculation). My main main point was that we have files created years ago, where is something, what I can't immitate now in my Office 2010 (I can just copy the old data range from older file and adapt the SQL query). Does it mean, I just can't find, how to do it in Office 2010? Or does it mean Microsoft excluded this option from later Excel versions?

thank you, regards, Tomas

 

[jkpieterse]

The latter. I still don't really get what advantage there is to have them as regular ranges.