Some Cells After MS Query Table Refreshed Have to Be Clicked before Formatting Works

%3CLINGO-SUB%20id%3D%22lingo-sub-2890668%22%20slang%3D%22en-US%22%3ESome%20Cells%20After%20MS%20Query%20Table%20Refreshed%20Have%20to%20Be%20Clicked%20before%20Formatting%20Works%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2890668%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20situation%20that%20is%20driving%20me%20crazy.%26nbsp%3B%20First%20off%2C%20I%20am%20using%20excel%20in%20Office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20in%20which%20I%20export%20data%20out%20of%20SAS%20tables%20into%20different%20sheets.%26nbsp%3B%20I%20have%20a%20main%20sheet%20where%20I%20use%20MS%20Queries%20to%20pull%20that%20data%20in.%26nbsp%3B%20I%20have%20formatted%20certain%20columns%20in%20each%20of%20them%20to%20be%20percentages%20and%20currency.%26nbsp%3B%203%20of%20the%204%20queries%20keep%20that%20formatting%20when%20the%20workbook%20is%20refreshed%2C%20however%2C%201%20of%20them%20is%20not%20refreshing%20correctly.%26nbsp%3B%20When%20refreshed%2C%20the%20data%20populates%20in%20a%20way%20that%20I%20have%20to%20click%20on%20the%20cell%20and%20move%20out%20of%20it%20for%20the%20format%20to%20take%20effect.%26nbsp%3B%20It's%20only%20happening%20in%20a%20couple%20of%20columns%20in%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EMade%20sure%20all%20the%20options%20for%20preserving%20the%20formatting%20have%20been%20checked%3C%2FLI%3E%3CLI%3EDeleted%20the%20query%20and%20recreated%20it%3C%2FLI%3E%3CLI%3ECreated%20the%20same%20query%20in%20different%20worksheets%20to%20see%20if%20there%20was%20some%20issue%20with%20the%20specific%20cells%20that%20I%20was%20having%20to%20click%20on.%26nbsp%3B%20Happens%20no%20matter%20where%20I%20put%20it%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20have%20put%20an%20all%20out%20blitz%20on%20Google%20but%20can't%20find%20an%20answer%20that%20fixed%20my%20problem.%26nbsp%3B%20I%20just%20can't%20figure%20out%20why%203%20of%20my%20queries%20work%20fine%20but%20this%20one%20doesn't.%26nbsp%3B%20All%20data%20that%20feeds%20in%20from%20the%20other%20tabs%20are%20formatted%20identically%20(general)%20on%20the%20tabs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20someone%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2890668%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2892430%22%20slang%3D%22en-US%22%3ERe%3A%20Some%20Cells%20After%20MS%20Query%20Table%20Refreshed%20Have%20to%20Be%20Clicked%20before%20Formatting%20Works%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2892430%22%20slang%3D%22en-US%22%3EAnyone%20that%20can%20take%20a%20crack%20at%20this%20for%20me%3F%3C%2FLINGO-BODY%3E
New Contributor

Hello all.

 

I have a situation that is driving me crazy.  First off, I am using excel in Office 365.

 

I have a workbook in which I export data out of SAS tables into different sheets.  I have a main sheet where I use MS Queries to pull that data in.  I have formatted certain columns in each of them to be percentages and currency.  3 of the 4 queries keep that formatting when the workbook is refreshed, however, 1 of them is not refreshing correctly.  When refreshed, the data populates in a way that I have to click on the cell and move out of it for the format to take effect.  It's only happening in a couple of columns in the query.

 

I have tried the following:

 

  • Made sure all the options for preserving the formatting have been checked
  • Deleted the query and recreated it
  • Created the same query in different worksheets to see if there was some issue with the specific cells that I was having to click on.  Happens no matter where I put it

I have put an all out blitz on Google but can't find an answer that fixed my problem.  I just can't figure out why 3 of my queries work fine but this one doesn't.  All data that feeds in from the other tabs are formatted identically (general) on the tabs.

 

I am hoping someone can help.

 

Thanks!

 

 

2 Replies
Anyone that can take a crack at this for me?
Well, I ended up rebuilding it all in Power Query vs. MS Query and everything seems to be working better.