How Do You UNSORT Data in Excel?
The sorting feature of Excel is very useful to organize and put data into a structured format. But many times, we think of a better way to do it — only after we complete the data sorting!
So somehow we have to revert the sorting that was done earlier.
A simple step known to many is just to undo your sort using the shortcut “CTRL+Z”. But what if you’ve already:
- Completed a bunch of other formatting steps after your sort that you do not want to undo or…
- You have saved and closed the workbook
Then you’ll have to try one of the options below.
Contents
Solutions
Create a reference to the raw data before you do any formatting, using either of the below methods:
- Create an additional column of Serial numbers (S. No)
- Make a backup of the workbook / worksheet so the original sequence is not lost
Trying out the solutions
Create an additional column of serial numbers (S. No)
- Insert a column to the left of column “A”. Name the header “Serial no.”
2. Fill numbers 1,2 in the cells A2 and A3 respectively and drag this down to the end of the table to autocomplete serial numbers.
Or…
3. Another option is to use the formula “Row ()-1
” and drag the formula down to the last row of data in the table.
4. Now the data can be formatted/sorted based on your requirements. When the user wants to go back to the original order of data, the S. no. column can be sorted from A-Z/ smallest to largest.
Make a backup of the workbook / worksheet so the original sequence is not lost
To make it simple, a copy of the worksheet can be saved in the same workbook. In the event your system hangs or slows down often, it is advisable to have a backup of the entire workbook.
Below is a video link that explains how to unsort in excel using the s.no column
How Does VBA Help Here?
With the increasing demand for data manipulation, where Excel plays a major role, it is cumbersome to keep track and apply the above solutions every single time. This is where VBA steps in.
Below are the code snippets to automate the above solution. They must be copied to the VBA editor of the document and run once before sorting data.
Code to insert a “S.no” column with values
Sub original_sortorder() 'Find the last non-blank cell in column A(1) Dim LastRow As Long With ActiveSheet.UsedRange LastRow = .Rows(.Rows.Count).Row End With ' Insert a column before the current “first column” Range("A:A").Insert 'Write the header for te inserted column Cells(1, 1).Value = "S.no" 'Loop to insert a serial number For i = 2 To LastRow Cells(i, 1).Value = i - 1 Next End Sub
Code to create a backup or copy of worksheet in the same location as the document holding data
Option Explicit Sub SaveWorkbookBackup() Dim AWB As Workbook, BackupFileName As String, i As Integer, Ok As Boolean Dim temp On Error GoTo UnableToSave Set AWB = ActiveWorkbook 'Assign full path of file along file name to variable BackupFileName BackupFileName = AWB.FullName 'Checking whether file is saved 'If file is not saved then saving the file If AWB.Path = "" Then 'Displaying Save as dialog box for file saving Application.Dialogs(xlDialogSaveAs).Show Else 'changing the file extension temp = Split(BackupFileName, ".") BackupFileName = temp(0) & ".bak" Ok = False With AWB .Save 'Creating Backup of file .SaveCopyAs BackupFileName Ok = True End With End If UnableToSave: 'Code for error handling Set AWB = Nothing If Not Ok Then MsgBox "Backup Copy is Not Saved!", vbExclamation, ThisWorkbook.Name End If End Sub
Code to create a copy of the workbook in any specific location
Sub SaveWorkbookBackupToDrive() Dim AWB As Workbook, BackupFileName As String, i As Integer, Ok As Boolean Dim DriveLoc As String On Error GoTo UnableToSave 'Specify the path for making back up in D drive DriveLoc = "D:\" 'Initializing the variables Set AWB = ActiveWorkbook BackupFileName = AWB.Name Ok = False 'Checking whether file is saved 'If file is not saved then saving the file If AWB.Path = "" Then 'Displaying Save as dialog box for file saving Application.Dialogs(xlDialogSaveAs).Show Else 'Deleting file if backup file already exists If Dir(DriveName & BackupFileName) <> "" Then Kill DriveLoc & BackupFileName End If With AWB .Save 'Creating the back up file .SaveCopyAs DriveLoc & BackupFileName Ok = True End With End If UnableToSave: 'Code for error handling Set AWB = Nothing If Not Ok Then MsgBox "Backup Copy is Not Saved!", vbExclamation, ThisWorkbook.Name End If End Sub
Summary
The sorting feature of Excel helps us organize and analyze data the most efficient way. There will be times that you want your raw data back that may not be possible if the workbook has already been saved or advanced formatting has already been done. In these cases, you can apply the given solutions. Using the provided VBA code could help you do the job faster.