Sorting Excel 2016 rows by a custom formula requires using the SORT function is not possible as it is exclusive to newer versions. Instead, you must leverage the legacy Sort dialog combined with a helper column that uses functions like MID, FIND, or LEFT to extract specific characters for ranking. This approach ensures your data orders correctly based on complex criteria without relying on non-existent dynamic array features.
Understanding the Limitation in Excel 2016
Many modern tutorials suggest using theSORTBY function to sort data based on another range. However, this function was introduced in Microsoft 365 and Excel 2021. If you are using Excel 2016, attempting to enter =SORTBY(A2:B10, C2:C10) will result in a #NAME? error. The engine simply does not recognize the function name. Therefore, the strategy shifts from dynamic array formulas to static helper columns. A helper column acts as an intermediary layer that calculates the sort key for each row, allowing the standard sorting tools to process the data accurately.
Creating a Helper Column for Letter-Based Sorting
To sort rows based on one or multiple letters within a string, you first need to isolate those letters into a new column. Suppose you have a list of product codes in column A (e.g., "Item-A", "Item-B") and you want to sort them alphabetically by the letter following the hyphen. You can create a helper column in column B. In cell B2, enter a formula that extracts the relevant character. For example, if the letter is always at the fifth position, use:=MID(A2, 5, 1)MID function parameters accordingly. Once you have populated this formula down the entire dataset, column B contains the raw values that determine the sort order. This method is robust because it works in all versions of Excel, including 2016, and does not break when new rows are added if you convert your range to a Table. For more information on managing tables effectively, see Retrieving Auto-updated Data Added to Table in Excel: A Comprehensive Guide.
Executing the Sort Operation
With the helper column populated, the next step is to apply the actual sort. Select any cell within your data range. Navigate to theData tab on the Ribbon and click the Sort button. This opens the Sort dialog box. In the "Column" dropdown, select the header of your helper column (e.g., "SortKey"). Ensure the "Sort On" option is set to "Values" and choose either "A to Z" for ascending order or "Z to A" for descending order. It is critical to check the "My data has headers" box if your range includes header rows; otherwise, Excel may treat the first row of data as a header, potentially causing misalignment or errors in subsequent sorts. Click OK to apply the changes. Your main data in columns A and beyond will now rearrange itself according to the calculated values in column B.
Handling Multiple Sorting Criteria
Sometimes a single letter is not enough to define a unique order. You might need to sort primarily by one letter and secondarily by another. In Excel 2016, you can handle this by adding additional helper columns. Create a second helper column in C2 with a formula for the secondary criterion, such as:=MID(A2, 7, 1)Data tab and open the Sort dialog again. Add a second level by clicking "Add Level". Set the second condition to sort by the new helper column in column C. This creates a hierarchical sort where the primary key takes precedence. This multi-level approach mimics the behavior of complex dynamic sorting found in newer Excel versions but relies on explicit column management. If you frequently perform these operations, consider bookmarking How to Find Display Name List Names in Two Separate Columns in Excel: A Tech Support Guide for techniques on managing related data fields efficiently.
Maintaining Data Integrity After Sorting
Once sorted, you may wish to hide or delete the helper columns to clean up the worksheet. You can right-click the helper column header and select "Hide" to remove it from view without deleting the underlying logic. If you delete the column, remember that any future additions to your dataset will not automatically generate new sort keys unless you use an Excel Table structure. Converting your range to a Table (Ctrl+T) ensures that formulas in the helper column auto-fill as new rows are appended. This prevents the common issue where new data remains unsorted or out of order. Additionally, if you are moving data between sheets during this process, refer to How to Copy Range of Cells from Excel Sheet B to Sheet A (Case A1) to ensure your references remain intact.
Frequently Asked Questions
Can I use VBA to sort by custom formula in Excel 2016?
Yes, you can write a VBA macro to evaluate a formula and sort the range programmatically. However, using a helper column with standard functions is generally faster and easier to maintain for most users. VBA requires enabling macros and understanding basic scripting, which adds unnecessary complexity for simple sorting tasks.
What happens if my text contains numbers instead of letters?
The MID and FIND functions work equally well with numeric characters. If you are extracting numbers, Excel will treat them as text strings during the sort. To ensure numerical sorting rather than alphabetical (where "10" comes before "2"), you should wrap your extraction formula in the -- operator or use VALUE() to coerce the text into a number.
Does hiding the helper column affect the sort order?
No, hiding a column only changes its visual presentation. The underlying data remains part of the selected range used for sorting. As long as the helper column is included in the original selection or the source table, Excel will use its values to determine the final arrangement of the visible data.
Why did my sort reorder other columns incorrectly?
This usually occurs when the "Expand the selection" option is not chosen during the sort process. Always ensure that all related columns move together. If you selected only the helper column before clicking Sort, Excel might ask whether to expand the selection. Choose "Expand the selection" to keep associated data aligned with their respective keys.
Is there a way to avoid helper columns entirely?
In Excel 2016, avoiding helper columns for complex custom sorting is difficult. While you can use advanced filtering, it does not persistently sort the data. The helper column method is the most reliable native workaround until you upgrade to a version that supports dynamic array sorting functions.