How to Convert Columns to Rows in Excel (3 Easy Ways)

We’ll use a sample dataset with five columns: Serial No., Products, Volume, Unit Cost, and Revenue to illustrate how to take data arranged in columns and present it in rows within Excel.

Method 1 – Transposing Data to Convert Columns to Rows

The data shows Sales by Month in different countries. Now, we will be transposing the columns to rows.

1.1. Using the Context Menu Bar

We will use the Paste Options from the Context Menu Bar to convert columns to rows. Here are the steps:

Steps:

Select the whole dataset.
Right-Click on the dataset and the Context Menu Bar will open.
Click on the Copy button (you can also select the dataset and then press CTRL+C)

As a result, the dataset will be highlighted.

Select the B11 cell for your output.
Right-Click on the cell, and under Paste Options, choose Transpose (T).

You will see the following image.

1.2. Applying Paste Special Feature

Steps:

Select the whole dataset.
Under Home, click on the Copy command found under the Clipboard group of commands.

You’ve now copied the dataset.

Select the B11 cell for your output.
Under the Home tab, click on Paste.
Choose Paste Special option.

Mark Transpose.
After that, press OK.

You will see the converted rows.

Method 2 – Using INDIRECT & ADDRESS Functions in Excel

This method uses INDIRECT, ADDRESS, COLUMN, and ROW functions.

Steps:

Select a cell where you want the result. We have selected the B11 cell.
Use the corresponding formula in the B11 cell:

=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))

Press ENTER.

Formula Breakdown

COLUMN(B4)—> returns the column number from a given reference.
- Output: {2}.
ROW(B4)—> returns the row number from a given reference.
- Output: {4}.
ROW(B4) – ROW($B$4) + COLUMN($B$4)—> becomes {4}-{4}+{2}. Here, the Dollar Sign ($) will fix column B.
- Output: {2}.
COLUMN(B4) – COLUMN($B$4) + ROW($B$4)—> becomes {2}-{2}+{4}. Here, the Dollar Sign ($) will fix column B.
- Output: {4}.
The ADDRESS function will return the position of row number 4 and column number 2.
- Output: {“$B$4”}.
The INDIRECT function will return the value of that cell.
- Output: SL No.

Drag the Fill Handle icon to AutoFill the corresponding data in cells B12:B15.

Additionally, drag the Fill Handle icon to AutoFill the corresponding data in cells C12:G15.

You will see the converted rows.

Method 3 – Using TRANSPOSE Function to Convert Columns to Rows

Sometimes you may want to rotate cells. While the methods mentioned above can help you rotate data, they might introduce duplicate values. As an alternative approach, we will use a formula that leverages the TRANSPOSE function. We’ll revisit the same dataset we used previously to demonstrate this method.

Steps:

Select a blank cell. (we selected B11). Count the number of cells in B4:F9. In this case, it is 30 cells.
Type the range of the cells you want to transpose. In our example, we want to transpose cells from B4 to F9. So the formula would be:

=TRANSPOSE(B4:F9)

Press ENTER.

The formula will be applied to more than one cell. Here’s the result:

How to Solve TRANSPOSE Function & SPILL Errors in Excel

The Transpose function is bound to the original data. For example, if we change the value in D5 from 14 to 2, the new value will automatically be updated. But the reverse isn’t true.