Reshaping your data easily – Case study [Pivot tables FTW]

Late. Jayaram, my uncle is also a teacher. When I was a kid, I used to spend a lot of time with him, learning all sorts of things. He taught me chess, maths and so many life lessons. I remember one such lesson very vividly.  One day, he asked me to do something. I did it in a very long way. After seeing me struggle for several minutes, he chipped in and showed me how to do it easily. He then said, “when someone asks you where your nose is, you don’t twist arm around your head. You just point to your nose directly.”

The idea is that when you have a direct, simple way to do something, you should use it.

Nose and pivot tables… how are they connected?

We are coming to the point. Recently, learneagerly, one of our forum users asked a question about how to transform (reshape) a set of data in Excel.

Marc L, one of our Excel ninjas, posted an awesome VBA script to do that.

Later in the day, I chipped in and shared a formula approach to transform the data.

I suggest checking out both approaches for learning more about VBA & INDEX formula respectively.

After posting my answer, I got thinking… May be there is a more direct way to reshape the data.

Why, yes, there is. You can use Pivot Tables.

Let’s take a look at the data & problem first

Here is a snapshot of raw data and expected output.

As you can see, we have two columns of data and we need to extract n (here it is 6) items from first column, then n from second column and lay them out in output. We repeat this until we run out of the data.

Reshape this thing with a Pivot

The first step is to add two extra columns to your raw data. Let’s call them Running & Repeat.

• Running: with numbers 1 thru 6 and repeat the pattern (just auto fill or copy paste)
• Repeat: with 6 cells of 1, 6 cells of 2… and repeat this pattern (auto-fill)

But what if I want a different n

Even better. use formulas. Let’s say your data starts from H6:I6

• Running:  =MOD(ROWS($I$6:I6)-1,n)+1
• Repeat:  =IF(J6=1,SUM(K5,1),K5)

Related: Using ROWS() to generate running numbers in Excel.

Now that we have these extra columns, select all the data (2 columns of data + 2 extra columns we just added) and insert a pivot table.

Set up the report by,

• Adding Repeat & Running to row labels area (in that order)
• Add Col A & Col B to values area.
• Move the ? values to row labels area (by dragging it)
• Position ? values between Repeat & Running row labels.
• Your pivot report’s last column will have the transformed data.
• Viola, nose pointed!

Download Example Workbook

Here is the example workbook. Examine the pivot table & formulas in Running & Repeat columns to learn more.

Get your Excel muscles in to shape

Are you struggling to find your nose or worse still, twisting your arm on the way? If so, check out our Excel school program. We have awesome online lessons, beautiful explanations, powerful techniques and easy to understand downloads. It won’t be long before you are smelling roses.

Check out our Excel School online class & join today.

How do you reshape your data?

Pivot Tables and Power Query are my go to tools for almost all kinds of reshaping problems. Often, I indulge in INDEX formulas or a bit of VBA. For example, just a few days ago, I had to split first 100,000 digits of Pi ? in chunks of 3 digits, 3 digits and 14 digits in a pattern. As the data is too long, loading it Excel cell was impractical. Loading it in to multiple lines with each having x digits was impractical (as I may need to split them in another pattern). So I used a simple VBA script to zap the data and get what I need.

In case you are curious: I made a chart to celebrate the Pi day (14th of March) with our community on Twitter.

But when I am not splitting irrational stuff, I usually rely on Pivot tables or PQ.

What about you? How do you reshape your data? Please share your approaches and tips in the comments section.