You are at the perfect place if you are looking to flip data horizontally in Excel. In this post, we have covered several simple techniques to flip data horizontally. Consequently, stick with us and follow the instructions.
Assume that you have a table as shown below and you want to flip this data horizontally.
Or, you have a dataset like the below image. In this article, we are going to solve the problem conveniently.
1. Using Sort Command to Flip Data Horizontally in Excel
In the first method, we are going to sort horizontally with the simple built-in Sort command under the Data tab. Just expressed in the images below.
- Select any cell in the dataset.
- After hitting the Sort, this pop-up will appear and then we will follow the steps shown lower down. As we want to flip data horizontally, we have to click Options…
- After that check ‘Sort left to right’ and hit ‘OK’.
- Now, as in step:1, we have selected data that we want to sort. Then, we choose the Row Sort by(Age in Row 5) option in step:2 and like in step:3, order (Smallest to Largest) and press the button ‘OK’.
- And we have got the ascending order of ages of some people all over the world.
2. Transpose Twice and Sort with a Helper Command
In many cases, we will not have the data shown in Method 1. If not, we will transpose the data table and then sort it with our requirement and after that, we will again transpose to get our original dataset but this time with a sorted version.
- First of all, we will select the table and then press Ctrl+C (Copy the table) and select a cell with a mouse right click and then go to Paste Special…
- Now we will add a Helper Column in the last column of the table as shown in the image below.
- We again transpose this data table by copy (Ctrl+C) and Paste Specials (where the transpose option is available)
- Additionally, we will go through the steps as shown in the below image. In the first step, we will select the data we prefer to sort. After that, we will go to Sort under the Data tab.
- We then select Sort By (in this case COLUMN O) and Order (Largest to smallest) and click ‘OK’ .
- Once again, we again transpose this data table by copy (Ctrl+C) and Paste Specials (where the transpose option is available)
- Now we got the table that we started our journey with but in this case from largest to smallest ages.
3. Applying Excel VBA Code to Flip Data Horizontally
If you are a VBA freak like me, you may not oppose using a VBA code to flip data! Just execute the following simple steps.
- Select the table that is required to flip.
- Then press Alt+F11 and the box shown below will appear.
- Copy the below VBA code below and paste it into the pop-up dialogue box. And hit F5.
Sub FlipDataHorizontally() Dim xRng As Range Dim WrkRng As Range Dim ArY As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next xTitleId = "Horizontally Flipping Data " Set WrkRng = Application.Selection Set WrkRng = Application.InputBox("Range", xTitleId, WrkRng.Address, Type:=8) ArY = WrkRng.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 1 To UBound(ArY, 1) k = UBound(ArY, 2) For j = 1 To UBound(ArY, 2) / 2 xTemp = ArY(i, j) ArY(i, j) = ArY(i, k) ArY(i, k) = xTemp k = k - 1 Next Next WrkRng.Formula = ArY Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
- The flipped table will be the image shown below.
Download Practice Workbook
You can download the practice workbook from the following download button.
I hope these strategies will simplify your projects or jobs. Additionally, if you aspire to master this topic, you may download the workbook and use it for your own practice. Please let me know in the comment section if you have any queries, concerns, or suggestions.