Can Office Script UDFs Work in Excel What-If Data Tables? | WPS Spreadsheets
Question details
The user asks whether Office Script user-defined functions can be used inside Excel What-If Data Tables and why they may fail to calculate correctly.

- Product
- Microsoft Excel / WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants to use an Office Script UDF in an Excel What-If Data Table to calculate multiple input scenarios automatically.
- Observed behavior
- The Data Table processes the entire range rather than cell by cell, and asynchronous Office Script UDF calculations cannot reliably return values for individual iterations.
Before testing this behavior, create a small What-If Data Table with the Office Script UDF and compare the table result against the same UDF entered in a normal cell. This confirms whether the UDF works normally but fails inside the Data Table.
Use native WPS Spreadsheets formulas for reliable What-If Data Tables
Use this method when you need a Data Table that recalculates reliably, because native WPS formulas run synchronously and can be evaluated as part of the table range.
Launch WPS Office, click WPS Spreadsheets, and open the workbook that contains the What-If Data Table.
Select the top-left cell of the Data Table range and look at the formula bar to find the Office Script UDF reference.
In the formula bar, replace the Office Script UDF call with a WPS built-in formula that references the Data Table input cell, such as =B1*1.1.
Press F9 to recalculate the worksheet, then check that the Data Table fills the output range with the expected values.

Use helper cells to pre-calculate the Office Script UDF outside the Data Table
Use this if you must keep Office Script UDF results; calculate each value in a helper cell first, then have the Data Table reference the helper results.
Switch to WPS Spreadsheets for Predictable Data Table Calculations
Because Office Script UDFs inside Excel What-If Data Tables are a Microsoft Excel limitation, WPS Spreadsheets is a free, lightweight alternative that opens your .xlsx files and handles Data Tables with standard synchronous formulas. Its familiar ribbon and worksheet functions let you replace script-based calculations without changing your workflow.
- 1. Download WPS Office: Go to https://www.wps.com and click Download, then install WPS Office on your device.
- 2. Open your Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and browse to the .xlsx file containing the Data Table.
- 3. Replace the Office Script UDF: Select the Data Table formula cell, replace the Office Script UDF with a native WPS formula that references your input cell.
- 4. Recalculate and save: Press F9 to recalculate, confirm the Data Table fills correctly, then save the file as .xlsx.

Frequently Asked Questions
Why do Office Script UDFs fail inside Excel What-If Data Tables?
Excel populates a Data Table as a complete range rather than cell by cell, and Office Script UDF calculations may be asynchronous. This means results cannot be reliably linked to individual table iterations.
Can WPS Spreadsheets run Office Script UDFs?
No. Office Scripts are a Microsoft 365 Excel feature. WPS Spreadsheets uses standard worksheet functions and its own macro tools instead.
What is the reliable WPS alternative for a What-If Data Table?
Replace the Office Script UDF with a native WPS formula that references the Data Table input cell. In WPS Spreadsheets, open the Data tab and use the What-If Analysis Data Table tool.
Will my .xlsx workbook with a Data Table open in WPS Spreadsheets?
Yes. WPS Spreadsheets opens .xlsx workbooks, keeps existing formulas and formatting, and can save back to .xlsx format.
Is there a setting to force Office Script UDFs to be synchronous?
No. Office Script calculations remain asynchronous; standard worksheet functions are the synchronous alternative. WPS native formulas avoid this limitation.




