Transpose Excel columns to rows or rows to columns – 5 Methods Explained

48
112323

In Excel one can easily convert data columns to rows or multiple data rows to columns which is technically named transpose.

What is Transpose?
In Excel switching or rotating columns to rows or rows to columns is called Transposing the data.

And this actually shifts the dimensions of data and along with it definitely the address of different data bits will also change. Therefore, if you are into transposing your data, do it with caution as if you shift the data the dependent cells might miscalculate the figures.

Love reading about Excel? Subscribe my youtube channel dedicated to Excel.

Many already know how to do this. But for those who don’t this one minute Excel tutorial is for them. By the way, yes! you can use pivot tables to shift rows as columns. To learn how to make pivot tables read this readers’ favourite tutorial. And most probably you will figure out the way how rows are shifted as columns using pivot tables.

We have several ways to switch or rotate Excel data columns to rows or vice versa to accomplish this task. Following are the transpose methods we are discussing today in which few involve converting columns to rows using Excel formulas:

  • Use Paste Special
  • Use TRANSPOSE function
  • Use INDIRECT function
  • Use INDEX function
  • Use Paste Special + Common Sense

Method 1: Convert columns to rows using Paste Special

Copying and Pasting is one great thing happened to PC. With Paste Special it really went miles ahead. In Excel you can copy a data and then using paste special functionality you can twist, rotate, manipulate, process and do other things with the data very easily including Transpose i.e. flipping columns to rows or rows to columns. To do this following steps will help:

Step 1: Select the data that you want to transpose and copy it using the copy button or with Ctrl+C shortcut.

Step 2: Go to the cell where you want the data to paste. And go to Home tab > clip board group > click the drop down button just under paste button > select:

  • under paste sub-group click transpose button that comes with this icon; OR
  • Select paste special. This will open the paste special dialogue from which check the transpose checkbox and click OK button

excel transpose 1

Your data will be transposed instantly. Told ya! it is easy!

Method 2: TRANSPOSE Function to switch columns to rows or vice versa

Yes there is a function that can do the somersault called TRANSPOSE. The use of this function is a little tricky and requires knowing more than pressing TAB so follow the steps closely.

IMPORTANT: Determine the rows and columns of your data. For example you have 2 columns and 7 rows. Once you transpose the data it will be 2 rows and 7 columns.

Step 1: Determine the rows and columns of data. In our case it is 7 rows and 2 columns

Step 2: Select a vacant region in the worksheet which is 2 rows deep and 7 columns wide.

Step 3: Press F2 key to enter edit mode while the region still selected.

Step 4: Write TRANSPOSE function following the equal sign and enter the data range that you want to transpose

Step 5: Hit CTRL+SHIFT+ENTER. Yes! Not just Enter key but Ctrl+Shift+Enter as this is an array formula

excel transpose 2

TANGO! Your data is transposed!

Benefit of TRANSPOSE Function

The best thing with using TRANSPOSE function is that if you change the values in the source range then transposed range will also change accordingly. So basically it is a live-transposed-range of the source-range.

But as this is an array function this is very much dependent on the source and if you try to change anything this tends to break. Therefore, even if it is good for many reasons, it is hard go by in many situations. So follow along to learn some more easier to go tricks to get the data transposed in Excel.

Method 3: TRANSPOSE Using INDIRECT function

INDIRECT function was introduced in one of my favourite article some time back in which we learned how to build a reference to worksheet based on cell value. I must confess that I used to think INDIRECT has very little use but this little function can prove tremendous help.

Today we will look at another use of INDIRECT function i.e. to transpose the data. However, if you use this function the treatment for column to row shift is a little different from row to column shift. So I will explain both one by one.

Transpose – Column to Row using INDIRECT

Suppose our data is based on 2 columns A and B and 7 rows from 1 to 7.

The data of column A has address like A1, A2, A3,…. A7. In this A is constant whereas due to additional rows we have increasing numbers from 1 to 7. If you understand up to this, you will understand what we are about to learn.

Lets say you want to put the transposed data starting at cell C10. I will put this formula in cell C10 and press Enter key:

=INDIRECT(“A”&COLUMN()-2)

And this formula in C11 and press Enter:

=INDIRECT(“B”&COLUMN()-2)

Now select these two cells and drag the fill handle across to column I. So there you have the data transposed. But lets understand what formula is actually doing.

excel transpose 3

Understanding INDIRECT approach to TRANSPOSE
As told earlier Indirect formula helps in converting text strings to cell references. COLUMN function actually gives us the number of column in which the formula is entered. For example if you put =COLUMN in any cell of column A it will give the value of 1 as it is first column. Similarly if you put the COLUMN function in any cell of column I, it will return 9 as it is 9th column.

So with the column function we were able to incremental numbers as we drag the formula across columns. But as we inserted the formula in third column and the not first column so it would have returned 3 in which case the first address to be fetched would have been A3. But we want the values from first cell of column A therefore we had to insert “-2”. This way we got 1, 2, 3 and not 3, 4, 5

So the formula: =INDIRECT(“A”&COLUMN()-2)

In the above formula “A” is provided as text string which is joined (concatenated) using “&” with the values generated through COLUMN function. And thus successfully built the cell references.

Now if you change the values in source range the transposed range will also change. But it is much more flexible as it won’t break if you change one item in transposed range manually.

Transpose – Rows to Columns using INDIRECT

Using the same technique learnt above, we can transpose the data from rows to columns. But in this case you will use ROWS() function instead of COLUMNS to work correctly.

As you are converting rows to columns therefore you will be going down the rows and thus you require number increments as rows are jumped. So simply adjust the formula as per your needs and use ROW instead of COLUMN and it will do the rest for you.

No you can’t simply replace the COLUMN function with ROW function as mentioned above to convert the rows back to columns. Instead you will have to use the combination of INDIRECT, ADDRESS and ROW function.

In my case to transpose first row to column I used the formula: =INDIRECT(ADDRESS(10,ROW()-11))

Whereas to convert the second row to column I used this formula: =INDIRECT(ADDRESS(11,ROW()-11))

excel transpose 4

Method 4: TRANSPOSE using INDEX functions

Using the technique learnt to transpose columns to rows above. You can use INDEX function if building reference is difficult for you and you still have your head spinning.

INDEX function is one great addition to Excel functions and formulas repository. Lets have a look at the syntax of INDEX very briefly so that we understand how can we use it to transpose the data.

INDEX(array, row_num, [column_num])

array: the range, in our case it is the range we want to transpose

row_num: the row number. In this function we have mention row number to help it fetch the value

[column_num]: this is the optional argument. If not mentioned the column number doesn’t change and it will be the same in which function is applied. But today we need this option badly!

Transposing Rows to Columns using INDEX function

Suppose our data is arranged from cell A1 to G2. Two rows and seven columns in other words. So if we transpose the data we will have 2 columns and 7 rows.

To transpose the data arranged in a row to column go to the cell from where you want the data to start. I selected cell C6 and D6 and put theses formula:

Cell C6: =INDEX($A$1:$G$2,1,ROW()-5)

Cell D6: =INDEX($A$1:$G$2,2,ROW()-5)

Select both cells and drag the fill handle down to next six rows i.e. row 12. And you will get the data transposed from rows to columns.

excel transpose 5

Understanding the formula
Lets take some time to understand the mechanics of this formula. The formula we applied in cell C6 is as follows:

=INDEX($A$1:$G$2,1,ROW()-5)

array: [$A$1:$G$2] – this is the range that we want to transpose. The reference is made absolute so that as we drag the formula this array stays static and does not change.

row_num: [1] – this represent the first row of the data and we mentioned it so that INDEX function recognize that we want it to fetch the values only from row 1 of selected array i.e. $A$1:$G$2.

column_num: [ROW()-5] – Our data A, B, C, … G is in one row but across 7 columns. That means the address of:

  • value A is first row-first column
  • value B is first row-second column
  • value C is first row-third column; and so on…

So as we drag the formula down, we want the formula to get the values from 1st, 2nd, 3rd, 4th, 5th, 6th and 7th column. To do this we have to tell the column number. Now one way is to mention column number manually every time we put the INDEX function in next cell so that it fetches the right value.

But to automate the process of feeding column numbers to the function, I used ROW() function as the argument of column number (column_num). And as I drag the fill handle down, the ROW function will churn out row numbers inside the formula.

ROW function gives the row number. Now as I have put the INDEX function cell C6 the ROW function will tell that ROW number is 6. But we want it to be 1 as I want to fetch the values from the first column of the selected array. That is the reason we have “-5” following ROW() inside the formula. So it reduces the numbers generated by ROW function by 5 and this it will give 1, 2, 3, … 7.

Transposing Columns to Rows using INDEX function

Once you understand how INDEX function has fetched the values in above situation. Its easy to understand how to tackle the data which is arranged in columnar form and we want it to shift in rows.

For this we will make two changes:

for row_num: now we will use COLUMN() function generate numbers so that as we drag the formula towards right the row number will get updated because we are jumping across columns and thus COLUMN function will fetch the column number. Adjust the value generated by column number by adding or subtracting constants so that you get the desired result.

for column_num: mention 1 or 2 depending on the column within the selected array from which you want the values fetched.

Example

For example my data is based in 2 columns 7 rows and is housed in column C and D starting at Row 6.

I want the data transposed in rows 13 and 14 in column F. Therefore I put these formula:

cell F13:

=INDEX($C$6:$D$12,COLUMN()-5,1)

cell F14:

=INDEX($C$6:$D$12,COLUMN()-5,2)

Select both cells and drag to the right until column L

excel transpose 6

Method 5: Paste Special + Common Sense = Paste Ultimate!

We saw TRANSPOSE function went ahead of Paste Special as it was not only able push columns as rows (transpose) but were also live as transposed range was able to update as source change. We fixed some of the TRANSPOSE nuisances through INDIRECT approach. But writing formula and then dragging the range takes time.

But can we get TRANSPOSE like result using paste special? The answer is yes!

Paste Link
To get the live feed of source range we can use Paste Special’s inhouse capability called Paste link. Paste link pastes the cell references and not the values in cell and if source data changes, the linked cell will also change.

However, for unknown reasons, if you try to transpose and paste link at the same time Excel does not allow that i.e. you can either transpose or paste link. In other words, if you click Paste Link button with transpose option checked, the moment you select transpose option the paste link button gets disabled.

To get around this misery we took help of our Agent-X named common sense. Follow along!

Step 1: Copy the range and go to any vacant space in the same worksheet.

Step 2: Hit Alt+Ctrl+V to invoke paste special dialogue box and hit the button paste link. This will paste NOT the cell values but the cell references of the range you copied.

Step 3: Select the range you just pasted and hit Ctrl+H. This will bring up the REPLACE dialogue.

Step 4: In ‘Find what’ field mention equal sign i.e. “=” (without quotes) and in replace with field mention “HS=” and hit Replace ALL button. This will actually convert the formula into text as you have just replaced the equal sign with HS=

Step 5: Copy the range again and now go to the place where you want to put the transposed range. Hit Alt+Ctrl+V to bring the paste special dialogue box. Select the Transpose option and click OK. This will effectively transpose the data.

Step 6: To revert the formulas back in place so that you get the correct values in place from the source range. Hit Ctrl+H again. This time in Find what field put HS= and in replace with mention =. Hit Replace ALL button.

excel transpose 7

Now you have the live-transposed-range of the source WITHOUT using TRANSPOSE function or similar alternatives. How cheeky was that! 😉

So how do you do the transpose? If you have new ideas or any better versions of the ideas discussed do let me know in the comment section

