In this article, we will demonstrate to you how to remove sort in excel. In Microsoft Excel, we often sort data for the convenience of our work. Sometimes we need to sort data temporarily not permanently. So, in this article, we will illustrate 3 methods of removing sort from excel data to get back to the original dataset.
Download Practice Workbook
You can download the practice workbook from here.
3 Easy Methods to Remove Sort in Excel
After going through all three methods, you will be able to remove sorting easily from an excel dataset. Just read the methods and do all the steps with us carefully.
1. Use Conventional Undo Command to Remove Sort in Excel
The conventional undo command is Ctrl + Z. We can use this process to undo any kind of action. To remove sorting, this command is also applicable. To demonstrate this method to you we will use the following dataset. We have filer buttons in the header cells. Now, in this example for your convenience we will sort the data first then we will remove the sorting. Let’s take a look at the steps how we can do this.
- Firstly, click on the filter button of the header cell D4.
- Secondly, select “Sort Smallest to Largest” from the available options.
- Thirdly, we can see the sorting icon in cell D4. The value in the “Sales Amount” column is also sorted from smallest to largest value.
- After that, press Ctrl + Z.
- Lastly, we can see that there is no more sorting in our dataset. So, we get back the original dataset.
NOTE: The undo command is only applicable to undo an immediate action. If you close the worksheet and open it after 2 hours or 5 days, you will not be able to remove sorting with the Ctrl + Z command.
2. Remove Sort in Excel Using ‘Sort & Filter’ Option
We can also use the “Sort & Filter” option from the “Editing” section of the Home tab. We will use the dataset of our previous example to demonstrate this method. So, to understand this method just do the below steps with us.
- First, click on the filter button of the header cell D4.
- Next, select the option “Sort Largest to Smallest”.
- So, we can view the sorting icon in cell D4. Also, we get the sorted values of the “Sales Amount” column from largest to smallest.
- Then, go to the Home.
- After that, from the “Editing” section of the Home tab, open the “Sort & Filter” drop-down.
- Select the opinion Clear from the drop-down.
- Finally, we can see that there is no sorting icon in the header cell D4.
NOTE: It is important to notice that the use of the “Sort & Filter” option only removes the sorting icon. This option does not restore the dataset to the original version.
3. Insert Index Column to Remove Sort in Excel
The most effective way to remove sorting from an excel worksheet is by inserting an Index Column. With this additional column, we can always tell the status of the dataset if it is sorted or not. We have the following dataset in table form and we will insert an index column to remove the sort of this dataset. To perform this action just follow the below steps.
- In the beginning, do a right-click at any cell in the “Sales Amount”.
- Next, go to the Insert option and select “Table Column to the Right”.
- So, we can see an additional column at the right side of the column “Sales Amount”.
- Then, we renamed the additional column to “New Index”.
- Now, insert value 1 in cell E5. Select cell E5 and drag the Fill Handle tool to the end of the column.
- Then, from the dropdown of the right bottom corner select the option Fill Series.
- We get a series of index numbers in the new “Index”.
- After that, click on the filter icon in cell C4. Sort the column City by using the sort option “Sort A to Z”.
- The values of column “City” are sorted alphabetically from A to Z. If we notice the values of the column “New Index” are also sorted with the column “City”.
- So, the “New Index” column keeps a trace of whether our data is sorted or not. Now if we want to remove the sorting from our dataset just sort the “New Index” column from “Smallest to Largest”.
- Finally, we get not only the sorted value of the “New Index” column but also the column “City”.
NOTE: If instead of table format you are filtering data using data range, the filtering option will not be available in the newly inserted column. You have to apply the filtering option again in the newly inserted column.
In the end, from this article, you get to know about 3 methods of how to remove sort in excel. Download our practice workbook, which is attached to this post, and practice yourself for the best result. If you have any questions or you are aware of any new method to remove sort in excel, please leave a comment below. We will do our best to respond as quickly as possible.