Formatting of Columns is Not Retain when I Refresh Query in Excel 2016

All,

When I refresh a query in Excel 2016, the formatting of columns is lost. I have set all properties to retain as far as I am aware, i.e. External Data Properties , i.e. "Preserve column/filter/layout" and "Preserve Cell Formatting" are both checked. I can't find any other settings that would impact this.  Is there somewhere else I need to enforce this?

Regards


Rod

|

Hi Roderick,

Settings may not retained if Excel is having a system glitch that might affect the performance. If this issue only appear on a specific document, we suggest that you perform a repair for Office application by  follow the steps provided on this link.

Let us know if you need further assistance.

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Sorry Ryan,

I did the repair but the issue remains. 

Regards

Rod

1 person found this reply helpful

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thank you for posting back. Have you tried uninstalling and reinstalling the program and check if it works. We suggest that you try booting your device in clean boot to start you device by using a minimal set of drivers and startup programs, then check the issue if still persists.

We'll keep an eye out for your response.

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Roderick,

We are familiar with this problem, it happens when "Preserve column/filter/layout" option was unchecked. The table columns lost its formatting and checking this checkbox again is not enough to restore the column formatting.

I would like to propose you a workaround to overcome this. The workaround is to recreate the table on the grid.

Please follow the steps below to do that (Please note that the custom cell formatting you did for this table on the grid will be lost).
1. Delete the table (Select the whole table on the grid and press Del).
2. Create the table using this data source:
     a) Open "Existing Connections" dialog (“Data” --> “Get & Transform Data”) and choose the data source, click "Open".
     b) On "Import Data" dialog check the "Table" and "Existing worksheet" options, and select the cell on the grid where you want to place your new table, click OK.

Please let us know if this worked for you.

Regards,

Natalia

1 person found this reply helpful

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Natalia, 

I am afraid this didn't work. I deleted the table, and reloaded as instructed then adjusted the formatting but it still reverted to default formatting after I refreshed the query,

Regards


Rod

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Roderick,

 

Thank you for taking the time to check.

 

To better understand your problem, we would appreciate the following information:

1. Can you please share your Excel version? Go to File > Account > and look for Excel version under Product Information.

2. Did you change the formatting of the table cells on the grid?

3. Sending me the problematic workbook will also help. Just make sure that your workbook doesn’t contain any sensitive information in it – remove the sensitive data or scramble it before sharing the workbook with me.

 

If you cannot share you workbook, the following information will be very useful:

1. The formatting of problematic column in the Query Editor: Select the query on the "Queries & Connections" pane > Edit > Select column on the preview table > Right Click > Change Type > The current formatting type is checked.

2. The formatting of problematic column on the table on the grid after the table is created: Select needed cell in the table on the grid > Right Click > Format Cells.. > The current formatting type is selected.

3. The unexpected formatting of problematic column on the table on the grid after the refresh: Select needed cell on the table on the grid > Right Click > Format Cells.. > The current formatting type is selected.

 

Your feedback and help are greatly appreciated!

 

Regards,

Natalia

Excel Team

 

1 person found this reply helpful

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Hi Natalia,

Sorry for taking a long time to reply. Where can I send the test excel spreadsheet and database to, no option to provide attachments here?  In answer to your questions

Version info 

yes, formatting was changed on Tables cells in Grid.

Regards


Rod

Was this reply helpful?

Yes
No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated August 31, 2021 Views 11,979 Applies to: