 # How to transpose a values in a row to column using formulas… [Quick tip]

Share

This is interesting, I am in Columbus to meet one of my college friends. I remember him as a very meticulous person from college days. So it is no surprise when he showed me his massively impressive finance tracker last night. He has been tracking expenses, income, credit card payments and gas (petrol) consumption since 2008. Very impressive indeed.

Then out of blue he said, he has a problem with his spreadsheet. In this own words,

When entering data for credit cards, I use one column per card. But in my report view, I want to show credit card details in rows. How do I do this?

Something like this: ### Transposing values in a row to column using formulas

If it is a one time process, my friend can use Paste Special > Transpose feature and be done. But this is no one time business. So lets understand which formula helps us do this.

1. Lets assume original data is in \$F\$4:\$J\$5. Row 4 has card names & Row 5 has amounts.
2. Wherever you want the out put, just list running numbers (1,2,3….) in a column. Lets say these are in cells D10:D14.
3. To get the first card name, you can use the formula =INDEX(\$F\$4:\$J\$4, \$D10)
4. To get the first amount due, use the formula =INDEX(\$F\$5:\$J\$5, \$D10)
5. Now drag both these formulas down and you are done!

This is good, but I don’t like the extra column…

If that is the case, you can use the ROWS() formula to generate these running numbers for you on the fly. For example,

=INDEX(\$F\$4:\$J\$4, ROWS(\$A\$1:A1)) would work perfectly.

### Play with this formula

See the embedded Excel workbook below. Play with the formula.

### How do you transpose values?

I love using INDEX formula. I use it for transposing values, tables, getting a cell value (or reference) from a large table, use it along with MATCH etc. It is a very versatile formula and I keep learning new uses for it.

What about you? Do you transpose values often? What formulas do you use? Please share using comments.

### More on transposing your data:

If you like to transpose, wrestle or arm twist your data often, then you are at right place. Chandoo.org has tons of tutorials, material and tricks on this. Start with these:

Also, check out more quick tips.

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

Excel School made me great at work.
5/5

– Brenda

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.

## Related Tips

### 23 Responses to “How to transpose a values in a row to column using formulas… [Quick tip]”

1. Ramanan says:

Can we use it in this way?
=INDEX(\$B\$1:\$F\$2,COLUMNS(\$B\$4:B4),ROWS(\$B\$4:B4))
Copied across B4:C8

• Ramanan says:

Data from A1:F2

2. sam says:

Why not just use TRANSPOSE(F4:J4) array entered over say F11:F14

3. Duncan says:

If the ranges are going to remain constant in size, i agree with Sam: use an array TRANSPOSE formula as it does the job abd array formulas come with all sorts of protection!

Duncan

4. AlexJ says:

Try a pivot table

5. juanito says:

sam beat me to it.

That said, it's still a great example of how to use INDEX() - a function I should never have lived without for so long!

- juanito

6. Jordan Goldmeier says:

I've opened my mouth and been wrong before. So, at the risk of that happening again here, I believe the the formula above is missing a comma at the end. It should be:

=INDEX(\$F\$4:\$J\$4, ROWS(\$A\$1:A1), )

• Jordan Goldmeier says:

Well, no that's wrong what I said. Don't listen to me.

7. Jomili says:

I get lost on the formulas, so I use a macro:

'---------------------------------------------------------------------------------------
' Author : SNB remake of a Jomili Macro
' UpDated : 01/29/2013
' Purpose : To allow copy/Transpose paste links
'---------------------------------------------------------------------------------------
'Get the range to copy (need both the Worksheet and the area to copy)
With Application.InputBox("Select the Range you want to copy", "Range Selection", , , , , , 8)
c00 = .Parent.Name
End With
'Get the range to copy to (need both the Worksheet and the area in which to paste)
With Application.InputBox("Select the Range into which you want to copy", "Range Selection", , , , , , 8)
c02 = .Parent.Name
End With
'Exit if no beginning or ending range is chosen
If c00 = "" Or c01 = "" Then Exit Sub
'Redimension the c01 range to 1 less
ReDim sn(Range(c01).Cells.Count - 1)
'counting from 0 to the upper limit of c01-1, put in a formula
For j = 0 To UBound(sn)
sn(j) = "='" & c00 & "'!" & Range(c01).Cells(j + 1).Address
Next
'Paste it into the target sheet, transposed, formatted as general
With Sheets(c02).Range(c03).Resize(UBound(sn) + 1)
.NumberFormat = "general"
.Value = Application.Transpose(sn)
End With
End Sub

8. Kannan says:

Dear Sir,
Sample file link shows error message

http://chandoo.org/wp/2013/05/14/how-to-transpose-a-values-in-a-row-to-column-using-formulas-quick-tip/%3Cbr

