Sorting data in Excel is quite a common task to do. There are multiple ways available both to sort and undo sort data in Excel. After applying the sort command to your data, by default they don’t revert back to their original state. In this tutorial, you can learn to undo sort in Excel with 3 exclusive methods.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
3 Methods to Undo Sort in Excel
1. Use CTRL + Z to Undo Sort in Excel
One quick way to sort your data in Excel is using the Sort command from the DATA tab.
If you did so and want to undo sort then,
❶ Just press CTRL + Z immediately after sorting your data.
This shortcut key will instantly undo a sort and revert back the data to its original state.
Read More: How to Undo and Redo in Excel (2 Suitable Ways)
2. Use Clear Command to Undo Sort in Excel
Another way that you might have followed to sort your data in Excel is that,
❶ You’ve selected your data.
❷ Then clicked on Filter from the DATA tab, under the Sort & Filter group.
❸ Then you’ve clicked on the drop-down icon.
❹ And selected any of the options,
- Sort A to Z
- Sort Z to A
If you’ve followed the above method to sort your data in Excel, then you undo sort by using the following method.
❶ Go to the DATA tab first.
❷ Under the Sort & Filter group, you will find the Clear command. Just click on it.
You can also find the Clear command by following,
HOME > Editing > Sort & Filter > Clear
Read More: How to Sort Data in Excel Using Formula
- Sort Alphabetically In Excel And Keep Rows Together
- How to Undo a Save in Excel (4 Quick Methods)
- [Solved!] Excel Sort Not Working (2 Solutions)
- Undo Changes in Excel after Save and Close (2 Easy Methods)
- How to Add Sort Button in Excel (7 Methods)
3. Undo Sort and Revert Back Data to the Original State in Excel
If you follow the second method of this article to undo sort in Excel, it will not revert back the data to its original state.
However, following this method will do.
To undo, sort, and revert back data to its original state, you will need an additional tracker column.
This additional column will keep track of the serial number of the individual rows. Thus after undoing the sort, if we sort the tracker column, our data will consequently revert back to their original state.
Anyways, follow the steps below to do so,
❶ Add an additional column to the data table that stores the serial number of the rows in your data table.
❷ Now go to the DATA tab. Then select FILTER from the Sort & Filter group.
❸ Click on the drop-down icon of any of the table headers.
❹ Select Sort A to Z or Sort Z to A and hit the OK button to sort your data.
Now you will see that the data have been sorted and the serial numbers of the rows have been messed up.
❺ To undo a sort, go to the DATA tab. From the Sort & Filter group, select Clear to undo sort.
So you have undone sort successfully. But if you look at your data, you can see that they didn’t revert back to their original state.
❻ To take your data back to its original state, click on the drop-down icon of the tracker column and select Sort Smallest to Largest, and hit the OK button.
This will rearrange the serial number of the tracker column. And you can see that your data have been reverted back to their original state.
Remove Filter and Revert Back Data to the Original State
But if you want to remove the Filter command from your data and then revert back your data to the original state, then
❼ Go to the DATA tab and click on Filter.
This will remove the Filter command from your data.
❽ Lastly, select your data then go back to the DATA tab. From the Sort & Filter group, click on the A to Z icon.
So after following all these steps, you will have your data reverted back to their original state as in the picture below:
Read More: How to Use Advanced Sorting Options in Excel
Things to Remember
- The second method doesn’t revert back the data to its original state.
- The first method only works immediately after applying the sort command.
- If you want to revert back your data to the original state, use the third method.
To sum up, we have discussed 3 methods to undo sort in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
- How to Sort Multiple Columns in Excel (5 Quick Approaches)
- [Fixed!] Undo and Redo in Excel Not Working (3 Simple Solutions)
- Sort Columns in Excel without Mixing Data (3 Ways)
- How to Undo Scroll Lock in Excel (With Easy Steps)
- Arrange Numbers in Ascending Order in Excel Using Formula
- How to Redo in an Excel Sheet (2 Quick Ways)
- Difference Between Sort and Filter in Excel
Kindly help me, Yesterday I have worked in excel work sheet and not dine any kind of serial number and then i have applied filter on product names but by mistake I have not done filter on quantity and worksheet Ihave closed worksheet when again I have opened the sheet my product quantity and products does not tallying, kindly tell me any solution about how I can revert to my orgnal data table state Thanks
This should not happen with Filter. As soon as you clear the Filter, everything should be normal again. I suppose you SORTED the Products instead, excluding the Product Quantity. Then this becomes a complicated scenario.
I don’t know which Excel version you are using but you may try the following solutions. However, I can’t guarantee whether they will work for sure.
1. For Office365: Open the workbook. Go to File > Info > Version History. Check for any previous versions listed there. If not, then go to File > Info > Manage Workbook > Recovered Unsaved Workbooks. This will take you to the recovery folder for excel files. Look for a file with your workbook’s name.
2. For Excel 2019-2016: Open the Workbook. Go to File > History. Hopefully, you will find the previous versions listed there.
3. You can also check for any previous version using File Properties. Go to the file location. Right-click on the file name. Go to Properties > Previous Versions. Check for any previous versions listed there.
4. To manually check the recovery folder, go to File > Options > Save > Save Workbooks. Then copy the AutoRecover File Location and paste it on the File Explorer address bar.
You may visit this blog post from Microsoft for more >> https://support.microsoft.com/en-us/office/view-previous-versions-of-office-files-5c1e076f-a9c9-41b8-8ace-f77b9642e2c2
I hope you will be able to recover the file. Best of luck!
Thanks & Regards,
Md. Shamim Reza (ExcelDemy Team)
Dear Gerry Conroy,
You are most welcome.
Shamima | Project Manager | ExcelDemy