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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for the Transpose feature to convert columns to rows or the inverse one in Excel, then you have come to the right place. In this article, we’re going to show you how to convert columns to rows in Excel.


3 Methods to Convert Columns to Rows in Excel

Here, we will describe 3 methods to convert columns to rows in Excel. In addition, for your better understanding, we’re going to use the following dataset. Which contains 5 columns. They are Serial No, Products, Volume, Unit Cost, and Revenue.

Dataset to Convert Columns to Rows in Excel


1. Transposing Data to Convert Columns to Rows

Here, we have the dataset. The data shows Sales by Month in different countries. Now, we will be converting the columns to rows.


1.1. Use of Context Menu Bar

Here, we will use the Paste Options from the Context Menu Bar to convert columns to rows in Excel. Actually, this is the easiest option for converting columns to rows. Eventually, you can also convert rows to columns with this feature in Excel. Now, let’s see the steps given below.

Steps:

  • Firstly, select the whole dataset >> Right-Click on the dataset.
  • Secondly, from the Context Menu Bar >> Click on the Copy button. Or, after selecting the dataset, press CTRL+C on the keyboard to copy the dataset.

Use of Context Menu Bar to Convert Columns to Rows in Excel

As a result, the dataset will be highlighted.

  • Now, select the B11 cell where we want to keep the output.
  • Then, Right-Click on the cell >> from the Context Menu Bar >> choose Transpose (T) which is under the Paste Options.

Lastly, you will see the following image.

Using Paste Options from the Context Menu Bar to convert columns to rows in Excel

Read More: VBA to Transpose Multiple Columns into Rows in Excel


1.2. Applying Paste Special Feature

Here, we will use the Paste Special feature to convert columns to rows in Excel. Now, let’s see the steps given below.

Steps:

  • Firstly, select the whole dataset.
  • Secondly, from the Home Ribbon >> you may click on the Copy command which is under the Clipboard group of commands.

Applying Paste Special Feature to Convert Columns to Rows in Excel

As a result, the dataset will be copied.

  • Now, select the B11 cell where we want to keep the output.
  • Then, from the Home tab >> go to Paste command.
  • Then, choose Paste Special option.

At this time, a new dialog box named Paste Special will appear.

  • Now, mark Transpose.
  • After that, press OK.

Lastly, you will see the converted rows.

Read More: Convert Columns to Rows in Excel Using Power Query


2. Use of INDIRECT & ADDRESS Functions in Excel

Now, you can use a combination of some functions to convert columns to rows in Excel.  Here, we will use INDIRECT, ADDRESS, COLUMN, and ROW functions.

Steps:

  • Firstly, you must select a cell, where you want to keep the result. Here, we have selected the B11 cell.
  • Secondly, you need to 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)))
  • Thirdly, press ENTER.

Use of INDIRECT & ADDRESS Functions in Excel to Convert Columns to Rows

Formula Breakdown

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

  • After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in cells B12:B15.

  • Similarly, you have to drag the Fill Handle icon to AutoFill the corresponding data in cells C12:G15.

Lastly, you will see the converted rows.

Read More: How to Transpose Duplicate Rows to Columns in Excel


3. Using TRANSPOSE Function to Convert Columns to Rows

Sometimes you may want to rotate cells. Actually, you may do this by the previous methods, and also by using the VBA with Transpose option. But that might create matched values. So, in this section we will see another way, you can use a formula using the TRANSPOSE function. To explain the method, we will consider the dataset used in the previous formula.

Steps:

  • Firstly, select a blank cell. Here, we select the B11 cell. Count the number of cells in B4:F9. In this case, it is 30 cells.
  • Secondly, type the range of the cells you want to transpose. In this example, we want to transpose cells from B4 to F9. So the formula for this example would be:
=TRANSPOSE(B4:F9)

Using TRANSPOSE Function to Convert Columns to Rows

  • Then, press ENTER.

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

Read More: How to Convert Multiple Columns into a Single Row in Excel


How to Solve TRANSPOSE Function & SPILL Errors in Excel

The Transpose formula version is bound to the original data. So, 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.

How to Solve TRANSPOSE Function & SPILL Errors in Excel

If we change any transposed cells, the original set will not change. Instead, we will get a SPILL error and the transposed data will disappear.


How to Use TRANSPOSE Function with Blank Cells in Excel

The TRANSPOSE function will convert the empty cells to “0”s. Follow the image below, you will get the scenario.

How to Use TRANSPOSE Function with Blank Cells in Excel

Read More: How to Transpose Formulas Without Changing References in Excel


How to Convert Rows to Columns in Excel

Here, you can also convert rows to columns in Excel. Let’s have the following dataset. Where the information is row-wise decorated. Now, we want to make a dataset that will be column-wise decorated.

How to Convert Rows to Columns in Excel

Here comes the most interesting part, you can follow method-1 to convert the rows to columns. Actually, you can follow any of the methods. As the method 1 is the easiest one so we have mentioned it. After following the steps, you will get the following converted dataset.

Read More: How to Transpose Every n Rows to Columns in Excel


Practice Section

Now you can practice the explained methods by yourself.

Practice Section to Convert Columns to Rows in Excel


Things to Remember

Here, the process of converting columns to rows or vice-versa all those methods also works when you want to convert a single column to a row or vice-versa.


Download practice workbook

You can download the Practice Workbook that we used to prepare this article. Furthermore, you can change or modify data & find new outputs accordingly.


Conclusion

So, these are the three easy methods how to convert columns to rows in Excel. You can apply any of these three methods. So, we hope you find this article useful. Furthermore, feel free to share your thoughts in the comment section.


Further Readings

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Ratul Khan
Ratul Khan

Hello! Here is my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and I am very interested in research.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo