Excel Table Formatting Does Not Re-Apply After Refreshing Data From SQL Server

Question

How would I force cell formatting to take effect automatically after a data refresh?

I have a connection set up to pull data from SQL Server to a table in Excel.

However, upon initial pull and refresh - cell formatting does not take effect on these cells, until I interact with them in some way such as double-clicking, or pressing F2, or even using Text To Columns to fix them all at once.

My settings indicate to preserve cell formatting, and it is preserved, it just doesn't seem to apply until interaction of some kind occurs.

See here before and after shots:

The issue is affecting formatting of my entire table, the specific column shown IS a date type in SQL Server.

Is there something I'm completely overlooking to have cell formatting apply to cells after data refresh, or if this is just a bug in Excel, would it be possible to maybe macro out a Text To Columns action after every data refresh?

I'm on Excel 2013, with SQL Server 2008 R2.

Any help appreciated, Thanks.

EDIT 1: No formulas exist in table, has been tested on multiple machines and on Excel 2013 and 2016 and issue still occurs.

Is there some sort of formula in those cells? Try: https://superuser.com/questions/836324/cells-not-updating-automatically — justiceorjustus, Aug 22 '17 at 19:09
@justiceorjustus No formulas present in the table, it's just a straight pull from a database with the intent of formatting the date and a few other columns, as well as adding slicers and filters. — RoflcopterV RoflcopterV, Aug 22 '17 at 19:10
Try that and see if it helps. Your issue feels like how an excel sheet behaves when that option turned off... I don't know if it affects formatting. — justiceorjustus, Aug 22 '17 at 19:12
It was already automatic, tried changing it to manual, tested it again, changed it back to automatic, same behaviour still. I also sent the workbook to a co-worker to test against it being my install/machine, they are experiencing the same issue. I'm going to install excel 2016 and verify that the issue persists there as well. — RoflcopterV RoflcopterV, Aug 22 '17 at 19:16
@justiceorjustus I have the datatype of `Date` set as 'date' in SQL Server 2008 R2 — RoflcopterV RoflcopterV, Aug 22 '17 at 19:44

justiceorjustus · Accepted Answer · 2017-08-23T12:54:06.680

2

I think this is an issue of Excel not recognizing your data as a date. Try this cast for your date from SQL:

CAST(YourDate AS smalldatetime) "Date"

edited Aug 23 '17 at 12:54

answered Aug 22 '17 at 19:45

justiceorjustus

2,017
1
19
42

This worked, apparently the SQL Datatype of `date` doesn't translate well to Excel, however smalldatetime is perfect. I originally had the definition command type set to Table, changing it to SQL and manually selecting every column with some being cast as more Excel-Friendly datatypes worked perfectly! – RoflcopterV RoflcopterV Aug 22 '17 at 20:02
@RoflcopterVRoflcopterV Glad to help! – justiceorjustus Aug 22 '17 at 20:04

Excel Table Formatting Does Not Re-Apply After Refreshing Data From SQL Server

1 Answers1