Excel boasts a wide variety of features to analyze and organize datasets. Anyone can quickly sort and filter massive tables within a few seconds using built-in tools and functions such as the SORT in Excel.
Sorting is easy!
But there are a few circumstances where we will be asked to flip the entire column or row.
For that, as well, we need to leverage the same steps used for sorting the data.
Welcome to this quick guide, where we will discuss reversing the order in Excel. It includes crucial tips to flip data in Excel. So, hold tight and read this Excel reverse order guide till the end.
Download the Example Excel Sheets
Flipping a row or column in Excel is a tricky task. However, with practice, you can ensure the task is executed quickly and correctly.
Here is the Excel file, including the dataset used to demonstrate the functions and tools to reverse order, in the following sections of this article.
You can skip downloading the above file if you have your own Excel spreadsheet ready with the required dataset.
Difference between Sorting and Reverse Ordering
Sorting is one of the most commonly executed tasks for organizing tables in ascending or descending order with respect to rows or columns. There are various built-in tools and functions for sorting datasets in both ascending and descending order.
On the other hand, reversing or inverting order means flipping the data across rows or columns while ensuring that the sequence remains intact.
Please know that there is no formula or built-in tool to reverse order in Excel.
You need to follow an extra workaround to reverse order in the given sequence.
How to Reverse Order in Excel
Note that you can flip data across both rows and columns. The steps to reverse order across columns are simple compared to rows.
Let’s start with flipping columns.
How to Flip Data across Columns in Excel
Reversing order across columns in Excel can be done using three methods.
For now, consider the following example where we have employee names in the first column and their annual salary in the second column.
Task: To flip the data across the first column. We need to make sure that the reverse order is in the same sequence as the original one.
METHOD #1 – Using The Helper Column to Reverse Order in Excel
This is a comparatively lengthy process. However, it is the simplest and best for beginners at Excel.
Let’s begin,
- Select the entire first column by clicking on the column header letter “A”
- Right-click
- Choose “Insert” from the dropdown menu
(A new blank column will be added beside the selected column) - 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 and drag it till the end of the table
As shown in the above GIF, Excel 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 main menu and go to the “Home” tab
- Click on the “Sort & Filter” dropdown in the “Editing” group
- Select the “Filter” option
- You will now observe 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 Largest to Smallest” option from the menu
As seen in the following GIF, the table will get instantly sorted in descending order, by taking the Helper Column as the reference. It ultimately leads to reversing the order of the Full Name Column.
The helper column can be deleted once you are done with the reverse ordering.
METHOD #2 – Using the Helper Column and SORT function to Reverse Order in Excel
In this method also we will use the helper column created in the first method and combine it with the SORT function to reverse data across the Employee Name Column.
Explained: The SORT Function
The SORT function sorts the contents of the range or array either in ascending or descending order.
It creates a dynamic array of tables that auto-updates whenever the original data table changes.
Here is the general syntax for the SORT function,
=SORT(array, [sort_index], [sort_order], [by_col])Each argument of the above formula is explained below:
- “array” – It needs to be replaced with the range or array to be sorted
- “sort_index” – This is an Optional argument used to specify the column index by which the table needs to be sorted. Here, the default value is 1 (the first column)
- “sort_order” – This is also an optional argument. Here, you need to specify the sort order: 1 for ascending (default) and -1 for descending.
- “by_col” – Here, you can specify whether to sort by column or by row. Enter “TRUE” to sort by column and “FALSE” (default) to sort by row.
Now, let’s combine this function with the helper column to reverse the order.
- Create a new table beside the actual one, as shown in the following image
(By doing so, you can make sure to keep your primary dataset safe) - Click on the cell “E2”
- Start typing “=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 select the headers while selecting the table range for the SORT function) - Press “,” to move to the sort_index argument
- Type “1” to tell the function to sort the first column of the selected range
- Press “,” to move to the next argument
- Type “-1” in place of the sort_order argument
- Complete the formula using “)”
- Press “Enter” on your keyboard
Here’s how the final formula will look:
=SORT(A2:C10,1,-1)After following the above steps, Excel creates a new dynamic table, as shown in the above GIF.
The newly created dynamic table updates automatically whenever any cell value is changed from the main table.
METHOD #3 – Using the INDEX and ROW function to Reverse Order in Excel
This is the final method to reverse data across a column in Excel. It doesn’t require any helper column compared to the first two methods.
It helps you to create a dynamic table similar to the second method. The new table also updates automatically whenever a new record is added, or any changes are made in the provided table range.
Explained: The INDEX Function in Excel
The INDEX function returns the contents of a cell from the given table based on the column and row number specified by the user.
It is used to extract single cells, entire rows, or columns of data. The General syntax of the function is as follows,
=INDEX(array, row_num, [column_num])Where,
- “array” – It is the range of cells or array to search through
- “row_num” – It is the row number from which to return a value
- “column-num” – This is an optional argument. It specifies the column number from which to return a value
We will also use the ROWS function to reverse order in Excel, along with the INDEX function.
Explained: The ROWS Function in Excel
The ROWS function is used to get the number of rows from a specific range. Just enter the cell or column range. 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.
Explained: The COLUMNS Function in Excel
The COLUMNS function in Excel returns the total number of columns in a given data range or an array.
The General syntax of the function,
=COLUMNS(array)It is a very simple formula that has a single mandatory argument. The “array” refers to the range of cells for which the number of columns is derived. You can either choose a single cell or a range of cells as the value for this argument.
Now, let us combine all the functions to flip data across the first column of the following table.
Let’s begin,
- Create a new table as shown below
- Select the cell “D2”
- Type “=INDEX”
- Choose the first option from the popup or press the “Tab” key
- Replace the array argument of the INDEX function with the data range “A2:B10”
- 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:$A$2”
- Complete the bracket for the ROWS function using “)”
- Press “,”
- Start typing “COLUMNS” and you will see the following prompt
- Select the first option from the popup or press the “Tab” on your keyboard
- In place of the array argument, enter “$A$2:A2”
- Complete the bracket for the COLUMNS function using “)”
- Ens the INDEX formula using “)”
- Press the “Enter” key
Our final formula should look as follows,
=INDEX($A$2:$B$10,ROWS(A2:$A$2),COLUMNS($A$2:A2))Finally, you need to apply this formula to the rest of the cells in the table.
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.
You can also double-click on the fill handle to serve the same purpose.
How to Flip Data across Rows in Excel
Now, in this section, let us flip the data available across a row.
We will consider the following example to demonstrate the process of flipping data across rows.
We have employee names in the first row and their salary in the second row.
We will use the SORT and TRANSPOSE functions to flip the data across the available rows.
Explained: The TRANSPOSE Function
THE TRANSPOSE function in Excel helps in transferring a series of cell values from a row to a column.
=TRANSPOSE(array)The “array” argument of the function is self-explanatory. You need to replace it with the row or column range that you wish to transpose.
Now, let us combine the SORT with the TRANSPOSE function in Excel to flip the data from the rows, as shown in the following image.
Steps:
- 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 in “SORT”
- As SORT is an Excel function, a popup will appear as shown below,
- Choose the first option or press the “Tab” on your keyboard
- 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 “-1”
- Complete the bracket for the SORT function using “)”
- End the TRANSPOSE function using “)”
- Press “Enter” on your keyboard
As seen in the above GIF, Excel will flip all the values from the selected row range.
To apply this formula to the next row, simply hover over the Fill Handle of cell B5 and double-click on it.
Conclusion
Though there is no built-in function or tool to reverse order in Excel, you can use the SORT and combine it with other functions such as ROWS, INDEX, and TRANSPOSE.
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 Excel.
Also, make sure to explore our blog for more tips and tricks to use Excel like a pro.