9. zurman says:

DIFFICULT. HOW WITH PIVOT TALBLE?

10. Ahmet K. says:

This topic was my first post on Chandoo.

http://chandoo.org/forums/topic/how-to-move-horizontal-data-vertically-to-another-page-via-formula

Thanks to NARAYANK991 I got my answer in no time. 🙂

The file doesn't exist under speedy any longer but if you need it I can add it again.

Kind regards.

Ahmet.

11. Gpay says:

I also use this often, so i simply record the Paste Special > Transpose feature, store it on my personal macro workbook & use it as a keyboard shortcut 🙂

Sub PasteValueTranspose()
'
' PasteValueTranspose Macro
'
' Keyboard Shortcut: Ctrl+e
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub

12. Dave says:

I typically just use the transpose array formula

13. Panchi says:

How would I go about doing the same but the other way around?

Meaning: If I have the data in 2 columns, and would like to present them in 2 rows:

Example:
Data Column B4 to B10
Data Column C4 to C10

and I want it represented in another sheet as Row B2 to H2
and Row B3 to H3 respectively.

• Chandoo says:

To transpose a table, see this: http://chandoo.org/wp/2013/02/01/transpose-table-excel-formula/

• Panchi says:

Works very nice!

Thank you very much!

14. Vijayendra says:

Dear Chandoo,
Thanks for the tricks.
I wanted to know is it possible to put any formula to get a answer in second column as well. In this case we need to put another formula or need to copy and change the cell reference for data. I looking the answer simply dragging or copying the first forula.
Thanks,
Vijayendra

15. Justin says:

I copy the row/column, then 'paste link' to where I want the transposed data. Then I use a macro to convert each cell to "absolute" values, then I copy/"paste transpose" those values. Then delete the original "non-transposed" values.

Here is the absolute-relative macro I use:

Sub Change_Cells_To_Absolute_Relative()
Dim RdoRange As Range, rCell As Range

Dim i As Integer

Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Relative row/Absolute column = 1" & Chr(13) _
& "Absolute row/Relative column = 2" & Chr(13) _
& "Absolute all = 3" & Chr(13) _
& "Relative all = 4")

If Reply = "" Then Exit Sub

On Error Resume Next

Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)

Case 1

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _
Application.ConvertFormula _
(Formula:=rCell.FormulaArray, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _
Application.ConvertFormula _
(Formula:=rCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)

End If

End If

Next rCell

Case 2

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _
Application.ConvertFormula _
(Formula:=rCell.FormulaArray, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _
Application.ConvertFormula _
(Formula:=rCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)

End If

End If

Next rCell

Case 3

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _
Application.ConvertFormula _
(Formula:=rCell.FormulaArray, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _
Application.ConvertFormula _
(Formula:=rCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)

End If

End If

Next rCell

Case 4

For Each rCell In RdoRange

If rCell.HasArray Then

If Len(rCell.FormulaArray) < 255 Then

rCell.FormulaArray = _
Application.ConvertFormula _
(Formula:=rCell.FormulaArray, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

End If

Else

If Len(rCell.Formula) < 255 Then

rCell.Formula = _
Application.ConvertFormula _
(Formula:=rCell.Formula, _
FromReferenceStyle:=xlA1, _
ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)

End If

End If

Next rCell

Case Else

MsgBox "Invalid Number Entered!", vbCritical

End Select

Set RdoRange = Nothing

End Sub

16. [...] How to transpose a values in a row to column using formulas [...]

17. Jordan Brookes says:

A very useful tool enjoy the macro in the comments above

18. Lukas (PL) says:

Hi guys,

some quite simple function, which solves this problem (Ramanan's comment -top - uses similar idea with Index() )

Assuming example given by Chandoo, data table begins in E4.
We want to start transposed table in for example B20. So:

=OFFSET(\$E\$4;COLUMNS(\$B\$20:B20)-1;ROW()- ROW(\$B\$20:B20)-1)

or (to be more clear)

=OFFSET(\$E\$4;COLUMN()-COLUMN(\$B\$20);ROW()- ROW(\$B\$20))

(Note: In my version of Excel, I use " ; " as separator, you need to change to "," in your version probably).

Then copy this formula till you get zeros. It's good to first copy only for labels in both dimensions, so you know ,where is the end of table.

If you see any error in this idea, please comment.

Cheers
Lukas

• Lukas (PL) says:

Hi guys,

Sorry , 1st formula should be:
=OFFSET(\$E\$4;COLUMNS(\$B\$20:B20)-1;ROWS(\$B\$20:B20)-1)

Cheers
Lukas

### Get FREE Excel & Power-BI Newsletter

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