How to unpivot Excel

You can ‘unpivot’ a table of Excel data in a few clicks using Easy Data Transform. For example, you might want to unpivot this table of averaged sensor data, arranged by hour (column) and day (row):

Into a form where there is a separate row for each hour and day:

• Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
• Start Easy Data Transform. Make sure the Auto Run button is pressed in.

• Download unpivot.zip and unzip it.
• Drag the unpivot.xlsx file onto Easy Data Transform.

• If you look in the Right pane, with pink input item selected, you will see the Excel data table that we want to unpivot.
• With the pink input item selected click the Gather transform button in the Left pane. You may need to scroll the Left pane down to see the button. If the button isn’t showing, check show advanced in the Left pane.

• A Gather transform will be added. Ensure the Gather transform is selected. In the Right pane check all the columns by clicking ‘select all columns’ and then uncheck the Date column. Set the key column name and Value column name as shown:

• The unpivoted data will then be shown.

• We can now output the unpivoted data to a new Excel file. With the Gather item selected click on the To File button in the Left pane (you may need to scroll down to see it).

• Choose the output file location and format. A green output item will be added and the new file will be created. No need to ‘run’ anything.

• Finally you can File>Save your transforms as a .transform file to re-use in the future.

For more information on Gather options see the Gather documentation.

As well as unpivoting data, Easy Data Transform also allows you to combine 66 transforms in many other ways to quickly and easily create complex data transformations for numerical, text and date data. For example you could add some additional transforms to produce timestamped data:

And re-pivot to average the data by day:

You can File>Open the unpivot.transform file included in unpivot.zip to try the above.

Easy Data Transform can process millions of rows, and input and output in multiple formats. If you need to unpivot lots of Excel sheets you can do it in a single operation using the batch processing feature.