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,)+1**n****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.