Excel column widths changing upon data refresh, DESPITE "adjust column widths" being unchcked

Question

I'm in the latest version of Excel 365, and have a data connection set up to a php page on my website. It works flawlessly, but every refresh makes the columns default back to being just wide enough to fit the source data.

BELOW the data-connected table, I have other formulas/values. These are longer strings, and as such they end up as "#####" when the column sizes get adjusted to match the incoming data.

How can I fix this?

Thanks!

Solution

It seems that the answer by user @Sach didn't work. It might be improved by, in addition to clearing "Adjust Column Width", to set in the properties the item "Preserve Column Sort/Filter/Layout".

If that does not help, a solution that will work is to use an intermediate hidden worksheet that will contain the data from the external data connection, and link your worksheet to that, instead of directly to the external data source.

The reason for the problem is that Excel actually stores formatting data in a cache with the retrieved data. When the data is refreshed, Excel invalidates this cache, so that the formatting is changed together with the data

Comments

[harrymc]

In addition to "Adjust Column Width", it might help to set in the table properties "Preserve Column Sort/Filter/Layout". Failing that, you might use an intermediate hidden worksheet for the external data connection and link to that instead.