How to Invert Data in Excel?

Today we are going to learn something different but very fun. I have faced many instances and also seen people face similar instances where they require to invert or flip the data in excel.

Sometimes users need to invert or flip datasets, tables, or ranges to facilitate the calculation they are going to do.

However, being a very useful tool to data analysts, Microsoft Excel doesn’t have any built-in option or feature to flip or invert data. But it is possible and easy to do.

So, let’s see how to invert the data in excel.

How to Invert Data in Excel?

Method#1 Sorting the Data with a Helper Column

If the data or range is in alphabetical order, you can simply use the sort option in excel to invert or flip the data.

Just select the column, range or data set you want to flip or invert, then from the Home ribbon go to Sort & Filter and select Sort to Z to A.

Sorting-the-Data-with-a-Helper-Column-1

And you will see that your data has been inverted or flipped in excel.

However, more often than not, the data set, table, or range we try to flip is not in alphabetical or numerical order.

So, we can create a helper column like in the picture.

Sorting-the-Data-with-a-Helper-Column-2

The idea is to select the helper column and sort it from Highest to Lowest and expand the selection which will reverse, flip, or invert the data.

Note: You can type the helper column manually but for bigger data sets typing manually is not a good idea. Just type 1 and 2 and then select both the numbers, then drag the fill handle down to fill up the rest of the helper column.

So, to flip the data which is not in any sort of order select the helper column, and then from the Home ribbon go to Sort & Filter and select Sort Largest to Smallest.

Sorting-the-Data-with-a-Helper-Column-3

You will see a dialogue box pop up. Make sure that the Expand the selection option is enabled. Now press Sort and you will see that your data has been inverted along with the helper column.

Sorting-the-Data-with-a-Helper-Column-4

Now that the job of the helper column is done, you can delete it.

Sorting-the-Data-with-a-Helper-Column-5

Method#2 Using the INDEX and ROWS formula

This method is a bit tricky to grasp. I am going to show you how to invert the data in excel by using the INDEX and ROWS formula.

First, let’s understand what the formulas do in excel.

The syntax of the INDEX formula is:

INDEX(array, row_num, [column_num])

Essentially the INDEX formula returns a value from the data set, array, table, or range you select based on the row and column number you specify.

We will learn more about the INDEX function in another blog. For now, just know that it returns a value based on your specified row and column references.

The ROWS function, on the other hand, returns the number of rows in the array or range you specify.

So, now type the following formula to invert the data in excel:

=INDEX($A$2:$A$8,ROWS(A2:$A$8))

Using-the-INDEX-and-ROWS-formula-1

Essentially the first argument of INDEX is the array you want to flip or invert. In the row number section if you can let excel know that the last row of the array you want to flip should be the first value in the inverted column, then you are good to go.

That’s why the ROWS function has been used. Notice the array in the ROWS function. The first row has been relatively referenced whereas the last row has been absolutely referenced.

So, for the value, the formula will return 7 as the number of rows in the array is 7. But as you drag the fill handle down, the formula will move down and the relative reference will also move.

Hence, the formula for the second value in the inverted column would be:

=INDEX($A$2:$A$8,ROWS(A3:$A$8))

Using-the-INDEX-and-ROWS-formula-2

 

You can see that relative referencing has changed the array in the ROWS formula from A2:$A$8 to A3:$A$8. This means, now as opposed to the first value in the inverted column, it will return the value of 6 as the total number of rows in the array A3:$A$8 is 6.

Remember the helper column in method 1. If you think about it, the ROWS function’s purpose is to recreate the helper column for the INDEX formula, so that it can return the values invertedly.

Conclusion

Inverting or flipping data in excel is not a regular task but it might be required sometimes to facilitate calculations. I have shown two methods here you can use any one of them.

Even if you don’t require the need to flip or invert data in excel ever, I think you will admit that it is a very fun and interesting feature.

So, there you go, now you know how to invert data in excel.

Leave a Comment