Calculation Issues

Can Office Script UDFs Work in Excel What-If Data Tables? | WPS Spreadsheets

Anum MushtaqAnum Mushtaq Nov 13, 2026 869 views

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.

Can Office Script UDFs Work in Excel What-If Data Tables?
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 you start

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.

Solution 1Recommended

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.

1
Open the workbook in WPS Spreadsheets

Launch WPS Office, click WPS Spreadsheets, and open the workbook that contains the What-If Data Table.

2
Locate the Data Table formula

Select the top-left cell of the Data Table range and look at the formula bar to find the Office Script UDF reference.

3
Replace the UDF with a native formula

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.

4
Recalculate and verify

Press F9 to recalculate the worksheet, then check that the Data Table fills the output range with the expected values.

Use native WPS Spreadsheets formulas for reliable What-If Data Tables
Synchronous recalculation: Native worksheet functions recalculate synchronously, so What-If Data Tables can update without waiting for asynchronous script completion.
Free Microsoft Office alternative

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. 1. Download WPS Office: Go to https://www.wps.com and click Download, then install WPS Office on your device.
  2. 2. Open your Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and browse to the .xlsx file containing the Data Table.
  3. 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. 4. Recalculate and save: Press F9 to recalculate, confirm the Data Table fills correctly, then save the file as .xlsx.
Built-in WPS formulas update synchronously in What-If Data Tables, avoiding asynchronous Office Script UDF issues.Full compatibility with Microsoft Excel .xlsx, .xls, and .csv formats.Free and lightweight Office suite for Windows, macOS, Linux, Android, iOS, and web.Familiar ribbon and worksheet interface for a smooth migration from Excel.Seamless support for What-If Analysis and Data Table workflows.
microsoft office alternative - wps office

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.