Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Transpose Table
Deze pagina in het Nederlands

Transposing the Formulas in a Table of Cells

Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.

Imagine the following table:

Now I'd like to transform that table into this one (see highlighted range):

If you try a copy > paste special > formulas > transpose, this is what you get:

But it is not what we want, is it.

With the following piece of VBA, you can easily transpose your table, without garbling your formulas:

Option Explicit

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas
End Sub

So how does this work?

First we check whether a range of cells has been selected, otherwise the code will produce a runtime error:

    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If

Next, we store the selected cells in an object variable (which is not really necessary), so we can work with that set of cells:

    Set oSel = Selection

Now the formulas of these cells are pulled into a variable of type Variant in one go:

    vFormulas = oSel.Formula

If you step through the code (using my example) the Locals window of the Visual Basic Editor (VBE) shows this for the variable vFormulas after executing the statement above:

As you can see, vFormulas is a 4*3 array.

Next we want to swap rows and columns of the vFormulas variable, for which I use the Transpose worksheet function:

    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)

The structure of vFormulas changes to this:

Presto! We now have a 4 * 3 array.

Finally, we need to push back the formulas to the worksheet. To be able to do that, we must specify a target range of the exact dimensions as the array variable. For that I use the Resize method. Note I also move down a couple of cells using Offset, so the copy of the table is placed 2 rows below the original:

    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas



All comments about this page:

Comment by: Tom (19-9-2006 11:42:12)

excellent example of how to code
as wells as the function preformed.


Comment by: Jan Karel Pieterse (19-9-2006 21:14:34)

Hi Tom,


Jan Karel Pieterse


Comment by: BK. Wibowo (19-9-2006 21:36:04)

It also can be done by:
Select Range,
Find: =
Replace with: blablabla=
Select Range again,
Find: blablabla=
Replace with: =
The formula will remain the same


Comment by: Jan Karel Pieterse (20-9-2006 06:25:19)

Hi BK,

Thanks for your addition.

I did know that one. I deliberately show the VBA method here, because it also shows how to push a range of cells into a VBA variant variable and vice-versa.


Comment by: Raja (21-9-2006 10:33:36)

Excelent example Jan. I think it will help a lot when you create reports (what I always do) and your boss needs reports in different style (for some reason).


Comment by: Chris (28-9-2006 05:45:32)

Very nice. My only critique is that you use an Exit Sub (a Goto by any other name is still a Goto).

Helpful indeed. I will have to keep this one filed away for future use.


Comment by: Jan Karel Pieterse (28-9-2006 06:26:41)

Hi Chris,

Good point. I do use this on occasion, because I prefer it to an If-Then-Else construct that spans almost the entire subroutine. I find those to more confusing than a sparsely used Exit sub when a certain "vital" test fails.

But that is a matter of personal preference I guess.


Comment by: Ufuk (18-1-2007 01:04:41)

That's perfect, this macro help me a lot to complate a work, thank you very much!


Comment by: Derek Turner (27-1-2007 07:55:12)

Sub TransposeSelection()
    TransposeFormulae Selection
End Sub
Sub TransposeFormulae(oSelection As Object)
    If TypeName(oSelection) = "Range" Then
        With oSelection
            .Offset(.Rows.Count + 2).Resize(.Columns.Count, .Rows.Count).Formula = Application.Transpose(.Formula)
        End With
        MsgBox "Please select a range of cells first.", vbOKOnly + vbInformation, "Transpose formulae"
    End If
End Sub

However, this fails in Excel 2002 with a Type Mismatch if any cell contains a string longer than 255 characters. It will also fail with multiple selections but this can be trapped with .Areas.Count


Comment by: Cale Maddy (7-4-2007 10:22:29)

Thank you so much for the select range, Ctrl-H method. I don't have a clue about visual basic and you just saved me an enormous amount of time.


Comment by: lennox de ronde (31-5-2007 17:11:48)

it can be don much more simple

when you make the formula use $+cell reference and then perform the transpose depending on the tranposeyou use cell$ of $cell
lennox, thanks anyway


Comment by: Jan Karel Pieterse (31-5-2007 21:25:21)

Hi Lennox,

You're correct if you did set up those absolute references properly. But how often have people NOT done that...

And my example always works, even with mixed references.


Comment by: John Salkeld (19-7-2007 03:45:15)

Very interesting - It would be of interest to include array formulae in this application


Comment by: rodstar (9-1-2008 06:30:32)

great solution!
any tutorial teaching how to program this code into Excel? I know a little about coding, but never did anything using VBE.

Other option is offer a xls to download and use the macro.




