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

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

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:

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

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:

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.

How to flip columns in Excel using a formula

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

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

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

…and reverses column A impeccably:

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:

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:

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:

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

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

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:
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:
3. Use Paste Special > Transpose one more time to rotate your table back:

Reverse data order horizontally with VBA

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

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:

You select the entire table, including the header row, and click OK. In a moment, 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.

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:

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