Convert columns to rows in Excel: detailed step-by-step guide with specific and easy-to-understand examples

When working with Excel, often you need to restructure your data before you can start any type of analysis. One common task that many users need to do is to convert columns to rows in Excel or vice versa, convert rows to columns in Excel. This is usually the case when you take a data set from a database or a website and need to convert the data so that the column data is displayed in rows and the row data is displayed in columns.

In this guide, FPT Shop will show you some easy methods that you can use to quickly convert columns to rows in Excel and convert rows to columns in Excel. Among them is a relatively simple method, but those who prefer a professional approach also have the option with a simple VBA code.

How to Convert Columns to Rows in Excel

1. Convert Columns to Rows in Excel from the right-click menu

One of the easiest ways to convert columns to rows in Excel is through the right-click menu. The example below is a data table in which quarterly sales values are displayed by row and country sales values are displayed by column.

Now, to convert the quarterly sales values data into columns and the country sales values into rows. You perform the following steps:

Step 1: Use the mouse to hover over the entire data range that you want to convert columns to rows in Excel.

Step 2: Use the keyboard shortcut Ctrl + C or right-click and choose the Copy option.

Step 3: Move to the position where you want to convert columns to rows in Excel, then right-click on a cell within the range you want to paste this data. From the drop-down menu, click on the right-pointing arrow icon of the Paste Special… option.

Step 4: From the submenu, click on the Transpose option, as shown below.

Step 5: Immediately, the previously copied data will be pasted, but the columns and rows will be swapped with each other.

2. Convert Columns to Rows in Excel using Paste Special

Another quick way to convert columns to rows in Excel is to use the Paste Special dialog box. As you can see, the Paste Special dialog box allows you to have more control over what you can paste. The example is still the data table above, if you want to convert columns to rows in Excel using the Paste Special feature, follow these steps:

Step 1: Select and copy (Ctrl + C) the entire range of data you want to convert columns to rows in Excel.

Step 2: Right-click on the cell where you want to paste the copied data, then choose the Paste Special… option (not click on the right-pointing arrow icon as in the above method). This action will open the Paste Special dialog box.

Step 3: In the Paste Special dialog box, click on the Transpose option, as shown below.

Step 4: Click the OK button to complete the process.

The above steps will copy the original data and then paste the transposed version of that data (where column data is converted to rows and row data is converted to columns). The advantage of using the Paste Special dialog box is that it allows you to choose what you want to paste into the converted data as well.

For example, you can select to paste only values, only formulas, only formats, or comments/notes. To do this, you need to make additional selections from the Paste options in the Paste Special dialog box.

3. Convert Columns to Rows in Excel using keyboard shortcuts

If converting columns to rows in Excel is something you need to do quite frequently, the advice is that you should use keyboard shortcuts to perform this action. For those who are used to using keyboard shortcuts, converting columns to rows in Excel with shortcuts is the fastest way to do it.

Special Paste shortcut keys for Windows

Here are the shortcut keys to copy the data and paste the Transpose version of that data (where columns are converted to rows).

To convert columns to rows in Excel using the above shortcut keys, follow these steps:

Step 1: Copy the data range that you want to convert

Step 2: Go to the destination cell where you want to paste the copied data

Step 3: Press the following keys in order ALT + E + S + E + Enter

In case you don’t want to paste the entire data range but only want to paste the values or formats or comments/notes, you can use the following shortcut keys:

• ALT + E + S + V + E + Enter – Paste values only
• ALT + E + S + F + E + Enter – Paste formulas only
• ALT + E + S + T + E + Enter – Paste formats only
• ALT + E + S + C + E + Enter – Paste comments and notes
• ALT + E + S + N + E + Enter – Paste data validation

Special Paste shortcut keys for Mac

If you’re using a Mac, you can use the following shortcut keys to open the Paste Special dialog box.

When the dialog box opens, you can make the necessary paste options and then click the OK button.

 4. Convert Columns to Rows in Excel using the TRANSPOSE function

Another method that you can use to convert columns to rows in Excel is to use the TRANSPOSE function. To understand how this function works in Excel, you can follow the example below. Here is the dataset that needs to be converted from columns to rows.

Use the TRANSPOSE function to convert columns to rows in Excel by entering the following formula:

Enter this formula in the cell where you want to output the converted data. In this example, the input cell is cell A7.

5. Convert Columns to Rows in Excel using VBA code 

If you prefer VBA, here is the code you can use to quickly convert columns to rows in Excel. Below is the VBA code to perform this:

When running the above code, it will ask you to select the range of the cells you want to convert, then it will ask you to select the destination cell where you want to output the result. Following are the steps to use Excel VBA code to convert columns to rows:

Step 1: Open the Visual Basic Editor. You can click the Visual Basic option from the Developer tab on the ribbon toolbar, or you can use the shortcut key ALT + F11.

Step 2: In the VB Editor window, click on the Insert option and then choose Module from the drop-down menu. This action will insert a new module where we will copy and paste the VBA code above.

