'%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 Fix Excel SORT and FILTER Functions Not Available
Question details
The user is encountering a function-not-valid error when trying to use dynamic-array functions like SORT and FILTER.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to use SORT or FILTER functions to organize data dynamically in a spreadsheet.
- Observed behavior
- Excel returns a function-not-valid error because the SORT and FILTER functions are unavailable or not recognized by the current software version.
Ensure you have an active internet connection to download any necessary Office updates and save your current work before initiating a software repair.
Check Your Excel Version and Install Updates
Verify that your version of Excel supports dynamic-array functions and apply the latest Microsoft 365 updates.
SORT and FILTER are dynamic-array functions introduced in Microsoft 365 and newer perpetual versions like Excel 2021. Older standalone versions, such as Excel 2019 or 2016, do not natively support these functions.
Launch Microsoft Excel, open a blank workbook, and click on 'File' in the top-left corner. Select 'Account' from the bottom of the left-hand menu.
Look under the 'Product Information' section on the right side to confirm your subscription or version. Ensure you are using Microsoft 365 or at least Excel 2021.
Click on the 'Update Options' button next to the Office Updates section, then select 'Update Now' from the drop-down menu to install the latest features.
Run a Quick Repair on Your Office Installation
Use the built-in Windows repair tool to fix corrupted Office files that might be causing the functions to disappear in supported versions.
Use WPS Office as a Free Alternative with High Compatibility
If your current version of Microsoft Excel doesn't support modern functions like SORT and FILTER and you prefer not to purchase an expensive upgrade, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative with a familiar user interface.
- 1. Download and Install WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Launch WPS Spreadsheet: Open the WPS Office desktop app and click on 'Spreadsheet' to start a new document or open an existing one.
- 3. Open Your Excel Files: Drag and drop your existing .xlsx files directly into the WPS interface to continue working on your data seamlessly.
Frequently Asked Questions
Why do SORT and FILTER show a #NAME? error in older Excel versions?
Older versions of Excel, such as Excel 2016 and 2019, do not support dynamic-array functions. If a workbook containing these functions is opened in an older version, Excel does not recognize the function name and returns a #NAME? error.
Can I get dynamic-array functions without subscribing to Microsoft 365?
Yes, you can purchase the perpetual license of Excel 2021 or newer standalone editions. These newer versions include native support for dynamic-array functions like SORT and FILTER without requiring a recurring monthly subscription.
Does the Office Quick Repair delete my personal files or settings?
No, running a Quick Repair from the Windows settings only checks and replaces corrupted installation files for Microsoft Office. It does not affect, modify, or delete your personal documents, spreadsheets, or customized application settings.
