How to Convert Data from Columns to Rows in Excel Is

This updated guide explains how to Convert Data from Columns to Rows in Excel Is through clear steps, practical examples, and important checks that help prevent common errors.

Method 1: Use Transpose in Paste Special

Step 1 : Open the Excel data file you may need to convert the column to row

Step 2 : Select (black out) the columns (or rows) you may need to convert into rows (columns) -> then press Ctrl + C to copy (or right-click and select copy function) in the function table that appears when Right click.

Convert Data from Columns to Rows in Excel Is � Method 1: Use Transpose in Paste Special screenshot 1

Then click the location to convert data and right click that cell -> select Paste Special .

Convert Data from Columns to Rows in Excel Is � Method 1: Use Transpose in Paste Special screenshot 2

When the Paste Special dialog box appears, put a checkmark in the box before Transpose

Convert Data from Columns to Rows in Excel Is � Method 1: Use Transpose in Paste Special screenshot 3

Finally click OK to complete. And here is the result of converting the column to row in Excel.

Convert Data from Columns to Rows in Excel Is � Method 1: Use Transpose in Paste Special screenshot 4

? This is the simplest way to convert columns into rows on an Excel spreadsheet, so you save a lot of time and effort compared to manually having to re-enter data if you get it wrong.

Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet

Usually you will rotate, convert cells by copying, pasting and using the Swap option. But doing that will create duplicate data. If you do not want to, you can use the TRANSPOSE function.

Step 1: Select the blank cells

First, select some blank cells. However, make sure to select the same number of cells as the original set of cells but in a different direction.

Convert Data from Columns to Rows in Excel Is � Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet screenshot 5

So we need to select eight horizontal cells, like the image below. This is where the new, swapped cells appear.

Convert Data from Columns to Rows in Excel Is � Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet screenshot 6

Step 2: Enter = TRANSPOSE (

While keeping those cells blank, enter: = TRANSPOSE (

Convert Data from Columns to Rows in Excel Is � Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet screenshot 7

Note that eight cells are still selected even after we have started entering the formula.

Step 3: Enter the range of original cells

Now enter the range of cells you may need to swap, the formula will be: = TRANSPOSE (A1: B4).

Convert Data from Columns to Rows in Excel Is � Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet screenshot 8

Then press CTRL + SHIFT + ENTER . This is the result after pressing CTRL + SHIFT + ENTER

Convert Data from Columns to Rows in Excel Is � Method 2: Use the TRANSPOSE Function Directly in the Spreadsheet screenshot 9

* Why is CTRL + SHIFT + ENTER instead of ENTER as usual functions

Why? Because the TRANSPOSE function is only used in array formulas and that is how you complete the array formula. An array formula, in short, is a formula that will be applied to multiple cells. Because you selected multiple cells in step 1 (you did that, didn't you?), The formula will be applied to multiple cells.

social shareand social share

FAQ

What is the easiest way to Convert Data from Columns to Rows in Excel Is?

Start with the requirements in this guide, then follow each step in order. Check the result after every major change so problems are easier to identify.