Spreadsheets

Spreadsheets skill

Read entirely for spreadsheet creation, editing, analysis, or visualization.

Decision Boundary

• Google Sheets targeted outputs also require routing/google_sheets.md. Otherwise, author local files with artifact tool.

Important Instructions

• For new workbooks or authorized redesigns, plan the simplest correct workbook that meets the task, audience, actual data and domain. If formulas become hard to read, first reconsider whether the workbook’s structure, layout, or logic is overcomplicated before simplifying individual formulas. Remove unnecessary or duplicated logic while preserving calculation correctness, required business relationships, and financial reconciliation
• Instruction precedence for workbook content, layout, and formatting is: user request > reference/template > domain defaults/conventions > general defaults.

Tools + Contract Requirements

• Author spreadsheet with @oai/artifact-tool JS and only load_workspace_dependencies executables/dependencies, never repo-local deps. If unavailable, check ~/.cache/codex-runtimes/codex-primary-runtime/dependencies/. Never modify dependency directories.
• In a writable, conversation-specific or tmp directory, create a node_modules symlink or Windows junction to the loader node_modules.
• Prefer to patch/rerun one .mjs builder. No heredocs or duplicate builders.
• Use the provided API reference for supported syntax. Its examples do not set workbook structure, formatting or formula defaults. Do not inspect package internals or prototypes. If blocked, run at most one targeted workbook.help("<api_or_feature>") query.
• No openpyxl, xlsxwriter, or pandas.ExcelWriter authoring unless asked, or @oai/artifact-tool is unavailable.
• Analyze with JS/formulas, else bundled Python (libraries) and JSON/CSV intermediates; other libraries only for missing capabilities.
• Use update_plan for complex work.
• In your final response, omit builders, previews, or other support files unless requested.
• Immediately before the first create/edit authoring command, run mark_artifact_operation_started.mjs successfully exactly once using the command below. Do not run it for read-only work. For edits, replace create with edit; adjust the expected count and output format to match the requested outputs.

  node container_tools/mark_artifact_operation_started.mjs --operation-kind create --expected-output-count 1 --output-format xlsx
  

Spreadsheet (Workbook) Complexity: Workbook Structure & Formulas

Keep the workbook simple, especially for focused tasks. A focused task produces a simple analysis, report or tracker for a specific question or workflow. It needs one main output, supported by the necessary inputs and calculations. “Focused” describes the scope of the task, not the number of source records.

Design the structure and formulas together so a reader can follow the inputs, useful calculation steps and final answer. Put summaries and main outputs first, show the work behind them, and avoid tabs or formulas that only repeat finished results. Keep separate schedules and output views when they serve distinct needs. Preserve required detail, the supplied template and the requested edit scope.

Workbook Structure

Tab Types & Relationships

Tab types describe the role each part of the workbook plays. They do not require separate tabs. A simple workbook can combine inputs, assumptions, builds and outputs in clearly labeled sections on one worksheet.

Inputs/Sources and Assumptions feed Builds; Builds calculate results and feed Outputs. These relationships describe how calculations flow, not the physical tab order. The same rules apply when roles share a tab.

Input / Sources contain the data the workbook starts from. Keep dedicated raw source or Actuals areas intact, with original values and source meaning separate from prepared calculations. Cleaning, mapping and source summaries may have their own labeled areas with clear provenance. Put business calculations, including historical calibration from actuals, in the build. Raw source data does not read results back from downstream areas.

Assumptions hold the editable drivers and controls used by the builds. When cases are needed, keep one authoritative Case selector on Cover or Assumptions. Group each driver with its Active Selection row first, followed by its labeled case inputs, such as Base and Downside, sharing the same period columns. Prefer these driver groups to separate whole-case blocks for new designs. The build links directly to each period's active input. Preserve a supplied layout during narrow edits, and do not add cases or a separate tab when the task does not need them.

Changing the Case selector updates the active forecast assumptions for each period. The same build keeps linking to those active cells and recalculates with the selected values. Outputs update from the build results while historical actuals remain unchanged.

