How to convert rows to columns in Excel (transpose data)

Convert rows to columns in Excel using Paste Special

Suppose you have dataset similar to what you see in the upper part of the graphics below. The country names are organized in columns, but the list of countries is too long, so we'd better change columns to rows for the table to fit within the screen:

To switch rows to columns in Excel, perform these steps:

1. Select the original data. To quickly select the whole table, i.e. all the cells with data in a spreadsheet, press Ctrl + Home and then Ctrl + Shift + End.
2. Copy the selected cells either by right clicking the selection and choosing Copy from the context menu or by pressing Ctrl + C.
3. Select the first cell of the destination range.

   Be sure to select a cell that falls outside of the range containing your original data, so that the copy areas and paste areas do not overlap. For example, if you currently have 4 columns and 10 rows, the converted table will have 10 columns and 4 rows.

4. Right click the destination cell and choose Paste Special from the context menu, then select Transpose.

For more information, please see How to use Paste Special in Excel.

As, you have just seen, the Paste Special feature lets you perform row to column (or column to row) transformations literally in a few seconds. This method also copies the formatting of your original data, which adds one more argument in its favor.

However, this approach has two drawbacks that prevent it from being called a perfect solution for transposing data in Excel:

• It is not well suited for rotating fully-functional Excel tables. If you copy the whole table and then open the Paste Special dialog, you will find the Transpose option disabled. In this case, you need either to copy the table without column headers or convert it to a range first.
• Paste Special > Transpose does not link the new table with the original data, so it is well suited only for one-time conversions. Whenever the source data change, you'd need to repeat the process and rotate the table anew. No one would want to waste their time on switching the same rows and columns over and over again, right?

How to transpose a table and link it to the original data

Let's see how you can switch rows to columns using the familiar Paste Special technique, but connect the resulting table to the original dataset. The best thing about this approach is that whenever you change the data in the source table, the flipped table will reflect the changes and update accordingly.

1. Copy the rows you want to convert to columns (or columns to be changed to rows).
2. Select an empty cell in the same or another worksheet.
3. Open the Paste Special dialog, as explained in the previous example and click Paste Link in the lower left-hand corner:
   
   You will have a result similar to this:
   
4. Select the new table and open Excel's Find and Replace dialog (or press Ctrl + H to get to the Replace tab straight away).
5. Replace all "=" characters with "xxx" or any other character(s) that does not exist anywhere in your real data.
   
   This will turn your table into something a bit scary, as you see in the screenshot below, but don't panic, just 2 more steps, and you'll achieve the desired result.
   
6. Copy the table with "xxx" values, and then use Paste Special > Transpose to flip columns to rows
7. Finally, open the Find and Replace dialog one more time to reverse the change, i.e. replace all "xxx" with "=" to restore the links to the original cells.
   

This is a bit long but elegant solution, isn't it? The only drawback of this approach is that the original formatting gets lost in the process and you will need to restore it manually (I'll show you a quick way to do this further on in this tutorial).

How to transpose in Excel using formulas

A quicker way to dynamically switch columns to rows in Excel is by using TRANSPOSE or INDEX/ADDRESS formula. Like the previous example, these formulas also keep the connections to the original data but work a bit differently.

Transpose data in Excel using VBA macro

To automate the conversion of rows to columns in Excel, you can use the following macro:

To add a macro to your worksheet, please follow the guidelines described in How to insert and run VBA code in Excel.

Switch columns and rows with the Transpose tool

If you need to perform row to column transformations on a regular basis, you may actually be looking for a faster and simpler way. Luckily, I have such a way in my Excel, and so do other users of our Ultimate Suite :)

Let me show you how to switch rows and columns in Excel literally in a couple of clicks:

1. Select any single cell within your table, go to the Ablebits tab > Transform group, and click the Transpose button.
   
2. The default settings work fine in most case, so you simply click Transpose without changing anything.

   If you'd like to Paste values only or Create links to source data to force the rotated table to update automatically with every change you make to the original table, select the corresponding option.
   

Done! The table is transposed, the formatting is preserved, no further manipulations are needed:

If you are curious to try this and 70+ other professional tools for Excel, I invite you to download a trial version of our Ultimate Suite. Thank you for reading and I hope to see you on our blog next week!