Productivity

Excel Quick Tips: How to Flip Cells & Switch Rows or Columns

Anthony Grant Updated 24-02-2020

Microsoft Excel is a powerful program that can help you analyze and visualize data. To get the best results your data needs to be in the proper format.

Advertisement

Occasionally you will run into data that is placed in a table, and you may not be able to work with the arrangement of rows and columns. Perhaps you have a column or row that you need to reverse entirely.

Unlock the "Essential Excel Formulas" cheat sheet now!

This will sign you up to our newsletter

Enter your Email

Flipping cells in a row or column can be a lot of work to do manually. Instead of re-entering all your data you can use these strategies to flip columns, turn columns into rows, and flip rows.

How to Flip Cells in Excel Columns

At first glance, there is no good way to flip columns upside down. Data sorting can sort by values or alphabetically but that doesn’t invert a column in Excel.

So what can you do? Copy the data and paste one by one? There’s a much better way to get this done.

Reversing the order of cells in a Microsoft Excel column is easy, and can be done in just a few steps with this nifty trick:

  1. Add a column to the left of the table you’d like to flip.
  2. Fill that column with numbers, starting with 1 and using the fill handle to create a series of numbers that ends at the bottom of your table.
  3. Select the columns and click Data > Sort. Select the column that you just added and filled with numbers. Select Largest to Smallest, and click OK.

Flipping Excel Table Columns

The columns are now inverted!

This is a useful trick to add to your toolbox when you’re managing columns in Excel How to Manage Columns in Excel Do you need to add, move, hide, or change columns in a complex spreadsheet? Don't panic. We'll show you basic Excel column operations to organize your data. Read More .

Inverting Excel Table Using Data Sort

If you like working with Excel shortcuts you can perform this trick with one click. This shortcut can perform a quick sort by the leftmost column; Either by smallest-to-largest or largest-to-smallest.

The real key to all this is the index column.

Quick Sort Excel to Invert Columns

Once you’ve got the data reversed you can delete the index column and you’re ready to go. This trick was done on an Excel table but it works on any number of columns you want to invert.

If you use Visual Basic (macros) with Excel How to Record a Macro in Excel 2016 Did you know that you could automate repetitive tasks in Microsoft Excel by using macros? We'll show you how to record a macro in Excel 2016 to save lots of time. Read More , you can use this code to accomplish the same thing. Just select the column you want to flip and run this macro:

Sub FlipColumns()
 Dim vTop As Variant
 Dim vEnd 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) = vTop
 Selection.Columns(iStart) = vEnd
 iStart = iStart + 1
 iEnd = iEnd - 1
 Loop
 Application.ScreenUpdating = True
End Sub

VBA Macros are very useful but if you’re not comfortable with them you can get results instantly using the data sorting trick.

If VBA does pique your interest you can learn more about it with a beginner’s tutorial on writing VBA macros A Beginner's Tutorial on Writing VBA Macros in Excel (And Why You Should Learn) If you use Excel regularly, it's worth learning how to create VBA macros and get access to many more functions and capabilities. Read More .

How to Switch Columns and Rows

Reversing a column has some use for data analysis but there are more ways you can manipulate data.

Another change you may want to make is switching the columns and the rows. That is, turn the column data into a row or row data into a column.

Just like inverting tables, copying and pasting information one by one is not the way to do it. Microsoft Excel has a very useful function to get this done.

  1. Select the columns you’d like to transpose and hit Ctrl+C or Edit > Copy.
  2. Click to an open cell and click Edit > Paste Special…
  3. Select Transpose

Transpose Excel Data Table to Switch Rows and Columns

Your column has now been turned into a row, with the topmost value placed on the left of the row. You might have a long row so make sure you’ve got some room.

Transposed Excel Column Into Row Using Paste Special

It works in the reverse order as well—you can transpose a row into a column. You can even transpose a block of data, which will turn the entire selection by 90 degrees.

How to Flip a Row in Excel

Now that you know the trick to flipping columns and you’ve seen the way to turn a column into a row; What about flipping a row in Excel?

To flip the cells in an Excel row you will use both of the tricks you learned together.

Because Microsoft Excel doesn’t support sorting rows, you’ll need to first turn the row into a column by using transpose. Once the row has been turned into a column, you can use the first trick to invert the column.

After you’ve reversed the column, transpose it back into a row and you will have inverted the entire row. It’s a rather roundabout way to get it done, but it works!

