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

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

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.

Add Index Column of Sequential Numbers to Retain Sort Order

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:

  1. Type a 1 in a blank column to the right of the data range/table.
  2. Double-click the fill handle to fill the number down. Checkout this article on the fill handle to learn more.
  3. Select Series from the Auto Fill Options menu to create a sequential list of numbers 1,2,3,…
  4. 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.
Reapply Filters After Adding Index Column if Data is Not in Table

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.

Create Series of Numbers with the Fill Handle and Ctrl Key

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!

13 comments

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

  • Don’t laugh on:

    How about just making a copy of the sheet, Do all Sorting on it!!!! Once done, delete it!!!! (:-:)

  • 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

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

  • 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

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

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

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly