It’s common for Excel users to sort their data set when they’re analyzing.
Sometimes, you may want to revert to the original data that you had before you did the sorting.
In this tutorial, I will cover four techniques you can use to undo the sorting you have done on a dataset. The method you choose will depend upon your dataset in your situation.
Let’s get to it!
Method 1 – Undo Sort Using Control + Z to Undo Sort
If you have just sorted the data and want to revert to the original data before the sort, you can do that by using the below keyboard shortcut.
Control + Z (in Windows)
To use the shortcut, hold the control key and then press the Z key once.
If you’re using a Mac, you can use Command + Z
This is the keyboard shortcut to undo your last action, and when your last action was to sort your dataset, using the shortcut would undo the sort.
Advanced Excel Tip: If you have used the above shortcut to undo the sort and you want to get the sort applied again, you can use the keyboard shortcut Control + Y
You can only use this keyboard shortcut to undo the sort action right after you have done the sorting. If you have done anything else after the sorting operation, you will have to use the above keyboard shortcut multiple times so that all the actions still the sorting action have been undone. This also means that any changes you made after sorting the data would also be undone.
Also read: How to SORT in Excel (by Rows, Columns, Colors, Dates, & Numbers)
Method 2 – Revert to Original Sort Using Helper Column
Let me show you a more advanced and foolproof way to revert to the original sort order of a dataset.
For this method, we will have to add a helper column where we would add sequential numbers before sorting the data.
And when you want to undo the sort and get the original data back, you can simply sort the helper column.
Below I have a data set where I have city names in column A and their sales values in column B, and I want to sort this dataset based on the sales values.
However, I want to have the ability to unsort this data and get the original data back in case I need it in the future.
Below are the steps to unsort a data set using a helper column:
- Add a new blank column to the left of column A. To do this, right-click on the column header and then click on the Insert option
- Add a column header for this helper column (I am using the text ‘Helper’)
- In the new column that is inserted, enter sequential numbers starting from 1
- Select the entire data set and sort it based on the sales column values.
This gives us the sorted data, along with an extra helper column, which we can use in case we want to revert to the original data using the below steps.
- Select the entire data set, including the Helper column
- Click the Data tab
- Click the Sort icon
- In the Sort dialog box, select Helper in the Sort By drop-down and set the Order to ‘Smallest to Largest’.
- Click Ok
The above steps would sort your entire data set using the helper column and bring it back to the original order where the numbers in the helper column were in a sequence (starting from 1)
Once you have the original sort order, you can delete the helper column if you want.
Note: You can only use this method if you have added the helper column before you do the sorting.
The benefit of this method is that it allows you to undo the sort anytime after you do the sorting. For example, if you sorted your data and did many other things in your worksheet, you can still get the original sort order back without disrupting the changes you made after the sorting. This is something you cannot do when using the keyboard shortcut covered in the previous method.
Also read: How to Sort Worksheets in Excel using VBA (alphabetically)
Method 3 – Create a Back-Up Copy of Your Data
One of the best methods to have the ability to revert to the original data set after sorting is to create a backup copy of your data set.
You can either create a copy of the worksheet and hide it or create a copy of the entire workbook.
This way, in case you need to get the original data sometime in the future after sorting it, you can refer to the backup copy of the data.
In this article, I’ve covered three methods you can use to undo the sort in Excel. if you have just sorted the data and want to get the original data back, you can use the keyboard shortcut Control + Z.
And if you want the ability to get the original data back sometime in the future after sorting that data, you can use the helper column with the sequential numbers method.
Other Excel articles you may also find helpful:
