Actually “Unpivot” a Pivot Table

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.

5 thoughts on “Actually “Unpivot” a Pivot Table

  1. Alternatively, you can click on the Data tab, Connections and remove the selected connection. You’ll get a message stating that “deleting the connection will separate this workbook from its data source(s)”.

    When you ok this, you’re left with a static table of data 🙂

  2. That’s a good method too, Lester, though you’d want to make sure your pivot table was tabulated and completely expanded or else your data would be sort of “hobbled”. It would probably work even better if you had a plain table but not a pivot table. Then you could pivot the table afterwards and still have pivot capabilities. I have no idea how I missed this one, thanks for the tip! 🙂

  3. I started off with reading *this* article because this relates directly to an issue that I’m encountering with users wanting to consume reports that we can generate out of our BI Center but whom we do not yet wish to give read permissions for the reporting database. But then I moved onto another article and felt “wow, she sounds an awful lot like me!” You’ve had a lot of the same questions, same responses, and I think my career track looks a little bit like yours (after reading your bio). Thanks for the insight, thanks for sharing, and please keep it up! It helps!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s