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

Often, we need to mirror data in Excel. This article will show you 4 quick ways to flip data in Excel upside down. We will implement two formulas, one command, and one VBA code to do so.


Download Practice Workbook


4 Handy Approaches to Flip Data Upside Down in Excel

To demonstrate the methods, we have selected a dataset with 3 columns: “Name”, “State”, and “City”. We have slightly altered this dataset whenever necessary.

How to Flip Data in Excel Upside Down


1. Using Sort Feature to Flip Data Upside Down in Excel

We will flip the data using the Sort feature in this first method. First, we will insert numbers in ascending order, then sort them in descending order to flip the data.

Steps:

  • To begin with, create a new column named “No.”.

How to Flip Data in Excel Upside Down 2

  • Then, type the number from 0 to 5. You can choose any number in ascending order.

How to Flip Data in Excel Upside Down 3

  • Next, select the cell range D5:D10.
  • Afterward, from the Data tab → select “Sort Z to A” under the Sort & Filter section.

How to Flip Data in Excel Upside Down 4

  • After that, a warning message will pop up.
  • Select “Expand the selection” and press Sort.

How to Flip Data in Excel Upside Down 5

  • Finally, this will sort the data and consequently, it will flip the data upside down.

How to Flip Data in Excel Upside Down 6

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


2. Combining INDEX and ROWS Functions to Flip Data Upside Down

We will merge the INDEX and ROWS functions to create a formula to flip data in the vertical direction.

How to Flip Data in Excel Upside Down 7

Steps:

  • Firstly, type the following formula in cell E5.

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

How to Flip Data in Excel Upside Down 8

  • Secondly, press ENTER. This will return the last value from the “Name” column to the first row.
  • Then, drag the Fill Handle downward to AutoFill the formula.
  • After that, drag the result to the right side.

How to Flip Data in Excel Upside Down 9

Formula Breakdown

  • To begin with, the INDEX function returns an output from the range B5:B10.
  • Here, the cell is specified by the ROWS function. The range B5:B$10 will return 6.
  • Then, in the next formula, it will be B6:B$10, which will return 5. Notice that the last value of the range is fixed. That is why the output will get smaller each time.
  • Thus, this formula works to flip data.
  • Finally, the output will look similar to this.

How to Flip Data in Excel Upside Down 10

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


Similar Readings


3. Flip Data Upside Down by Merging SORTBY and ROW Functions

In this section, we will combine the SORTBY and ROW functions to create a formula to flip data upside down.

Steps:

  • To begin with, type this formula in cell E5.

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

How to Flip Data in Excel Upside Down 11

  • Then, press ENTER. So, this will show the output from the formula.

How to Flip Data in Excel Upside Down 12

Formula Breakdown

  • At first, we selected the full range of our data which is B5:C10.
  • Then, we input the values from 5 to 10 inside the ROW(B5:B10) portion.
  • Lastly, we typed -1 to sort it in descending order.

Read More: How to Flip Data in Excel Chart (5 Easy Methods)


4. Applying VBA to Flip Data Upside Down in Excel

We will use an Excel VBA Macro to flip data upside down in Excel. Here, we will use the For Next Loop to go through each row and swap it with the relevant row. Moreover, the user will select the cell range without the header row using an InputBox.

Steps:

  • First, bring up the VBA Module window, where we type our codes.
  • So, press ALT+F11 to bring up this. Alternatively, from the Developer tab → select Visual Basic to do this.
  • Hence, the VBA window will pop up.
  • Next, from the Insert tab, select Module.
  • Here, we insert VBA code into Excel.

Insert Module

  • After that, type the following code inside the VBA Module window.
Option Explicit
Sub Flip_Data_Upside_Down()    
    'Declaring the Variables
    Dim cell_Range As Range
    Dim cell_Array, temp_Array As Variant
    Dim x1 As Integer, x2 As Integer, x3 As Integer    
    On Error Resume Next  'Ignoring all errors and continue
    'Setting user input
    Set cell_Range = Application.InputBox("Select the Range" _
    & " Without Header Row", "ExcelDemy", Type:=8)
    cell_Array = cell_Range.Formula
    Application.ScreenUpdating = False
    'Looping through the selected cell range
    For x2 = 1 To UBound(cell_Array, 2)
        x3 = UBound(cell_Array, 1)
            For x1 = 1 To UBound(cell_Array, 1) / 2
                temp_Array = cell_Array(x1, x2)
                cell_Array(x1, x2) = cell_Array(x3, x2)
                cell_Array(x3, x2) = temp_Array
                x3 = x3 - 1
            Next
    Next
    cell_Range.Formula = cell_Array
    Application.ScreenUpdating = True
End Sub

VBA Macro

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Flip_Data_Upside_Down.
  • Secondly, we are assigning the variable types.
  • Thirdly, we are ignoring all the errors using the “On Error Resume Next” statement.
  • Next, the user defines the working cell range using the InputBox method.
  • Then, we use the For Next Loop to go through the selected cell range.
  • Finally, the rows are swapped with the relevant rows to flip it upside down.
  • Thus, this code works.
  • Afterward, Save the Module.
  • Then, put the cursor inside the Sub procedure and press Run.

Code Run

  • Afterward, this code will ask the user to input the range.
  • Then, select the cell range B5:D10 and press OK.

Input Box

  • By doing so, it will flip the selected range vertically.

Output of Method 4

Read More: How to Flip Axis in Excel (4 Easy Methods)


Things to Remember

  • The SORTBY function is only available in the Excel 365 and Excel 2021 versions. For earlier versions, you can use method 2.
  • To preserve the formatting, you can use method 1.
  • If there are existing values within the spill range, then it will show a “#SPILL!” error.

Practice Section

We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.

Practice Dataset


Conclusion

We have shown you four quick ways to flip data in Excel upside down. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo