How to Remove Sort in Excel (3 Easy Methods)

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.

Use Conventional Undo Command to Remove Sort in Excel

STEPS:

  • 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.

Use Conventional Undo Command to Remove Sort in Excel

  • 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.

Use Conventional Undo Command to Remove Sort in Excel

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.

Remove Sort in Excel Using ‘Sort & Filter’ Option

STEPS:

  • First, click on the filter button of the header cell D4.
  • Next, select the option “Sort Largest to Smallest”.

Remove Sort in Excel Using ‘Sort & Filter’ Option

  • 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.

Remove Sort in Excel Using ‘Sort & Filter’ Option

  • 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.

Remove Sort in Excel Using ‘Sort & Filter’ Option

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.

Insert Index Column to Remove Sort in Excel

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”.

Insert Index Column to Remove Sort in Excel

  • So, we can see an additional column at the right side of the column “Sales Amount”.

Insert Index Column to Remove Sort in Excel

  • Then, we renamed the additional column to “New Index”.

Insert Index Column to Remove Sort in Excel

  • 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.

Insert Index Column to Remove Sort in Excel

  • 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”.

Insert Index Column to Remove Sort in Excel

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.


Conclusion

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.

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo