How to flip data in Excel: reverse columns vertically and rows horizontally

The tutorial shows a few quick ways to flip tables in Excel vertically and horizontally preserving the original formatting and formulas.

Flipping data in Excel sounds like a trivial one-click task, but surprisingly there is no such built-in option. In situations when you need to reverse the data order in a column arranged alphabetically or from smallest to largest, you can obviously use the Excel Sort feature. But how do you flip a column with unsorted data? Or, how do you reverse the order of data in a table horizontally in rows? You will get all answers in a moment.

Flip data in Excel vertically

With just a little creativity, you can work out a handful of different ways to flip a column in Excel: by using inbuilt features, formulas, VBA or special tools. The detailed steps on each method follow below.

How to flip a column in Excel

The reverse the order of data in a column vertically, perform these steps:

  1. Add a helper column next to the column you want to flip and populate that column with a sequence of numbers, starting with 1. This tip shows how to have it done automatically.
  2. Sort the column of numbers in descending order. For this, select any cell in the helper column, go to the Data tab > Sort & Filter group, and click the Sort Largest to Smallest button (ZA).
To flip a column in Excel, sort a column with numbers next to it.

As shown in the screenshot below, this will sort not only the numbers in column B, but also the original items in column A, reversing the order of rows: The order of data in the original column is reversed.

Now you can safely delete the helper column since you do not need it any longer.

Tip: How to quickly fill a column with serial numbers

The fastest way to populate a column with a sequence of numbers is by using the Excel AutoFill feature:

  • Type 1 into the first cell and 2 into the second cell (cells B2 and B3 in the screenshot below).
  • Select the cells where you've just entered the numbers and double-click the lower right corner of the selection. AutoFill a column with serial numbers.

That's it! Excel will autofill the column with serial numbers up to the last cell with data in the adjacent column.

How to flip a table in Excel

The above method also works for reversing the data order in multiple columns: Flipping a table in Excel

Sometimes (most often when you select the whole column of numbers prior to sorting) Excel might display the Sort Warning dialog. In this case, check the Expand the selection option, and then click the Sort button. Expand the selection.

Tip. If you'd like to switch rows and columns, use the Excel TRANSPOSE function or other ways to transpose data in Excel.

How to flip columns in Excel using a formula

Another way to flip a column upside down is by using this generic formula:

INDEX(range, ROWS(range))

For our sample data set, the formula goes as follows:

=INDEX($A$2:$A$7,ROWS(A2:$A$7))

…and reverses column A impeccably: Flip columns in Excel using a formula.

How this formula works

At the heart of the formula is the INDEX(array, row_num, [column_num]) function, which returns the value of an element in array based on the row and/or column numbers you specify.

In the array, you feed the entire list you want to flip (A2:A7 in this example).

The row number is worked out by the ROWS function. In its simplest form, ROWS(array) returns the number of rows in array. In our formula, it's the clever use of the relative and absolute references that does the "flip column" trick:

  • For the first cell (B2), ROWS(A2:$A$7) returns 6, so INDEX gets the last item in the list (the 6th item).
  • In the second cell (B3), the relative reference A2 changes to A3, consequently ROWS(A3:$A$7) returns 5, forcing INDEX to fetch the second to last item.

In other words, ROWS creates a kind of decrementing counter for INDEX so that it moves from the last item toward the first item.

Tip: How to replace formulas with values

Now that you have two columns of data, you may want to replace formulas with calculated values, and then delete an extra column. For this, copy the formula cells, select the cells where you'd like to paste the values, and press Shift+F10 then V, which is the fastest way to apply Excel's Paste Special > Values option. Replace formulas with values

For more information, please see How to replace formulas with values in Excel.

How to flip columns in Excel with VBA

If you have some experience with VBA, you can use the following macro to reverse the data order vertically in one or several columns:

Dim Rng As Range Dim WorkRng As Range Dim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next xTitleId = "Flip columns vertically" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Arr = WorkRng.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For j = 1 To UBound(Arr, 2) k = UBound(Arr, 1) For i = 1 To UBound(Arr, 1) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(k, j) Arr(k, j) = xTemp k = k - 1 Next Next WorkRng.Formula = Arr Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

How to use the Flip Columns macro

  1. Open the Microsoft Visual Basic for Applications window (Alt + F11).
  2. Click Insert > Module, and paste the above code in the Code window.
  3. Run the macro (F5).
  4. The Flip Columns dialog pops up prompting you to select a range to flip:
Flip columns in Excel by using a macro.

You select one or more columns using the mouse, not including the column headers, click OK and get the result in a moment.

To save the macro, be sure to save your file as an Excel macro-enabled workbook.

How to flip data in Excel preserving formatting and formulas

With the above methods, you can easily reverse the data order in a column or table. But what if you not only wish to flip values, but cell formats too? Additionally, what if some data in your table is formula-driven, and you want to prevent formulas from being broken when flipping columns? In this case, you can use the Flip feature included with our Ultimate Suite for Excel.

Supposing you have a nicely formatted table like shown below, where some columns contain values and some columns have formulas: A table with formulas and custom formatting to be flipped.

You are looking to flip the columns in your table keeping both formatting (grey shading for rows with zero qty.) and correctly calculated formulas. This can be done in two quick steps:

  1. With any cell in your table selected, go to the Ablebits Data tab > Transform group, and click Flip > Vertical Flip. Vertical Flip
  2. In the Vertical Flip dialog window, configure the following options:
    • In the Select your range box, check the range reference and make sure the header row is not included.
    • Select the Adjust cell references option and check the Preserve formatting box.
    • Optionally, choose to Create a back up copy (selected by default).
    • Click the Flip button. Choose to adjust cell references and preserve formatting when flipping a table.

Done! The order of data in the table is reversed, the formatting is kept, and cell references in the formulas are appropriately adjusted: The table is flipped, formatting is kept, cell references are adjusted.

Flip data in Excel horizontally

So far in this tutorial, we have flipped columns upside down. Now, let's look at how to reverse data order horizontally, i.e. flip a table from left to right.

How to flip rows in Excel

As there is no option to sort rows in Excel, you'll need to first change rows to columns, then sort columns, and then transpose your table back. Here are the detailed steps:

  1. Use the Paste Special > Transpose feature to convert columns to rows. As the result, your table will undergo this transformation: Transpose your data.
  2. Add a helper column with numbers as in the very first example, and then sort by the helper column. Your intermediate result will look something like this: Sort the transposed data by the helper column.
  3. Use Paste Special > Transpose one more time to rotate your table back: Transpose your table back.

Note. If your source data contains formulas, they may be broken during the transpose operation. In this case, you will have to restore the formulas manually. Or you can use the Flip tool included in our Ultimate Suite and it will adjust all the references for you automatically.

Reverse data order horizontally with VBA

Here is a simple macro that can quickly flip data in your Excel table horizontally:

Sub FlipDataHorizontally() Dim Rng As Range Dim WorkRng As Range Dim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next xTitleId = "Flip Data Horizontally" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Arr = WorkRng.Formula Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For i = 1 To UBound(Arr, 1) k = UBound(Arr, 2) For j = 1 To UBound(Arr, 2) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(i, k) Arr(i, k) = xTemp k = k - 1 Next Next WorkRng.Formula = Arr Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub

To add the macro to your Excel workbook, please follow these steps. As soon as you run the macro, the following dialog window will show up, asking you to select a range: Flip data in Excel horizontally using a macro

You select the entire table, including the header row, and click OK. In a moment, the data order in rows in reversed: The data order in rows in reversed.

Flip data in rows with Ultimate Suite for Excel

Similarly to flipping columns, you can use our Ultimate Suite for Excel to reverse the order data in rows. Just select a range of cells you want to flip, go to the Ablebits Data tab > Transform group, and click Flip > Horizontal Flip. Horizontal Flip in Excel

In the Horizontal Flip dialog window, choose the options appropriate for your data set. In this example, we are working with values, so we choose Paste values only and Preserve Formatting: Select the Horizontal Flip options.

Click the Flip button, and your table will be reversed from left to right in the blink of an eye.

This is how you flip data in Excel. I thank you for reading and hope to see you on our blog next week!

