How To Convert Columns to Rows in Excel?

Switching columns and rows in Excel, a process known as transposing, is a handy trick that helps you view and manage your data better.

Ever struggled with organizing your Excel data effectively? Did you know changing columns to rows can help you see your data differently? In this guide, we’ll explore the easy yet impactful technique of transposing data in Excel, enhancing your ability to analyze and visualize your information effortlessly.

In this guide, I’ll show you easy ways, like using a “keyboard shortcut” and a “special paste trick”, to change columns into rows in Excel.

Convert Columns to Rows Using Right-Click Shortcut

I need to convert this data from columns to rows and it is a very simple method to do this. Below are given the steps:

  • First, place the data you want to convert from columns to rows in one place, then right-click and copy the data.
select all data right click and click copy
  • Next, place the cursor where you want to transpose it, then right-click, select ‘Paste Special’, and choose the ‘Transpose’ option.
place cursor right click and choose transpose
  • After that, you’ll see your data will be converted from columns to rows, as you can see in this picture.
your data will be transpose from columns to rows
There's a small drawback to this method; whatever you copy from the original data, like if it has a formula, will be pasted as-is in the transposed data. If you want to copy only the data without formatting, you will have to use the method described below.

Transpose Data Using Paste Special Dialogue Box

This is another easy method, but in this method, the data will be pasted without formatting. The ‘Paste Special’ method gives you more control whenever you paste something. So, let’s get started.

  • Place the data you want to transpose in one location and then copy all of the data.
place data in excel right click and choose copy option
  • Next, use the shortcut key for ‘Paste Special’, which is CTRL + ALT + V, then a ‘Paste Special’ dialogue box will open.
  • After that, you need to choose the ‘Values’ option and the ‘Transpose’ option and click on the OK button.
choose value then transpose then ok button to transpose data
  • Then, you will see that the color formatting in my data has been removed and only the data has been pasted, as you can see in the image below.
you see only data copy without formatting

Convert Columns to Rows Using Keyboard Shortcut

You can convert columns to rows using a keyboard shortcut and the “Transpose” feature. Here are the steps:

  • First, you need to place the data into a new Excel worksheet where you want to perform the transpose operation, and then copy the data.
  • Next, place the cursor where you want to paste the data, and then use the shortcut key ALT + E + S + V + E. (This shortcut is used to paste data without including any formatting)
  • After that, click the OK button.
If you want to paste only certain parts of your data, like the value, comments, or notes, you can use these shortcuts:
  • ALT + E + S + V + E + Enter – pastes only the values
  • ALT + E + S + F + E + Enter – pastes only the formulas
  • ALT + E + S + T + E + Enter – pastes only the formatting
  • ALT + E + S + C + E + Enter – pastes only the comments and notes
  • ALT + E + S + N + E + Enter – pastes only the data validation

Transpose Formula to Convert Columns to Rows

Using the transpose formula, you can convert columns into rows. Let me explain how.

  • First, select the cells where you want the transposed data to appear. Make sure you have enough rows and columns to accommodate the transposed data.
  • In the selected cell or cells, type the following formula: [=Transpose (range)]. Replace “range” with the actual range of cells that you want to transpose.
  • Press Ctrl+Shift+Enter instead of just Enter. This is because TRANSPOSE is an array formula, and it needs to be entered using this key combination to work properly. If done correctly, Excel will surround your formula with curly braces {}.
  • The data from the selected range will now appear transposed in the cells you selected in Step 1.
transpose formula to transpose data
Note: If you want to convert rows into columns, then the steps mentioned above are the same. Whether you want to convert rows into columns or columns into rows, the steps are identical.


What function displays row data in a column or column data in a row?

Transpose Function displays row data in a column or column data in a row.

Shortcut to select an entire column in MS Excel?

To select an entire column in MS Excel you can use CTRL + SPACEBAR

How many columns and rows are in Excel?

There are a total of 16,384 columns and 1,048,576 rows.

Can I automate the process of converting columns to rows in Excel?

Yes, you can automate the process by using Power Query or creating a macro using VBA.

Read Next: Insert a Column in MS Excel

Abhishek Singh is an expert in Excel and tech learning, offering valuable insights into data manipulation and analysis. Alongside his proficiency in Excel, he is well-versed in on-page SEO techniques, ensuring websites rank higher in search engine results. His dedication to continuous learning makes him a valuable resource in the tech industry.