Data sorting is one of the most common tasks people do in Excel.
But what if you want to un-sort in Excel – i.e., is to revert back to the original data that has not been sorted.
Of course, there is Control Z that undoes your previous steps…
… but what if you closed the workbook and opened it to the next data and Control Z doesn’t work. Or you did some other stuff after sorting the data and you only want to undo the sort and not the rest of the things you did.
There is a simple fix to this ‘un-sorting’ problem.
Unsort Data Using a Temporary Column in Excel
To be able to unsort a dataset, you need a reference that you can use to go back to the original dataset.
Suppose you have the dataset as shown below and you want to sort it based on the country name in an alphabetical order.
And how do you unsort this data (say after 2 days)?
Below are the steps to unsort the data in Excel:
- Right-click on the left-most column.
- Click on the Insert column option. This will insert a blank column at the left of your dataset
- [Optional] Add a header in cell A1
- Add 1 in cell A2 and 2 in cell A3
- Select both the cells (A2 and A3), place the cursor over the bottom-right part of the selection. The icon will change to a plus icon.
- Click and drag. This will add incrementing numbers to all the cells
Once you have this set-up ready, you’re now all set to be able to unsort this data in case the needs arrive.
All you need to do is sort in an ascending order based on the temporary column that we added. This will give us the original data back.
In case you’re using VBA to sort a dataset, you won’t be able to undo it with Control Z, but using a temporary column will still work and help you unsort the data.
Other Options to Unsort Data
Apart from the obvious Control Z, another thing you can do to be able to go back to the original sort data is to create a backup of the dataset.
The best way of this would be to create a copy of the workbook and work on the copied version. In case anything goes wrong and you have to revert back to original data, you will have it in the backup workbook.
As a best practice, I recommend you make a backup copy at least every day. Or if you’re too prudent, you can also use a VBA macro to create a copy with timestamp whenever you save the workbook.
So these are some options that you have when you need to unsort in Excel and go back to the original dataset.
I hope you found this tutorial useful!
You may also like the following Excel tutorials: