Note: Someone at the Excel community board recommended asking this here.  My question is similar in spirit to this one, but I'm not working on a pivot table. It's a primary table generated from a SQL connection.

 

I have an excel table whose data is generated from an external SQL connection.

 

Id like to format the table nicely by (e.g.):

 

1.       Changing some column names

2.       Changing some column widths

3.       Applying conditional formatting to the data in the table.

 

When I refresh all on the spreadsheet, #3 is retained, but #1 is set to the value from SQL, and #2 is reset to the default column width.

 

Is what Im trying to do possible?  Does it require some amount of indirection to a new table?

 

Thanks!

Use formulas to link table on another worksheet and fomat as you please. Hide the worksheet with the external data connection.

HTH!

David Hager

Unrecognized Excel MVP (UEM)

• Marked as answer by DanJosef Tuesday, October 09, 2012 4:10 PM

Use formulas to link table on another worksheet and fomat as you please. Hide the worksheet with the external data connection.

HTH!

David Hager

Unrecognized Excel MVP (UEM)

• Marked as answer by DanJosef Tuesday, October 09, 2012 4:10 PM

Reposting reply which I accidentally deleted.

This sounds promising.  Please, could you provide a starting point on the functions/formulas to link the new table to the source table?
 
I tried something like this on a new sheet:
 
=IFERROR(Souce_Table_Name[#All],"")
 
This gets the data into my new sheet, and then I can format the data in the new sheet as a table and make my changes.  The changes are preserved after a refresh.
 
The only problem I can see is that the table is a fixed size (for whatever cells I copy the formula above into), rather than scaling to match the number of rows in the source table after the data refreshes.  This results in either a table that has many extra rows, or a table that is too short (the data extends beyond the "table" length).
 
Is the formula above what you had in mind?  Any way to treat the scaling of the length of the table?
 
Thanks!

 
The only problem I can see is that the table is a fixed size (for whatever cells I copy the formula above into), rather than scaling to match the number of rows in the source table after the data refreshes.  This results in either a table that has many extra rows, or a table that is too short (the data extends beyond the "table" length).

I think that I've mostly gotten around this point by using filters on the new table.  I can make the table much longer than I expect the data to be, and then filter out any rows with blanks in a column where I expect no blanks.  It's not perfect...  Sometimes I need to reapply the filter to get new data to show up. 

Thanks Dave for the starting point!

You can get the column names you want by editing your SQL so that it brings back text other than the column name. e.g. SELECT Qty "My Quantity Heading", Price "This is the Price" FROM etc.

This won't help if you're also trying to change the formatting in the Excel cell, but at least it will give you the text you want.