Excel column widths changing upon data refresh, DESPITE “adjust column widths” being unchcked
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!
microsoft-excel office365
asked May 9 '16 at 13:39
Codemonkey
2971313
add a comment |
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!
microsoft-excel office365
asked May 9 '16 at 13:39
Codemonkey
2971313
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.
– harrymc
Dec 17 at 16:43
add a comment |
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!
microsoft-excel office365
asked May 9 '16 at 13:39
Codemonkey
2971313
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!
microsoft-excel office365
microsoft-excel office365
asked May 9 '16 at 13:39
Codemonkey
2971313
asked May 9 '16 at 13:39
Codemonkey
2971313
edited May 9 '16 at 15:29
asked May 9 '16 at 13:39
Codemonkey
2971313
asked May 9 '16 at 13:39
Codemonkey
2971313
asked May 9 '16 at 13:39
Codemonkey
2971313
2971313
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.
– harrymc
Dec 17 at 16:43
add a comment |
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.
– harrymc
Dec 17 at 16:43
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.
– harrymc
Dec 17 at 16:43
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.
– harrymc
Dec 17 at 16:43
add a comment |
2 Answers
2
active
oldest
votes
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
answered Dec 19 at 7:13
harrymc
253k12259562
add a comment |
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
answered Jan 26 '17 at 18:39
Sach
1
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1074809%2fexcel-column-widths-changing-upon-data-refresh-despite-adjust-column-widths-b%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Dec 19 at 7:13
harrymc
253k12259562
add a comment |
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
answered Dec 19 at 7:13
harrymc
253k12259562
add a comment |
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
answered Dec 19 at 7:13
harrymc
253k12259562
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
answered Dec 19 at 7:13
harrymc
253k12259562
answered Dec 19 at 7:13
harrymc
253k12259562
answered Dec 19 at 7:13
harrymc
253k12259562
answered Dec 19 at 7:13
harrymc
253k12259562
253k12259562
add a comment |
add a comment |
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
answered Jan 26 '17 at 18:39
Sach
1
add a comment |
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
answered Jan 26 '17 at 18:39
Sach
1
add a comment |
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
answered Jan 26 '17 at 18:39
Sach
1
Go to DATA tab -> Connections. In the dialog box click "Click here to see where the selected connections are used". It will show the table in Excel that is being used by the connection. Click that table and Close the dialog box. Next, in DATA tab select Properties and in the dialog box uncheck "Adjust Column Width". Click OK. Now Refresh All and you will see that column width has not changed and all your ##### are replaced with the values.
Sach
answered Jan 26 '17 at 18:39
Sach
1
answered Jan 26 '17 at 18:39
Sach
1
answered Jan 26 '17 at 18:39
Sach
1
answered Jan 26 '17 at 18:39
Sach
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1074809%2fexcel-column-widths-changing-upon-data-refresh-despite-adjust-column-widths-b%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– harrymc
Dec 17 at 16:43