Retain the Original Sort Order in Excel

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

Retain the original sort order in Excel

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.

Add a column of sequential numbers

Now if I sort by any of the other columns, such as Date, for example…

Sort by date.

…all I have to do is sort the Index column from smallest to largest…

Sort index column smallest to largest

…and I return to my original sort order.

Reverts back to 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!


Your email address will not be published. Required fields are marked *

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

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

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter