'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Restore Missing SORT and UNIQUE Functions in Excel 365
Question details
Users need to restore the SORT and UNIQUE dynamic-array functions that have disappeared from Microsoft Excel 365 despite having an active subscription.
- Product
- Microsoft Excel 365 ProPlus
- Device & OS
- Windows
- Scenario
- Attempting to use dynamic-array functions to organize or filter data in Excel, only to find the functions are missing or returning errors.
- Observed behavior
- The SORT and UNIQUE functions are missing from the Excel formula library and return a #NAME? error when manually typed, likely due to a licensing sync error.
Ensure you have your Microsoft 365 account email and password ready, as you will need to sign back in after resetting your Office credentials.
Reset Office Credentials and Activation State
Clearing old cached credentials and resetting the Microsoft 365 activation state is the most effective way to restore dynamic array functions if your license sync is stuck.
Sometimes Excel fails to recognize an active Microsoft 365 subscription, temporarily disabling premium features like dynamic arrays. Forcing a complete sign-out and clearing the local credential cache will prompt Excel to re-verify your license and restore the missing functions.
Open Excel, navigate to File > Account, click Sign out for all connected Microsoft 365 accounts, and then completely close all Office applications.
Open the Windows Control Panel, go to User Accounts > Credential Manager > Windows Credentials, and remove all saved credentials that mention Microsoft Office.
Follow the official Microsoft guidelines to reset the Office 365 ProPlus activation state. This typically involves running a command line script or using the Microsoft Support and Recovery Assistant.
Restart your computer, open Excel, sign back into your Microsoft 365 account, and verify if the SORT and UNIQUE functions are now available.
Use SORT and UNIQUE Functions Seamlessly in WPS Office
WPS Spreadsheet fully supports modern dynamic array functions like SORT and UNIQUE out of the box, allowing you to organize data without worrying about complex subscription licensing errors or activation states.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet or create a new blank workbook.
- 2. Select the target cell: Click on the cell where you want the dynamic array results to start spilling.
- 3. Use the UNIQUE function: Type =UNIQUE(range) and press Enter to instantly extract distinct values from your selected data set.
- 4. Use the SORT function: Type =SORT(range) and press Enter to automatically sort your data array in ascending or descending order.
Frequently Asked Questions
Why did SORT and UNIQUE suddenly disappear from my Excel?
These dynamic-array functions are exclusive to Microsoft 365 and Excel 2021. If your Office activation state falls out of sync or downgrades to a non-subscription cache, Excel hides these premium functions.
Can I use SORT and UNIQUE in Excel 2019 or 2016?
No, the SORT and UNIQUE functions are not available in Excel 2019 or earlier standalone versions. You must upgrade to Microsoft 365 or use Excel for the Web to access dynamic arrays.
How do I know if my version of Excel supports dynamic arrays?
Open a blank workbook and type '=UNIQUE(' into a cell. If the autocomplete suggestion appears with a formula tooltip, your version supports dynamic arrays. If it throws a #NAME? error, it does not.
