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

Posted on March 16th, 2017 in Learn Excel , Pivot Tables & Charts - 10 comments

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 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)

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!

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.

### 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 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.

 Figuring out Employee Churn with Power Query [HR Analytics] Sand Pendulums – Lissajous Patterns in Excel
 Written by Chandoo Tags: downloads, INDEX(), Learn Excel, Microsoft Excel Formulas, MOD(), pivot tables, reader questions Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 10 Responses to “Reshaping your data easily – Case study [Pivot tables FTW]”

1. Karimmo says:

It's pretty easy to make it in PowerQuery. Just 2 steps right from the interface (no formula writing required) . Upload your original table to Power Query.
1. Select Column 'Col A' and Click -> Add Column -> Standard -> Modulo + enter '6' - and you have your Running column
2. Select Column 'Col A' and Click -> Add Column -> Standard -> Divide Integer + - enter '6' - and you have your Repeat column.
And that's it.

• Karimmo says:

Small adjustment required for proper grouping:
just add '-1' after column name in both formulas created by interface:

• Karimmo says:

With 3 more clicks you can reshape completely so that Pivot Table is no longer needed:
1. Select [Col A] and [Col B] columns - and click Transform -> Unpivot
2. Sort Asc 3 columns in order: [Repeat], [Attribute] and [Running]
3. Remove all columns except of [Values] - and load to spreadsheet.

• Abhay says:

Nice approach.

In case Col A and Col B were text values then you will have to first add Index column and then perform all above steps considering Index column as Column A and delete Index column before performing unpivot.

Checkout Power Query training course where similar case study explains above method in detail -

https://www.udemy.com/power-query-training-for-excel-2010-2013-2016-powerbi

• Rick says:

Very Nice!

Thanks

2. Rossa says: