How AI writes Excel formulas: generating them from a task description

A simple sum is easy to write, but conditions, lookups in a reference table and nested checks take time to look up syntax. AI for Excel formulas solves this: you describe the task in words, the model returns a formula, and you test it on three or four rows. Below is a prompt template, examples with the logic explained, a table of Russian and English function names, and code for generating formulas through the API.

How to describe a task to get a working formula

The model cannot see your sheet, so tell it four things: which columns and ranges, what to calculate, where the result goes and which Excel you use. The last point matters more than it seems: in Russian Excel functions have Russian names and arguments are usually separated by semicolons; in English Excel the names are English and the separator is a comma.

Sample sales table:

Prompt:

A suitable answer:

Check on the four rows: 5 × 1000 + 3 × 200 + 2 × 1000 = 5000 + 600 + 2000 = 7600. The mouse row is dropped because B3="Done" is FALSE, i.e. 0.

If the model suggests something like =SUMIFS(C2:C100*D2:D100,B2:B100,"Done"), that is wrong: SUMIFS accepts only a cell reference as the sum range, not a computed array. A working SUMIF variant is to add a helper column E with =C2*D2 and use =SUMIF(B2:B100,"Done",E2:E100).

Example formulas explained

SUMIF (СУММЕСЛИ): sum by one condition

Task: total sales for the "Electronics" category. Category in B2:B100, amount in C2:C100.

Argument order: criteria range, criterion, sum range. SUMIFS uses a different order — the sum range comes first: =SUMIFS(C2:C100,B2:B100,"Electronics",D2:D100,">1000"). Mixing these up is a common source of errors, so check the order in the model's answer.

VLOOKUP (ВПР) and INDEX/MATCH (ИНДЕКС+ПОИСКПОЗ): reference lookup

Task: Sheet1!A2 holds a SKU, Sheet2 is a reference list — SKUs in A, prices in B. You need the price in Sheet1!B2.

The last argument FALSE (or 0) means exact match; without it VLOOKUP does an approximate search and returns the wrong price on an unsorted list. A variant that doesn't depend on column order:

MATCH finds the row number of the SKU, INDEX takes the value from that row of column B. If the SKU is missing, both formulas return #N/A; to show an empty cell instead, wrap them: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"").

IF (ЕСЛИ): several thresholds

Task: bonus by sales volume in A2. Below 100,000 — 0%, from 100,000 to 499,999 — 5%, from 500,000 — 10%.

Check boundary values: 99,999 → 0; 100,000 → first condition false, second true → 5; 500,000 → both false → 10. State thresholds just as explicitly in the prompt ("from … inclusive"), or the model will decide on its own where exactly 500,000 goes.

COUNTIF and AVERAGEIF: count and average per group

The first counts how many times the category in A2 appears in the list, the second returns the average value for that category. The $ signs lock the range so you can fill the formula down.

Russian and English function names

Typing an English formula into Russian Excel gives a #NAME? error (#ИМЯ? in the Russian UI). So always state the interface language and the argument separator in the prompt. The separator depends on the system's regional settings: with Russian settings it is a semicolon.

Generating formulas through the API

If you need formulas in bulk (for a report template, say), it's easier to call the model from a script. Example with the OpenAI SDK:

from openai import OpenAI

client = OpenAI(base_url="https://api.zukko.pro/v1", api_key="fetch_your_key")

SYSTEM = (
    "You write Excel formulas. English interface, comma as the argument "
    "separator. Use English function names only. "
    "Reply with a single formula starting with =, no explanation."
)

def make_formula(task: str, model: str = "claude-sonnet-5") -> str:
    resp = client.chat.completions.create(
        model=model,
        messages=[

Models for this task: claude-sonnet-5, gpt-5.6-sol ($0.3 per 1M tokens) or glm-5.3-flash ($0.04). Which one writes correct formulas for your sheets more often is something to test yourself: run 10–15 of your own tasks through each and compare against a manual check. The current model list is at GET https://api.zukko.pro/v1/models or on the "Models" page in your dashboard.

Spend is calculated from usage in the response. For example, if a request took 150 input and 40 output tokens, on claude-sonnet-5 that is (150 + 40) × 0.3 / 1,000,000 = $0.000057, and 1,000 such formulas cost $0.057. Client setup is covered in the Python guide↗ and the quick start↗. If you don't want to write code, the same prompts work in the browser chat↗.

How to check an AI-generated formula

1. Build 3–5 test rows where the answer is easy to compute mentally, including edge cases (exactly at a threshold, an empty cell, a missing SKU).
2. Paste the formula and compare with your manual result.
3. Fill the formula down and make sure locked ranges ($) didn't shift.
4. If the result doesn't match, send the model the formula, the test data and the expected answer, and ask it to find the mistake.

FAQ

Does the model make mistakes in formulas?

Yes, which is why testing on sample data is mandatory. Typical mistakes: swapped argument order (SUMIF vs SUMIFS), a missing $, approximate VLOOKUP without FALSE, and English function names in Russian Excel.

Does this work for Google Sheets?

Yes. Say in the prompt that it's Google Sheets and which argument separator your settings use. The model can then use functions Excel doesn't have, such as QUERY or REGEXMATCH.

Can I generate DAX or Power Query M?

Yes, name the language explicitly: "write a DAX measure" or "write a Power Query step in M", and describe the tables and relationships between them.

What about newer functions like FILTER and SORT?

FILTER (ФИЛЬТР) and SORT (СОРТ) exist only in recent Excel versions. If the file will be opened in an older version, tell the model: "use only functions compatible with Excel 2016".