How To Un-Pivot Your Data In Excel


I do not claim to be an expert in Excel, but I do like to learn about it and use it when warranted. As such, it behooves me to try to keep abreast of what is going on in the world of Excel. In that vein, I came across information describing how to ”Un-pivot” data recently. It turns out that there is something called the Unpivot feature in Excel and it gives you the tool you need to change your data from columns into paired values. I have also learned that some folks call this going from wide to long, meaning the feature will let you go wide by having multiple columns for data for each row or long, meaning you will have a single data point for each row.

It doesn’t really matter what you call it, but I was so impressed with this information that I am now going to share it with you!

Follow the steps below to learn how to un-pivot your data:

  1. From the Data ribbon in Excel, open Query Editor.
  2. From the Get & Transform section, select the From Table option. If your data does not have clear formatting, the feature may first ask you to define a data range. Once you have done that, select the columns you would like to Unpivot.
  3. Click the Transform tab and select Unpivot Columns.
  4. Once you have executed the Unpivot, you will see two new columns at the right-hand side called Attribute and Value.
  5. Rename the columns to something intuitive for you.
  6. Save your work to the default location for Excel by clicking File | Close & Load.
  7. Should you want to select an alternate location, choose the Close & Load To option.

I hope you are as impressed with this information as I was!


About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *