Unpivot and Unpivot Compact

Default Keys: CTRL + Shift + Alt + Y

Intro:

If you are trying to use someone else’s report as the basis for one of your financial models, it is often easiest if you can change their report back to raw data first — which is exactly what the Unpivot Nutility was designed to help with!

This Nutility will take data that is reported horizontally in columns and changes it back to a raw data format (It changes CrossTab to Tabular). The picture below is visually what this looks like. The Input has the years and months going across, and the Unpivot Output has the same info turned back into a raw data format. Another way of describing these, is that the Input is a report format that might be created by a pivot table, and the Output is a raw data format that could be used to create that pivot table.

If you are aware of Excel’s Power Query ‘Get & Transform’ functionality, you might be wondering why you would want to use this Nutility vs that. There are a few reasons. First, Get & Transform only comes built-in with Excel versions 2016+, whereas the Unpivot Nutility works on Excel versions 2007+. Second, Unpivot can be used in more situations: it can unpivot data that has multiple header rows, and it can unpivot data that was hard-coded using Excel’s compact pivot format (where all attributes are crammed into one column). Finally, Unpivot is faster and simpler to use, at least in my admittedly somewhat biased opinion.

This Nutility requires you to have the Nutilities Excel add-in installed in order to use it. If you don’t know what Nutilities is, or where to get it, please start at my web-page here: http://iwishexcel.com . The add-in is completely free to use.

Examples and Instructions

Using the Unpivot Nutility is simple. When you run it you will be met with a series of 2 popups. The first popup asks that you highlight the entire range of data that you want to unpivot, including all header rows. The second popup will ask you to select the cell where your data begins (the cell immediately below your headers and to the right of any attribute columns). After those 2 popups, the Nutility will output your raw data to a new sheet in your workbook called ‘Unpivot’. That’s it. The only thing left for you to do is to manually add header columns on top of the raw data.

In the below pictured example, the Input is the table I fed the Nutility. For the first popup I selected all the cells with the light-green interior, which is all my data including 2 header rows (you may have more than 2). For the second popup, I selected the cell with the red X, which is the first cell of my data. The Output is what the Unpivot Nutility output to a new sheet for me.

The next picture below is a quick example of unpivoting a compact pivot table. Running the Unpivot Nutility for a compact pivot table is exactly the same as before. The only difference is that if the Nutility detects that you only have a single attribute column, based on your inputs to the 2 popup boxes, it will guess that you are unpivoting a compact table and display a popup asking you to confirm this. Once confirmed, you will be met with an Output just like before. In this example I used the same data-set as above, but I reformatted the input into a very smushed compact table. Your compact table may not looking exactly like this. Yours might have more columns and header rows, which is fine. If I had left years and months going horizontally, I would have had more columns and 2 header rows, however the unpivot process would still be the same.

Leave a Reply

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