To do this in a far simpler manner, you can use Visual Basic again. Just run the same macro as before, but replace all of the instances of the word column with the word row.

Sub FlipRows()
 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

Why Flip Rows and Columns?

Excel is widely used in data analysis. Data analysis and data visualization What Is Data Analysis and Why Is It Important? What is data analysis? We explain data mining, analytics, and data visualization in simple to understand terms. Read More are becoming a critical part of decision making in many different ways.

It’s not just in computer science, data analysis is used in:

  • Business
  • Marketing
  • Sports
  • Medicine
  • Real Estate
  • Machine Learning

Often times information used to analyze data will not be organized the way you might like. In order to get the best results, you should know how to set up data for processing.

Database tools like SQL are great for working with data, but they won’t organize your data for you. Excel can do this and give you results in Excel or Common Separated Value (CSV) files.

Knowing these tricks to flip columns and rows get you one step closer to getting the most out of your data.

Flip Data in Excel Easily

If you need to flip the data in a Microsoft Excel column or row, using one of the strategies above can save you a whole lot of time. And if you need to do it on a regular basis, using the Visual Basic macros will save you even more time. You’re ready to start getting your data to work for you!

Of course, there’s plenty more you can do with Excel. Check out these crazy Excel formulas that do amazing things or review our essential Excel formulas and functions cheat sheet The Essential Microsoft Excel Formulas and Functions Cheat Sheet Download this Excel formulas cheat sheet to take a shortcut through the world's favorite spreadsheet program. Read More .

Explore more about: Microsoft Excel, Microsoft Office 2016, Microsoft Office 2019, Microsoft Office 365, Microsoft Office Online, Spreadsheet.

Whatsapp Pinterest

Enjoyed this article? Stay informed by joining our newsletter!

Enter your Email

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Jason
    February 26, 2020 at 7:12 pm

    Stop calling software that has a free 7 day trial or similar free. It is infuriating.

  2. Seth
    January 29, 2018 at 4:55 pm

    Great tip for flipping a column!

  3. ronald moore
    November 3, 2017 at 9:27 pm

    You rock!

  4. Kevin
    February 17, 2016 at 7:54 pm

    Hi,
    The sorting works fine but I need an extra step... If you could advise.
    My project involves gathering random rows between 1 & 12. Then flipping them upside down. Then retrieving certain data from them. Simple lookups gather the info but, for example if I have 3 rows, (then flipped they become 12,11 and 10) but I would need row 10 to be at the top for the lookups to work. I've tried INDEX but to no avail :( any advice?

  5. jonathan
    January 13, 2016 at 6:35 pm

    If you have a lot of rows, start your new column with "1", then make the next value "=A1+1" (If A1 is where "1" is). Copy+Paste this formula down the entire column, then do your sort. No VBA required.

  6. Anonymous
    November 10, 2015 at 12:16 am

    This doesn't work for sorting random groups of numbers. Using your sort method would just sort both columns from greatest to lowest or whatever. Do you have any ideas for flipping columns of numbers?

    • Dann Albright
      November 10, 2015 at 4:29 am

      I'm pretty sure this does work for random numbers; the column that's being sorted is the one next to your column of numbers, not the random ones you've entered. Do you know what I mean? Or maybe I'm not understanding the problem you're having. Can you give me any more details?

      • HikingMike
        June 9, 2017 at 4:18 pm

        The problem is if there is no column of ordered numbers. He probably just wants to switch the order of the values, and there isn't any A-Z or whatever that sorting can go by. I suppose you could manually add a column of increasing numbers or a formula to number them like you said above. Some extra work required. I was able to do this. Though after swapping, the column with numbers derived from formula is broken.

        I am still curious if there is a way to reverse the order without any column that is in some kind of ascending/descending order already.

  7. Maria
    May 21, 2015 at 3:34 pm

    Impressive solution! So simple but I hadn't imagined it, thanks! !

    • Dann Albright
      May 23, 2015 at 8:49 am

      Glad you found it useful! It's saved me a lot of time.

  8. Bob
    March 3, 2015 at 3:19 pm

    Actually, you can sort Excel data in rows using the menu. When you click the "Sort" button and then select "Options...". Click the "Sort left to right" radio button.

    • Dann Albright
      March 4, 2015 at 8:54 am

      Right, but if you can do it the hard way, why would you choose to do it the easy way?

      Thanks for pointing that out. I was completely unaware of that option.