Fix Excel Crashes When Applying Conditional Formatting to a Data Table
Question details
The user needs a way to apply conditional formatting to a sensitivity-analysis data table without causing Microsoft 365 Excel to crash.

- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- A conditional-formatting formula using FORMULATEXT, FIND, and ISNUMBER works on normal cells, but the user needs to apply it to a sensitivity-analysis data table.
- Observed behavior
- Microsoft 365 Excel crashes or becomes unresponsive when the conditional formatting rule is applied to the data table.
Open a copy of the workbook, note the exact conditional-formatting formula, and confirm that automatic calculation can be disabled from Formulas > Calculation Options before testing.
Exclude the Data Table Range from the Conditional Formatting Rule
Use this solution when the formatting is needed on normal cells but causes Excel to crash only when it includes the sensitivity-analysis data table.
In Microsoft 365 Excel, go to Home > Conditional Formatting > Manage Rules.
Select the conditional formatting rule and change the Applies to range so it does not include the sensitivity-analysis data table.
Click Apply or OK, then recalculate or edit the data table to confirm Excel no longer crashes.

Test with Automatic Calculation Disabled
Use this solution to identify whether the data table calculation mode is conflicting with the conditional formatting rule.
Simplify the Conditional Formatting Formula and Check for Circular References
Use this solution if you must keep formatting inside the data table and need a more stable rule formula.
Open and Format Data Tables Safely in WPS Office Spreadsheets
If Microsoft 365 Excel keeps crashing on data-table conditional formatting, WPS Office is a free, lightweight alternative that opens Excel workbooks and gives you a familiar interface to review, edit, and save conditional formatting rules without the same calculation conflict.
- 1. Download WPS Office: Go to the WPS Office download page and download the free installer for Windows, macOS, Linux, Android, or iOS.
- 2. Open the Workbook in WPS Spreadsheets: Launch WPS Office, click Menu, and open the .xlsx workbook that contains the sensitivity-analysis data table.
- 3. Review Conditional Formatting Rules: Go to Home > Conditional Formatting > Manage Rules in WPS Spreadsheets, adjust the rule range or formula, and save the file in Excel-compatible format.

Frequently Asked Questions
Why does a conditional formatting formula work on normal cells but crash a data table?
Data tables use a separate calculation behavior for sensitivity analysis. A rule that uses FORMULATEXT, FIND, and ISNUMBER can create a calculation conflict when evaluated inside the table, making Excel crash or freeze.
Can I keep conditional formatting on my data table?
Yes, try simplifying the rule, referencing a helper column instead of FORMULATEXT, or excluding the data table from the rule range. Also set Formulas > Calculation Options to Manual before testing.
What should I check before applying the conditional formatting rule?
Open Home > Conditional Formatting > Manage Rules and confirm the Applies to range does not overlap the data table output. Then go to Formulas > Error Checking > Circular References to verify there is no self-reference.
Will installing Microsoft 365 updates fix the crash?
It may help. Install the latest Microsoft 365 updates, but if the crash persists, use manual calculation or move the conditional formatting outside the data table range.
Does WPS Office support Excel files with conditional formatting?
Yes. WPS Spreadsheets opens .xlsx workbooks and supports conditional formatting, so you can review and edit the rules in a free Office alternative.




