Bottom line: Learn how to retain and revert to the original sort order of a range or table in Excel.
Skill level: Beginner
Video Tutorial
Restore the Original Sort Order
Often times we receive a data set that does not have a column of sequential numbers or dates. If we sort one or more columns in the range, it can be difficult or impossible to revert back to the original sort order.
One way to solve this problem is to add a column of sequential numbers to the data set. I typically refer to this as an index column.
After sorting the data you can then go back and sort the index column to restore the original sort order.
Setting up the Index Column
It's important to setup the index column BEFORE you apply any sorting to the data.
Here are the steps to create the index column:
- Type a 1 in a blank column to the right of the data range/table.
- Double-click the fill handle to fill the number down. Checkout this article on the fill handle to learn more.
- Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…
- IMPORTANT: If you are NOT using an Excel Table then you will need to reapply the filters to include this new column. This must be done BEFORE you sort the other columns so that the index column is included in the sort.
If you are using an Excel Table then you do not have to worry about step 4. The new column will automatically be included in the table as long as you create it directly to the right of the last column. See the video above for more details.
Checkout my video on a Beginner's Guide to Excel Tables to learn more about the benefits of using this awesome feature of Excel.
Here is another method for using the fill handle.
Checkout my article on Fill Handle Hacks to learn more about this technique.
Other Techniques for Retaining Sort Order?
My friend Robbie asked me this question about retaining sort order the other day, which sparked the idea for this post, and this index column method is the one I've always used.
I've also mentioned it before in my article on how to prevent Excel from freezing when deleting rows.
However, I'm curious to know if you have other techniques or methods to solve this problem. Is there a different or better way?
Please leave a comment below and share your thoughts. Thank you!
Could you also use the VIEW tab in excel and maybe save your original view of table data and then use View options to restore original view/data?
I haven’t tried this, but thinking out loud.
I’m drawing a blank on the button name under VIEW tab.
Hi Bomar,
I believe you are referring to the Custom View feature?
Unfortunately it doesn’t work with sorting. It only works with filters. Great idea though.
Custom Views also don’t work when the file contains Excel Tables.
Thanks for the suggestion though!
I thought I was so smart when I figured this out a little while back and now when I see that you do the same thing just confirmed my smartness. It’s good to emulate the master.
I can only say thanks for your courses and all the help you’ve give me with Excel.
Haha awesome! Thanks for your support Fred. I really appreciate it.
Jon
My librarian brain and 30 years of Excel and database experience say that your method is the only one unless the data already has a implicit order to it.
Chris Wall, Bristol, United Kingdom
Of course, recourse to the undo command might – but might not work, and surely doesn’t qualify as satisfactory for many cases.
Another simple workaround: immediately copy the data set to another area, and copy/paste it back to the active data when you need to revert to the original sort arrangement.
Also, of course, the original data set may not have been “sorted” at all; it may have organized along lines that don’t exhibit any particular sorted sequence.
Thanks for the suggestions Abbott! That gave me the idea that you could also quickly duplicate the sheet just to keep the original order somewhere. As you said, reverting back would be a challenge if you modify the data at all.
Thanks again!
An Index column is the way to go.
Thanks Jon!
You have the easiest and best method
What is needed is to remove sort arrows from a table BUT just keep
one arrow for sorting!
I have several 4 wide columns and with the sort arrows on not wanting sort on individual sorting there is little room for headers
Currently I have to us a hidden duplicate of the table and refer to it by = formula. This does work but surely a one column table sort to sort the whole table is within the mental capacity of Microsoft programmers.
Jo King
Close the file without saving, then reopen it!
Thanks Bob! 🙂
Don’t laugh on:
How about just making a copy of the sheet, Do all Sorting on it!!!! Once done, delete it!!!! (:-:)
Best method to go great logic John
kya logic dikha re kutte
Thanks for the tip. I have created a Rank column by keying a 1 in the first row, then in the row below a formula for the cell above +1, double click the handle to copy the formula down, and finally copy the column and paste values. Your method saves several steps.
I’ve been using this technique too!
But I always thought that existed another way to do it.
Now, I know that it is the best way!!
Thanks for sharing this with us!!
Using VBA, Why not read the original table into an array variable and when you are finished, just write the array variable back to the range. you would need two macros one to read and one to write. assign Shortcut keys.
This is just a concept , however if any one is interested i can provide the code
pal madarchoda
press ctrl+z
Done
Thank you for the video – I have an excel table that I sorted in alphabetic order – however, I only picked one cell and it sorted one column correctly. However the remaining tabs are now not in sync with the data. How do i revert back to my original spreadsheet and start over?
use Time Machine or Revert to an earlier version in Excel
You need to have activated it to Browse Version History
How to arrange order of column sheet2 as like as sheet 1
Eg
Sheet 2
A
B
C
D
sheet 1
B
D
C
A
Learned on the Older versions of Excel. Just need to open up that type of spreadsheet for my data. Is there anyway to permanently go back to that format on my computer ?
Does excel campus have a series of videos teaching basic excel knowledge for beginners? I’ve been using it for years with about 2% of the knowledge base. I have never had my current problem before and suddenly it is frequently happening. My order gets scrambled! And I DO have sequential numbers on the left in their own column. I must be doing something to cause it?
Diane DellaValle
818-517-1119
I don’t know who you are, but if you have a Patreon, I’d like to buy you a beer. So simple, and you just saved me hours.