Transpose in Excel – convert rows to columns and vice versa

Transposing data in Excel is a task familiar to many users. Very often, after creating a complex table, it becomes obvious that it would be more efficient to expand it for more convenient analysis or charting from the available data.

In this article, you will find several ways to convert rows to columns (or columns to rows) in Excel. These solutions work in all versions of Excel (2013, 2010, 2007 and earlier), cover many possible scenarios, explain the most common mistakes, and show good examples of using transposition formulas in Excel.

  • Convert rows to columns using Copy > Paste
  • Transpose the table by copying / pasting links to the original data
  • Transposing data in Excel using formulas

Convert Rows to Columns in Excel with Paste Special

Let’s say you’ve created a spreadsheet in an Excel worksheet similar to the one shown at the top of the figure below. The country names are arranged in columns, but the list of countries can be very long, so it will be much more convenient for us to convert the columns to rows so that the table fits on the screen.

  1. Select the rows and columns you want to swap. If you want to expand the entire table, i.e. all cells with data on the sheet, click Ctrl + Home, and then Ctrl + Shift + End.
  2. Copy the selected cells. To do this, choose:
    • right click on them and select Copy (Copy) in the context menu.
    • click Ctrl + C.
    • click Copy (Copy) tab Home (Home) in the section clipboard (Clipboard).
  3. Select the first cell in the range where you want to copy the transposed data.

Note: It is important to select a cell that is outside the range of the original data so that the copied area and the pasted area do not intersect. For example, if the original data consists of 4 columns and 10 rows, then the transposed table will consist of 10 columns and 4 rows.

  1. Paste the copied data with the command paste Special (Special Paste) > Transpose (Transpose). You can open a dialog paste Special (Paste Special) in three ways:
    • On the Advanced tab Home (Home) in the section clipboard (Clipboard), click the small black arrow under the button paste (Paste) and select a command Transpose (Transpose).
    • Right-click on the cell where you want to paste the data, and from the context menu select paste Special (Special Paste) > Transpose (Transpose).
    • (finally the fastest way) Click Ctrl + Alt + V, select an option Transpose (Transpose) and click OK.

Note: If you are transposing cells that contain formulas, make sure all of those cells use absolute references (with the $ sign). This is necessary because the formulas will also be transposed and the cell references will be automatically updated.

As you just saw, the option Transpose (Transpose) in Excel allows you to convert rows to columns (or columns to rows) in just a few seconds. I’m sure this method is very useful when you want to transpose a table once. Using this method, you save the original data, and this is another argument in its favor.

However, the method described above has two significant drawbacks that do not allow us to consider it an ideal solution for transposing data in Excel.

  • This tool is not available when copying/pasting a full featured Excel spreadsheet as opposed to a simple range. In this case, you will have to copy the table without headers, or convert it to a range, i.e. right-click on any cell in the table and select from the context menu Table (Table) > Convert to Range (Convert to range).
  • A more serious limitation of this method is that it creates a copy of the original data, and if it changes, you have to repeat the process and transpose the table again. No one wants to waste their precious time converting the same rows and columns multiple times. Do you agree?

How to transpose a table referring to the original data

Let’s see how we can convert rows to columns in Excel using the technique we are already familiar with. Copy > Insert, so that the transposed data refers to the original data. The main advantage of this approach is that when the data in the original table changes, the transposed table will be updated to reflect these changes.

  1. Copy the rows you want to convert to columns (or the columns you want to convert to rows).
  2. Select an empty cell on the same or another sheet.
  3. Open a dialog box paste Special (Paste Special) as shown in the previous example, and click paste Link (Insert Link) in the lower left corner of the dialog box.The result will be something like this:
  4. Highlight the new data and open the Excel dialog box − Find and Replace (Find and replace). You can also press combination Ctrl + Hto go straight to the tab Replace (Replace).
  5. Replace all characters equal to “=” on “xxx“. In fact, you can replace them with any other characters that are not in the range in question.This will turn your spreadsheet into something slightly intimidating (see the picture below), but don’t worry, there are only 2 steps left and you will get the desired result.
  6. Copy the table with the symbols “xxx” and run the command paste Special (Special Paste) > Transpose (Transpose) to convert columns to rows (or rows to columns).
  7. Reopen the dialog box Find and Replace (Find and Replace) to replace all characters “xxx” on “=“, i.e. restore references to original cells. Ready!

Quick, quiet and elegant solution, right? The only downside to this approach is that the original table’s formatting will be lost during runtime, and you’ll have to manually restore it (I’ll show you a quick way to do this later in this tutorial).

Transposing data in Excel using formulas

If you are not looking for easy ways, you can use formulas to convert columns to rows and vice versa.

Convert rows to columns in Excel using the TRANSPOSE function (TRANSPOSE)

As can be understood from its name, the function TRANSPOSE (TRANSPOSE) is specially designed for transposing data in Excel. If you do not have much experience with formulas in Excel, and in particular with array functions, then please be careful to follow the following steps exactly as written.

In this example, we will be converting a table that lists US states with population data:

First, you need to determine the exact number of rows and columns in the table. In our example, this is 7 columns and 6 rows, including headers. Let’s remember these numbers and start converting rows into columns.

  1. Count the cells you want to transpose. In our case, these are 7 columns and 6 rows.
  2. Select an empty range of cells. Since the function TRANSPOSE (TRANSPOSE) changes the vertical to the horizontal dimension of the range, which means you need to select as many rows and columns as your source table has columns and rows, respectively. In our example, we select 6 columns and 7 rows.
  3. Press F2to enter edit mode.
  4. Write a function TRANSPOSE (TRANSPOSE) and indicate in parentheses the range of data to be transposed. In our case, it will look like this:

    =TRANSPOSE($A$1:$G$6)

    =ТРАНСП($A$1:$G$6)

