Organising data in Excel for analysis, part 2

Following on from my previous post, here is a time-saving suggestion for reshaping your data using recent versions of Excel, using unPivot.

Say you have data in a table like this (lets call this table 1):

and you want to convert to “long-format” like this:

This conversion could involve lots of cutting and pasting, dragging and dropping fill down …. etc., but most of the hard work can be done automatically if you have a recent version of Excel with the Power Query Editor.

Select table 1.

On the Data Tab on the ribbon, click Get Data at the far left.

Choose “From Other Sources >> from Table/Range”. The “Power Query Editor will open with table 1 data.

(Or you can click the “from Table/Range” item under get & transform data, to shortcut the steps above)

Select the relevant column(s) – in this case select Animal (click on the column header). Then, under the “Transform” Tab at the top, choose “Unpivot Columns >> unpivot other columns”. The data will immediately be reorganised to the format you wanted (hopefully).

Then, to get the data from the query editor to a spreadsheet, close the power query editor – click the close dialog cross at the top right, and choose KEEP in the “Do you want to keep your changes” dialog that appears.

The reorganised data will be placed into a new worksheet. Lets call this reorganised data, table 2.

Or, you can use the Close and Load menu to choose “Close and Load to …” and specify the location of the output table if you don’t want it in a new worksheet. You can save it in a specified place, and even a pivot table.

As a special bonus, you have now saved the data processing steps for this data transform. Lets say you have to repeat this with several sets of data – if you paste the new data into table 1 then right-click on table 2 and choose refresh from the context menu, the new data will be reorganised.

Do you have more complex data – say you also have a treatment grouping like this. Go through the same process, this time selecting the treatment and animal columns in the power query editor, before chosing unpivot other columns.

The Power Query Editor is a fantastically powerful tool. It can do much much more than this very simple example, and can remember processing steps so you can repeat the processing on new data. Search out some tutorials online and have a play with data.

For example you can sort; group; find and remove duplicates; split columns based on delimiters; add, delete or reorganise columns;  grab data from other spreadsheets or other sorts of data sources; combine data from a number of tables in different places in your worksheet/workbook; replace specific values (eg change treatment name “treatment1” to “E2” in columns of your table(s) wherever they occur); filter rows based on criteria (eg remove missing data or values outside a certain range); remove columns; and more …. lots of power to modify your data.