Google Sheets offers various tools and functions to analyze and organize datasets. Sorting and filtering the tables is a breeze with this spreadsheet program. You can flip row or column data with a few simple clicks.
To sort the dataset in ascending or descending order, you can use the Sort Range tool and SORT function.
But things get complicated when you wish to reverse order or flip data across rows or columns in Google Sheets.
In this article, let us learn Google Sheets reverse order. We will discuss the steps to invert order or, simply put, to flip data.
Download the Example Google Sheets
Inverting a row or column order in Google Sheets is a pretty tricky task. But with practice, you can learn all the methods discussed in this article to execute this task quickly.
Use the following link to make a copy of the Google Sheets with the dataset used to demonstrate reverse order in the following sections.
You can skip downloading the above file if you have a Google Sheet ready with the required dataset.
Difference between Sorting and Reverse Ordering
When we say sorting, it can be done in two ways: Ascending or Descending.
Sorting is one of the most common methods to organize data tables in spreadsheet software like Microsoft Excel and Google Sheets.
There are a range of built-in tools and functions to sort data in both ascending and descending order.
On the other hand, reversing or inverting order means flipping the data across rows or columns as per the given sequence.
There is no built-in tool or function in Google Sheets to reverse order. You need to combine sorting functions and tools to reverse order in a specific sequence.
Reverse Order in Google Sheets
Note that you can flip data across both rows and columns. The steps to reverse order across columns are simple compared to rows.
In this section, we will look at various methods to reverse order in Google Sheets.
Let’s start with flipping columns.
How to Flip Data across Columns in Google Sheets
There are three methods to reverse order across columns in Google Sheets.
Consider the following example. Here, we have employee names and their annual salary in no particular order.
Our task is to flip the data across the first column, as shown in the following image. It ensures the reverse order in the exact same sequence compared to the original column.
METHOD #1 – Using The Helper Column to Reverse Order in Google Sheets
This method requires you to follow a lengthy process compared to the third method. However, it is the simplest and best for beginners at Google Sheets.
We need to start by adding a new column to our table, as shown below.
Here are the steps to add the column.
- Select the entire first column by clicking on the column header letter “A” as shown below
- Right-click using your mouse
- Choose “Insert 1 column left” from the popup
- Type “Helper Column” in the cell A1
- Click on the first cell below the header row
- Type “1”
- Press “Enter”
- Now, enter the values for the next two rows of the column, as shown in the following image
- Select the cells from A2 and A3
- Hover over the Fill Handle that appears at the lower-rightmost corner of the cell A4
- Double-click on the Fill Handle
As shown in the above GIF, Google Sheets will instantly fill the numbers across the entire column.
Now, let us use this helper column to reverse order.
- Click on any cell of the table
- Press “CTRL + A” on your keyboard to select the entire table
- Hover to the toolbar section that sits below the main menu
- Click on the “Filter” icon
- Go back to the table, and you will see the filter icons across the table headers, as shown below
- Click on the filter icon beside the first column (Helper Column) header
- Select the “Sort Z to A” option from the popup
As you can see in the following GIF, the table is instantly sorted in descending order, with the first column (Helper Column) as the reference. It ultimately leads to reversing the order of the Full Name Column.
You can delete the helper column once you are done with the reverse ordering.
METHOD #2 – Using the Helper Column and SORT function to Reverse Order in Google Sheets
Here, we will use the helper column created in the previous method and the SORT function to reverse data across the Employee Name column.
Explained: The SORT Function
The SORT is a simple-to-use array formula in Google Sheets. It is used to create a dynamic table that auto-updates whenever the original data table changes.
Here is the general syntax for the SORT function,
=SORT(range, sort_column, is_ascending)Each argument of the above formula needs to be replaced with the proper information.
- “range” – Here, put the column or table range that you wish to sort
- “sort_column” – This argument needs to be replaced with the column number that you wish to sort
- “is_ascending” – It is an optional argument used to sort the selected column in ascending or descending order. You can enter “TRUE” and “FALSE” for sorting in ascending and descending order respectively
It is a simple-to-use function that creates a new sorted table.
Let’s use it to reverse order using the Helper Column.
- Create a new table as shown in the following image
(You can name the headers for your new table as per your choice) - Click on the cell “E2”
- Type “=sort”
- Select the first option from the popup or press the “Tab” key
- Replace the first argument with the table range “A2:C10”
(Do not include the headers while selecting the table range for the SORT function) - Press “,” to move to the sort_column argument
- Type “1” to tell the function to sort the first column of the selected range
- Press “,”
- Type “FALSE” in the is_ascending argument
- Complete the parentheses using “)”
- Press “Enter” on your keyboard
Here’s how the final formula should look:
=SORT(A2:C10,1,FALSE)The function creates a new dynamic table, as shown in the above GIF. It updates automatically whenever any cell value is changed from the main table.
In other words, by using this method, you make sure to keep your primary dataset safe.
METHOD #3 – Using the INDEX and ROW function to Reverse Order in Google Sheets
It is the last method to reverse data across a column in Google Sheets.
For this method, we don’t need to create a Helper Column, as discussed in the previous two methods.
It lets you create a dynamic table similar to the second method. The new table updates automatically whenever something changes in the provided table range.
Explained: The INDEX Function in Google Sheets
This function is used to extract data from a specific cell depending on the row and column number provided by the users. Refer to the following general syntax of the function,
=INDEX(reference, [row], [column])Here’s how to deal with the arguments of the INDEX function,
- “reference” – It is the range from where you wish to extract rows and columns
- “row” – It needs to be replaced with the row number corresponding to the cell from where you wish to extract the data
- “column” – Similar to the second argument, replace this argument with the column number corresponding to the cell from where you wish to extract the values
It is a pretty straightforward formula that returns a single cell value based on the user inputs.
Explained: The ROWS Function in Google Sheets
It is the second function we will need to reverse order in Google Sheets.
The ROWS function is used to get the number of rows from a specific range. Simply put, it counts and returns the number of rows across the range defined by the users.
Here’s the general syntax of the ROWS function,
=ROWS(range)The “range” argument needs to be replaced with the cell or column range.
The function will return the total number of rows within the given cell or column range.
Now, let us combine these two functions to flip data across the first column of the following table.
Here are the steps:
- Create a new table as shown below
(You can name the header as per your wish) - Select the cell “D2”
- Type “=index”
- Choose the first option from the popup or press the “Tab” key
- Replace the reference argument of the INDEX function with the cell reference “A2:B10”
- Press the “F4” key on your keyboard to insert absolute reference as shown below
(The “$” symbol represents absolute reference in Google Sheets) - Press “,” to move to the next argument of the INDEX function
- Type “rows” and you will see the following options
- Select the first option from the popup
- Now, for the range argument of the ROWS function, let us put the cell reference as “A2:B10”
- Next, manually put the “$” symbols as shown in the following image
- Complete the bracket for the ROWS function using “)”
- Complete the bracket for the INDEX function using “)”
- Press the “Enter” key
Our final formula should look as follows,
=INDEX($A$2:$B$10,ROWS(A2:$B$10))Finally, you need to apply this formula to the rest of the cells.
Select the cell D2 and hover over the Fill Handle on the lower-rightmost corner. Now, you need to click on that Fill Handle and drag it to the end of the table.
How to Flip Data across Rows in Google Sheets
Now, in this section, let us flip the data available across a row.
Consider the following example.
We have employee names in the first row and their salary in the second row.
To flip the data in the available rows, we need to use the SORT and TRANSPOSE functions in Google Sheets.
Explained: The TRANSPOSE Function
You can use the TRANSPOSE function in Google Sheets to transfer a series of cell values from a row to a column.
Here’s the general syntax for the function,
=TRANSPOSE(array_or_range)The “array_or_range” argument of the function is pretty self-explanatory. Replace it with the row or column range that you wish to transpose.
Let us combine the SORT with the TRANSPOSE function in Google Sheets to flip the data from the rows, as shown in the following image.
- Create a new table as shown in the above image
- Select the cell “B5”
- Type “=transpose”
- Choose the first option from the popup or press the “Tab” key
- Now, type “sort”
- A popup will appear as shown below,
- Choose the first option
- Next, type “transpose” again in the place of the range argument of the SORT function and select the first option
- Provide the cell reference “B1:J1” for the array_or_range argument of the TRANSPOSE function
- Complete the bracket for the TRANSPOSE function using “)”
- Press “,” to move to the sort_column argument of the SORT function
- Type “1”
- Press “,” to move to the is_ascending argument of the SORT function
- Type “TRUE”
- Complete the bracket for the SORT function using “)”
- Complete the bracket for the first TRANSPOSE function using “)”
- Press “Enter” on your keyboard
Here’s what the final formula should look like,
=TRANSPOSE(SORT(TRANSPOSE(B1:J1),1,true))Now, as you can see in the above GIF, all the values from the selected row range have been flipped.
To apply this formula to the next row, simply hover over the Fill Handle of cell B5 and double-click on it.
The formula remains the same except for the array_or_range argument, which uses a new row range, as shown in the following image.
To Summarize: Google Sheets Reverse Order
Though there is no built-in function or tool to reverse order in Google Sheets, you can use the SORT and combine it with other functions such as ROWS, INDEX, TRANSPOSE, and ARRAYFORMULA.
I hope you learned all the bells and whistles of flipping the cell values across rows and columns through this article.
Feel free to comment below if you are having any difficulties or particular errors while inverting orders in Google Sheets.
Also, make sure to explore our blog for more tips and tricks to use Google Sheets like a pro.