Table of contents

46 comments

  1. To easily reverse an array by rows (provided it is on a spreadsheet, and thus has rows, say A1:A300 (it would not work as easily for pure arrays for which the ROW function wouldn't work (but the SEQUENCE function would be a pretty easy substitute):
    =LET(rng, $A$1:$A$300, rngTwo, HSTACK(rng, ROW(rng)), CHOOSECOLS(SORT(rngTwo, 2,-1),1))

    The same basic idea would also work for multicolumn arrays, and also could easily be adapted for single and multi row arrays (and also reversing rows AND columns in an array)

  2. Thank you

  3. You saved my life

  4. Thank you so much. It is very clear and helpful

  5. Thankyou so much for the simple solution for flip

  6. Hello dear Team,
    I need your help on that matter,
    1. HBC 1920 the result should be 1920 HBC
    2. HBC 1760 the result should be 1760 HBC
    3. P-296800 the result should be -296800 P
    Thanks a lot in advance please.

    1. Please help sir,

      1. Hi!
        Unfortunately, I cannot solve your problem with a formula. Your cells have a different data pattern.

        1. So Kindly check the Sr.No. 1&2 are the same pattern,
          so may be the formula for this.

            1. Thanks a lot Sir,
              This is working very well.

  7. Sub FlipDataVertically()
    Is missing !!!!

  8. Hi,

    I want to know how to move transposed data to multiple columns to get next rows data

  9. here is another method to to flip columns in Excel by using a formula:
    =INDIRECT(ADDRESS(ROW($A$7)-ROW(A2)+ROW($A$2),COLUMN(A2))) inested of this formula
    INDEX($A$2:$A$7,ROWS(A2:$A$7))

  10. Brilliant idea about the "helper column". It solved my issue right away!

  11. "As there is no option to sort rows in Excel, ..."

    In fact, there is an easier method for sorting rows horizontally:

    1. Insert a helper-row , say e.g. row 1, and fill it with numbers 1, 2, ...
    2. Select the whole range, including the first (helper-) row
    3. On Home tab go to Editing group, Select "Custom-Sort".
    4. Important: Press "Options" button, and check the box labeled "Sort left to right", then press OK.
    5. In sort properties, select sort by Row 1, order descending (large to small).
    6. Press OK.

    1. thanks

  12. Very nice Article,
    How we can more than one column flip to use index formula?

    1. Hello!
      There are many ways in this article how to transpose multiple columns. This cannot be done using the INDEX function.

  13. The shortest, simplest, most elegant way to flip data in a column is the following:

    =OFFSET(A$1,COUNTA(A2:A7),)

    This is my original invention :-)

    My formula refers to your explanation in the paragraph whose header is:
    "How to flip columns in Excel using a formula"

  14. Excellent tricks
    May God bless you.

  15. Hi Svetlana,

    Is it any CSE formula can replace the following formula without using SEQUENCE as it can be supported only in Office 365? Thanks.

    INDEX(range,SEQUENCE(ROWS(range),,ROWS(range),-1))

  16. Thank you very much for such a nice and helpful article.

  17. Good Job..
    Thanks for the article..

  18. Thank you, the first paragraph of this nice article was all I needed to be reminded of ;-)

    The gentleman above, Balan, is probably trying to rearrange a series of text lines of the form "Title value" (with only three different titles) into a table. I'd suggest importing the text with space separator, so that he would have two columns, titles and values. Then replicating and moving up one row the values column, twice. The values table is one third of the lines, can use titles to get rid of the extras two thirds.

  19. I have problem in excel plz solve
    Type A
    Feedback B
    reply C
    Type D
    Feedback E
    reply F

    I want like

    Type Feedback reply
    A B C
    D E F

  20. Indeed, a very good tutorial! It provides user-friendly information without going overboard with jargon and technical terms. By all means, the raw basics are outlined in this nicely-written article, using some text and images. I always encourage my students to refer to your sites for speedy answers to Excel questions.

    Keep the great work up, Svetlana!

    1. Dear Dr. Choueiri,

      Thank you very much for your encouraging comment!

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)