How to Transpose Excel Data

Contents
  1. Learn how to flip cells and switch rows to columns or columns to rows
  2. How to flip column cells in Excel
  3. How to Swap Row Cells in Excel
  4. Swap only two columns or rows
  5. Swap columns and rows
  6. Use VBA to swap rows or columns (advanced)
  7. Flip columns or rows
  8. Swap two columns or rows
  9. Transpose entire range
  10. Reverse Columns and Rows in Excel

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

Excel is a powerful data analysis tool, but some things, like browsing cells in a row or column, can be very tricky. There is a slow manual way and there are quick tricks to flip rows, flip columns or convert columns to rows in seconds.

In the following article, you will learn how to quickly flip an entire column from bottom to top, swap a row from right to left, and 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

While there are many sorting options in Excel to sort cells alphabetically, there really isn’t a built-in option to easily reverse a collection of arbitrary data, such as names.

This does not mean it is impossible. It just means you have to take a few extra steps to use Excel’s sort feature to flip a column.

  1. Right click column A and choose Insert to add a new column to the left of the seller column you want to sort.

  2. Enter 1 in the first cell (row 2). Enter 2 in the second cell (line 3).

  3. Hold down a key Ctrl , place the corner of the mouse in the lower right corner of the cell with the number 2. The cursor changes to two horizontal lines. Double-click the left mouse button to auto-populate the rest of the column with the last seller’s name. This will automatically fill the rest of the column with the numbers 3 through 8.

  4. Select the entire table.

  5. On the menu At home select Sort and filter on the tape. Select Custom Sort

  6. Install for sort on meaning Column A in front of sort on meaning Cell values And Order From big to small † Click Okay

This will not only flip the entire column from bottom to top, but also cause everything in the rest of the table to change to the correct seller name.

You can now right click on column A to select it, right click on column A and select Delete to delete the numbered column.

You now have the same original table you started with, but the entire column and data has been flipped from bottom to top.

How to Swap Row Cells in Excel

What if you want to see sales data from December to January instead of January to December?

You can actually use the same approach as above to flip columns with a small change.

First, add a new line directly below the header and number these cells from 1 (before January) to 12 (before December).

Now follow the same steps to sort the table, but this time sort by row instead of column.

  1. Select only columns A through M.

  2. On the menu At home select Sort and filter on the tape. Select Custom Sort

  3. Click parameters: and choose Sort from left to right

  4. Install for sort on meaning Line 2 in front of sort on meaning Cell values And Order From big to small † Click Okay

You will now see that your entire first column is repeated with flipped cells in the header column. Since you’ve selected all the columns of data, all the rows of data are also flipped so that everything lines up perfectly.

Now click with the left mouse button on line 2 and delete the numbered line.

Swap only two columns or rows

If you want to switch to adjacent rows or columns, there is a handy mouse trick you can use in Excel to achieve it.

In this sample spreadsheet, you can change Julie Moore’s string to John Hudson’s in just a few clicks.

Here’s how you do it:

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

  2. Hold down a key Shift , move the mouse cursor to the top edge of Julie Moore’s cell. The mouse cursor changes to a crosshair.

  3. Hold down a key Shift drag the mouse to the top edge of the John Hudson cell until the line just above that line is marked with a dark line.

  4. When you release the left mouse button, the two lines switch places.

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

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

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

Swap columns and rows

What if you want to swap the entire header column with the entire seller column while preserving data integrity in the spreadsheet?

Most people prefer to do it manually, not even knowing that Excel has a built-in “transpose” function that will do it for you.

You’ll need an area where you can create your new “transposed” range of data, so create a new sheet in your spreadsheet called Sales by Month.

  1. Select the entire cell range for the entire sales data table. Click Ctrl-C to copy the entire range.

  2. Click on the new sheet you are creating. Right click call A1 and select an option transpose in section Paste options

  3. You now see the original spreadsheet, but with the header row swapped with the first column and all the data in the range ordered to align correctly.

Using Excel’s transpose feature can save you hours of manual editing 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. In fact, you can perform any of the above mentioned tasks 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 order an entire row from left to right or a column from top to bottom, you can create a function Flip_Columns() or Flip_Rows() for this.

To generate this code, select the menu Developer and choose View code

If Developer isn’t in the menu, you can add one. Click File Click parameters: and choose Customize ribbon † Search in this window Developer in the left pane and add it to the right. Click Okay and the Developer menu item appears.

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

 Sub Flip_Rows () 
Dim vTop как вариант
Dim vEnd как вариант
Dim iStart как целое число
Dim iEnd как целое число
Application.ScreenUpdating = False
iStart = 1
iEnd = Selection.Rows.Count
Сделать, пока iStart 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 with cells in a row by flipping all the columns in that row using the following code.

 Sub Flip_Columns () 
Dim vLeft как вариант
Dim vRight как вариант
Dim iStart как целое число
Dim iEnd как целое число
Application.ScreenUpdating = False
iStart = 1
iEnd = Selection.Columns.Count
Делать пока iStart 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 any of these VBA scripts by selecting the row or column you want to rotate, going to the code editor window, and clicking the launch icon in the menu.

These VBA scripts are a quick method to flip cells in a column or row with a single click, but they won’t flip cells in 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 ​​in a worksheet by selecting two values ​​and running the following VBA code.

 Sub Swap () 
Для i = 1 в 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 are next to each other or on top of each other. Remember, you can only change two cells with this code.

Transpose entire range

You can use VBA to take a selection from a sheet (as in the scripts above), transpose the range, and then paste it somewhere else (or in another sheet).

There is even a method for this in VBA:

 Set DestRange = Application.WorksheetFunction.Transpose (SelectedRange) 

However, doing this in VBA is a lot more work than it’s worth because as you’ve seen earlier in this article, transposing a whole range is nothing more than copying and pasting that range with just a few clicks.

Reverse Columns and Rows in Excel

As you can see, it is very easy to swap columns and rows, change cells or transpose an entire range in Excel.

Once you know what your data should look like, you can choose the best option and flip or transform those cells in just a few easy steps.

Leave a Reply

Your email address will not be published.