Transpose a table of data using Excel Formulas
Today lets tackle a familiar data cleanup problem using Excel – Transposing data.
That is, we want to take all rows in our data & make them columns. Something like this:
The easy solution – use Paste Special > Transpose
Long time Chandoo.org readers already know this. Excel has a builtin feature that lets you transpose data with a single click.
 Just select your original data
 Press CTRL+C to copy
 Go to an empty area and open Paste Special (CTRL+ALT+V)
 Select Transpose.
 Done!
Although this approach works, it creates a copy of your original data. So whenever original numbers change, you must waste precious key strokes & time redoing 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.
 Create a 7×6 grid in your worksheet
 About this, write numbers 1 to 6 (cells D20:I20).
 Similarly, write numbers 1 to 7 beside it (cells B23:B29).
 Now use INDEX formula to transpose data like this:
 =INDEX(myData, D$20, $B23)
 Copy this formula all over and you are done!
See the illustration below to understand how this works.
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.
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:
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 scrollbar 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.
 Reverse a list of values using INDEX formula
 Extract unique values from a list
 Fetch value at intersection of a row & column item [2 way lookup]
 Extract data using Advanced Filter & VBA
 
 

Leave a Reply
Details about our Power Pivot Course [and a video for those of you not interested]  Transpose a table quickly using Copy – Paste [Quick tips] 
27 Responses to “Transpose a table of data using Excel Formulas”
Awesome explantion, it works greate for me.
Hello Chandoo. Your site is very useful.
Please help me. I have a quiestion.
What software do you use to develop animated instruction?
Thank you Chandoo
And How to convert video file to gif file with small size?
I use Camtasia to record & convert the videos to GIFs. See here for information on what we use:
http://chandoo.org/wp/about/whatweuse/
Thank you Chandoo
Might I recommend an alternative that involves no array formulas, is easier to debug and less computationally intensive.
1) Highlight area to be transposed and copy
2) Paste special –>Paste Link, somewhere else on the sheet
3) Highlight new area and Find/Replace “=” with “xxx”
4) Copy new area, paste special –> transpose
5) Find/Replace “xxx” with “=”
Now you have a direct link to the cell with no fancy formula required
Joey,
Really nice suggestion and great for keeping processing overheads down on complex dashboards.
Thanks for posting.
LeonK
Thanks Joey for this beautiful tip. Donut for you…
Awesome!!!
Is there a way to do this for data that is in a table where even if new data is addedd to the table the whole table is still transposed?
I would also like to know the same
The above approach works for any size of data. But if the shape of original data table changes (for example from 6×7 rows to 9×12 rows), then you would just need to resize output table formulas accordingly (from 7×6 to 12×9). Then the formulas fetch correct transposed results.
If you know how big your table can grow to, then you can set up a large grid (something like 20×20) and use IFERROR formulas to suppress any errors.
Note: use ROWS() and COLUMNS() so that you can transpose any size table with ease.
Is there a way to only use the named formula?
=INDEX(myData, COLUMNS($D20:D$20),ROWS($B$23:$B23))
into:
=INDEX(myData, COLUMNS( myData),ROWS(myData))
maybe using the Small() formula?
Hello all,I am new and I would like to ask that what are the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course wiziq.com/course/125comprehensiveintroductiontosql of SQL tutorial online?? or tell me any other guidance…
would really appreciate help… and Also i would like to thank for all the information you are providing on sql.
Nice piece of work..
I am new in blogging line so just spending time researching and learning.
I like your work alot..
Hope to get ua feedback n help..thanku
Hi
I would like to transpose a column to a row, but it should check for the next available blank row before transposing. How would I do that?
Thank you
[...] On Friday, we learned how to transpose a table of data using Excel formulas. [...]
I would use an Index formula with two match’s. Something like:
=INDEX($D$7:$J$12;MATCH(M$6;$C$7:$C$12;);MATCH($L7;$D$6:$J$6;))
Paste this formula in cell M7. When using this formula, you have total control. You can remove column’s and put it in a different order.
With the first match you get the column of the country, the second match gets the column of the fruit.
I wrote a small subroutine that will do something similar. Let me know what you think.
Sub TransposeRangeLink()
Dim myRange As Range, myCell As Range
Set myRange = ActiveSheet.Range(“myRange”)
Set myCell = ActiveCell
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
myCell.Offset(j – 1, i – 1).Formula = “=” & myRange(i, j).Address(False, False)
Next j
Next i
End Sub
how to learn macro in easiest way
i want to more abt transpose and index.
can you explain me in brief…
hi
guys you make me look like a wizard
keep the articles coming
wandy kay
[...] Hello Please read up on it at Chandoo's website: How to transpose data in Excel – using formulas? – Introduction to TRANSPOSE Formula & other tec… [...]
I am just a basic user. How can use these same formulas from one sheet going to a new sheet?
Hi Sir
How to remove below blank row while doing transpose data in excel….
A B C D E F G
T13070514 Gokhale Satish Prabhakar M63 Gokhale Suneeta Satish F56 Gokhale Supriya Satish F33
T18220214 Blank
T18220214 Blank
T18220214 Gogate Satish Madhav M56 Dandekar Devashree Dilip F59 Dandekar Abhay M58 Dandekar Chetna Abhay F55
T18220214 Dandekar Dilip M65
T18220214 Blank
T18220214 Blank
Satam Prabhakar A M67 Satam Prerna Prabhakar F62
Karlekar Jayashri F67 Patki Charulata F69
Kindle help us….Waiting for your reply
Thanx for your cooperation..
THanx & REgards
SUdhir Mahadik
[…] Can you please refer the below links. MS Excel: TRANSPOSE Function (WS) How to transpose data in Excel – using formulas? – Introduction to TRANSPOSE Formula & other tec… […]
Hi there,
I can also recommend using Index and Match to transpose data.
This can be achieved by putting the row lookup value in the column heading, and the column lookup value in the row heading. The Index formula reads as follows: Index(data range, match(row range,column heading,0),match(column range, row heading, 0)).
You will need to use the row headers from the source range as column headers in the destination range, and vice versa for the column headings. Happy to provide an example if anyone needs it.