Rearrange a multi heading dataset into a single heading one which is Pivot ready

{ 8 Comments }

Any well arranged dataset should be “Pivot Table” ready with the following 3 important properties:

  1. There should be no merged and centered cells; and
  2. Every column should have a unique heading; and
  3. Every column should have only 1 heading

Here’s one dataset which violates all rules mentioned above.

  1. Headings in row 1 are merged; and
  2. The headings in row 2 are not unique
  3. Every column has headings in row 1 and row 2.

To be Pivot Table friendly, this dataset will have to be restructured into a 4 column one – Week, Equipment, Jobsite and Widgets as shown below:

I have achieved the desired result by using Data > Get & Transform (also known as Power Query in earlier versions of MS Excel).  The solution is dynamic for new rows and columns added to the data on the Input worksheet – one simple has to go to Data > Refresh All.  You may download my solution workbook from here.

In this workbook, there is another example of how one can transform a multi heading dataset into a Pivot Table ready dataset.  The primary difference between this and the previous dataset is that there are 2 descriptive columns to the left (as against only one in the previous example).

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi, I’m trying to download the 2nd workbook but it requires a sharepoint log in and I’m not in the directory – can you help?