Aung Shine
Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to flip the cell values in a row of a dataset to better analyze. We can perform the task very easily using some features and functions in excel. This article will show you 4 simple methods to Flip Rows in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
4 Simple Methods to Flip Rows in Excel
MS Excel is a very useful program. Often, we may have to flip the contents of a row for some reason. It’s easy to do that in excel. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents Salesman, Product, and Net Sales in respective columns. Here, we’ll rearrange the order of the columns. That means, we’ll flip the contents of the rows. Therefore, go through the methods below to perform the task.
1. Flip Rows in Excel with Paste Transpose Feature
In our first method, we’ll apply the Paste Transpose feature. And we’ll introduce a helper column to get our job done. So, follow the steps below to carry out the operation.
STEPS:
- First, copy the range B4:D7 by pressing the Ctrl and C keys together.
- Then, choose cell B9 to paste it there.
- After that, right-click the mouse.
- In the pop-out context menu, select Paste Transpose.
- Thus, you’ll see the dataset as shown in the following figure.
- Now, we’ll insert a helper column.
- For this reason, input 1,2, and 3 in the range F9:F11.
- Next, select the range B9:F11.
- Afterward, go to Home ➤ Editing ➤ Sort & Filter ➤ Custom Sort.
- As a result, the Sort dialog box will pop out.
- In the Sort by section, choose Column F in Column, Cell Values in Sort On, and Largest to Smallest in Order fields.
- Uncheck the box for My data has headers.
- Press OK.
- Consequently, your data will appear as it’s displayed in the below image.
- Delete the original data.
- Again, copy the range B9:F11.
- Subsequently, click cell B4.
- Apply the Paste Transpose.
- Lastly, delete the helper row.
- Thus, you’ll get the required outcome.
- Look at the following picture where we have our final result.
Read More: How to Flip Columns and Rows in Excel (2 Easy Methods)
2. Use Excel Sort Feature for Flipping Rows
This time, we’ll use the Sort feature. Hence, learn the following steps.
STEPS:
- Firstly, introduce a helper row in row 8.
- Then, select the range B8:D8.
- Next, click Home ➤ Editing ➤ Sort & Filter ➤ Custom Sort.
- Consequently, the Sort dialog box will appear.
- Uncheck the box for My data has headers.
- Press Options.
- Click the circle to Sort left to right.
- Press OK.
- Subsequently, choose Row 8 in Row, Cell Values in Sort On, and Largest to Smallest in Order fields.
- Press OK.
- Finally, it’ll return the desired output.
Read More: How to Flip Excel Sheet from Left to Right (4 Easy Ways)
Similar Readings
- How to Flip Axis in Excel (4 Easy Methods)
- Flip Data in Excel from Bottom to Top (4 Quick Methods)
- How to Flip Data Upside Down in Excel (4 Ways)
3. Apply VBA to Flip Rows in Excel
Moreover, we can apply excel VBA for flipping rows. Now, follow the below process to use VBA.
STEPS:
- First of all, go to Developer ➤ Visual Basic.
- As a result, the VBA window will appear.
- Copy the below code and paste it there.
Sub FlipRows()
Dim rg As Range
Dim rnge As Range
Dim ar As Variant
Dim i As Integer, j As Integer, k As Integer
On Error Resume Next
xTitleId = "Flip Rows"
Set rnge = Application.Selection
Set rnge = Application.InputBox("Range", xTitleId, rnge.Address, Type:=8)
ar = rnge.Formula
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 1 To UBound(ar, 1)
k = UBound(ar, 2)
For j = 1 To UBound(ar, 2) / 2
xTemp = ar(i, j)
ar(i, j) = ar(i, k)
ar(i, k) = xTemp
k = k - 1
Next
Next
rnge.Formula = ar
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
- Save the file and close the window.
- Now, select Developer ➤ Macros.
- Thus, the Macro dialog box will emerge.
- Select FlipRows.
- Press Run.
- You’ll get another dialog box.
- Select your desired range in the Range.
- At last, press OK.
- In this way, it’ll return the dataset after flipping the rows.
Read More: How to Flip Data Horizontally in Excel (3 Effective Methods)
4. Flip Rows with Simple Formula
In our last method, we’ll insert the INDEX and COLUMNS functions to flip rows. So, learn the process.
STEPS:
- Select cell B9 at first.
- Then, input the formula:
=INDEX($B4:$D4, COLUMNS(B4:$D4))- Press Enter.
- Thus, it’ll return the formula output.
- Afterward, use AutoFill to complete the rest.
- In such a way, you can also get the flipped dataset.
Read More: How to Change Excel Sheet from Right to Left (4 Suitable Ways)
Conclusion
Henceforth, you will be able to Flip Rows in Excel following the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