When cases are used, display the selected case on each worksheet by linking to the authoritative selector. Keep only one editable selector; distinguish source actuals and separately labeled comparison cases from the active forecast.

In historical periods, the active assumption row may link to ratios or other measures calculated from actuals in a build. Show that history once, aligned with the build's historical period columns, to help the user set forecast assumptions. The forecast active row selects the chosen case's assumptions and feeds the build. Forecast results must not feed back into the assumptions driving that same forecast. Historical calibration is a business calculation, not a terminal Check/Audit result.

Build tabs pull source inputs and assumptions to combine historical analysis, current results and/or a forecast. Bring the relevant inputs and applicable assumptions into clearly labeled rows or columns, then calculate the results on the build. Keep periods aligned and chronological. Show meaningful steps, subtotals and totals so readers can follow the logic—for example, headcount and compensation driving personnel cost, or revenue less COGS producing gross profit. Each step should do useful work. Do not hide the whole calculation in one dense formula or make the build merely repeat finished results from elsewhere.

For a simple calculation, a small labeled assumption block can sit beside it. For a larger build, link important drivers from their control area and show the useful calculation steps. Use one set of forecast schedules driven by the active assumptions, organized by the business sequence, such as revenue, headcount, vendors and cash. Do not mirror the Assumptions grid, add Case columns or parallel named-case forecasts, or apply the selector only to finished results.

A requested case comparison still needs each case's correctly evaluated results. If the requested simultaneous current results cannot be produced with the supported single-build design, explain the limitation and agree on the calculation or refresh method before building the comparison. Do not omit it, link both cases to the active result, or silently substitute snapshots, TABLE, arrays, dense formulas or a hidden second build. Preserve explicit user/template requirements and the separately authorized native-feature and capture workflows below.

Output / Summary tabs consolidate the builds and tell the main story. These might be named “Overview,” “Summary,” “Exec Summary” or “Dashboard,” depending on the task. Bring across finished build results, show how matching totals roll into higher-level totals and put the main summary above the detail. Readers should be able to trace a headline result to its supporting build without finding the same calculation repeated elsewhere. Keep input retrieval, case selection and detailed business logic in the owning build/control area. Do not route forecast results through Assumptions before presenting them. Historical references used to set drivers and linked case/period displays remain allowed.

Check / Audit tabs review source data and builds for completeness, consistency and reconciliation. They may calculate their own diagnostics, but do not own business calculations or feed assumptions, builds or outputs. Nothing outside the check/audit area should depend on its results.

Cover, if useful gives a complex workbook a simple front page, especially for recurring or shared workflows. Include the company/project name or available logo, workbook title and relevant period or as-of date, with generous whitespace and restrained branding. Place it first. Keep analysis and methodology off the cover. Skip it for focused tasks or when the main output provides enough context.

For complex workbooks, use a separate ReadMe only when source choices, joins, scoring or refresh steps need more explanation than nearby notes. Explain the method and material limitations without repeating outputs or giving a tab tour. Put it last. Multiple sources alone do not require one.

Apply Style guidance to these tab and section roles, so formatting helps readers distinguish the main answer, editable inputs and supporting calculations.

Tab Names

Use concise names that describe each tab's purpose, such as Check or Audit for a reconciliation tab. Preserve established names during unrelated edits. For new forecast work, use Forecast review for review checks, Forecast variance for comparisons with a prior forecast, or Sensitivity for assumption tests. Do not label these tabs or views Movement or Forecast movement.

Tab Order & Progression

For a new workbook or authorized redesign, start with one clear primary view that answers the task. Start with one tab, or two when the original source needs to stay separate, for focused tasks such as a department budget versus actuals report, a peer-company valuation comparison, a weekly marketing campaign report, an appointment-capacity tracker or a research measurement log with unit conversions. Preserve required source tabs and dependencies. Put the requested summary above the supporting detail and calculations. Add another tab only for a distinct source, calculation, reader or workflow need; do not create a separate tab for every role. Keep review commentary, refresh instructions and documentation beside the relevant work when they do not need a separate workflow.

