How to Flip Rows in Excel (4 Simple Methods)

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.

Flip Rows in Excel with Paste Transpose Feature

  • Thus, you’ll see the dataset as shown in the following figure.

Flip Rows in Excel with Paste Transpose Feature

  • Now, we’ll insert a helper column.
  • For this reason, input 1,2, and 3 in the range F9:F11.

Flip Rows in Excel with Paste Transpose Feature

  • Next, select the range B9:F11.
  • Afterward, go to Home ➤ Editing ➤ Sort & Filter ➤ Custom Sort.

Flip Rows in Excel with Paste Transpose Feature

  • 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.

Flip Rows in Excel with Paste Transpose Feature

  • Consequently, your data will appear as it’s displayed in the below image.
  • Delete the original data.

Flip Rows in Excel with Paste Transpose Feature

  • 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.

Flip Rows in Excel with Paste Transpose Feature

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.

Use Excel Sort Feature for Flipping Rows

  • Next, click Home ➤ Editing ➤ Sort & Filter ➤ Custom Sort.

Use Excel Sort Feature for Flipping Rows

  • Consequently, the Sort dialog box will appear.
  • Uncheck the box for My data has headers.
  • Press Options.

Use Excel Sort Feature for Flipping Rows

  • Click the circle to Sort left to right.
  • Press OK.

Use Excel Sort Feature for Flipping Rows

  • Subsequently, choose Row 8 in Row, Cell Values in Sort On, and Largest to Smallest in Order fields.
  • Press OK.

Use Excel Sort Feature for Flipping Rows

  • Finally, it’ll return the desired output.

Use Excel Sort Feature for Flipping Rows

Read More: How to Flip Excel Sheet from Left to Right (4 Easy Ways)


Similar Readings


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.

Apply VBA to Flip Rows in Excel

  • 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.

NOTE: The COLUMNS function returns the column numbers of B4:D4. The INDEX function retrieves the cell value that’s present in the intersection of COLUMNS(B4:$D4) outputs in the range $B4:$D4.

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.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo