This situation happens a lot. Someone creates a pivot table based on some source data. They copy and paste it as values and that becomes your new data source. But really you would have preferred the unpivoted source data.

Can you easily unpivot the data? Yes!

I have created a pivot table based on some source sales data.

• Rows area – I’ve added Customer Name, Customer Country and Sales Channel.
• Column area – I’ve added the Product sold.
• Values area – I’ve added the Total.

We can see this creates a new set of data where each product has its own column heading. This is pivoted data.

This the original source data. It’s in an unpivoted format. We want to go from the pivoted data to the unpivoted data.

Select your data to unpivot (which has been pasted as values from a pivot table) and go to the Data tab in the ribbon and press the From Table/Range button.

Select the columns to unpivot in the query editor. These are all the product columns.

1. Left click on the first column heading.
2. Hold the Shift key.
3. Left click on the last column heading.

Right click on any of the selected columns and select Unpivot Only Selected Columns from the menu.

The query editor preview will show the unpivoted data. You can rename the columns from the default Attribute and Value to something more relevant with a double left click on the heading. In our case these should be Product Sold and Total.

Go to the Home tab in the query editor and press the Close & Load button to save the query and output the resulting data to a new sheet. This is a very quick way to reformat your data back to a more usable tabular format.