How To Flip Order Of Data In Excel

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

Flipping information in Excel sounds like a trivial one-click task, but surprisingly in that location is no such built-in choice. In situations when you need to reverse the data order in a column arranged alphabetically or from smallest to largest, yous tin obviously use the Excel Sort characteristic. Simply how do you flip a column with unsorted information? Or, how do you reverse the social club of data in a table horizontally in rows? You lot volition get all answers in a moment.

How to flip data in Excel vertically
- Flip a column using the Sort characteristic
- Flip a table in Excel
- Flip columns using formulas
- Reverse information club in columns with VBA
- Flip data preserving formatting and formulas
How to flip data in Excel horizontally
- How to flip rows in Excel
- Opposite data society horizontally with VBA
- Flip data in rows with Ultimate Suite for Excel

Flip data in Excel vertically

With just a piddling creativity, you lot tin can work out a handful of dissimilar means 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 gild of data in a column vertically, perform these steps:

Add a helper column side by side to the column y'all desire to flip and populate that cavalcade with a sequence of numbers, starting with one. This tip shows how to take information technology washed automatically.
Sort the column of numbers in descending lodge. For this, select any cell in the helper column, become to the Data tab > Sort & Filter group, and click the Sort Largest to Smallest push button (ZA).

To flip a column in Excel, sort a column with numbers next to it.

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

Now you tin can safely delete the helper cavalcade since you lot do not need it any longer.

Tip: How to quickly fill a column with series numbers

The fastest fashion 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 beneath).
Select the cells where you lot've simply entered the numbers and double-click the lower right corner of the selection.

That'southward it! Excel volition autofill the cavalcade with serial numbers upwardly to the terminal prison cell with information in the adjacent column.

How to flip a tabular array in Excel

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

Sometimes (most often when you lot select the whole column of numbers prior to sorting) Excel might brandish the Sort Warning dialog. In this example, check the Expand the selection choice, and and so click the Sort push button.
Expand the selection.

Tip. If y'all'd like to rotate data from rows to columns or vice versa, apply the Excel Transpose feature or other ways to convert rows to columns demonstrated in How to transpose in Excel.

How to flip columns in Excel using a formula

Another manner to flip a cavalcade upside downwardly is by using this generic formula:

Index(range, ROWS(range))

For our sample data prepare, the formula goes equally follows:

=INDEX($A$two:$A$seven,ROWS(A2:$A$7))

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

How this formula works

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

In the array, you feed the entire list y'all desire to flip (A2:A7 in this example).

The row number is worked out by the ROWS office. In its simplest course, ROWS(array) returns the number of rows in array. In our formula, it'due south the clever use of the relative and accented references that does the "flip cavalcade" play tricks:

For the commencement jail cell (B2), ROWS(A2:$A$7) returns 6, so INDEX gets the last item in the listing (the 6^th item).
In the second cell (B3), the relative reference A2 changes to A3, consequently ROWS(A3:$A$seven) returns 5, forcing INDEX to fetch the second to last particular.

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

Tip: How to supercede formulas with values

Now that you have ii columns of data, you may desire to replace formulas with calculated values, and so delete an extra column. For this, copy the formula cells, select the cells where yous'd like to paste the values, and printing Shift+F10 then V, which is the fastest mode to apply Excel'due south Paste Special > Values option.

Replace formulas with values

For more data, delight 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 tin use the following macro to reverse the data order vertically in one or several columns:

            Sub FlipColumns() 		Dim Rng As Range 		Dim WorkRng Every bit Range 		Dim Arr As Variant 		Dim i As Integer, j Equally Integer, g As Integer 	 		On Error Resume Next 	 		xTitleId = "Flip columns vertically" 		Set WorkRng = Awarding.Selection 		Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Accost, Blazon:=8) 		Arr = WorkRng.Formula 		Application.ScreenUpdating = False 		Application.Adding = xlCalculationManual 	 		For j = i To UBound(Arr, 2) 			m = UBound(Arr, i) 				For i = i To UBound(Arr, 1) / 2 					xTemp = Arr(i, j) 					Arr(i, j) = Arr(chiliad, j) 					Arr(k, j) = xTemp 					m = thousand - 1 				Next 		Next 	 		WorkRng.Formula = Arr 		Application.ScreenUpdating = True 		Awarding.Adding = xlCalculationAutomatic 	 	End Sub

How to use the Flip Columns macro

Open the Microsoft Visual Basic for Applications window (Alt + F11).
Click Insert > Module, and paste the above code in the Lawmaking window.
Run the macro (F5).
The Flip Columns dialog pops upwards prompting yous to select a range to flip:

Flip columns in Excel by using a macro.

Yous select 1 or more columns using the mouse, non including the cavalcade headers, click OK and get the result in a moment.

To relieve the macro, be certain to relieve your file as an Excel macro-enabled workbook.

How to flip information in Excel preserving formatting and formulas

With the to a higher place methods, y'all can easily opposite the data order in a column or tabular array. But what if you non only wish to flip values, only cell formats too? Additionally, what if some information in your table is formula-driven, and you want to foreclose formulas from beingness cleaved when flipping columns? In this case, you can use the Flip characteristic included with our Ultimate Suite for Excel.

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

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

With whatever prison cell in your table selected, go to the Ablebits Data tab > Transform grouping, and click Flip > Vertical Flip.
In the Vertical Flip dialog window, configure the post-obit options:
- In the Select your range box, check the range reference and make sure the header row is not included.
- Select the Adapt cell references selection and bank check the Preserve formatting box.
- Optionally, cull to Create a dorsum upward copy (selected past default).
- Click the Flip button.

Done! The society 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

And then far in this tutorial, we accept flipped columns upside down. Now, allow'south wait at how to reverse data order horizontally, i.due east. flip a table from left to right.

How to flip rows in Excel

As at that place is no option to sort rows in Excel, you'll demand to get-go change rows to columns, then sort columns, and then transpose your table back. Here are the detailed steps:

Use the Paste Special > Transpose feature to convert columns to rows. As the result, your table will undergo this transformation:
Add a helper column with numbers every bit in the very first example, then sort by the helper column. Your intermediate result volition expect something like this:
Use Paste Special > Transpose i more time to rotate your table dorsum:

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

Opposite data gild 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 Equally Range 		Dim Arr Every bit Variant 		Dim i As Integer, j Equally Integer, thou As Integer 	 		On Error Resume Side by side 	 		xTitleId = "Flip Data Horizontally" 		Set WorkRng = Application.Pick 		Set WorkRng = Awarding.InputBox("Range", xTitleId, WorkRng.Accost, Blazon:=viii) 		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 = thou - ane 			Next 		Adjacent 	 		WorkRng.Formula = Arr 		Application.ScreenUpdating = True 		Application.Calculation = xlCalculationAutomatic 	Cease Sub

To add the macro to your Excel workbook, please follow these steps. Every bit 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

Yous select the entire tabular array, including the header row, and click OK. In a moment, the data club 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 information in rows. Only select a range of cells y'all want to flip, go to the Ablebits Data tab > Transform grouping, 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 nosotros cull Paste values just and Preserve Formatting:
Select the Horizontal Flip options.