Tips for structuring a workbook

Updated 2026-11-13

How to lay out a workbook so that it becomes a clear, reliable service: where to put input and output cells, how labels and formats help, and what to consider for random and time functions and for large tables.

How a call uses your workbook

For each call, SpreadAPI writes the caller's input values into the input cells, lets the workbook calculate, and reads the output cells. An optional input the caller does not send takes the default you defined. Nothing a caller sends is stored in the workbook, and one call never affects the next.

This leads to a simple layout rule: values that change per call go into input cells, everything else stays in the workbook.

Input cells

  • One value per cell, without a formula. An input is a single cell that holds a plain value. A cell with a formula is offered as an output instead.
  • Put a label next to it. When you add a cell, SpreadAPI looks for a text label up to four cells to the left, then up to four cells above, and suggests the title and the name from it. Loan amount becomes the title "Loan amount" and the name loan_amount.
  • Format it first. A cell formatted as a percentage becomes a percentage input; see Formatting and number formats.
  • Leave a sensible value in the cell. It is suggested as the default when you make the input optional, and it keeps the workbook calculating while you build.
  • Use a drop-down list for fixed choices. A cell with a list rule becomes an input with allowed values; see Input validation.

Output cells

  • Point outputs at the final results. An output is a cell with a formula, or a range of cells. Callers see outputs only; intermediate cells stay private.
  • Label them like inputs. The label to the left or above becomes the title and name.
  • Return a table as one range. Select the whole block and add it as one output. The caller gets it as rows of values; see Add outputs.
  • Catch errors you expect. An output whose formula ends in an error returns the error text, for example #N/A. Wrap the formula in IFERROR or IFNA to return a clear value instead.
  • Add a text output for messages. A formula such as =IF(B1>B2,"Amount exceeds limit","OK") gives callers a readable status next to the numbers.

One sheet or many

Both work; inputs and outputs can sit on any sheet. A layout that has proved practical:

  • One sheet for inputs and outputs, with labels in one column and values next to them.
  • Other sheets for the calculation steps and for lookup tables.

The parameter sheet then rarely changes, even when you rework the calculation. That keeps cell addresses stable when you bring in a new version of the file; see Replace the workbook.

Names, tables and lookups

  • Defined names and Excel tables work and make formulas easier to read; see Supported Excel features.
  • Keep lookup data inside the workbook, on its own sheet. Links to other workbooks are not followed.
  • For lookups, point XLOOKUP or INDEX with MATCH at the range that holds the data rather than at whole columns.

Functions that change by themselves

NOW, TODAY, RAND, RANDBETWEEN and RANDARRAY return a new value whenever the workbook calculates. Two things follow for a service:

  • Results for identical inputs can be stored and reused. With caching on, a second call with the same inputs can receive the stored result, including the earlier random number or time. See Caching and performance for how to get a fresh calculation.
  • A result is harder to reproduce. If a caller should be able to repeat a calculation, make the date or the random seed an input instead: the caller sends 2026-11-13 and the formulas use that cell rather than TODAY().

Large workbooks

  • Size. Each plan has a limit for the size of a workbook; see File size limits. Unused sheets, old data and formatting applied to whole rows or columns add size without adding value.
  • Speed. Workbooks with very many formulas or very large lookup tables take longer to calculate. Remove calculations no output depends on.
  • Outputs. Return what callers need. A range output with thousands of cells makes every answer large.

Before you publish

  1. Check that every input cell holds a plain value and every output cell the formula you expect.
  2. Try realistic and extreme values in the test panel; see Test before publishing.
  3. Add limits to inputs where the model only makes sense within a range; see Input validation.
  4. Describe the service and its parameters for AI assistants; see Write good descriptions.

For developers and agents

  • Inputs are single cells without a formula; outputs are single cells or ranges. A service needs at least one parameter before it can be published.
  • Label search: up to four cells to the left, then up to four cells above; empty cells are skipped and the search stops at the first cell that holds a number.
  • Name from a label: lower case, ä ö ü ß become ae oe ue ss, spaces and hyphens become _, other characters are dropped. Without a label the name is the cell address in lower case, for example b2.
  • For an agent building a service, suggestions follow the same idea: labelled cells without a formula that formulas reference directly are suggested as inputs, labelled formula cells that no other formula uses are suggested as outputs. See Build a service with an agent.

FAQ

How should I structure my Excel file for SpreadAPI?

Put each value that changes per call into its own cell with a label next to it, and point outputs at the cells with the final results. Keeping inputs and outputs on one sheet and the calculation on others makes later updates easy.

Can inputs and outputs be on different sheets?

Yes. A parameter can sit on any sheet of the workbook; its address carries the sheet name, for example Rates!B4.

Why does SpreadAPI suggest a name like "b2" for my parameter?

No text label was found next to the cell, so the cell address is used. Put a label up to four cells to the left of or above the cell, or type a name yourself in the dialog.

Can I use TODAY() or RAND() in a service?

Yes, they calculate. Keep in mind that a result for identical inputs can be stored and reused, so the same date or random number may be returned again; making the date an input avoids this.

Does one call change the workbook for the next caller?

No. Input values apply to that call only, and nothing carries over from one call to the next.

How do I make my service faster?

Remove sheets and calculations no output depends on, avoid formulas over whole columns, and keep range outputs small. Caching answers repeated identical calls quickly; see Caching and performance.

What happens with the values that are in my input cells when I publish?

They are part of the published workbook, and each call replaces them with the caller's values. The value in the cell is also suggested as the default when you make an input optional; see Input validation.

Can a caller see my intermediate calculations?

No. Callers get the outputs you defined and nothing else. Cells that are not outputs stay in the workbook.

Should I use named ranges for my parameters?

You can use names in formulas, and they make a model easier to read. Parameters themselves are bound to cell addresses, so the cell you add is what counts.