How to Transpose Excel Data

Learn how to flip cells and switch rows to columns or columns to rows

A woman using a calculator and a computer

lovelyday12/Getty Images

Excel is a powerful tool for analyzing data, but some things like flipping cells in a row or column can be a real hassle. There's the slow manual way, and there are quick tricks to flip rows, flip columns, or transpose columns into rows in just seconds.

In the following article, you'll learn how to quickly flip an entire column from bottom to top, swap a row from right to left, and how to completely swap rows with columns.

These instructions apply to Microsoft Excel 2010, 1013, 2016, 2019, and Excel for Office 365.

How to Flip Column Cells in Excel

Screenshot of a complete sales spreadsheet in Excel.

While there are lots of sorting options in Excel for organizing cells alphabetically, there isn't really a built-in option to simply flip a collection of random data, like names.

That doesn't mean it's imposslble. It just means you need to follow a couple extra steps in order to use Excel's sort feature to flip the column.

  1. Right-click column A, and select Insert to add a new column to the left of the Salesperson column you want to sort.

  2. Type 1 in the first cell (row 2). Type 2 into the second cell (row 3).

  3. Hold down the Cntrl key and place the mouse corner on the lower right corner of the cell with the number 2. The cursor will change to two horizontal lines. Double-left click the mouse to auto-fill the rest of the column down to the last salesperson's name. This will autofill the rest of the column with numbers from 3 through 8.

  4. Highlight the entire table.

  5. In the Home menu, select Sort & Filter from the ribbon. Select Custom Sort.

  6. Set Sort By to Column A, Sort On to Cell Values, and Order to Largest to Smallest. Click OK.

This will not only flip the entire column from bottom to top, but it'll make sure all of the data in the rest of the spreadsheet swap to match the correct salesperson's name.

A screenshot of the Excel column data flipped.

Now you can right click column A to select it, right-click Column A, and select Delete to remove the numbered column.

Now you have the same original table you started with, but with the entire column and data flipped bottom to top.

How to Swap Row Cells in Excel

What if you want to see sales data from December to January, rather than January to December?

You can actually use the same approach as above for flipping columns, with a slight variation.

First, add a new row just under the header, and number those cells from 1 (under January) to 12 (under December).

A screenshot of a new numbered row under the header in Excel.

Now follow the same steps to sort the table, but this time you'll sort by row rather than by column.

  1. Highlight only columns A through M.

  2. In the Home menu, select Sort & Filter from the ribbon. Select Custom Sort.

  3. Click Options, and select Sort left to right.

  4. Set Sort By to Row 2, Sort On to Cell Values, and Order to Largest to Smallest. Click OK.

Now you'll see that your entire first column is resorted with the cells in the header column flipped. Since you highlighted all columns that contained data, all the data rows get flipped as well, so that everything lines up perfectly.

A screenshot of the header row with cells flipped in Excel.

Now left click on row 2 and delete the numbered row.

Swap Only Two Columns or Rows

If you want to flip to adjoining rows or columns, there's a useful mouse trick you can use in Excel to accomplish this.

In this example spreadsheet, you can swap the Julie Moore row with the John Hudson row with just a few clicks of the mouse.

Here's how you do it:

  1. Highlight the entire row with Julie Moore in column A.

  2. Hold down the Shift key, and move the mouse cursor to the top edge of the Julie Moore cell. The mouse cursor will change to crosshairs.

  3. With the Shift key still held down, drag the mouse to the upper edge of the John Hudson cell until the line just above that row highlights with a dark line.

  4. When you release the left mouse button, the two entire rows will swap.

If you want to swap columns instead of rows, you can highlight one column, and then follow the same procedure.

The only difference is that you drag the mouse cursor to the side until the line after the column you want to swap with highlights with a dark line.

You can swap multiple rows or columns using this same technique. You just need to highlight multiple rows or columns in the first step, and then follow the same procedure.

How to Swap Columns and Rows

A screenshot of the sheet transposed with rows and columns swapped

What if you want to swap the entire header column with the entire Salesperson column, while maintaining the integrity of the data in the spreadsheet?

