> For the complete documentation index, see [llms.txt](https://docs.coherent.global/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coherent.global/build-spark-services/how-to-prepare-an-excel-file-for-spark.md). # How to: Prepare an Excel file for Spark Spark is able to process complicated Excel files with ease. It is still recommend optimizing workbooks before uploading to Spark in order to make them more usable in Excel and to get the best results from Spark. ## Add default values for inputs If there are no stored values in Excel, Spark is 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](/build-spark-services/neuron.md) interprets the formulas. For more information about these messages and the self-test, see [Upload log messages](/build-spark-services/upload-log-messages.md). ## 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`](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611) or [`IFNA`](https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461). * For dates, use the universal [ISO 8601](https://www.iso.org/iso-8601-date-and-time-format.html) `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`](https://support.microsoft.com/en-us/excel/functions/averageifs-function), [`COUNTIFS`](https://support.microsoft.com/en-us/excel/functions/countifs-function), [`MAXIFS`](https://support.microsoft.com/en-us/excel/functions/maxifs-function), [`MINIFS`](https://support.microsoft.com/en-us/excel/functions/minifs-function), or [`SUMIFS`](https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b) 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 a text using by adding `'`. | For Spark, it is important 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, especially in circumstances where the dynamic spill range may change dimensions. Use functions like [`INDEX`](https://support.microsoft.com/en-us/excel/functions/index-function), or [`CHOOSEROWS`](https://support.microsoft.com/en-us/excel/functions/chooserows-function), [`CHOOSECOLS`](https://support.microsoft.com/en-us/excel/functions/choosecols-function), or [`TAKE`](https://support.microsoft.com/en-us/excel/functions/take-function) 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`](https://support.microsoft.com/en-us/excel/functions/round-function) function for any important output values and their intermediate steps to ensure consistency in precision between Excel and Spark. Even Excel natively has floating point errors! ## Performance recommendations
General * Minimize the number of worksheets. * Break unnecessary [Workbook Links](https://support.microsoft.com/en-us/excel/manage-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](https://support.microsoft.com/en-us/office/paste-options-8ea795b0-87cd-46af-9b59-ed4d8b1669ad) 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](https://support.microsoft.com/en-US/Excel/locate-and-reset-the-last-cell-on-a-worksheet) on each Excel sheet as close to `A1` as possible to minimize file sizes.
Functions * Avoid [volatile](https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation#volatile-and-non-volatile-functions) functions as much as possible ([`INDIRECT`](https://support.microsoft.com/en-us/excel/functions/indirect-function), [`OFFSET`](https://support.microsoft.com/en-us/excel/functions/offset-function), etc.). * The [`INDIRECT`](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261) function limits calculation performance optimization opportunities. Consider rewriting this formula to use a combination of [`CHOOSE`](https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc), [`MATCH`](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a), [`INDEX`](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd), and/or [`OFFSET`](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66). * Where possible, use sorted lookups to improve performance for [`MATCH`](https://support.microsoft.com/en-us/excel/functions/match-function), [`VLOOKUP`](https://support.microsoft.com/en-us/excel/functions/vlookup-function), [`XMATCH`](https://support.microsoft.com/en-us/excel/functions/xmatch-function), [`XLOOKUP`](https://support.microsoft.com/en-us/excel/functions/xlookup-function), 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`](https://support.microsoft.com/en-us/excel/functions/sumifs-function) or [`XLOOKUP`](https://support.microsoft.com/en-us/excel/functions/xlookup-function) with an array of `criteria` or `lookup_value`s. 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](https://support.microsoft.com/en-US/Excel/dynamic-array-formulas-and-spilled-array-behavior) is encouraged as it reduces repetitive formula entry in the workbook and is easier to optimize for performance. * Where [Dynamic array formulas](https://support.microsoft.com/en-US/Excel/dynamic-array-formulas-and-spilled-array-behavior) 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`](https://support.microsoft.com/en-us/excel/functions/lambda-function) functions and helpers, minimizing the use of these functions helps to improve performance. {% file src="/files/26wlZkDCIufADUnuhMTc" %} {% file src="/files/vqTLBswLXTcNEIY33ITG" %}
Tips for optimizing performance obstructions Microsoft [Tips for optimizing performance obstructions](https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-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 [`VLOOKUP`](https://support.microsoft.com/en-us/excel/functions/vlookup-function)s, consider using [`MATCH`](https://support.microsoft.com/en-us/excel/functions/match-function) and multiple [`INDEX`](https://support.microsoft.com/en-us/excel/functions/index-function) functions. * Keep array (a.k.a CSE/{}) formulas as small as possible and consider using [`SUMPRODUCT`](https://support.microsoft.com/en-us/excel/functions/sumproduct-function) instead. * Use comma syntax with [`SUMPRODUCT`](https://support.microsoft.com/en-us/excel/functions/sumproduct-function); using it with `*` is less efficient, especially for arrays. * Avoid using [Database functions](https://support.microsoft.com/en-us/excel/database-functions-reference) if possible. * Avoid using complicated formulas and functions in defined names. * Avoid using the [`SUM`](https://support.microsoft.com/en-us/excel/functions/sum-function) function for long chains of cumulative sums.
## Additional resources
Excel performance {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %}
Excel resources {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %} {% embed url="" %}