When you hear about flipping texts and data in Excel, it may seem like a one-click trivial task. Surprisingly, this is not the case as there is no built-in feature that makes this easier with the click of a button. But what if, in that special case, you want to go flip your data upside down and reverse column order? So, how exactly do you flip data and texts in Excel?
In our guide below, we explain to you how you can flip text upside down in Excel. Let’s get started.
Flipping data in Excel Upside down with the help of Column and Sort
You can easily flip your Excel text upside down by creating a help column beside your data. Next you can sort the new column Here is what to do:
1. Start by clicking on an empty cell. Type in 1 in the cell and click 2 in the empty cell below it.
2. Select the two cells and drag fill icon down until Excel automatically numbers the other cells. Do this until the end of the cells with data on the first column.
3. Click the Data tab on the main menu ribbon.
4. In the Sort & Filter group, click on the Sort Largest to Smallest icon (a Z and A below it with an arrow on the side pointing down)
5. In the displayed Sort Warning click on the checkbox “Expand the selection.”
6. Click the Sort button. You will notice the helper column and price column are reversed in order.
7. Now delete the helper column.
Flip data in Excel using Formulas
You can also flip your Excel; data by using formulas. Microsoft 365 offers users new formulas that make it easier for them to easily filter and reverse the order of column or table data in Excel. In Microsoft 365, you can use SORT BY formula, but in other Microsoft versions, you will use the INDEX formula.
INDEX (range, ROWS (range))
To use the INDEX formula, here is what to do:
Suppose you have data that range from cell A2:A12, and you want to flip the data. You will use the formula:
=INDEX($A$2:$A$12,ROWS(A2:$A$12))
The INDEX function will return the value from the cell based on the number specified in the second argument. The function will return data in reverse order.
Flipping text and columns in Excel using macro
You can also use VBA to reverse the order of your data vertically in one or several columns. Follow the steps below when using VBA code to flip data:
1. In an open Excel worksheet, open the Microsoft Visual Basic application. You can also use keyboard keys Alt + F11.
2. Click on the Insert option > Module.
3. Paste the code below in the code window.
Dim TopRow As Variant Dim LastRow As Variant Dim StartNum As Integer Dim EndNum As Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum < EndNum TopRow = Selection.Rows(StartNum) LastRow = Selection.Rows(EndNum) Selection.Rows(EndNum) = TopRow Selection.Rows(StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 Loop Application.ScreenUpdating = True End Sub
4. Run the macro code.
5. Afterward, the Flip Columns window will show up, here you can choose your preferred range to flip.
6. Select the columns you want to flip, excluding the column headers.
7. Click OK to get your results.
Use Kutools for Excel
Kutools is an Excel Addin that comes with a simple-to-use interface. It helps Excel users to use simplify work with its one-click functions. You can use this addon to flip data vertically or horizontally.
1. Download and install Kutools
2. Highlight the data you would wish to flip
3. Click on the kutools tab>range
4. Select Flip vertical/horizontal>>All
Results
Similar reads