Keep separate schedules when the work requires them, such as revenue, payroll, depreciation and debt builds in a financial model. One or two tabs is a starting point for the examples above, not a limit on every workbook. Do not shrink text, hide necessary calculations or discard records to meet a tab count or fit one printed page. Preserve the supplied template and existing architecture during narrow edits.

One output worksheet can contain several useful sections. Keep original sources and substantial builds separate when needed; do not create multiple output tabs for the same answer.

For a file with multiple tabs, the physical left-to-right order is Outputs → Builds → Inputs/Sources/Internal, with a separate Assumptions control panel kept easy to reach, usually just after the primary output and before build tabs. Covers, key outputs (executive summary, financial statements, etc.) belong toward the left; working builds sit in the middle when needed; data, sources, inputs and internal documentation sit toward the right. A two-tab workbook has Output on the left and Input on the right. The logical calculation flow is Source/Input and Assumptions → Build → Output; a visible control panel may sit to the left of its builds. Do not confuse tab position with calculation sequence. Within a horizontal build, factors may feed intermediate results from left to right; preserve chronological period columns. Within a single worksheet, inputs and supporting calculations below can feed the main answer above. Preserve an intentional user/reference layout; do not reorganize a narrow edit to enforce this default.

Checks and Audit

Checks/Audit are terminal review areas and are not required for focused tasks. They read source/build evidence and may calculate or summarize their own diagnostics within that area. No formula outside a terminal check/audit area may use its results, directly or through helpers, names or dynamic references. This includes assumptions, business calculations, summaries, presented outputs, displayed statuses and output gates. Keep necessary input validation in the owning input/build logic; checks observe it independently. When separate tabs are useful, keep Checks/Audit and internal documentation toward the right. In complex workbooks, a divider such as Internal >> can group them with source data; follow Style guidance for divider and child-tab colors. Preserve useful supplied controls and notes, but do not add separate tabs for a few lines.

Build Structure and Formula Flow

Arrange labeled rows and columns so a reader can follow starting data, assumptions, useful calculation steps, subtotals and results. Follow the physical layout above; the logical sequence of inputs to results does not require every build to run from top to bottom.

• Row progression: make the useful business steps visible, such as quantity × rate, capacity used ÷ capacity available, or a balance plus its movements. Link the clean input and applicable assumption into their own labeled rows, then calculate the result on that build. Do not add trivial steps just to create more rows.
• Active assumptions: select the active assumptions once in the control area and link each period's cells directly into the same build. Do not bypass the active row, repeat case selection across schedules, put a forecast inside Assumptions or maintain parallel case builds. Resolve a required comparison's calculation and refresh method as described in Tab Types & Relationships.
• Historical reference: Assumptions may link to historical ratios calculated from actuals in a build to help set forecast drivers. Trace the cells: this actuals-only reference must not create a feedback loop from the forecast into its own assumptions.
• Column progression: keep comparable items, scenarios and periods aligned. Use the shared headers and controls described in Anchoring and Dates and Time Periods, rather than repeating them beside each calculation.
• Roll-forwards: show opening balance, relevant movements and closing balance. Normally link each new period's opening balance to the prior period's closing balance, preserving the model's actual timing and conventions.
• Reuse: keep one place that owns each calculation, then link matching results into summaries and useful output views. Apply the matching-input, period, unit, rounding and override conditions in Formula Construction.

A tab that only repeats linked values from another tab or workbook is a red flag. Build tabs should perform useful calculations and show the steps. Output tabs should bring results together and calculate relevant subtotals or totals where needed. A useful output may link directly to completed build results without adding new calculations. Keep a linking-only tab when it serves a clear source, import or reporting need; otherwise, combine or remove it within the authorized scope. Do not invent calculations merely to justify a distinct reader view.

Workbook Structure Examples

Formulas

Apply these rules to newly added or edited formulas and their affected dependencies. Follow the user's preferences and supplied template; preserve unrelated formulas and layout during narrow edits. Design formulas to support the workbook structure above: the reader should be able to follow the inputs, useful calculation steps and final answer.

Formula Construction

