Pinterest Stumbleupon Whatsapp
Advertisement

Excel is a powerful program How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis. Follow our step-by-step demonstration to learn all about it. Read More  that can help you analyze and visualize your data Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Visualize Your Data & Make Your Spreadsheets User Friendly With An Excel Dashboard Sometimes, a simple spreadsheet format isn't engaging enough to make your data accessible. A dashboard allows you to present your most important data in an easy-to-digest format. Read More . For best results, your data needs to be in the proper format. If you need to flip the cells in a row or column, it can take a long time to do it manually—instead of re-entering all of your data, use these strategies to flip rows, turn columns into rows, and flip rows.

Flipping Cells in Excel Columns

Reversing the order of cells in an Excel column is quite easy, and can be done in just a few steps:

  1. Add a column next to the column you’d like to flip.
  2. Fill that column with numbers, starting with 1 and going up to the number of rows in the column you’d like to reverse.
  3. Select both columns and click Data > Sort. Select the column that you just added and filled with numbers. Select Largest to smallest, and click OK.

excel-column-flip

Once you’ve done this, the column you wanted flipped will be reversed, and you can delete the column you just created.

If you’re a user of Visual Basic (macros) with Excel Better Than Batch: A Windows Scripting Host Tutorial Better Than Batch: A Windows Scripting Host Tutorial Read More , you can use this code to accomplish the same thing. Just select the column you want to flip and run the following 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

Transposing Columns Into Rows (and Vice Versa)

If you have a column of numbers that you want to turn into a row, Excel has a very useful function to do just that for you with minimal effort.

Advertisement
  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 and hit OK.

excel-transpose

Your column has now been turned into a row, with the topmost value placed on the left of the row. To turn a row into a column, you follow the same procedure. You can even transpose a block of data, which will turn the entire selection by 90 degrees.

Flipping Cells in Excel Rows

To flip all of the cells in an Excel row, you need to combine the previous two steps. Because Excel doesn’t support sorting rows, you’ll need to first turn the row into a column by using the instructions for transposing above. Once the row has been turned into a column, you can use the instructions for flipping the values in a column.

After you’ve reversed the column, copy and paste it using Paste Special… and Transpose. 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 How You Can Make Your Own Simple App With VBA How You Can Make Your Own Simple App With VBA For those of you that would really love to be able to write your own application, but have never typed a single line of code before, I'm going to walk you through making your very... Read More again. Just run the same macro as above, 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

Flipping Excel Cells Made Easy

If you need to flip the data in an 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. Now get out there and flip some data!

Do you need to manage data in Excel 10 Amazingly Useful Spreadsheet Templates to Organize Your Life 10 Amazingly Useful Spreadsheet Templates to Organize Your Life Is your life a hotch-potch of missed deadlines, forgotten shopping and reneged commitments? Sounds like you need to get organized. Read More ? What strategies do you use? Share your tips below!

Leave a Reply

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