How to Fix LAMBDA, BYROW, and BYCOL #NAME? Errors in Excel
Question details
The user needs to fix a sudden issue where LAMBDA, BYROW, and BYCOL functions return a #NAME? error and no longer appear in Formula AutoComplete.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Using advanced array functions, particularly after the computer wakes from sleep or resumes operation.
- Observed behavior
- Functions that previously worked return a #NAME? error across all workbooks. Standard troubleshooting like restarting the computer, signing out of Office, and running Quick or Online Repair does not resolve the issue.
Ensure you have an active Microsoft 365 subscription and are fully signed into your account, as advanced functions like LAMBDA are exclusive to Microsoft 365 and will become unrecognized if subscription validation fails.
Verify Microsoft 365 Subscription and Account Status
Check your account status and update Excel, as losing the Microsoft 365 connection can cause exclusive functions to revert to a #NAME? error.
Functions like LAMBDA, BYROW, and BYCOL are only available to Microsoft 365 subscribers. If Excel temporarily loses connection to Microsoft's servers (such as after waking from sleep) or your session expires, the app may fall back to a perpetual license state (like Excel 2019/2021) which does not support these functions.
Launch Microsoft Excel, click on the **File** tab in the top-left corner, and select **Account** from the left-hand menu.
Look under the **Product Information** section. Verify that it says 'Subscription Product Microsoft 365'. If it shows an error or a different version, click **Sign In** or **Fix Me** to re-authenticate.
Click the **Update Options** button next to your product details, and select **Update Now** to ensure you are running the latest build.
Close all Excel windows entirely and reopen the application to see if Formula AutoComplete recognizes the functions again.
Test the Functions in a New Blank Workbook
Isolate the issue to confirm whether the problem lies within a specific corrupted workbook or across the entire Excel application.
Compare Builds and Escalate the Issue
If standard repairs fail and the issue affects all workbooks, gather version details to report the bug to IT or Microsoft Support.
Try WPS Office for a Stable Spreadsheet Experience
If you frequently encounter sudden missing functions, recurring errors, or frustrating software bugs in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for all your spreadsheet, document, and presentation needs without unexpected feature losses.
- 1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen instructions to set up WPS Office.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files directly to continue your work seamlessly.
Frequently Asked Questions
Why do LAMBDA and BYROW suddenly show a #NAME? error?
This usually happens because Excel temporarily lost your Microsoft 365 subscription status (often due to internet connectivity or sleep mode bugs), causing the application to revert to a state that does not support these advanced dynamic array functions.
What does a #NAME? error mean in Excel?
A #NAME? error indicates that Excel does not recognize a formula name or text within a formula. This occurs if a function is misspelled, text lacks double quotation marks, a defined name is missing, or the function is not supported by your current Excel version.
How do I check if my Excel version supports LAMBDA?
Go to File > Account. Look at the Product Information section. You must have a 'Subscription Product Microsoft 365' to use LAMBDA, BYROW, and BYCOL. Standalone versions like Excel 2019 or Excel 2021 do not support these specific functions.