• Use direct references, familiar functions and meaningful intermediate calculations. Follow Build Structure and Formula Flow to show the work; do not hide an entire build in one dense formula or add trivial helpers just to make formulas shorter.
• Keep raw data, editable assumptions, mappings and business rules in labeled cells or tables. Mathematical, index and control constants may remain in formulas. Keep calculated results as formulas so they update with their inputs.
• Fixed cutoffs or categories from the user's request can appear directly in formulas when result labels state the rule. For example, label COUNTIFS(B2:B100,">1000") as Invoices over $1,000, without adding an input cell for 1000. Use one labeled input cell when the cutoff is user-adjustable or serves as a shared assumption across different calculations.
• Calculate a shared result once and reuse it when the inputs, period, units, rounding and overrides match. Keep independent reconciliations independent.
• Use consistent formulas across comparable rows and periods, while preserving intentional differences such as historical versus forecast logic, one-off adjustments and overrides.
• Keep business calculations in the owning build and necessary input guards with their inputs or dependent build logic, following the terminal Checks/Audit rule. Do not invent business restrictions or wrap ordinary calculations in repeated workbook-wide validation gates. For example, use =SUM(I11:I12) for a valid total; do not add an IF that rejects a negative result unless the business rule requires it.

Anchoring

Use $ to fix only the part of a reference that must stay in place when a formula is copied. Anchor shared rows, columns or individual cells so the workbook can reuse one period header, assumption block, item column or Case selector instead of repeating it beside every calculation.

For example, =SUMIFS(Amount,Month,C$4,Item,$A8) reads the period above and the item at the left. Copied one column right it uses D$4; copied one row down it uses $A9. The aligned named ranges represent the source columns; they do not require named ranges in the delivered workbook.

A period-specific assumption should move with its period: =C8*C$3 becomes =D8*D$3 when copied across. A single assumption shared by every period should stay fixed: =C8*$B$3 becomes =D8*$B$3. Choose between them from the model's meaning, not by adding $ everywhere. Use keyed lookups when source and destination orders differ; anchoring cannot make mismatched row positions equivalent. Quote cross-sheet names, for example ='Build'!E14.

Dates and Time Periods

• When calculations depend on a reporting date, use the date specified by the task or source. Use TODAY() only when calculations should update with the current date. Use a fixed reporting date when results should remain tied to a particular date. Label any assumed date. Preserve source deadlines and flag conflicts with derived deadlines.
• Review the template's calendar, period layout and source grain before building formulas. Use real dates where the source supports them, with number formats for display; do not invent a missing reporting year. Derive period filters and labels from the shared header rather than hardcoding months in individual formulas.
• For a new Week of label, use the week's first business day as the underlying date: Monday by default, moved forward for holidays only when a holiday calendar is supplied. Follow an explicit source/template week convention. Do not invent holidays or relabel a week-ending date as a week start.
• When several time scales are needed and the template does not prescribe a layout, place the broader summaries to the left and finer detail to the right: Annual | Quarterly | Monthly | Weekly. Include only the time scales needed for the task. Keep periods chronological from left to right within each group; use the supplied fiscal calendar and week convention.
• Separate different time scales with narrow, blank, unfilled spacer columns; do not extend formatting down the entire column. Do not add a spacer merely between actual and forecast months in one continuous schedule. Align matching period columns across Assumptions, builds and summaries where practical. When recent actuals help set drivers, include that historical reference on Assumptions in the same period column as the build, followed by the matching forecast periods. Within a continuous schedule, use one shared period header rather than repeating identical date rows above every subsection. Keep it visible when useful; separate tables with different column meanings may need their own headers, and print titles can repeat headers on printed pages.
• Match each period to its own assumptions and data. Roll detail into summaries using the right calculation: sum additive amounts, use the appropriate ending balance for stocks, and calculate ratios or weighted averages from the relevant components. Do not sum monthly percentages or double-count weeks that cross month boundaries.

For a monthly summary of daily dates, with C4 holding the first day of the month and aligned source ranges, use =SUMIFS(Amount,Date,">="&C$4,Date,"<"&EDATE(C$4,1),Item,$A8). The next-month exclusive upper bound includes the full last day, including timestamps. Equality to C$4 is appropriate only when the source already stores that same monthly key.

Choosing Formulas and Excel Tools

• Totals and products: use SUM over the relevant detail for total rows. Use PRODUCT for a result built by multiplying a range of numeric factors, or direct multiplication for a simple two-cell calculation. Use SUMPRODUCT for a sum of matching quantity × rate pairs or a weighted calculation. Keep ranges aligned and bounded; do not include both subtotals and their detail. Check required factors first: PRODUCT ignores blank/text cells in a referenced range, which can make missing inputs look like a valid result.
• Conditional counts, sums and averages: prefer COUNTIFS, SUMIFS and AVERAGEIFS for new formulas, even with one criterion, so another condition can be added consistently. Avoid choosing COUNTIF, SUMIF or AVERAGEIF for new work by default; preserve a valid existing/template convention during a narrow edit. This preference does not prohibit an ordinary IF condition.
• Lookups: INDEX/MATCH, VLOOKUP and XLOOKUP are all useful. Follow the user's preference and the workbook's established approach where it works. Make exact versus approximate matching intentional, handle missing keys explicitly and confirm whether duplicate keys should be rejected, matched once or aggregated. Do not substitute a first-match lookup for a required sum.
• Conditional logic: use a short IF for a simple choice. Nested IF formulas are appropriate when they express necessary, understandable logic, including advanced Finance calculations. For a long list of categories or editable rules, prefer a mapping table or labeled steps. Preserve rule order, boundaries, gaps and the unmatched case; do not replace useful business logic merely to reduce nesting.
• Formula choices to avoid: do not introduce LET, array/spill formulas, MAP, REDUCE or LAMBDA. Use familiar formulas and labeled intermediate steps. Normal range arguments in functions such as SUMIFS and SUMPRODUCT remain appropriate, as do the lookup, INDIRECT, OFFSET and CHOOSE patterns below. Preserve required existing/template behavior and do not rewrite unrelated formulas during a narrow edit. Formula length alone is not the test: the reader must be able to understand and extend the calculation.
• Sensitivity analysis: use a native What-If Data Table only for an explicitly requested native sensitivity analysis or required existing/template behavior, when supported. Do not introduce TABLE into an ordinary forecast or case comparison, or manufacture a second varying input with a metric selector. Excel supports one or two varying inputs; the current Artifact Tool supports only two-variable tables, with both input cells on the table’s worksheet. Read Data Tables before creating one. Two inputs test one output across their combinations; use separate tables for additional outputs. If the requested native design is unsupported, explain the limitation before agreeing on a formula-based design or change-input/recalculate/restore process. Label captured results and their refresh method. Ordinary case comparisons follow the single-build and comparison boundary above.

An Excel Table, PivotTable and What-If Data Table are different features. Check the chosen tool and destination's support. If a requested native feature cannot be created or preserved, explain the limitation before substituting a formula or static result. Keep API setup and feature-specific execution details in the relevant tool reference.

Scalable Formulas and Brief Explanations

Use the patterns below when they make recurring updates easier without hiding the calculation. Choose the simplest approach that supports the actual update workflow, not just the current snapshot.

• Assumption and Case selection: prefer one numeric Case selector with labeled case names and CHOOSE or OFFSET to select the active assumptions. INDEX/MATCH or XLOOKUP remain valid when they fit the layout. In each driver group, put Active Selection above its case inputs, sharing the same period header. For example, with Case in B3 and two case values in I22:I23, active I21 can be =CHOOSE($B$3,I22,I23); the matching build input is simply ='Assumptions'!I21. Anchor and validate the selector. Do not repeat the choice in the build or maintain a second editable copy of the drivers. The simple CHOOSE example assumes validated numeric case inputs. Otherwise, test the selected source value before a reference can turn a blank into zero. Preserve a valid zero. A missing unselected case must not block the active case. In an agreed comparison, mark only the affected case and dependent deltas unavailable. Keep necessary validation local to the driver and reuse it. OFFSET and INDIRECT are volatile, so keep references bounded and consider recalculation cost.
• New monthly source tabs: if the workflow receives a separate tab in the same format each month, a visible month-to-tab registry and bounded INDIRECT references can support new periods without rewriting the reference pattern. Register the new tab and extend the summary periods or bounded ranges when needed. Validate the expected layout, tab names and source coverage; quote and escape sheet names correctly. For a new workflow without that constraint, one source table with a Month column may be simpler.
• Explain recurring updates: when a less familiar formula materially improves the workbook, add a short explanation near its control or in the existing guide: why it helps, what the user can change and how to extend it safely. For example: “Add the new month tab in the same layout and register its name in Setup. Extend the summary period and ranges if needed; the formulas keep the same reference pattern.” Keep this brief; do not add comments to every formula or create a new instruction tab for one note.

Missing Inputs, Errors and Overrides

• Do not invent missing source data or substitute a different metric. If required data is absent, leave the result unavailable and state the specific missing input beside its data or setting and briefly in the response. For a rate, preserve the requested numerator, denominator, population and period; do not substitute another available denominator.
• Distinguish a real zero from missing data, an unavailable result and something that is not applicable. Use "n.a.", a deliberate "" blank, or an exposed error according to the user's preference and the calculation's meaning. Right-align n.a. and similar placeholders when they sit among numeric results; do not turn them into numeric zero for appearance.
• IFERROR can be useful for a deliberate, understood fallback, but must not hide unexpected failures. Prefer testing the expected condition directly, or IFNA/a lookup's not-found result when only a missing match is expected. Do not blanket-wrap formulas in IFERROR(...,0) or IFERROR(...,"") to make broken references and bad inputs disappear. Text "n.a." and the #N/A error are different; choose intentionally and ensure downstream formulas handle the result correctly.
• Guards such as ISNUMBER must not turn a failed prerequisite into a healthy zero or an understated issue count. Keep unexpected failures visible in the affected results, even when an intermediate formula returns text or a blank instead of an error.
• Handle necessary validation in the input/build that owns it, affecting only the relevant outputs. A SUMIFS result of zero does not prove matching records exist; retain a source-coverage test when no match must remain blank or unavailable. Keep the issue visible without spreading the same long guard through every summary formula or pulling a global status from Checks/Audit.
• A matched lookup key does not prove its value is populated. Check required source values before a lookup or reference can turn a blank into zero; preserve a permitted numeric zero.
• Add manual overrides only when the task, template or established workflow needs them. Otherwise, calculate directly from the relevant drivers; do not add an optional override row to every result.
• Preserve deliberate zero overrides, blanks, one-off adjustments and rounding. A blank optional override may mean “use the base”; a zero override may mean “use zero.” Do not treat those as the same condition.

Circular References and Iterative Calculation

Avoid unintended circular references. Use intentional circular logic only when the requested model needs it and the selected tool and target engine support it. Document the loop and its purpose; preserve or deliberately configure iteration, maximum iterations and maximum change. Verify convergence after representative input changes and save/reopen. Do not silently enable iteration, change application-wide settings, or treat cached values, a successful export or an error-free scan as proof. If calculation or setting preservation cannot be verified, report the limitation and use a verified workflow or a mathematically equivalent non-circular approach within scope. Keep Checks/Audit outside the loop.

An explicit/template case-capture workflow may use a self-retaining IF in an output area to store a selected case's result while the same model calculates the other cases. This is a snapshot, not a live recalculation of every case; assumptions and business calculations must not depend on it. Define initialization and capture/refresh steps, show the captured case and stale-state warning, and verify each case is captured and retained correctly in the intended engine. Convergence alone does not prove capture correctness. Do not introduce this pattern as a default scenario comparison.

Present case results as a compact Case comparison, with each case named above comparable metric rows and period columns. Keep capture/refresh instructions secondary and label saved snapshots clearly; a new label or layout does not make them live.

Formula Examples

These examples assume the inputs, ranges and units described. Named ranges stand for labeled source ranges, not a requirement to add names. Preserve the task's missing-data policy and material rounding.

…(truncated)