48 COMMENTS

  1. Hello,

    i am trying to convert/transpose multiple rows with unique code into different columns.

    Can you help with a formula to solve this problem without using manual method

    Thank you in advance

    Regards
    Folabi

    sample initial data
    Branch Code Terminal ID Total Count
    10 10320101 8,212
    10 10320102 6,196
    10 10320103 8,099
    10 10320104 6,217
    11 10320111 1,180
    11 10320112 7,727
    12 10320121 296
    12 10320122 7,584
    12 10320123 7,270
    13 10320131 7,888
    13 10320132 5,607
    13 10320133 6,464
    14 10320141 9,650
    14 10320142 8,547
    14 10320143 230
    15 10320151 7,806
    15 10320152 8,230

    To have a table to look like this

    Branch Code Transaction count – Terminal 1 Transaction count – Terminal 2 Transaction count – Terminal 3 Transaction count – Terminal 4
    10 8,212 6,196 8,099 6,217
    11 1,180 7,727
    12 296 7,584 7,270
    13 7,888 5,607 6,464
    14 9,650 8,547 230
    15 7,806 8,230

  2. Dear Hasan,

    i have data like below there are 10,000 lines

    A
    1003
    1006
    1008
    1007
    1009

    B
    1234
    456

    and i want to convert the data as below.

    A 1003 1006 1008 1007 1009
    B 1234 456

    Please help me out.

    Regards,
    Sai

  3. Hi,
    I have a spreadsheet as well to transpose, but every 14 lines I need the data to start filling again in column
    Here is the data to transpose:
    Santa Ana Kiosk Mobile Not present
    Orange Kiosk Mobile In repair
    Irvine Kiosk Mobile In service
    Santa Ana Kiosk Software V2
    Orange Kiosk Software V3
    Irvine Kiosk Software V1

    and I want it to end up looking like this..
    Santa Ana Orange Irvine
    Kiosk Kiosk Kiosk
    Mobile Mobile Mobile
    Not present In repair In service
    Kiosk Kiosk Kiosk
    Software Software Software
    V2 V3 V1

    So every 4th row the formula needs to restart, or I would end up with a long horizontal string of data, which would be just as bad as the current long vertical string of data….
    any ideas?
    Thanks

  4. Hi. Thanks very much for this, it is exactly what I needed. Only difference though is that I need to transpose multiple consecutive rows into multiple consecutive columns on a different sheet but keeping links. I can do it one by one using Transpose function but I’ve got about 70 rows. Your article has help me how to do these in an array but you explain it if it’s in the same sheet, how can I do it if it is in a different sheet please? Thank you

    • Using same formulas discussed just append sheet name followed by ! Mark before cell reference.

      Say your sheet name is Nov17 and you want to refer to cell B2 of this sheet then it will be like this: Nov17!B2

  5. Hello Hasaan,

    Thank you for your website. Great Learning in here.

    I got a question want to ask about the Transpose – Rows to Columns using INDIRECT.

    Can You explain what is the -11 use for? =INDIRECT(ADDRESS(10,ROW()-11))

    as I want to link some data in column to row at another worksheet.

    Thank You a lot
    Vincent

  6. Hello,
    If I have data in a row in Sheet A, and I want to paste it into Sheet B, but shifted over a column, how do I achieve that? Every time I attempt it in a straightforward way, it just gives me the error that the copy area and paste arent the same size.
    This works fine if i am doing it in the same sheet, I can put it wherever I want, but I get that error otherwise.

    The goal here is to get a row from a sheet with a large amount of data onto a different sheet that has only the data i want, but with an additional blank column preceding the new data. Does that make sense?

  7. Hello,
    Hope i will get help. I have data like this
    Prabu 1
    Prabu 2
    Prabu 3
    Babu 1
    Babu 2
    I want to convert into like this

    Prabu 1 2 3
    Babu 1 2
    Note: It is small record i can do transpose. But i have more then 10,000 lines. Pls help

  8. Can anyone help me please,
    i have data like this..
    ABC: 100|100 200|200 300|300 and so on.
    if i want to transpose then i need only one cell copied to paste somewhere in limited cells….what i need is actually i want to copy only one cell in a column. i also tried to hide one column but when paste then it shows the hide cells also….tell me how i can copy and paste the required cells only.

  9. How can i transpose data from row to column other way round then excel does by default. Example, i need to transpose 1,2,3,4 row data into column as 4,3,2,1 not as 1,2,3,4. Many thanks in advance

  10. How can we transpose multi columns to a single row?i.e i have data 1 to 5 from B2:F2
    6 to 10 from B3:F3 , 11 to 15 from B4:F4 i want result in J2 Column 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15.
    i try to use transpose but not works fine any formula could you have?thanks

  11. How can we transpose multi columns to a single row?i.e i have data 1 to 5 from B2:F2
    6 to 10 from B3:F3 , 11 to 15 from B4:F4 i want result in J2 Column 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15.
    i try to use transpose but not works fine any formula could you have?thanks

  12. Hello,
    Can anyone please help me to transpose the data as below. I have huge number records to be converted like this. any help would be highly appreciated…

    code1 Code2 1 2 3 4 5 6 7
    W05 404616 32 38 95 75 74 85 57
    W06 404616 19 49 56 65 68 54 54

    code1 Code2
    W05 404616 1 32
    W05 404616 2 38
    W05 404616 3 95
    W05 404616 4 75
    W05 404616 5 74
    W05 404616 6 85
    W05 404616 7 57
    W06 404616 1 19
    W06 404616 2 49
    W06 404616 3 56
    W06 404616 4 65
    W06 404616 5 68
    W06 404616 6 54
    W06 404616 7 54

  13. Hi can help me with this.

    for example

    a b c
    1 1a 1a 1b 1b 1c 1c
    2 2a 2a 2b 2b 2c 2c

    will become

    1 2
    a 1a 1a 2a 2a
    b 1b 1b 2b 2b
    c 1c 1c 2c 2c

  14. Hello Hasan
    Hope you are doing well at outset accept my felicitation for the great help I tried lot of site but found the resolution at your site.
    i tried the Transpose – Column to Row using INDIRECT formula you explained above and found it works well in the same sheet but what if we need it from different sheet i.e from sheet1 to sheet2 and one more thing in the same formula{=INDIRECT(“A”&COLUMN()-2)} could you explain why we are doing -2 whats the logic behind that?
    Thanks and Regards

    • Thanks Avtar I am glad you found the solution here.

      I am using INDIRECT formula to construct a reference which fetches the value from the resultant reference. As my original data is vertical (column wise) I want my data to be horizontal (row wise). So in original shape the column reference “A”/”B” remains constant as we go from top to bottom within the same column.

      So while transposing I need A or B to be constant thats why I concatenated it inside INDIRECT function and then used COLUMN function to get incremental 1,2,3 and so on as I drag the fill handle to the right. So final output can be A1, A2, A3… or B1, B2 and B3…

      -2 is used because I started from column C which is third column but I wanted the data to be fetched starting from column 1. So 3-2=1

      To get the data from another sheet you just have make a little addition of ‘worksheet name’ before column reference inside INDIRECT function. Suppose the name of worksheet is “Factory” then formula will become:

      =INDIRECT(“Factory!A”&COLUMN()-2)

  15. Hi I would like to transpose a portion of a row into a column and then duplicate the remainder of the row to match. Example below

    AB10 ABERDEEN S* 011 AB10 AB11 AB12 AB13 AB14 AB15 AB30 AB31 AB32
    AB22 ABERDEEN N* 006 AB16 AB21 AB22 AB23 AB24 AB25

    to

    AB10 ABERDEEN S* 011 AB10
    AB10 ABERDEEN S* 011 AB11
    AB10 ABERDEEN S* 011 AB12
    AB10 ABERDEEN S* 011 AB13
    AB10 ABERDEEN S* 011 AB14
    AB10 ABERDEEN S* 011 AB15
    AB10 ABERDEEN S* 011 AB30
    AB10 ABERDEEN S* 011 AB31
    AB10 ABERDEEN S* 011 AB32
    AB22 ABERDEEN N* 006 AB16
    AB22 ABERDEEN N* 006 AB21
    AB22 ABERDEEN N* 006 AB22
    AB22 ABERDEEN N* 006 AB23
    AB22 ABERDEEN N* 006 AB24
    AB22 ABERDEEN N* 006 AB25

    I have thousands of records to change and the rows are not consistent lengths (the part I need to duplicate is though)

  16. Hello everyone,
    I have data and all the data is in the separate column. Each column I want to transpose as row values. For example, all the column headings in one row as shown in below, however, one heading should consist only 7 values. First table shows the data what I have and the second table shows what I expect to after transposing. Can anyone help me?
    Table 1
    A B C
    1 54 28.1
    5 51 28.1
    3 50 28.1
    4 49 28.1
    5 48 28.1
    6 47 28.1
    7 46 28
    4 45 28.1
    2 44 28.1
    3 43 28.1
    7 44 28.1
    5 43 28.1
    6 46 26.7
    5 41 26
    1 39 26.7
    2 40 26.7
    3 41 26.7
    5 42 26.7
    5 43 27
    6 44 27
    4 45 27

    Table 2
    A B C
    1 5 3 4 5 6 7 54 51 50 49 48 47 46 28.1 28.1 28.1 28.1 28.1 28.1 28
    4 2 3 7 5 6 5 45 44 43 44 43 46 41 28.1 28.1 28.1 28.1 28.1 26.7 26
    1 2 3 4 5 5 4 39 40 41 42 43 44 45 26.7 26.7 26.7 26.7 27 27 27

  17. I have data like this.
    John Age 45
    John Education Graduate
    John Phone 344555
    John Mail-id John@x.com
    John zipcode 600304
    John Height 160
    Wayne Age 30
    Wayne Education Post Graduate
    Wayne Phone 567839
    Wayne Mail-id Wayne@y.com
    Wayne zipcode 600405
    Wayne Height 165
    Neil Age 50
    Neil Education Doctorate
    Neil Phone 465780
    Neil Mail-id Neil@z.com
    Neil zipcode 657890
    Neil Height 170

    What I want is like this
    Name Age Education Phone Mail-id zipcode Height
    John 45 Graduate 344555 John@x.com 600304 160
    Wayne 30 Post Graduate 567839 Wayne@y.com 600405 165
    Neil 50 Doctorate 465780 Neil@z.com 657890 170

    Can you offer a solution

  18. Dear Team,

    I hope you are having a good day.
    I have question an excel spread sheet where they are multiple rows and column.

    i want to transpose the whole row B and C as example :
    Number s5 s10 s20 s25 s50 s100
    88775232 10 10 6 2 5 0
    89614654 60 50 0 0 0 5

    I want to put this cells text into 3 columns in Organized way. Like below..
    Item Qty Number
    s5 10 88775232
    s10 10 88775232
    s20 6 88775232
    s25 2 88775232
    s50 5 88775232
    s5 60 89614654
    s10 50 89614654
    s100 5 89614654

    is there any formula to do it?
    Any help would be highly appreciated…

  19. Hi,

    Is it possible to transpose paste from rows into columns, and also pushing the data in the below columns further down so that they are not deleted/replaced?

    Many Thanks

    • Im not sure if its possible with transpose. But it is possible with fill handle while holding Shift key. Would love to know if there is any way.

  20. ——————————————————————————————————–
    |First |Second |Third |Forth | |First |Class |2,500 |
    ——————————————————————————————————–
    |Class 1 |Class 4 |Class 8 |Class 6 | |Second |Class 4 |3,600 |
    ——————————————————————————————————–
    |2,500 |3,600 |1,000 |4,500 | |Third |Class 8 |1,000 |
    ——————————————————————————————————–
    | | | | | |Forth |Class 6 |4,500 |
    ——————————————————————————————————–
    |Fifth |Sixth |Seventh |Eighth | |Fifth |Class 3 |8,000 |
    ——————————————————————————————————–
    |Class 3 |Class 3 |Class 5 |Class 2 | |Sixth |Class 3 |2,100 |
    ——————————————————————————————————–
    |8,000 |2,100 |1,600 |5,200 | |Seventh |Class 5 |1,600 |
    ——————————————————————————————————–
    | | | | | |Eighth |Class 2 |5,200 |
    ——————————————————————————————————–
    |Ninth |Tenth |Eleventh |Twelfth | |Ninth |Class 4 |1,000 |
    ——————————————————————————————————–
    |Class 4 |Class 1 |Class 3 |Class 2 | |Tenth |Class 1 |1,200 |
    ——————————————————————————————————–
    |1,000 |1,200 |4,800 |6,300 | |Eleventh|Class 3 |4,800 |
    ——————————————————————————————————–
    | | | | | | Twelfth |Class 2 |6,300 |
    ——————————————————————————————————–

  21. First/Second/Third/Forth : First/Class 1/2,500
    Class 1/Class 4/Class 8/Class 6 : Second/Class 4/3,600
    2,500/3,600/1,000/4,500 : Third/Class 8/1,000
    : Forth/Class 6/4,500
    Fifth/Sixth/Seventh/Eighth : Fifth /Class 3/8,000
    Class 3/Class 3/Class 5/Class 2 : Sixth /Class 3/2,100
    8,000/2,100/1,600/5,200 : Seventh/Class 5/1,600
    : Eighth/Class 2/5,200
    Ninth /Tenth/Eleventh/Twelfth : Ninth/Class 4/1,000
    Class 4/Class 1/Class 3/Class 2 : Tenth/Class 1/1,200
    1,000/1,200/4,800/6,300 : Eleventh/Class 3/4,800
    :Twelfth/Class 2/6,300

    Sorry, it should be looks like that, the / is denotes to separate each data in a cell.

  22. First Second Third Forth First Class 1 2,500
    Class 1 Class 4 Class 8 Class 6 Second Class 4 3,600
    2,500 3,600 1,000 4,500 Third Class 8 1,000
    Forth Class 6 4,500
    Fifth Sixth Seventh Eighth Fifth Class 3 8,000
    Class 3 Class 3 Class 5 Class 2 Sixth Class 3 2,100
    8,000 2,100 1,600 5,200 Seventh Class 5 1,600
    Eighth Class 2 5,200
    Ninth Tenth Eleventh Twelfth Ninth Class 4 1,000
    Class 4 Class 1 Class 3 Class 2 Tenth Class 1 1,200
    1,000 1,200 4,800 6,300 Eleventh Class 3 4,800
    Twelfth Class 2 6,300

    Dear sir, Could you please help to solve with a function or formula to let the data at the left hand side is evenly in row by row to change in column’s form just like the right hand side?

    Thank you very much.

  23. First Second Third Forth First Class 1 2,500
    Class 1 Class 4 Class 8 Class 6 Second Class 4 3,600
    2,500 3,600 1,000 4,500 Third Class 8 1,000
    Forth Class 6 4,500
    Fifth Sixth Seventh Eighth Fifth Class 3 8,000
    Class 3 Class 3 Class 5 Class 2 Sixth Class 3 2,100
    8,000 2,100 1,600 5,200 Seventh Class 5 1,600
    Eighth Class 2 5,200
    Ninth Tenth Eleventh Twelfth Ninth Class 4 1,000
    Class 4 Class 1 Class 3 Class 2 Tenth Class 1 1,200
    1,000 1,200 4,800 6,300 Eleventh Class 3 4,800
    Twelfth Class 2 6,300

    Dear sir, could you please help to think the formula that I can change the data in row by row at the left hand side to the same data but in column to column, just like the right hand side.

    Thank you very much.

  24. I need help. Suppose I have the below data in sheet 1:
    10
    20
    30
    40
    50
    60
    The above numbers are in one column and in adjacent rows

    I would want to use a formula in order to get the below result in the same sheet or another sheet:
    20 40 60
    The above result does not contain any cells in between the numbers.
    Could you please help?

    • Hey Ace,
      Thanks for stopping by and posting a question.

      The way I understood is that you have you a data 10,20 and so in column A starting from cell A1 which extends to A6.

      Now you want to transpose the data but also skip one cell so that you get just 20 40 and 60. For that one way is using the following formula:
      =INDIRECT(“A”&COLUMN()*2)
      in cell A8 and dragging the fill handle three cells to the right.

      Hope this helps.

      – Hasaan

  25. Cell
    A1 DYNACORE FITNESS LLC
    A2 % TERESA MIDDLETON
    A3 915 SW RIMROCK STE 201-110
    A4 REDMOND, OR 97756
    A5
    A6 Name of employee
    A7 1052 COLUMBIA LLC
    A8 1423 NE 6TH AVE
    A9 CAMAS, WA 98607

    I want to put this cells text into 4 columns in Organized way. Like below..

    DYNACORE FITNESS LLC %TERESA MIDDLETON 915SW RIMROCK STE 201-110 REDMOND

    1052 COLUMBIA LLC 1423 NE 6TH AVE CAMAS, WA 98607

    is there any formula to do it? any help would be highly appreciated…

    • You can use good ol’ CONCATENATE function for that or use its shorter form “&” to get this done.
      I can see you need two lines so you can following formulas in two rows like this:
      =A1&” “&A2&” “&A3&” “&A4
      =A6&” “&A7&” “&A8&” “&A9

      Let me know if it helped. Thank you for visiting and commenting

  26. Dear Team,

    hope you are having a good day.
    my question may look simple but i need you help on this.

    i have an excel spread sheet where they are multiple rows and column.

    i want to tranpose the whole column E as a new row, keeping the data of Column A-D as it is.

    sno name given name asset number type LCD Type of LCD
    1 John 12345 87654 PC 56732 Monitor
    2 mark 12346 87653 Laptop 56731 Monitor
    3 james 12347 87651 laptop 56722 Monitor
    4 august 12348 87652 PC 56733 Monitor
    5 grey 12349 87659 PC 56781 Monitor

    requirement is to move LCD and type of LCD to a new row keeping the user details same

    • So you just need to transpose only one column E? And keep the rest of the data the same way? Its like making only one column to a row and keeping the rest as columns? Upload the example image/file on google drive and share the link here so that I can help better. Thanks for asking!

  27. I’m needing some help as I can’t get these functions to work with my issue.

    I’m wanting to create a formula then transpose it.
    IE
    =Sheet1!E1&Sheet1!E2
    =Sheet1!F1&Sheet1!F2

    I did the formula in one cell the dragged right to get exactly what I wanted. Unfortunately, I can get these formulas to transpose properly to a column to make it useful.

    • I found a solution to my issue. While dragging the formula to the right works then transposing to be vertical didn’t result in what I needed. This was obvious my issue.

      Before dragging the formula horizontally I changed the formula
      =Sheet1!E1&Sheet1!E$2
      =Sheet1!F1&Sheet1!F$2
      This resulted in the transpose working properly when rotating to vertical.

      • Hey thanks for the follow up message. Read your messages late. Good to hear that you resolved while experimenting! Excellent

  28. Hello,

    I’m trying to apply INDIRECT for transposing a formulas, as you wrote that : “…simply adjust the formula as per your needs and use ROWS instead of COLUMNS and it will do the rest for you.” but it’s not working.

    Can you help me with this problem and make an example.

    Thank you in advance,

    Kind regards,
    Nemanja

    • Hey Nermanja,

      Thank you for taking out time and visiting the website. Your question has actually sorted out a mistake. I have given the correct steps and have now included the animated illustrations as well to make the steps even clearer. Thank you for asking and helping me finding the mistake.

      Hasaan

LEAVE A REPLY