Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Flip Data Vertically in Excel (4 Quick Methods)

The article will show you how to flip data vertically in Excel. If a user wants to reverse his data so that the data are stored from bottom to top in the Excel sheet, he or she may need to use the feature or formula for flipping data vertically. There are easy methods in Excel to do this. We will be discussing these methods in the later sections of this article.


Download Practice Workbook


4 Ways to Flip Data Vertically in Excel

The dataset contains personal information about some folks.

how to flip data vertically in excel

Let’s go through the following sections to see how to flip vertically these data.


1. Combining INDEX and ROWS Functions to Flip Data Vertically

We will show you how to use the INDEX and ROWS functions to flip data vertically in a dataset in this section. Let’s have a look at the following description for a better understanding. We will not use the Serial column in this example.

Steps:

  • First, make new columns to store the vertically flipped data and type the following formula in cell E5.

=INDEX(B$5:B$10,ROWS(B5:B$10))

Combining INDEX and ROWS Functions to Flip Data Vertically

The formula will return the bottom name of column B.

  • After that, press the ENTER button and you will see the last name of the dataset in E5 which is Anna-diane.

  • Thereafter, drag the Fill icon to the right to AutoFill the adjacent column.

Combining INDEX and ROWS Functions to Flip Data Vertically in Excel

Thus you can flip data vertically in Excel using INDEX and ROWS functions.

Read More: How to Flip Data Upside Down in Excel (4 Ways)


2. Applying SORTBY and ROW Functions to Vertically Flip Data

We can also use the SORTBY and ROW functions to flip data vertically. The steps of the procedure are given below. We will not use the Serial column in this example.

Steps:

  • First, make new columns to store the vertically flipped data and type the following formula in cell E5.

=SORTBY($B$5:$C$10,ROW(B5:B10),-1)

Applying SORTBY and ROW Functions to Vertically Flip Data

The formula will return an array with the vertically flipped data of the Name and Profession columns.

  • After that, hit the ENTER

Thus you can flip data vertically in Excel using the SORTBY and ROW functions.

Note: You will need Excel 365 (Office 365) or web version of Excel to use the SORTBY function.

Read More: How to Flip Data in Excel from Bottom to Top (4 Quick Methods)


Similar Readings


3. Vertically Flip Data Using Excel Sort Feature 

Another way of flipping data vertically in Excel is to apply the Sort feature. We can easily do this by using a column with serial numbers. Let’s go through the process below.

Steps:

  • First, select the cells B5 to B10 and then select Home >> Sort & Filter >> Sort Largest to Smallest.

Using Excel Sort Feature to Flip Data Vertically

  • After that, you will see a warning message. Make sure you select Expand the selection (if you deliberately don’t want to go with only the selected portion) and click on Sort.

There you go, you will see your data flip vertically after this operation.

Using Excel Sort Feature to Flip Data Vertically

Read More: How to Flip Data from Horizontal to Vertical in Excel (6 Methods)


4. Using VBA to Flip Data Vertically

We can also use Microsoft Visual Basic for Applications (VBA) to flip Excel data vertically. Let’s see the procedure.

Steps:

  • First, go to the Developer Tab and then select Visual Basic or press ALT+F11.

Opening Visual Basic Window

  • After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

  • Now, type the following code in the VBA Module.
Sub FlipDataVertically()
    Dim mn_Cell_Range As Range
    Dim mn_Cell_Array, mn_Temp_Array As Variant
    Dim a1, a2, a3 As Integer
    On Error Resume Next
    Set mn_Cell_Range = Application.InputBox("Select the Range" _
    & " Without Header Row", "ExcelDemy", Type:=8)
    mn_Cell_Array = mn_Cell_Range.Formula
    Application.ScreenUpdating = False
    For a2 = 1 To UBound(mn_Cell_Array, 2)
        a3 = UBound(mn_Cell_Array, 1)
            For a1 = 1 To UBound(mn_Cell_Array, 1) / 2
                mn_Temp_Array = mn_Cell_Array(a1, a2)
                mn_Cell_Array(a1, a2) = mn_Cell_Array(a3, a2)
                mn_Cell_Array(a3, a2) = mn_Temp_Array
                a3 = a3 - 1
            Next
    Next
    mn_Cell_Range.Formula = mn_Cell_Array
    Application.ScreenUpdating = True
End Sub

Using VBA to Flip Data Vertically

VBA Code Breakdown

  • Firstly, we named the Sub procedure FlipDataVertically.
  • After that, we defined the variable types.
  • Thereafter, we use the “On Error Resume Next” statement to ignore all the errors.
  • The user then specifies the working cell range using the InputBox method.
  • Next, the For Next Loop is then used to loop through the selected cell range.
  • After that, we exchange the rows with the relevant rows to flip them vertically.
  • Finally, we run the code.
  • After that, go back to your sheet and run the Macro.

Running VBA Code to Flip Data

  • The execution of this operation will show you a message box, asking you to select the range that you want to flip vertically. Select the range and click OK.

After that, you will see your Excel data flip vertically.

Vertically Flipped Data

Read More: How to Flip Rows in Excel (4 Simple Methods)


Practice Section

Here, we’re giving you the dataset of this article so that you can practice these methods on your own.


Conclusion

In the end, hope you will learn the basic ways of how to flip data vertically in Excel after reading this article. If you have any better suggestions or questions or feedback regarding this article, please share them in the comment box. This will help us enrich our upcoming articles and provide you best.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo