ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #425 - Revisiting unpivoting

Author: David Lyford-Tilley

Published: 21 Dec 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Hello all and welcome back to the Excel Tip of the Week! A very merry Christmas from all of us here at ICAEW to you and yours. This week we have a Creator post in which we’re going to look at how at some uses of the very handy “unpivot” data operation in Power Query. This is building on our first look at the tool in TOTW #318.

Finding unique values

Let’s say we have a single column of data with duplicates in it:

Excel Community
We can remove the duplicates and be left with just the unique values by using Data => Remove duplicates, or in Excel 365 by using the UNIQUE function. But what about if we have multiple columns of data – let’s say attendance at a course by day?
Excel community

Excel’s inbuilt duplicate-removing functions both work on the presumption that you care about unique rows. But what if instead you want a comprehensive list of all the students? To do it with one of the above approaches you would have to first copy and paste until all the data was in one column. But it is much simpler by unpivoting in Power Query.

First, we use Data => From Table/Range to import the data into PQ:

Excel community
Now we select all 4 columns and use Transform => Unpivot Columns:
Excel community
Finally, we remove the day of the week column and use right click => Remove duplicates. We can then export our resulting list:
Excel community

Unpivoting or repivoting a task list

Unpivoting can also be used more literally to take 2D data and condense it into a simpler columnar format. Let’s take this task rota:

Excel screenshot

The data here is already 'pivoted' – with the 'day of the week' data displayed horizontally and the tasks data vertical, and then the assigned people in the 2D intersectional area between them. This format makes lookups and other kinds of further manipulation a bit trickier, so we might want to use unpivot to get it into a more straightforward columnar layout.

The simplest approach here is to select the Job column and use Unpivot other columns:

Excel screenshot

That plus a few quick column renamings will get our output sorted.

But there’s another option here that’s really handy – we can repivot this data in a different way! So for example we could pivot the job column and choose 'Don’t aggregate' from the list of operations, and we get this:

Excel Screenshot

And just like that, we have a version with the people listed in columns and the tasks across the rows! And you could use this approach to get any layout you wanted from any other layout.

Check out all these examples in this example file.

Join the Excel Community

Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

}
}