Comment by: Jan Karel Pieterse (9-1-2008 10:41:14)

Hi rodstar,

Just open the VBE (alt+F11), insert a module (Insert, Module) and paste in the code shown above.

Then return to Excel. Select the range you want processed, hit alt+F8 to get to the macro list and run the macro called TransposeFormulas


Comment by: Danièle Questiaux (18-4-2008 00:35:54)

As you say very well, your example first is useful(!), but is also one of those examples that clarifies the steps used in VBA, and that is priceless!
All the added comments are also a great idea to have for novices at VBA (I/m one of them).


Comment by: colin (21-4-2008 07:45:15)

Thanks Lennox, the original answer was a little intimidating for the layman


Comment by: Robert (8-6-2008 05:20:56)

This works great. However it does not work with array formulas (the type you have
to press <CRTL><SHIFT><ENTER> to create.

Any idea how to make it work for arrays. I have tables as big as 64*64 and once I
transposed them I have to click on each cell after the subroutine to press
<CRTL><SHIFT><ENTER> and make then array again (the sub routine transfers them back
to regular formulas)

By the way your explanation on top is great and very easy to follow.


Comment by: Jan Karel Pieterse (8-6-2008 21:49:27)

Hi Robert,

Indeed, it fails with array formulas. For single cell array formulas, this
workaround works:

Sub TransposeFormulas()
    Dim vFormulas As Variant
    Dim oSel As Range
    Dim oCell As Range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range of cells first.", _
                vbOKOnly + vbInformation, "Transpose formulas"
        Exit Sub
    End If
    Set oSel = Selection
    vFormulas = oSel.Formula
    vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
    oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count,
oSel.Rows.Count).Formula = vFormulas
    For Each oCell In oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count,
        oCell.FormulaArray = oCell.Formula
End Sub


Comment by: peter nthiga (1-7-2008 05:58:41)

pliz help on how to transpose access table rows to column in vb 6


Comment by: Jan Karel Pieterse (1-7-2008 10:22:35)

For help with Access I suggest you try the microsoft Access newsgroups.


Comment by: Giovanni (29-12-2008 13:11:50)

Excellent does what it says on the tin. Did exactly what I wanted, Thanks, saved me a lot of time!


Comment by: Dennis Sawyer (12-2-2009 04:01:53)

You are a flippin life saviour. I have 7 sheets i need to do this on and this code has just saved me about 7 hours work. Thank you thank you works a treat


Comment by: Danièle (15-4-2009 01:41:24)

Real time saviour on all counts!
I copied the code in a module no problem, but getting back to it, I realised that you have not commented at the beginning when it was written, your name as author etc.
Module codes are in general commented with the author's name, and purpose (the name here says it all), and it is great to get that as authors tend to have their own style of coding, and as a learning tool, that information helps recognise the differences between codes. It is all a selfish request really, but it is also nice to pass on code snippets with their authors names!
I remembered where i got this one from no problem, so added youe name in a comment form .
Thanks for really great code (this one and many others, but alo for the precise explanation of how it all works.
Many Thanks!


Comment by: Jan Karel Pieterse (15-4-2009 02:43:24)

Hi Dani├Ęle,

Thanks for your comments, and you're right, I should start adding my name to the routines.


Comment by: vino (13-3-2010 02:15:39)


how to hide the formulas in excel


Comment by: Jan Karel Pieterse (13-3-2010 10:53:49)

Hi Vino,

To hide your formulas, you need to:
1. Select the cells
2. Hit Control+1 to get to the cell properties dialog
3. Click the Protection tab
4. Check "Hidden"
5. Protect the worksheet

Remember to unlock cells the user needs to have access to.


Comment by: Dang (4-5-2010 20:32:28)

I have excel with the following format

A A1 A2 A3 A4
B B1 B2 B3 B4

and would like to change into the following format

A A1
A A2
A A3
A A4
B B1
B B2
B B3
B B4

How do I do this in excle please advice...

Thank you,


Comment by: Jan Karel Pieterse (5-5-2010 07:38:25)

Hi Dang,

I recommend you to ask questions like this one at


Comment by: Nath (18-5-2010 19:22:55)

How to make a cell resize to a picture or object such as word when pasted on to a cell, so that you dont need to hover and resize the cell manually; i am not a VBA user; is ther any trick not using VBA , please


Comment by: Jan Karel Pieterse (18-5-2010 20:53:10)

Hi Nath,

I wouldn't know how, except by use of VBA.
Sorry to be of little help there.


Comment by: Nath (18-5-2010 22:36:19)

Can i venture into VBA for the same task of resizing an object in to a specifc cel size without handling the edges; infact i was very much helped by your topic in 'Show picture'. I saw a couple of them on you tube videos. But yours was the best, so easy to undestand even without a video clip; thanks mate; help me if you can to resize an object to a cell the moment i paste it using vba! many thanks


Comment by: Jan Karel Pieterse (18-5-2010 23:56:51)

Hi Nath,

If you select the picture object, then this macro will size it according to cell dimensions:

Sub SizePic()
    With Selection
        .Width = .TopLeftCell.Width
        .Height = .TopLeftCell.Height
    End With
End Sub

Note that this may change the aspect ratio of the picture.


Comment by: Nath (19-5-2010 04:56:59)

Thanks Jan, without a clue, i copy pasted and tried using on a picture and what magic, it just solved it! thanks a lot....


Comment by: John R (28-10-2010 10:34:06)

Very well done! Thank you. This helped a lot


Comment by: cOOL_t (12-11-2010 02:17:05)

Hi everybody!
This is almost exactly what I needed. So, I have a question for you:

How is it possible to alter the sub in order to transpose the data from Sheet1 to Sheet2?

P.S. My knowledge of VBA is almost non-existent, so please keep it simple.

TX in advance!


Comment by: Jan Karel Pieterse (13-11-2010 11:56:56)

Hi cOOL_t,

You must change this line :

oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas


Worksheets("Sheet2").Range("A1").Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas


Comment by: cOOL_t (13-11-2010 12:42:51)

to Jan Karel Pieterse

TX, it's working like a charm. Much oblidged!


Comment by: Sahitya (23-5-2011 05:12:44)


Hi to all
Am new to arcobjects can anyone help me about export attribute table to excel with transpose option..

Plz help me

Advance Thanks..



Comment by: Tina (6-12-2012 01:14:48)

Amazing!! This did EXACTLY what I needed it to, so glad I found this post! Thank you *so* much =)


