Excel pivot table changes entirely when I click on "enable editing"

[rambo919]

This is a wtf situation, I have to work with a spreadsheet received from a client. The pivot table is well formatted visually, for some reason it opens in protected view and the yellow "enable editing" button shows, so to do anything at all I click it..... the entire pivot table looses all formatting and multiple columns disappear.....

what?

I can get around this by copy and pasting the received table to another sheet.... but wtf is going on here?

 

[SlinkyMike]

This is a wtf situation, I have to work with a spreadsheet received from a client. The pivot table is well formatted visually, for some reason it opens in protected view and the yellow "enable editing" button shows, so to do anything at all I click it..... the entire pivot table looses all formatting and multiple columns disappear.....

what?

I can get around this by copy and pasting the received table to another sheet.... but wtf is going on here?

Try the option: "preserve cell formatting" in the pivot table options on the layout and formatting tab.

Are those columns hidden, gone or just resized so as to make them hard to see?

 

[Pineapple Smurf]

@TribbleZA

 

[swift412]

My guess: "Refresh data when opening the file" is enabled under the Data tab under PivotTable Options, and when you click on Enable Editing, it updates from the source data file, and because the data is different, it throws off all the formatting.

 

[rambo919]

Spoiler: before

Spoiler: after

"Refresh data when opening the file" is indeed enabled but I cannot disable it without first enabling editing, the source is the first sheet so it's not even external.

Out of interest the data reflected is the same as I received last year so it seems to default back to a previous result.

Anyway it's no trainsmash I don't actually need to enable editing to copy from it I just wondered wtf excel was doing cause it made no sense.

 

[I.am.Sam]

check the column names was the same as before cause if it has changed even though the data is the same this might be the issue

if you have column A as date and the pivot was based on date it would change if the column name is now Date and Time but the data is based on a date