Transpose a table of data using Excel Formulas

Share

Facebook
Twitter
LinkedIn

Today lets tackle a familiar data clean-up problem using Excel – Transposing data.

That is, we want to take all rows in our data & make them columns. Something like this:

Transpose a table of data using Excel Formulas - How to & Tutorial

The easy solution – use Paste Special > Transpose

Long time Chandoo.org readers already know this. Excel has a built-in feature that lets you transpose data with a single click.

  1. Just select your original data
  2. Press CTRL+C to copy
  3. Go to an empty area and open Paste Special (CTRL+ALT+V)
  4. Select Transpose.
  5. Done!

Using Paste Special to transpose a table of data - demo

 

Although this approach works, it creates a copy of your original data. So whenever original numbers change, you must waste precious key strokes & time re-doing the transpose. This is exactly the opposite of awesome.

So, lets move to formulas.

Formula Solution #1 – Using INDEX & Helper cells to transpose a table

Lets say, we have named our original data as myData

Lets also say myData has 6 rows & 7 columns. That means, the transposed table will have 7 rows & 6 columns.

  1. Create a 7×6 grid in your worksheet
  2. About this, write numbers 1 to 6 (cells D20:I20).
  3. Similarly, write numbers 1 to 7 beside it (cells B23:B29).
  4. Now use INDEX formula to transpose data like this:
  5. =INDEX(myData, D$20, $B23)
  6. Copy this formula all over and you are done!

See the illustration below to understand how this works.

Transpose data using INDEX formula & Helper cells

Formula Solution #2 – Using INDEX formula & no helper cells

Sometimes, we cannot really use a helper column. That brings us to our next solution.

In above solution, the helper cells are giving us running numbers from 1 to 6 (and 1 to 7). We can use ROWS() and COLUMNS() formulas to generate these running numbers.

So our new formula will be

=INDEX(myData, COLUMNS($D20:D$20),ROWS($B$23:$B23))

Once you write and copy paste this formula, it will automatically supply the required numbers to INDEX formula and does the magic.

How does it work?

Well, that is for you to figure out. See this illustration to get started.

Transpose a table using INDEX, ROWS, COLUMNS Formulas

Formula Solution #3 – Using TRANSPOSE formula

Do you know there is a formula that does all of this. It is called – TRANSPOSE !!!

What is TRANSPOSE formula?

TRANSPOSE formula takes a range of values (or an array) and transposes them and returns another array.

Since this formula always returns an array, we cannot use it in one cell. But we can select a range of cells & then write TRANSPOSE in them and press CTRL+SHIFT+Enter to get the values transposed.

See this demo:

Using TRANSPOSE Excel formula to transpose data

Awesome, isn’t it?

Download Transpose Example Workbook & Play with it

Click here to download the workbook containing all these technique. Play with it to understand these formulas better.

How do you transpose your data?

I prefer using INDEX with ROWS & COLUMNS approach. This is very versatile & elegant. Also this approach lets me extract only a small window of large data set (by offsetting row & column numbers with something like scroll-bar position).

What about you? Which formulas do you use to transpose your data? Please share your tips & ideas using comments.

More formulas for data massaging

If you wrestle often with data & rely on coffee to get going, then you can use some help. Go thru below articles to learn more.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

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
Excel formula list - 100+ examples and howto guide for you

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.

Advanced Pivot Table tricks

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.

8 Responses to “Create a Combination Chart, Add Secondary Axis in Excel [15 Second Tutorial]”

  1. [...] Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one) [...]

  2. [...] set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]

  3. [...] To show the years, I have used another dummy series and plotted it on secondary axis (related: how to add secondary axis?) [...]

  4. Thanks for this one!

  5. [...] Choisissez la colonne « Daily Completed » et ajoutez-la au graphique. Une fois ajoutée, changez le type de graphique pour cette série à histogramme (lisez comment combiner 2 types de graphiques en un : combine 2 different chart types in one) [...]

  6. Nat says:

    How do i create a chart that has negative numbers on axis x and y and plot them correctly? I cannot seem to understand how to do this, please help.
    Thanks.

    Nat

  7. MSWebReviewer says:

    You can also plot 2 or more Y axes in Excel using EZplot or Multy_Y from Office Expander.com
    There is a demo version to try.
    Cheers.

Leave a Reply