Most people opt to do this manually, without realizing that Excel has a built-in feature called "transpose" that'll do it for you.

You're going to need an area where you can create your newly "transposed" range of data, so create a new sheet in your spreadsheet called "Sales By Month".

  1. Highlight the entire range of cells for your entire table of sales data. Press Cntrl-C to copy the entire range.

  2. Click in the new sheet you create. Right click call A1, and choose the Transpose option from Paste Options.

  3. Now you'll see your original spreadsheet, but with the header row swapped with the first column, and all of the data in the range organized to line up correctly.

Using Excel's transpose feature can save you hours of manual editing work when you want to reorganize your spreadsheets and view data in different ways.

Use VBA to Swap Rows or Columns (Advanced)

VBA is a powerful feature in Excel. You can actually perform each of the tasks above with very simple VBA code.

Then convert your VBA function into a custom Excel function that you can call whenever you want to swap rows or columns.

Flip Columns or Rows

To reorganize an entire row from left to right, or a column from top to bottom, you can create a Flip_Columns() or Flip_Rows() function to accomplish this.

To create this code, select the Developer menu and select View Code.

If Developer is not listed in the menu, you can add it. Click on File, click Options, and select Customize Ribbon. In this window, find Developer on the left pane and Add it to the right. Click OK and the Developer menu option will appear.

To Flip the rows of a selected column in your sheet, you can use the following VBA code:

Sub Flip_Rows()
Dim vTop As Variant
Dim vEnd As Variant
Dim iStart As Integer
Dim iEnd As Integer
Application.ScreenUpdating = False
iStart = 1
iEnd = Selection.Rows.Count
Do While iStart < iEnd
vTop = Selection.Rows(iStart)
vEnd = Selection.Rows(iEnd)
Selection.Rows(iEnd) = vTop
Selection.Rows(iStart) = vEnd
iStart = iStart + 1
iEnd = iEnd - 1
Loop
Application.ScreenUpdating = True
End Sub

You can do the same thing with the cells in a row by flipping all of the columns in that row using the following code.

Sub Flip_Columns()
Dim vLeft As Variant
Dim vRight As Variant
Dim iStart As Integer
Dim iEnd As Integer
Application.ScreenUpdating = False
iStart = 1
iEnd = Selection.Columns.Count
Do While iStart < iEnd
vTop = Selection.Columns(iStart)
vEnd = Selection.Columns(iEnd)
Selection.Columns(iEnd) = vRight
Selection.Columns(iStart) = vLeft
iStart = iStart + 1
iEnd = iEnd - 1
Loop
Application.ScreenUpdating = True
End Sub

You can run either of these VBA scripts by selecting the row or column you want to reverse, going into the code editor window, and clicking on the run icon in the menu.

These VBA scripts are a fast method to reverse cells in a column or row with just one click, but they won't flip the cells in any related data rows, so use these scripts only when you want to flip a column or row and nothing else.

Swap Two Columns or Rows

You can swap any two values on a sheet by selecting the two values and running the following VBA code.

Sub Swap()
For i = 1 To Selection.Areas(1).Count
temp = Selection.Areas(1)(i)
Selection.Areas(1)(i) = Selection.Areas(2)(i)
Selection.Areas(2)(i) = temp
Next i
End Sub

This VBA code will swap any two cells, whether they're side by side or one on top of the other. Just keep in mind you can only swap two cells with this code.

Transpose an Entire Range

It is possible to use VBA to take a selection from a sheet (just like in the scripts above), transpose the range, and then paste it somewhere else (or in a different sheet).

There is even a method in VBA for this purpose:

Set DestRange = Application.WorksheetFunction.Transpose(SelectedRange)

However, doing this in VBA is much more work than it's worth, since as you saw earlier in this article transposing an entire range is no more than copying and pasting that range with just a few clicks of the mouse.

Flipping Columns and Rows in Excel

As you can see, flipping columns and rows, swapping cells, or transposing an entire range is very easy in Excel.

Once you know what you want your data to look like, you can choose the best option and flip or convert those cells in just a few, simple steps.