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.

Solutions

Create a reference to the raw data before you do any formatting, using either of the below methods:

1. Create an additional column of Serial numbers (S. No)
2. 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)

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