How to Convert Rows to Columns or Columns to Rows in Excel

Here is another small tip for Microsoft Excel. Many at times, the data is presented in different format in different Excel sheets and while moving the data from one sheet to another imposes copying the date from a row to a column or from a column to a row.

It’s easier to just convert using the Transpose function but it may be little tricky while using functions and formulas.

So how to copy the data from a row to a column or vice versa?

First let’s consider that we want to copy data from a row to a column. First copy the row and go to the sheet where you want to paste. In the column where you want to insert, right click at the column or cell and Click Paste Special. Check the box which says ‘Transpose’ and click Ok. This method is same in Office 2007 and earlier versions.

The same thing applies which converting from a column to row. Also, this method not only applies to converting the whole rows to columns but also any group of vertical cells to horizontal cells or vice versa.

Converting rows to columns and columns to rows while using formulas:

If you are using formulas in your sheet, then you may have two cases.

In Case 1, you are copying the referenced rows or columns along with the rows or columns from where it is being referred. In this case, you need not worry about copying or converting as the reference in the formulas are automatically adjusted in the new sheet.

In Case 2, you are not copying the referenced rows or columns along with the rows or columns from where it is being referred. In both absolute and relative reference, the values it is being referred will not be referred correctly.

So to correct this, in case 2, again you have two options.

Option A: You can use referencing style like “=Sheet1!$B$1” or if you are calling from another Excel Book, then “=[Book1.xls]Sheet1!$B$1”. But in this case one must be careful not to change the location of the files and it will affect the referencing values.

Option B: You can just copy and past only the values while you convert from rows to columns or columns to rows. To do this, while you are in Paste Special, check both Transpose and Values (under Paste).