When I first started working with Pivot Tables, especially those generated from an external data connection, I remember doing constant google searches that looked something like this:
- unpivot pivot table excel 2010
- remove pivot table excel 2010
- Remove pivot table data connection keep pivot data
- Depivotize a pivot table
- Why are pivot tables complete jerks
- What if I want to get rid of the connection but keep the data? Why do you hate me?
Repeat, ad nauseam.
I have since gotten into the habit of copying and pasting the data “as values” from one sheet to another to “unpivot” them, which I find most useful when I’m trying to create a static report that I can send in excel and not have to worry about the data refresh working or not working for another user. It’s really not that hard, I guess. But in the early days, I know I would have liked to just know that there was another option I wasn’t aware of. I don’t know why I didn’t think of this earlier, to be honest.
So here’s the quick answer – save the tab of your workbook as a csv file. Close the file. Open it again. Then save it as an xlsx again. Yeah. That just kills that connection completely, don’t it? Of course it kills all formatting etc as well, but that’s usually what I was looking to do anyway! It also has less chance of some kind of tragic copy/paste user error. So there! I thought I would share, because I wish I had this info 3 years ago.