When working in a spreadsheet program such as Google Sheets, there may be times where you want to flip your data around and reverse the order of a list or table.
Luckily, this is an easy task to do. There are a few good methods that you can use to quickly invert your data.
In this tutorial, I will show you how to reverse the order of your data in Google Sheets.
Reverse Order Using an Index Number
The simplest way to reverse the order of your data is to create a “helper column” with an index number that you can use to sort by.
Essentially what you will be doing is creating a column where you will be numbering every row and then you can sort that column from largest to smallest.
Here is how this is done:
1. First figure out where you want your “helper” column to be and add it to your table or list. I have inserted a column at the beginning of my data for this
2. Number the first two rows of your “helper” column in numerical order. This will be your index number.
3. Highlight the two index numbers that you have entered in the previous step
4. Double click on the square in the bottom right corner to number your entire list. Alternatively you can click and hold while dragging your cursor down and your cells will be incremented as you drag your cursor down.
5. You should now have a numbered list like below where your “helper” column is in numerical order
6. Highlight your entire dataset
7. In the top menu select Data>Sort range
8. If your data has a header row, make sure that box is checked. Then in the Sort by section, make sure to choose whichever column is your “helper” column. Then you will want to sort the data from Z to A. Click Sort when this is all set up
9. Your data should have now inverted and what was on the bottom will appear on the top, and what was on top, will appear on the bottom
Formula to Reverse Order
Using a helper column to manually sort is an easy option, but you can also quickly flip your data using a formula.
To do this we will be using a combination of the INDEX function and ROWS function.
The syntax of the combined function will be this:
- date_range – this is the range of data that you want to reverse the order for. If you have a header row you will have to leave it out of this range
Here are the steps to do this:
1. If you have header rows in your data, copy and paste them into empty cells next to the data that you want to sort. For this method, you will want to leave any header rows out of your formula
2. Select the cell where you want your reversed table or list to appear. In that cell type “=INDEX” and then press Tab to begin entering the formula. Enter the range of data that you will be sorting and enter a comma when you are done with this step. Make sure that your data range is locked by using absolute references for this data range. Pay attention to where the dollar symbols ($) are in my formula.
3. Next type ROWS and again put in the entire data range that you are reversing into the formula. This time the very first cell in the data range will not have an absolute cell reference (no $ symbols) but the second part of the data range will need to have an absolute cell reference. When you are done with this step put two closing parentheses and press Enter to calculate the formula
4. This formula now should have pulled the very last row from the original data set onto the top of the new data range.
5. Copy the formula and paste it down as many rows long as the original dataset is
6. You should now see that your entire list or table has been reversed
The benefit of using the formula method is that as your original dataset changes, your sorted values will also update automatically.
I have now shown two methods for reversing a list or table in your spreadsheet. The first method is much easier but involves a little bit more manual work since you need to add a helper column to be able to sort by it.
The second method is a little bit more complicated, but you end up with a table that is returned by a formula which means as your original data set changes, your new sorted table will also update. This can be a good thing if you are dealing with dynamic data.
More Google Sheets Tutorials:
How to Transpose Data