How to: Prepare an Excel file for Spark

Spark is able to process most Excel files with ease, but the larger and more complex your sheets and calculations are, the more likely optimization will help to get the best results out of Spark. With that in mind, before uploading an Excel file to Spark, it is recommended to follow some best practices and adjusting the Excel files accordingly.

Review the Excel tips

The Excel tips will help to ensure the most optimal performance in Excel and Spark.

Add default values for inputs

If there are no stored values in Excel, Spark is unable to perform a self-test across all the calculations in the Excel workbook. To benefit the most from the self-test and spot errors more easily, it is best to always add default values for inputs.

For more information about these messages and the self-test, see Review the Upload log.

Avoid data type clashes

By far the most common issue when uploading services to Spark arises when the data in a cell doesn’t match the type of data Spark thinks should be there. Excel is more flexible; it does not mind, for instance, if there’s text in a column that otherwise contains only numbers, it just ignores it or returns an error message if that cell is part of a calculation.

To reduce potential API integration issues, try to have inputs and outputs maintain the same data types across the domain of inputs and outputs. In other words, a cell value 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.

An example of this is a range of values like the following: 0, 1, 2, 3, 4+ that is used for a Data Validation List for anXinput. The inclusion of a plus sign here means that the entire list should now be designated as text in order for it to function properly in Spark. Although this does work in Spark, we would suggest to avoid type mismatches as they may not be compatible with other integrations.

Set the numbers to be a text using '.	In Format Cells set the Xinput Number Category to Text.

Set data validations

One good way to avoid these data type clashes (and the API integration issues they may cause) is to proactively set up data validations for the inputs within your Excel file and set the Number Category to Text for mixed inputs. This way, any API request made with parameters outside the defined validations will return an error message. See Set data validations for more information.

Further reading

For information on data types, see: Data types for Spark and request_data.

Apply rounding to outputs

We strongly encourage you to use the Excel ROUND function for any Xoutput values to ensure consistency between Excel and Spark. Even Excel natively has floating point errors!

Reference the root cell for spill ranges

When referencing a result from a dynamic array spill range, always reference the root cell (e.g. B4#) rather than directly linking to an interior cell. Neuron may not always be able to identify calculations that reference the interior cell references.

Remove unnecessary data from the workbook

Although not necessary we recommend making a version of the Excel workbook that is specifically for the purpose of deploying calculations.

• Remove unnecessary formatting (this can have a large impact on file sizes).

• Break any External Links.

• Remove any content that is not related to the calculation of outputs.

• For formulas that do not change depending on inputs, copy and paste values.

• Ensure your Xinputand Xoutputare only defined as a Workbook scoped Named Range.

Check for warnings and errors

After uploading a service, Spark will identify any warnings or errors encountered during the conversion process and the full output will be visible in the Upload log tab of the upload modal.

Upload logs from an existing service can be viewed in its Service Documentation. Under Upload details, scroll down to the final entry and click View upload log. More information about the log messages can be found in Neuron.