Search

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

Share

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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

## Related Tips

Charts and Graphs

Learn Excel

Learn Excel

Learn Excel

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

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

=IF(ISODD(ROUNDUP(ROWS(\$D\$2:D2)/6,0)),INDEX(\$A\$2:\$A\$19,ROWS(\$D\$2:D2)-6*(ROUNDUP(ROWS(\$D\$2:D2)/6,0)-ROUNDUP(ROWS(\$D\$2:D2)/12,0))),INDEX(\$B\$2:\$B\$19,ROWS(\$D\$2:D2)-6*(ROUNDUP(ROWS(\$D\$2:D2)/6,0)-ROUNDUP(ROWS(\$D\$2:D2)/12,0))))

3. Richard says:

Any idea how you would do this if column A was in one worksheet and column B in another and you wanted the result in a different worksheet?

4. John says:

Power query is my go to data transformation tool if I want to build something robust and reusable.

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.