Excel Columns to Rows: 3 Easy Ways to Transpose Your Data

Do you want to rotate your data, changing your Excel columns to rows or vice versa?

Switching data from columns to rows or from rows to columns is known as transposing the data. When you convert columns into rows, the dimensions of data and the cell address will also change. Therefore, you should be careful while transposing the data. There are several ways to transpose columns to rows. We list three simple ways you can do this.

Method 1: use the transpose feature in paste drop-down menu

Let’s take the example of regional sales data in a table. Our data include row headers, column headers, and numerical data. Take a look at the images below and follow the steps below to convert the data in columns to row data and vice versa.

Step 1: Select the data set you want to transpose.

Step 2: On the Home tab, in the clipboard group, click Copy. You can also use the shortcut key Ctrl+C (Command+C on the Mac).

Step 3: On the worksheet, select the top-left cell of the destination.

Step 4: On the Home tab, in the clipboard group, click Paste and then click Transpose. 

When using this method, there is no link between the source and destination tables. If you change the source data, the transposed data does not change.

Method 2: use the transpose() function to convert excel columns to rows

Another way of doing this is to use the actual Transpose() function.

Step 1: Select the destination area. In this case, as the original is five rows by four columns, this is an area four rows deep and five columns across.

Step 2: Press the F2 function key to enter into edit mode while the target area is still selected, or click in the Formula Bar.

Step 3: Write “=” (without quotes) followed by the Transpose function, and in brackets, select the range you want to transpose.

Step 5: In some versions of Excel (especially versions earlier than Excel 2019), you may need the combination of Ctrl+Shift+Enter keys to save this formula. However, in later versions of Excel, you can press either Enter or Ctrl+Shift+Enter.

When using the Transpose function, if you change the values in the source range, the values in the transposed range automatically update. A disadvantage of using the Transpose function is that this array formula may break when you make changes to the number of source rows or columns. Therefore, you need to be careful if you are handling complex data ranges.

Method 3 – using paste special

If you need more control over your transposition of columns to rows and vice versa, you can use the Paste Link option in the Paste Special drop-down menu.

In the previous method, if the source range changed, the Transpose formula could break. This can be avoided by using Paste Link. This pastes the cell references into each cell. If the values in the source range are changed, the linked cells will also change. Though this function sounds complicated, it’s fairly simple, provided you follow the steps correctly.

Step 1: Copy the data range and go to the top-left of the destination range on the same worksheet.

Step 2: Press the combination of Ctrl+Alt+V (Command+Ctrl+V on the Mac) to get the Paste Special dialogue box. Then press the Paste Link button. The result is that the cell references of the range are copied as formulas.

Step 3: Select the range you just pasted in the destination range and hold down Ctrl+H (or Command+H on the Mac) to invoke the Find and Replace dialogue box.

Step 4: In this box, enter “=” (without quotes) in the “Find what” field and “HS=” (without quotes) in the “Replace with” field, and click the Replace All button. The formulas are converted into text, as you have just replaced the “=” with “HS=.” You can use text other than “HS,” but you must convert the formulas into standard text. We are only using “HS” as a placeholder to help us move values in the spreadsheet.

Step 5: Copy this second range. Select the destination range where you want the transposed range to go. Hold down the Ctrl+Alt+V keys (Command+Ctrl+V on the Mac) to invoke the Paste Special dialogue box again. Check the Transpose option and click OK. This will convert the columns to rows.

Step 6: Now you can convert the transposed data back to formulas. Hold down Ctrl+H again (Command+H on the Mac) to invoke the Replace dialogue box. This time, enter “HS=” (without quotes) in the “Find what” field, “=” (without quotes) in the “Replace with” field, and click the Replace All button.

Now you have the live-transposed range of data without using the Transpose function. You have swapped your Excel columns to rows and vice versa.

Microsoft Excel is an important part of your toolkit, especially if you manage any kind of data. It allows you to store and analyze data easily and with great flexibility. Learning MS Excel can be easy if you put your mind to it. And, of course, our fun and comprehensive course on Excel is always available to help you along.

Would you like to learn more shortcut keys, such as the Ctrl+Alt+V combination shown in this article? If so, read our article on Excel Keyboard Shortcuts.

---

Frequently Asked Questions

How do I convert multiple columns to rows in Excel?

You cannot transpose entire columns, but smaller ranges of data in multiple columns can be. You need to select the data first. Highlight the first range to be copied, then hold down Ctrl (Command on the Mac) and highlight the other ranges. Copy the data. Then go to where you want the data to be pasted, and go to Home – Paste – Transpose.

What is the shortcut to convert rows to columns in Excel?

The shortcut to convert rows to columns or transpose columns to rows is to press Ctrl+Alt+V (Control+Command+V on the Mac), the letter ‘e,’ then Enter.

How do I switch rows and columns in an Excel chart?

There are three ways to transpose rows and columns in an Excel chart. The first is to go to Chart Design – Switch Row/Column. The second is to right-click in some empty space in the chart, click on “Select Data,” and click on “Switch Row/Column.” Thirdly, you can also go to Chart Design – Select Data – Switch Row/Column.