Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Flipping Data

Many people use Excel as a simple database manager, entering information in different rows of a worksheet. As you are working with your data tables, you may come across a need to reverse the order of the rows in the table. Thus, if you have a table with ten rows, the rows would go from ten to one instead of one to ten.

There is no intrinsic function in Excel that allows you to flip data in this manner. However, you can use the sorting capabilities of Excel to accomplish the same thing by following these general steps:

  1. Insert a new column immediately to the left of your data table.
  2. In the cells of the new column, enter the numbers 1 through however many rows there are in your table.
  3. Select the rows that make up your data table.
  4. Choose Sort from the Data menu. Excel displays the Sort dialog box. (See Figure 1.)
  5. Figure 1. The Sort dialog box.

  6. In the Sort By drop-down list, indicate you want to sort by your newly created column.
  7. Click Descending as the type of sort.
  8. Click on OK. Excel reorders your data in the reverse order of what it was.

If you have to do a lot of data flipping on a daily basis, using the above steps can get rather tiring. In this case, you may want to create a macro to do the job for you. The following macro, FlipRows, will do the trick:

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

In order to use this macro, all you need to do is select the rows you want flipped and run it. The macro will not change your data, other than flipping the rows. In other words, it will not add any columns of information.

An interesting feature of this approach is that you can quickly adapt it to flipping columns of data. All you need to do is change all occurrences of the word "Rows" to "Columns." Thus, the following becomes the new 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

Again, simply select the columns you want to flip and then run the macro.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2653) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Comments for this tip:

arif    25 Jul 2014, 17:26
thank you thank you thank you.
i had some old training in VBA for excel and your code to reverse columns worked flawlessly for me.
Ian    28 Feb 2014, 15:42
ran to flip rows ok but all fomula in cells are gone, only numbers are left.

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 4+5 (To prevent automated submissions and spam.)
 
          Commenting Terms
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2013)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2013)

Our Products

Premium Newsletters

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2014 Sharon Parq Associates, Inc.