If you have a table of data that you want to sort by certain criteria, but you know that you'll want to revert back to the original order after you've sorted it, I have a really easy tip for you.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
In the example above, let's say that I want to sort these entries by date, and then go back to how it looks now.
The way to do this is simple. Before I sort, I just add another column and fill it with sequential numbers. I like to label the column Index, but you can name it whatever you like.
Important Note: The index column should be hardcoded values in the cells. The numbers should NOT be created with something like the ROW function because the values would change when the data is sorted. We don't want the values to change.
Now if I sort by any of the other columns, such as Date, for example…
…all I have to do is sort the Index column from smallest to largest…
…and I return to my original sort order.
Straightforward yet effective–and super easy!
Do you have other simple tricks that you use all the time in Excel? I'd love to hear about them in the comments.
Hope this helps!
Copy the entire table to another page of the same file, then use the original or copy and do what ever you want to do. When you want the original file, you have a copy.
Simple bur smart
Yip – I do this alot! Sometimes if there are hierarchies within groups
i add columns with data arranged as: Group > Sub Group > Sub-Sub Group, etc. – this also helps with Pivot table / reporting levels.
RE: Your email of 23/03/23 “Retain the Original Sort Order in Excel”
Good Tip. However my database is already sorted on a 4 field filter. I suspect after I use your index method I will lose this Sort critriea. Is there any way of saving the filter criteria?
Power query.
I need power query videos
Regarding retaining original sort order…I simply make a copy of the sheet right next to it in the workbook! I find this works the best because invariably, I do a simple sort, but then end up doing other things and mess up everything!
I feel dumb for not thinking about doing this. I always just copy and past the data into a new sheet. This saves time and has more use cases.
As soon as I saw the subject line of this email. I remember what I used to do some 2 decades ago. Exactly the same you published here. So I am thankful to my self that I am also a smart person.
Thank you.