'%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)
Fix Excel Crash When Combining BYCOL and INDIRECT on Mac
Question details
Microsoft Excel crashes or displays a resource depletion error on Apple Silicon Macs when specific dynamic array and volatile functions are combined.
- Product
- Microsoft Excel 365
- Device & OS
- macOS 14.5 (Apple Silicon)
- Scenario
- Using BYCOL, INDIRECT, and INDEX functions together in complex spreadsheet formulas.
- Observed behavior
- Excel version 16.85 crashes unexpectedly or displays an 'Excel ran out of resources while attempting to calculate one or more formulas' error.
Ensure you have saved all open workbooks and backed up your files to prevent data loss before modifying complex formulas or reverting your Excel application version.
Revert to an Earlier Version of Excel (Version 16.84)
Since this is a known bug in Excel 16.85, downgrading to the previous stable version is currently the most effective temporary workaround while Microsoft investigates.
The development team has identified compatibility and resource management issues when calculating these specific arrays in version 16.85 on Apple Silicon. Rolling back your application restores stability.
Open Microsoft AutoUpdate on your Mac, click 'Advanced', and uncheck 'Automatically keep Microsoft Apps up to date' to prevent Excel from reinstalling the buggy version.
Open your Mac's 'Applications' folder, locate the 'Microsoft Excel' app, and drag it to the Trash. Empty the Trash to completely remove version 16.85.
Navigate to the official Microsoft Office for Mac release history page in your browser and download the installer package specifically for Excel version 16.84.
Run the downloaded installer. Once finished, launch Excel, open your spreadsheet, and verify that formulas containing BYCOL and INDIRECT now calculate without triggering a crash.
Restructure Your Formula to Remove Volatile Functions
If downgrading your software is not an option, modifying your spreadsheet to eliminate volatile functions like INDIRECT can often bypass the resource depletion error.
Switch to WPS Office to Avoid Calculation Crashes
If Microsoft Excel continues to struggle with complex array formulas on your Mac, consider using WPS Office. It provides a lightweight, highly optimized spreadsheet environment that smoothly handles heavy calculations without crashing.
- 1. Download the Installer: Visit the WPS Office website and download the free version designed specifically for macOS.
- 2. Install WPS Office: Open the downloaded package and follow the on-screen instructions to install the suite on your Mac.
- 3. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx file to instantly resume your work with improved stability.
Frequently Asked Questions
Why does the 'Excel ran out of resources' error appear on Mac?
This error generally occurs when Excel struggles to allocate sufficient memory for complex mathematical operations. In version 16.85 on Apple Silicon, a specific software bug causes this resource mismanagement when processing nested dynamic arrays like BYCOL alongside volatile functions.
Is INDIRECT a bad function to use in Excel?
While highly versatile for dynamic referencing, INDIRECT is a 'volatile' function. This means it forces a recalculation every single time any change is made anywhere in the workbook. Overusing it in large datasets or nesting it inside dynamic array functions can severely degrade performance and cause crashes.
When will Microsoft fix the BYCOL and INDIRECT crash?
Microsoft's development team is actively investigating the compatibility issue reported in version 16.85. While there is no exact release date for the fix, users should monitor the Microsoft 365 release notes and update to newer builds once the patch is confirmed.
