How to Resolve Sorting Limitations with Snips in Excel

Sorting with Snips in Excel is a known limitation due to the way Excel interacts with references in formulas. This article will help you understand the issue, explain why it happens, and guide you through a potential workaround.

Context: The Sorting Issue

Snips are linked references within Excel. Sorting these Snips is tricky because Excel has certain limitations on sorting non-absolute formula references. Filtering, however, remains unaffected and fully functional.

The challenge lies in how Excel handles cell references during sorting. Sorting disrupts dynamic references, causing Snips to lose their intended context. Unfortunately, resolving this is not straightforward due to Excel's inherent behavior and how DataSnipper interacts with it.

Solution: Using Absolute References

To enable sorting while retaining Snip functionality, you can leverage absolute references. This approach involves linking cells to their Snips using absolute references, ensuring the references remain intact during sorting.

Steps to Implement the Workaround

1. Copy the Original Data

   • Use any copy method to duplicate the cells containing Snips.

2. Paste Links

   • Use the Paste Links functionality via the Excel ribbon or mouse. This creates a new data table with dynamic references to the original Snips.

3. Convert Dynamic References to Absolute References

   • Here is a solution that will automatically convert any pasted link formula to an absolute reference:
     • Press Alt + F11 to open the VBA editor.
     • Insert a new module (Insert > Module).
     • Paste the following VBA code into the module (see code)
      

   Sub ConvertToAbsoluteReferences()
       Dim rng As Range
       Dim cell As Range
       Dim formulaStr As String
   
       ' Set the range of cells where you've pasted the links
       Set rng = Selection
   
       ' Loop through each cell in the range and update its formula to absolute references
       For Each cell In rng
           If cell.HasFormula Then
               ' Change the cell's formula to an absolute reference
               formulaStr = cell.Formula
               formulaStr = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
               cell.Formula = formulaStr
           End If
       Next cell
   End Sub
   

1. Close the VBA editor.

2. Run the Macro

   • Select the range of cells you just pasted with relative references.
   • Press Alt + F8, choose ConvertToAbsoluteReferences, and run the macro.
   • This will automatically convert all formulas in the selected range into absolute references (e.g., =$A$2, =$A$3), which will work as desired when you apply sorting or filtering.
     
     

3. Sort Data

   • Once converted, you can sort the data, and Snips will retain their references.