How to: Prepare an Excel file for Spark
Spark is able to process complicated Excel files with ease. Optimize your workbook before uploading it to Spark to get the best results from Spark.
Add default values for calculation inputs
If there aren't any values in the calculation input cells (default values), Spark may be unable to perform a full self-test.
The self-test is performed after the Excel conversion to code to verify that the calculations in the Excel file match how Neuron interprets the formulas. For more information about these messages and the self-test, see Upload log messages.
Avoid data type inconsistencies
There may be mismatches in interpretation of input values between numbers and text. In Excel, a cell can result in a number, date, or text. To reduce potential API integration issues:
Try to ensure inputs and outputs maintain the same data types across the domain of inputs and outputs. In other words, a cell should only always be a number, or always a date, or always text. Mixing numbers, dates, and text in one cell will lead to inconsistencies in the inputs and outputs in Spark. This is common in:
Data validation lists such as
1,2,3+.Formulas that return
""such as=IF(A2>B2, A2, "").
For dates, use the universal ISO 8601
YYYY-MM-DDformat.Avoid introducing the empty string
=""into cells.For single value inputs, apply Data Validation to limit the domain of inputs.
When using conditional aggregation functions such as
AVERAGEIFS,COUNTIFS,MAXIFS,MINIFS, orSUMIFSfunctions, be sure thecriteria_rangeandcriteriause the same datatypes. For example, if thecriteriais a text value, then all the cells in thecriteria_rangeshould also be text. Otherwise, inconsistent and unexpected results may be returned.
For example, a range of values 0, 1, 2, 3, 4+ is used for a Data Validation List for an Xinput. It would be best to define all of the entries as text rather than numbers.


For Excel consistency, set the numbers to be text using by adding '.
For Spark to recognize this as text, in Format Cells, set the Xinput Number Category to Text.
Reference the root cell for spill ranges
When referencing a result from a dynamic array spill range, wherever possible reference the root cell, for example B4#, rather than directly linking to an interior cell. This is especially relevant in circumstances where the dynamic spill range may change dimensions. Use functions like INDEX, or CHOOSEROWS, CHOOSECOLS, or TAKE to select items from the range.

Apply rounding to outputs
Use the Excel ROUND function for any important output values and their intermediate steps to ensure the same precision between Excel and Spark.
Performance recommendations
Additional resources
Last updated