Note: Always use absolute references when working with a function TRANSPOSE (TRANSP).

  1. Press Ctrl + Shift + Enter.

Note: Be sure to click Ctrl + Shift + Enterbecause it’s an array formula. If you just press to enter the formula Enter, then it won’t work.

Ready! Our data is transposed, 7 columns turned into 6 columns, which is what we wanted to get.

Benefits of the TRANSPOSE function

The main advantage of the function TRANSPOSE (TRANSPOSE): The converted table retains its link to the data source, i.e. whatever changes you make to the original data, the transposed table will also change.

Disadvantages of the TRANSPOSE Function

  • The formatting of the original table will not be preserved in the converted table, as seen in the figure above.
  • If some cells of the initial table were empty, then there will be zeros in the transposed cells.
  • You will not be able to edit cells in the converted table because the function TRANSPOSE (TRANSP) is extremely dependent on the data source. If you make an attempt to change any cell, you will receive a warning message: You cannot change part of an array (You can not change part of the array).

And finally, no matter how good and simple the function is TRANSPOSE (TRANSP), it lacks flexibility and is therefore not the best solution in many situations.

Converting a String to a Column or a Column to a String Using the INDIRECT Function

This method is very similar to the previous one. Its main advantage over the function TRANSPOSE (TRANSPOSE) is that it leaves the possibility to change the data in the transposed table, while maintaining the relationship with the original table.

In this example, we will use a small table so that you can focus on the transposition process and not on the data in the table. We will use a slightly convoluted combination of functions INDIRECT (INDIRECT) and ADDRESS (ADDRESS).

Suppose you have data in 4 columns (A – D) and in 5 lines (1 – 5):

  1. Enter the formula shown below into the top left cell of the target range (let’s say this is the cell A7), and press Enter:

    =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

    =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

    If the data does not start on a line with a number 1 and not in a column A, then a slightly more complex formula is required:

    =INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

    =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1)))

    Where A1 is the top left cell of the source table. Also note the use of absolute and relative references.

  2. Copy the formula to the entire range where you are going to put the transposed data by dragging the autofill handle in the lower right corner of the selected cell (or range of cells).

That’s all! In your new table, all columns have been converted to rows. However, the transposed cells look very flat and boring compared to the original data.

But don’t worry, it’s not a problem at all. Here’s how you can easily restore the original formatting:

  • Copy the original table.
  • Select the transposed table.
  • Click on the selected area with the right mouse button and in the section Paste Options (Paste Options) select Formatting (Formatting).

To summarize, a method with a function INDIRECT (INDIRECT) seems to be a more complicated solution to the problem of converting rows to columns than with the function TRANSPOSE (TRANSP).

Advantages: You can safely edit any cell(s) in the transposed table, while it will remain linked to the source table and will be automatically updated every time you make changes to the original data.

Disadvantages: In fact, I see only one – the formatting of the original data is not preserved. However, you can easily restore it as shown above.

Now you understand how the function works INDIRECT (INDIRECT), and you might want to take a closer look at what this formula actually does and why we used the combination with the function ADDRESS (ADDRESS). If you are not interested in the technical details, you can skip the next section.

INDIRECT (INDIRECT) and ADDRESS (ADDRESS) functions – syntax and logic

Function INDIRECT (INDIRECT) is used to create an indirect reference to a cell. For example, if you want to put the value of a cell B1 into a cell A8, then write in the cell A8 the following formula:

=INDIRECT("B1")

=ДВССЫЛ("B1")

But the real power of the function INDIRECT (INDIRECT) is that it can turn any text string into a link, including a string you create with other functions and other cell values. That is what we are going to do now. If you understand this, then everything else will be easy to understand 🙂

I think you remember that in the formula we used 3 more functions – ADDRESS (ADDRESS), COLUMN (COLUMN) and ROW (LINE).

Function ADDRESS (ADDRESS) is used to get the cell address from the row and column numbers you specify. Remember: first the row, then the column. For example, ADDRESS(8;1) will return $ A $ 8.

Function COLUMN (COLUMN) returns the column number from the given cell reference. For example, COLUMN(A3) will return a value 1because the column A is the first column. As you might guess, the function ROW (ROW) works exactly the same, only for strings.

Now let me recap the entire formula we used to convert rows to columns in an Excel sheet:

=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))

=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))

As you can see, in the function ADDRESS (ADDRESS) we set the column first and then the row, and that’s the trick! Remember that the function ADDRESS (ADDRESS) understands the first number as the row number and the second number as the column number. In other words, the function ADDRESS (ADDRESS) takes the column number returned by the function COLUMN (COLUMN) and turns it into a row number, then takes the row number and turns it into a column number, i.e. swaps rows and columns.

Now that you know what each function does, let’s put the logic behind the whole formula together:

  • Features COLUMN (COLUMN) and ROW (ROW) return the column and row numbers, respectively;
  • function ADDRESS (ADDRESS) swaps row and column;
  • function INDIRECT (INDIRECT) outputs the flipped data to another cell in an Excel worksheet.

Not scary at all, right?

Transposing data with a VBA macro

It’s not difficult to write a script that will transpose rows into columns, provided that you have a good command of VBA. If not, you can find many ready-made macros on the Internet. But if you are working with large tables, remember that the method Transpose in VBA is limited to 65536 elements. If your array goes beyond this limit, then all extra data will be silently lost.

Leave a Reply