Skip to main content

How to resolve sorting limitations with Snips in Excel

Why sorting Snips in Excel is limited, and a VBA workaround that converts pasted links to absolute references so Snips keep their context when sorted.

Sorting Snips in Excel is a known limitation caused by how Excel handles references in formulas. This article explains why it happens and walks through a workaround. Filtering is unaffected and works normally.

The sorting issue

Snips are linked references within Excel. Sorting them is tricky because Excel has limitations on sorting non-absolute formula references. Sorting disrupts dynamic references, causing Snips to lose their intended context. This is due to Excel's inherent behaviour and how DataSnipper interacts with it.

Solution: use absolute references

To enable sorting while keeping Snip functionality, link cells to their Snips using absolute references so the references stay intact during sorting.

  1. Copy the original data. Duplicate the cells containing Snips using any copy method.

  2. Paste links. Use the Paste Links option from the Excel ribbon or right-click menu. This creates a new data table with dynamic references to the original Snips.

  3. Convert dynamic references to absolute references. Press Alt + F11 to open the VBA editor, insert a new module (Insert > Module), and paste the code below.

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 and update its formula to absolute references
    For Each cell In rng
        If cell.HasFormula Then
            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 pasted, press Alt + F8, choose ConvertToAbsoluteReferences, and run it. This converts all formulas in the selected range into absolute references (for example =$A$2, =$A$3), which work as expected when you sort or filter.

  3. Sort the data. Once converted, you can sort the data and Snips retain their references.

Important notes

  • Formatting: cell formatting stays intact. You can adjust it manually after sorting if needed.

  • Limitations: this workaround does not address dynamic references beyond the converted range. Ensure all references are absolute before sorting.

  • VBA access: macros must be enabled in your Excel workbook to run the script.

Did this answer your question?