Comment by: dan (9-2-2013 16:36:39)

this is the best excel macro i have ever seen!!!

thank you for saving hours of work


Comment by: michael fozouni (6-3-2013 04:31:58)

First, let me extend my sincere thanks and gratitude for all your work in providing great tips on excel.

Second, I ran your "Transpose" macro without selecting a range first. However, the routine did not prompt to ask me to select a range. Am I doing something wrong here maybe?

Many thanks in advance.



Comment by: Jan Karel Pieterse (6-3-2013 11:35:25)

Hi Michael,

The macro takes whatever the current selection is and pastes the transposed formulas of that selection below the selection. There are no prompts.


Comment by: Michael (6-3-2013 13:01:04)

Dear Jan,
Thanks much for your prompt response. I guess whereI fail to understand is the message box where it asks you to select a range?

In any event, many many thanks for answering my question and once again, congratulations on your invaluable site!



Comment by: Jan Karel Pieterse (6-3-2013 19:34:25)

The message box should only pop up when you have something else selected, like a chart.


Comment by: Michael (6-3-2013 20:12:31)

Got it, thanks.


Comment by: luciana (26-9-2013 20:19:11)

Perfect VBA!! Helped a lot!! Only one question... Is there a way to keep the formating from the orginal table? because when I use the simple ┬┤transpose┬┤ from excel it keeps my formating, like writing in different color, or a cell painted in another color, etc, but it messes up the formulas. but if I use this Macro it keeps the formulas perfectly, but I would have to format all over again.... is there a way to insert the formating in the VBA? I think there isn┬┤t, just thought I should confirm first.
Thank you very much for the Macro though... helps a lot!


Comment by: Jan Karel Pieterse (27-9-2013 11:17:58)

Hi Luciana,

You could add the paste-special transpose to the macro as a first step, after which you let the rest of the macro do the formula trick:

Sub TransposeFormulas()
     Dim vFormulas As Variant
     Dim oSel As Range
     If TypeName(Selection) <> "Range" Then
         MsgBox "Please select a range of cells first.", _
                 vbOKOnly + vbInformation, "Transpose formulas"
         Exit Sub
     End If
     Set oSel = Selection
     vFormulas = oSel.Formula
     vFormulas = Application.WorksheetFunction.Transpose(vFormulas)
     oSel.Offset(oSel.Rows.Count + 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
     oSel.Offset(oSel.Rows.Count + 2).Resize(oSel.Columns.Count, oSel.Rows.Count).Formula = vFormulas
End Sub


Comment by: luciana (27-9-2013 20:35:01)

It worked perfectly! Thank you!


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.