Rafiul Haq
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.
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.”.
- Then, type the number from 0 to 5. You can choose any number in ascending order.
- Next, select the cell range D5:D10.
- Afterward, from the Data tab → select “Sort Z to A” under the Sort & Filter section.
- After that, a warning message will pop up.
- Select “Expand the selection” and press Sort.
- Finally, this will sort the data and consequently, it will flip the data upside down.
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.
Steps:
- Firstly, type the following formula in cell E5.
=INDEX(B$5:B$10,ROWS(B5:B$10))
- 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.
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.
Read More: How to Flip Rows in Excel (4 Simple Methods)
Similar Readings
- How to Flip Columns and Rows in Excel (2 Easy Methods)
- Change Excel Sheet from Right to Left (4 Suitable Ways)
- How to Flip Excel Sheet from Left to Right (4 Easy Ways)
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)
- Then, press ENTER. So, this will show the output from the formula.
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.
- 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 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.
- Afterward, this code will ask the user to input the range.
- Then, select the cell range B5:D10 and press OK.
- By doing so, it will flip the selected range vertically.
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.
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!
