Sorting data is a very common task in Excel. That’s what spreadsheets are all about no!?
But, sometimes we can mess up our dataset and want to revert back to the original data. CTRL + Z works when you want to undo the changes immediately. But what do you do when you’ve saved and closed a file? Or when you have made other changes and don’t want to unwind ALL of your work?
Don’t stress, there is a simple solution!
Unsort Data In Excel
My opening caveat is that this technique only works BEFORE you mess up the data. If you have already messed it up and CTRL+Z doesn’t work, then you’ll need to roll back to the original file (you did make a back up didn’t you…?).
With that out of the way, let’s get on with it.
To be able to unsort a dataset, you’ll need to create a reference or unique key that you can use to roll back to the original dataset.
Let’s use the example below.
We have a list of cryptos with a number of tokens. Assume this is the original list and you want to “freeze” this view and perhaps sort this alphabetically by crypto or by the number of tokens. This is how I would “unsort” after playing around with the data.
- Right-click on column A and insert a new column
- Label the new column something you’ll remember – we’ll go with “Original View” in this example
- In cell A2, type in 1 and in cell A3, type in the formula =A2+1
- Hover over the bottom right of cell A3 and double-click once it’s turned into a “+” icon
That’s it! Now all you need to do is sort by ascending order whenever you want to reset the table to the original dataset view. If you hide column A, just make sure you include it in the filter when you’re playing around with column B (Crypto) and column C (Tokens).
Other Options To Unsort Data
As I mentioned at the start, CTRL + Z can be a lifesaver in certain situations. However, CTRL+Z is not available if you’re coming back to the data after modifying the spreadsheet or using some VBA.
Alternatively, create backups of your dataset. If it’s a small dataset, create a “backup” tab within the same workbook, or create a backup file if it’s a large and heavy dataset. Keep up with Excel best practices and save iterations of your work periodically so you don’t lose a chunk of work!
If you’re fortunate enough to be using Office 2016 or 2019 for Mac backups won’t be as big of an issue. It’s quite straightforward to roll back to earlier versions.
Thanks for reading and I hope this tutorial saves you some time!
You may also be interested in: