Phillip Burton

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

A company’s sales by region displayed by a horizontal and vertical table. 

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.

Excel in Microsoft Excel 2: Intermediate to Expert advanced

Last Updated May 2023

Bestseller
  • 153 lectures
  • Intermediate Level
4.5 (405)

Get Expert certified! Levels 6-10 for Microsoft Excel 2010, 2013, 2016 and Excel 2019. Covers 77-728, 77-427 & 77-428. | By Phillip Burton, I Do Data Limited

Explore Course

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.

select data in Excel

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. 

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.

Write “=” (without quotes) followed by the Transpose function, and in brackets, select the range

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.

transpose function excel

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.

copy data range

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.

paste special dialogue box, paste link button

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.

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.

find what =,replace with HS=, replace all

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.

paste special dialogue box, transpose

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.

find and replace dialogue box

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.

live-transposed range of data

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.

Page Last Updated: October 2021

Top courses in Excel

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo

Courses by Phillip Burton

70-461, 761: Querying Microsoft SQL Server with Transact-SQL
Phillip Burton, I Do Data Limited
4.5 (15,082)
Bestseller
70-461 Session 6: Querying Microsoft SQL Server (T-SQL code)
Phillip Burton, I Do Data Limited
4.6 (1,885)
Excel in Microsoft Excel 3: Pivot Tables Intro & Masterclass
Phillip Burton, I Do Data Limited
4.8 (184)
Microsoft SQL Server Reporting Services (SSRS): 2022 edition
Phillip Burton, I Do Data Limited
4.5 (4,001)
Bestseller
SAP Crystal Reports 2020 (and earlier versions)
Phillip Burton, I Do Data Limited
4.6 (869)
Bestseller
SQL Server Integration Services (SSIS) - An Introduction
Phillip Burton, I Do Data Limited
4.5 (7,804)
Bestseller
Excel in Microsoft Excel 4: VBA macros for Excel - an intro
Phillip Burton, I Do Data Limited
4.9 (63)

Courses by Phillip Burton