How To Flip Data In Excel Upside Down
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 lilliputian ane-click task, but surprisingly at that place is no such built-in choice. In situations when y'all need to contrary the information order in a column bundled alphabetically or from smallest to largest, you tin can obviously utilise the Excel Sort feature. Merely how practice yous flip a column with unsorted data? Or, how do you contrary the guild of information in a table horizontally in rows? You volition get all answers in a moment.
Flip data in Excel vertically
With simply a footling creativity, y'all can work out a scattering of different means to flip a column in Excel: past using inbuilt features, formulas, VBA or special tools. The detailed steps on each method follow below.
How to flip a cavalcade in Excel
The reverse the order of data in a column vertically, perform these steps:
- Add a helper cavalcade adjacent to the column you want to flip and populate that column with a sequence of numbers, starting with one. This tip shows how to have it washed automatically.
- Sort the cavalcade of numbers in descending order. For this, select any prison 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 merely the numbers in column B, but as well the original items in column A, reversing the order of rows:
At present you tin can safely delete the helper cavalcade since y'all practice not need it any longer.
Tip: How to quickly make full a column with series 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 prison cell and ii into the 2d prison 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 pick.
That's it! Excel volition autofill the column with serial numbers upwardly to the last jail cell with information in the adjacent column.
How to flip a tabular array in Excel
The higher up method also works for reversing the data order in multiple columns:
Sometimes (near oft when y'all select the whole column of numbers prior to sorting) Excel might brandish the Sort Warning dialog. In this case, check the Expand the selection option, and and so click the Sort push button.
Tip. If you'd like to rotate data from rows to columns or vice versa, use the Excel Transpose feature or other means to convert rows to columns demonstrated in How to transpose in Excel.
How to flip columns in Excel using a formula
Some other way to flip a cavalcade upside down is by using this generic formula:
INDEX(range, ROWS(range))
For our sample information set, the formula goes as follows:
=INDEX($A$two:$A$7,ROWS(A2:$A$7))
…and reverses column A impeccably:
How this formula works
At the heart of the formula is the INDEX(assortment, row_num, [column_num]) office, which returns the value of an chemical element in array based on the row and/or cavalcade numbers you lot specify.
In the array, you feed the entire list y'all want to flip (A2:A7 in this example).
The row number is worked out by the ROWS function. In its simplest form, ROWS(assortment) 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 cavalcade" fox:
- For the first cell (B2), ROWS(A2:$A$7) returns half dozen, so Index gets the last item in the list (the half dozenthursday item).
- In the second jail cell (B3), the relative reference A2 changes to A3, consequently ROWS(A3:$A$vii) returns v, forcing Index to fetch the second to final item.
In other words, ROWS creates a kind of decrementing counter for INDEX so that it moves from the last detail toward the first item.
Tip: How to replace formulas with values
At present that yous accept two columns of data, you may want to replace formulas with calculated values, and and so delete an extra cavalcade. For this, copy the formula cells, select the cells where you'd like to paste the values, and printing Shift+F10 then V, which is the fastest way to apply Excel's Paste Special > Values choice.
For more information, please see How to replace formulas with values in Excel.
How to flip columns in Excel with VBA
If y'all have some feel with VBA, y'all can use the post-obit macro to reverse the data order vertically in 1 or several columns:
Sub FlipColumns() Dim Rng Equally Range Dim WorkRng As Range Dim Arr As Variant Dim i Equally Integer, j Every bit Integer, thousand As Integer On Error Resume Next xTitleId = "Flip columns vertically" Set WorkRng = Application.Selection Set WorkRng = Awarding.InputBox("Range", xTitleId, WorkRng.Address, Type:=viii) Arr = WorkRng.Formula Application.ScreenUpdating = False Awarding.Calculation = xlCalculationManual For j = 1 To UBound(Arr, two) grand = UBound(Arr, 1) For i = ane To UBound(Arr, one) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(k, j) Arr(yard, j) = xTemp yard = k - 1 Next Next WorkRng.Formula = Arr Application.ScreenUpdating = True Application.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 Code window.
- Run the macro (F5).
- 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 go the result in a moment.
To salvage the macro, be certain to save your file as an Excel macro-enabled workbook.
How to flip information in Excel preserving formatting and formulas
With the above methods, you tin can easily reverse the data order in a cavalcade or table. Just what if you not only wish to flip values, merely cell formats also? Additionally, what if some data in your table is formula-driven, and y'all desire to prevent formulas from being broken when flipping columns? In this case, y'all can use the Flip characteristic included with our Ultimate Suite for Excel.
Supposing you have a nicely formatted tabular array like shown beneath, where some columns contain values and some columns have formulas:
Y'all are looking to flip the columns in your tabular array keeping both formatting (grey shading for rows with zero qty.) and correctly calculated formulas. This can be done in ii quick steps:
- With any 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 following options:
- In the Select your range box, cheque the range reference and make sure the header row is not included.
- Select the Arrange prison cell references option and check the Preserve formatting box.
- Optionally, choose to Create a support copy (selected by default).
- Click the Flip button.
Done! The order of information in the table is reversed, the formatting is kept, and cell references in the formulas are accordingly adjusted:
Flip data in Excel horizontally
So far in this tutorial, we have flipped columns upside downward. At present, let'southward look at how to contrary data order horizontally, i.due east. flip a table from left to correct.
How to flip rows in Excel
As in that location is no option to sort rows in Excel, you lot'll need to first change rows to columns, and so sort columns, and then transpose your table dorsum. Hither are the detailed steps:
- Apply the Paste Special > Transpose feature to convert columns to rows. Every bit the issue, your table will undergo this transformation:
- Add together a helper column with numbers every bit in the very first instance, and then sort past the helper column. Your intermediate outcome will look something similar this:
- Apply Paste Special > Transpose one more fourth dimension to rotate your table dorsum:
Annotation. If your source data contains formulas, they may be broken during the transpose performance. In this case, yous 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.
Contrary data order horizontally with VBA
Here is a simple macro that tin quickly flip information in your Excel table horizontally:
Sub FlipDataHorizontally() Dim Rng As Range Dim WorkRng As Range Dim Arr As Variant Dim i Every bit Integer, j As Integer, g As Integer On Error Resume Next xTitleId = "Flip Data Horizontally" Set up WorkRng = Application.Selection Set WorkRng = Awarding.InputBox("Range", xTitleId, WorkRng.Address, Blazon:=viii) Arr = WorkRng.Formula Awarding.ScreenUpdating = False Application.Adding = xlCalculationManual For i = ane To UBound(Arr, 1) k = UBound(Arr, two) For j = one To UBound(Arr, 2) / 2 xTemp = Arr(i, j) Arr(i, j) = Arr(i, k) Arr(i, k) = xTemp one thousand = k - 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 presently equally 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 guild in rows in reversed:
Flip data in rows with Ultimate Suite for Excel
Similarly to flipping columns, yous can apply our Ultimate Suite for Excel to reverse the order information in rows. Only select a range of cells you want to flip, get to the Ablebits Data tab > Transform grouping, and click Flip > Horizontal Flip.
In the Horizontal Flip dialog window, choose the options advisable for your data set. In this case, we are working with values, so we choose Paste values but and Preserve Formatting:
Click the Flip button, and your tabular array will exist reversed from left to correct in the glimmer of an eye.
This is how you flip information in Excel. I thanks for reading and promise to see yous on our blog next week!
You may besides exist interested in
How To Flip Data In Excel Upside Down,
Source: https://www.ablebits.com/office-addins-blog/2017/07/26/flip-data-columns-rows-excel/#:~:text=Just%20select%20a%20range%20of,and%20click%20Flip%20%3E%20Horizontal%20Flip.
Posted by: jimenezenwhat.blogspot.com
0 Response to "How To Flip Data In Excel Upside Down"
Post a Comment