How to Excel turn the column

In the table editor Microsoft Office Excel to change the order of rows in the column intended sort operation data. It determines the order of placement of values in the column, comparing them with each other. However, it is sometimes necessary to change the order of the rows on the back, regardless of the values in the cells. One way to do this is to use built - in Excel functions.

You will need

The table editor Microsoft Office Excel 2007 or 2010.

Instruction

You will have to manually enter in the formula ' first and last cells in the editable column. If the table is quite large and does not fit in one screen, use the scroll and make a note of the line numbers of the first and last cells turn region.

Select sheet free column to support column - its height will match the height of the original column. Set the input cursor to the first cell of this column. Click on the "Formulas" menu in the table editor and the command group "Library functions" open the drop-down list of "References and arrays". In the functions list, select "offset".

In the first field - "Link" - the dialog box that opens, type the address of the last cell of the column to overturn. Before marking column and column in the address put the dollar signs. For example if the column ends in cell F63, the entry should look like this: $F$63.

The entire formula in the next box - "Smeasy" - will have to dial manually. First, copy here and paste in the form of Excel features and operations: (ROW()-ROW())*-1. Then inside the parentheses of the second function LINE() enter the address of the first cell, turn over the column, as in the previous step, separating the letters and numbers with a dollar sign. For example, if the address of the first cell F25, the whole line should look like this: (ROW()-ROW($F$25))*-1.

In the next form field "Cols" - vote zero and leave the other fields empty. Click OK and the cell with the formula displays the contents of the last row turn column.

Spread this formula down a column to a height of overturn of the column - drag the black dot in the lower right corner of the cell down to the desired row. As a result, all auxiliary column in reverse order will be filled in with the values of the original.

Highlight and copy this vertical range of cells. Then right click the mouse on the first cell in the source column in the context menu, select "Values" under "paste Options". This operation is completed, and an auxiliary column formulas can be removed.

Is the advice useful?