How to flip data in Excel preserving formatting and formulas To save the macro, be sure to save your file as an Excel macro-enabled workbook. You select one or more columns using the mouse, not including the column headers, click OK and get the result in a moment. The Flip Columns dialog pops up prompting you to select a range to flip:.Click Insert > Module, and paste the above code in the Code window.Open the Microsoft Visual Basic for Applications window ( Alt + F11).Set WorkRng = Application.InputBox( 'Range', xTitleId, WorkRng.Address, Type:=8)Īpplication.Calculation = xlCalculationManualĪpplication.Calculation = xlCalculationAutomaticĮnd Sub How to use the Flip Columns macro XTitleId = 'Flip columns vertically' Set WorkRng = Application.Selection In other words, ROWS creates a kind of decrementing counter for INDEX so that it moves from the last item toward the first item.ĭim Arr As Variant Dim i As Integer, j As Integer, k As Integer On Error Resume Next 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.For the first cell (B2), ROWS(A2:$A$7) returns 6, so INDEX gets the last item in the list (the 6 th item).In our formula, it's the clever use of the relative and absolute references that does the 'flip column' trick: In its simplest form, ROWS(array) returns the number of rows in array. The row number is worked out by the ROWS function. In the array, you feed the entire list you want to flip (A2:A7 in this example). For our sample data set, the formula goes as follows:Īt the heart of the formula is the INDEX(array, row_num, ) function, which returns the value of an element in array based on the row and/or column numbers you specify.

Change columns to rows in excel