• Dp

How To Inverse the Data Range in MS Excel || Turn Data Upside Down in Excel

In some situations, you will be required to Inverse the data like the transpose function which does the switching of columns and rows, there is no direct formula like invert to inverse the complete data range upside down in the MS Excel.

Now, in this tutorial, I will show you how to inverse the data range in MS Excel.

In this example consider 10 people and their corresponding scores and let's start inverting the data range.

Of course, the simplest method of turning the data upside down is to

1. Add one additional column and name the column as "order change" and

2. Type the sequence of the data range starting from 1 to 10

3. Select the entire data range C5:E14 and select custom sort.

4. From the drop down menu in the sort dialog box, sort by Order change column, in the order largest to smallest

5. So you data will get inverse.

Got it friends!!

Other than this,let us see more automated and smarter way by using formulas.

Here I will be using combination of two formulas, Index and Row Functions here.

So, let us understand ROWS function used for.

ROWS function will extract the number of Rows in any selected array.

Let's try this by typing formula in cell L5 as = ROWS(C5:C14), which would give the result as "10". If I change the range, the result changes.

Similarly, INDEX function looks at an array and pick the cell value with reference of row number and column number, it is similar to Vlookup function.