Hey there, let's tackle this 96-well plate sorting headache you're having with Excel 16.13 on your Mac 10.12.6. That UI limit of 79 custom list entries is super frustrating, but we've got two solid workarounds to fix this:

方法1：用辅助列生成排序键（无需宏，简单直接） #

This method skips custom lists entirely and uses formulas to generate sortable keys, perfect for both your regular and special sorting needs:

常规排序（A1-A12 → B1-B12 → ... → H1-H12） #

In a blank column (say, column B), enter this formula in cell B1 and drag it down to cover all your well IDs:

=CODE(LEFT(A1,1))-64&TEXT(RIGHT(A1,LEN(A1)-1),"00")

• How it works: Converts the letter to a number (A=1, B=2...H=8), formats the row number as two digits, then combines them. A1 becomes 101, A10 becomes 110, so sorting by this column will put everything in the correct order.
• To use: Select your entire data range, then sort by the auxiliary column.

特殊排序（A1 → B1 → A2 → B2 → ... → H12） #

Adjust the formula to prioritize row numbers first:

=TEXT(RIGHT(A1,LEN(A1)-1),"00")&CODE(LEFT(A1,1))-64

• How it works: Formats the row number as two digits first, then appends the letter's numeric value. A1 becomes 011, B1 becomes 012, A2 becomes 021—sorting by this column will give you that row-first order you need.

方法2：用VBA创建完整的自定义排序列表（一劳永逸） #

If you prefer using custom lists for sorting, we can bypass the 79-entry UI limit with a quick VBA script to add all 96 well IDs:

Step 1: Add the regular sort custom list #

1. Open your Excel file, press Option + F11 to launch the VBA Editor
2. Click Insert → Module from the menu bar
3. Paste this code into the module:

Sub Add96WellPlateList()
    Dim wellList(1 To 96) As String
    Dim rowNum As Integer, colNum As Integer
    Dim index As Integer
    
    index = 1
    ' Generate A1-A12, B1-B12...H1-H12 sequence
    For colNum = 65 To 72 ' ASCII codes for A to H
        For rowNum = 1 To 12
            wellList(index) = Chr(colNum) & rowNum
            index = index + 1
        Next rowNum
    Next colNum
    
    ' Add to custom sort lists
    Application.AddCustomList ListArray:=wellList
    MsgBox "96-well plate regular sort list added successfully!"
End Sub

4. Click the green run button in the toolbar to execute the macro. Once you see the success popup, close the VBA Editor
5. You'll now see this full list in your custom sort options

Step 2: Add the special row-first sort custom list #

If you need the A1,B1,A2,B2... sequence, replace the code with this:

Sub AddSpecialWellPlateList()
    Dim wellList(1 To 96) As String
    Dim rowNum As Integer, colNum As Integer
    Dim index As Integer
    
    index = 1
    ' Generate A1,B1,A2,B2...H12 sequence
    For rowNum = 1 To 12
        For colNum = 65 To 72
            wellList(index) = Chr(colNum) & rowNum
            index = index + 1
        Next colNum
    Next rowNum
    
    Application.AddCustomList ListArray:=wellList
    MsgBox "96-well plate special sort list added successfully!"
End Sub

Note: Save your file as .xlsm (Macro-Enabled Workbook) to keep the custom lists accessible. When you reopen the file, enable macros to use the lists.

总结 #

• The auxiliary column method is great for one-off sorts and doesn't require macros
• The VBA method sets up permanent custom lists for faster future sorting

内容的提问来源于stack exchange，提问作者NikkiAtWork