For the complete documentation index, see llms.txt. This page is also available as Markdown.

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, "") .

    • Functions that capture errors such as IFERROR or IFNA.

  • For dates, use the universal ISO 8601 YYYY-MM-DD format.

  • 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, or SUMIFS functions, be sure the criteria_range and criteria use the same datatypes. For example, if the criteria is a text value, then all the cells in the criteria_range should 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.

Set the numbers to be a text using '.

In Format Cells set the Xinput Number Category to Text.

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.

To use any element from a spill range, always reference the root cell.

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

General
  • Minimize the number of worksheets.

  • Break unnecessary Workbook Links to reduce the chance of unintended behaviors.

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

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

  • For cells containing formulas that do not change depending on inputs, Paste Values to reduce the number of calculations that need to be performed.

  • For calculations that are repeated, try to reference the calculation once rather than having it repeated many times in the workbook.

  • Try to keep the last cell on each Excel sheet as close to A1 as possible to minimize file sizes.

Functions
  • Avoid volatile functions as much as possible (INDIRECT, OFFSET, etc.).

  • The INDIRECT function limits calculation performance optimization opportunities. Consider rewriting this formula to use a combination of CHOOSE, MATCH, INDEX, and/or OFFSET.

  • Where possible, use sorted lookups to improve performance for MATCH, VLOOKUP, XMATCH, XLOOKUP, etc. See the sample file below.

    • Ensure the lookup_array is sorted.

    • Exclude the header row from the lookup_array parameters.

    • Whole-column references such as =VLOOKUP(B1, A:A, 1, FALSE) are not recommended.

  • For worksheets that use a large number of lookups or sums, consider using SUMIFS or XLOOKUP with an array of criteria or lookup_values. This provides Spark with the opportunity to optimize the calculations collectively for better performance, see the sample file below.

  • As an extension of the previous point, broader use of Dynamic array formulas is encouraged as it reduces repetitive formula entry in the workbook and is easier to optimize for performance.

  • Where Dynamic array formulas cannot be used, for columnar formula ranges, dragging the formulas down using the bottom right handle is better than copying and pasting. This is more easily identified as the same formula range in the Excel XML description of the formulas.

  • Avoid using multiplication as boolean in a calculation. Instead of =A1*B2*(C3=23) use =IF(C3=23,A1*B1) to minimize the number of mathematical operations.

  • Although Spark has extensive support for LAMBDA functions and helpers, minimizing the use of these functions helps to improve performance.

Tips for optimizing performance obstructions

Microsoft Tips for optimizing performance obstructions key takeaways for Spark users:

  • Avoid the use of forward reference to refer to cells that are to the right or below.

  • Minimize the use of circular references with iteration ("unroll" them instead).

  • Avoid links between workbooks.

  • Minimize links between worksheets.

  • Minimize the used range.

  • Be aware that whole-column references may not be as performant.

  • In lieu of multiple VLOOKUPs, consider using MATCH and multiple INDEX functions.

  • Keep array (a.k.a CSE/{}) formulas as small as possible and consider using SUMPRODUCT instead.

  • Use comma syntax with SUMPRODUCT; using it with * is less efficient, especially for arrays.

  • Avoid using Database functions if possible.

  • Avoid using complicated formulas and functions in defined names.

  • Avoid using the SUM function for long chains of cumulative sums.

Additional resources

Excel performance
Excel resources

Last updated