In the Microsoft Office Excel spreadsheet editor, the data sorting operation is used to change the order of rows in a column. It determines the order in which values are placed in a column by comparing them with each other. However, sometimes it is necessary to reverse the order of the rows, regardless of the values in the cells. One way to do this is to take advantage of built-in Excel functions.
Necessary
Tabular editor Microsoft Office Excel 2007 or 2010
Instructions
Step 1
You will have to manually enter the addresses of the first and last cells in the edited column into the formula. If the table is large enough to fit into one screen, scroll down and note or write down the line numbers of the first and last cells in the flipped area.
Step 2
Select a free column on the sheet for the auxiliary column - its height will match the height of the original column. Place the insertion point in the first cell of this column. Go to the "Formulas" tab in the spreadsheet editor menu and in the "Function Library" command group open the "References and Arrays" drop-down list. In the list of functions, select the line "OFFSET".
Step 3
In the first field - "Link" - of the opened dialog, type the address of the last cell of the flipped column. Place dollar signs in front of the column and column designations in this address. For example, if the column ends in cell F63, the record should look like this: $ F $ 63.
Step 4
The entire formula in the next field - "Offset_by_lines" - will have to be typed manually. First, copy and paste the following set of functions and operations into an Excel form: (LINE () - LINE ()) * - 1. Then, inside the parentheses of the second LINE () function, write the address of the first cell of the reversible column, as in the previous step, separating letters and numbers with a dollar symbol. For example, if the address of the first cell is F25, this whole line should look like this: (LINE () - LINE ($ F $ 25)) * - 1.
Step 5
In the next field of the form - "Offset_by_columns" - put zero, and leave the rest of the fields blank. Click OK, and the formula cell displays the contents of the last row of the flipped column.
Step 6
Extend this formula across the column to the height of the flipped column - drag the black dot in the lower right corner of the cell down to the required row with the mouse. As a result, the entire auxiliary column will be filled in reverse order with the values of the original one.
Step 7
Select and copy this vertical range of cells. Then right-click on the first cell of the source column and select "Values" in the "Paste Options" section of the context menu. This completes the operation and you can delete the auxiliary column with formulas.