Step 3: Copy and paste the above VBA code into the module window on the right-hand side. 

Step 4: Close the VBA Editor.

Step 5: Press ALT + F8, select the ‘ConvertColumnsToRows‘ macro in the Macro dialog box, then click the Run button. Then the macro will guide you through the options and output the result in the destination cell.

Here are some things to know when using VBA code to convert columns to rows in Excel:

• If you want to reuse the above code in the same Workbook, save this Excel file as a macro-enabled file (with the file extension .XLSM)
• The changes made by the VBA macro cannot be undone, so you should make a backup copy of the Excel data table before running the macro.
• In case you want to use this code in all your Workbooks, you need to save this code in the Personal Macro Workbook. Once you have saved the VBA code in the Personal Macro Workbook, you can access the code from any Workbook on your system.

How to Convert Rows to Columns in Excel

1. Convert Rows to Columns in Excel from the right-click menu

If in the above method you perform converting columns to rows in Excel through the right-click menu, then this option will convert rows to columns in Excel in the opposite direction. The steps are also performed similarly as follows.

Step 1: Use the mouse to select the entire data range that you want to convert rows to columns in Excel. Then press the keyboard shortcut Ctrl + C or right-click and choose the Copy option.

Step 2: Click on the position you want to convert rows to columns in Excel, then right-click, from the drop-down menu select the arrow pointing right of the Paste Special… item and select the Transpose icon.

Step 3: Immediately, the copied data will be pasted, but the rows and columns will be swapped with each other.

Here are some important points to note about this method:

• The pasted data will retain the original formatting from the copied data. As in the example above, the column names are still orange and the Quarter rows are still blue.
• If there are any formulas in the original data, those formulas will also be copied. If there are any cell references used in those formulas, those references will be adjusted (if necessary) to ensure that the formulas provide you with the appropriate values.

A minor drawback of this method is that it will always copy everything from the original data and paste it into the converted data (including formats, formulas, comments/notes, and data validation). If you only want to copy the data without the formats, this cannot be done using this method. But you can use the next method below.

2. Convert Rows to Columns in Excel using Paste Special

In case you want to convert rows to columns in Excel, you also use the Paste Special dialog box like converting columns to rows in Excel above. The steps are also performed similarly as follows:

Step 1: Use the mouse to select and copy (Ctrl + C) the entire data table you want to convert rows to columns in Excel.

Step 2: Right-click the cell where you want to paste the copied data, then choose the Paste Special… option from the drop-down menu.

Step 2: In cell A5, enter the following formula: 

=TRANSPOSE($A$1:$F$3)

However, because this formula needs to be applied to other empty cells, press the keyboard shortcut Ctrl + Shift + Enter.

Step 3: Immediately, the data table will be converted from rows to columns in Excel 

Here are some things you should know when using the TRANSPOSE function:

• The result data you receive is an array associated with the original data set. So, if you make any changes to the original data set, it will automatically be reflected in the result data.
• If you want to delete the original data set, you must first convert the result formula into static values so that it is no longer linked to the original data set. If you do not do this and delete the original data set, you will receive 0 in all cells.
• The TRANSPOSE formula will only convert the values and formats of the original data set will not be copied.
• When using the TRANSPOSE function, make sure that there are enough empty cells to accommodate the result of the function. In the case that any cell has been filled, you will receive the #SPILL! error message.

Conclusion

Above, you have just explored 5 different methods that you use to convert columns to rows in Excel quickly, as well as some solutions to convert rows to columns in Excel. The easiest way to perform converting columns to rows in Excel and converting rows to columns in Excel is to use the Paste Special dialog box. This option also has the advantage of allowing you to have more control over what you want to paste and pasting into the converted data. Of course, you can also use the built-in TRANSPOSE function to pre-build converting columns to rows in Excel or use VBA code to perform this.

You may also like

5 Essential Excel Functions for Beginners

Master the fundamentals of Excel with these 5 basic functions! Learn how to use SUM, AVERAGE, COUNT, MIN, and MAX in order to make calculating and analyzing data easier and more efficient.

A Simple Method for Adding Watermark to Excel for All Spreadsheets in No Time

Watermarking in Excel is a great way to mark your ownership and protect the content on your Excel spreadsheets. If you’re unsure how to do it, you can follow the steps outlined in this article.

How to recover unsaved or overwritten Excel files effectively: a sure-fire method

Knowing how to recover unsaved Excel files can be extremely helpful in cases when your computer unexpectedly shuts down or you accidentally click on “Don’t save” when Excel asks if you want to save your changes before closing. Follow this article to learn the step-by-step process of how to accomplish it.

5+ Effective Ways to Convert PDF to Excel Quickly, Useful Office Tricks

Knowing the ways to convert PDF files to Excel below will enable you to easily edit and add content inside. Office workers and students definitely must know this trick if they want to simplify their tasks.