AI_TABLE

The AI_TABLE function leverages a large language model (LLM) to generate structured tabular data based on natural language prompts. It uses the Mistral AI Chat API with JSON mode to produce 2D arrays suitable for Excel, enabling automated table creation and data transformation without manual data entry.

The function operates in three modes depending on the inputs provided. With only a prompt, it generates a complete table from scratch (e.g., “Create a table of smartphone features”). When a header array is provided, it constrains the output to match the specified column structure. When source data is supplied, the model transforms or summarizes that existing data according to the prompt—enabling operations like “summarize sales by category” on a detailed transaction dataset.

This implementation connects to any OpenAI-compatible API endpoint, with Mistral AI as the default provider. The function uses JSON mode (response_format: {"type": "json_object"}) to ensure structured output that can be reliably parsed into a 2D array. For more details on Mistral’s available models, see the Mistral models documentation.

The temperature parameter controls output randomness: a value of 0 (default) produces deterministic, consistent results ideal for reproducible analysis, while higher values (up to 2.0) introduce variation. The max_tokens parameter (default 1500) limits response length and associated API costs.

Practical applications include generating product comparison matrices, transforming transaction logs into summary reports, creating structured feature tables from specifications, and organizing unstructured text into analysis-ready formats. The LLM’s semantic understanding enables intelligent groupings and categorizations that go beyond simple sorting or filtering operations.

This example function is provided as-is without any representation of accuracy.

Excel Usage

=AI_TABLE(prompt, api_key, header, source, temperature, max_tokens, model, api_url)
  • prompt (str, required): The request describing what table content to generate
  • api_key (str, required): API key for authentication.
  • header (list[list], optional, default: null): Optional 2D list with column headers as the first row
  • source (list[list], optional, default: null): Optional 2D list of source data to transform into the table
  • temperature (float, optional, default: 0): Controls randomness in AI response (0.0 = deterministic, 2.0 = highly random)
  • max_tokens (int, optional, default: 1500): Maximum tokens in the AI response (5 to 5000)
  • model (str, optional, default: “codestral-2508”): Model ID to use. Default is “codestral-2508”.
  • api_url (str, optional, default: “https://api.mistral.ai/v1/chat/completions”): OpenAI-compatible API endpoint URL. Default is “https://api.mistral.ai/v1/chat/completions”.

Returns (list[list]): 2D list of extracted table data, or error message string.

Example 1: Demo case 1

Inputs:

prompt temperature max_tokens model
Create a table listing the features of 4 different smartphones including brand, model, camera quality, battery life. 0 1500 codestral-2508

Excel formula:

=AI_TABLE("Create a table listing the features of 4 different smartphones including brand, model, camera quality, battery life.", 0, 1500, "codestral-2508")

Expected output:

Brand Model Camera Quality Battery Life
Apple iPhone 13 Dual 12MP Up to 19 hours
Samsung Galaxy S21 Triple 12MP Up to 25 hours
Google Pixel 6 Dual 50MP Up to 24 hours
OnePlus 9 Pro Quad 48MP Up to 20 hours
Example 2: Demo case 2

Inputs:

prompt header temperature max_tokens model
Generate a table of top 5 tourist destinations. Country Popular Attractions Best Time to Visit Average Cost 0 1500 codestral-2508

Excel formula:

=AI_TABLE("Generate a table of top 5 tourist destinations.", {"Country","Popular Attractions","Best Time to Visit","Average Cost"}, 0, 1500, "codestral-2508")

Expected output:

Country Popular Attractions Best Time to Visit Average Cost
France Eiffel Tower, Louvre Museum, Palace of Versailles Spring and Fall $ | | Italy | Colosseum, Vatican City, Amalfi Coast | Spring and Fall | $
Japan Mount Fuji, Kyoto Temples, Tokyo Skytree Spring and Autumn | | Australia | Great Barrier Reef, Sydney Opera House, Uluru | September to November | $
Example 3: Demo case 3

Inputs:

prompt source temperature max_tokens model
Summarize the sales data by product category (2 categories). Product Category Sales Amount 0 1500 codestral-2508
Laptop Tech 1200
Mouse Tech 25
Keyboard Tech 75
T-Shirt Apparel 20
Jeans Apparel 50
Laptop Tech 1350
Hoodie Apparel 45

Excel formula:

=AI_TABLE("Summarize the sales data by product category (2 categories).", {"Product","Category","Sales Amount";"Laptop","Tech",1200;"Mouse","Tech",25;"Keyboard","Tech",75;"T-Shirt","Apparel",20;"Jeans","Apparel",50;"Laptop","Tech",1350;"Hoodie","Apparel",45}, 0, 1500, "codestral-2508")

Expected output:

Category Total Sales Amount
Tech 2650
Apparel 115
Example 4: Demo case 4

Inputs:

prompt header source temperature max_tokens model
Generate a table of top 5 tourist destinations. Country Popular Attractions Best Time to Visit Average Cost 0 1500 codestral-2508

Excel formula:

=AI_TABLE("Generate a table of top 5 tourist destinations.", {"Country","Popular Attractions","Best Time to Visit","Average Cost"}, , 0, 1500, "codestral-2508")

Expected output:

Country Popular Attractions Best Time to Visit Average Cost
France Eiffel Tower, Louvre Museum, Palace of Versailles Spring and Fall $ | | Italy | Colosseum, Vatican City, Amalfi Coast | Spring and Fall | $
Japan Mount Fuji, Kyoto Temples, Tokyo Skytree Spring and Autumn | | Australia | Great Barrier Reef, Sydney Opera House, Uluru | September to November | $
USA Grand Canyon, Statue of Liberty, Yellowstone National Park Spring and Fall $$$

Python Code

Show Code 
import requests
import json

def ai_table(prompt, api_key, header=None, source=None, temperature=0, max_tokens=1500, model='codestral-2508', api_url='https://api.mistral.ai/v1/chat/completions'):
    """
    Uses an AI model to generate a structured table (2D list) based on a prompt, with optional header and source data.

    This example function is provided as-is without any representation of accuracy.

    Args:
        prompt (str): The request describing what table content to generate
        api_key (str): API key for authentication.
        header (list[list], optional): Optional 2D list with column headers as the first row Default is None.
        source (list[list], optional): Optional 2D list of source data to transform into the table Default is None.
        temperature (float, optional): Controls randomness in AI response (0.0 = deterministic, 2.0 = highly random) Default is 0.
        max_tokens (int, optional): Maximum tokens in the AI response (5 to 5000) Default is 1500.
        model (str, optional): Model ID to use. Default is "codestral-2508". Default is 'codestral-2508'.
        api_url (str, optional): OpenAI-compatible API endpoint URL. Default is "https://api.mistral.ai/v1/chat/completions". Default is 'https://api.mistral.ai/v1/chat/completions'.

    Returns:
        list[list]: 2D list of extracted table data, or error message string.
    """
    if not api_key:
        return "You must include an API key to use this function. Sign up for a free API key at https://aistudio.google.com/, https://console.mistral.ai/, or other providers and add your own api_key.  You may use any OpenAI compatible API, just update the api_url parameter."
    if not isinstance(temperature, (float, int)) or not (0 <= float(temperature) <= 2):
        return "Error: temperature must be a float between 0 and 2 (inclusive)"
    if not isinstance(max_tokens, int) or not (5 <= max_tokens <= 5000):
        return "Error: max_tokens must be an integer between 5 and 5000 (inclusive)"
    table_prompt = f"Generate a well-organized table based on this request: {prompt}"
    if header is not None and header and len(header) > 0:
        header_str = ", ".join(str(col) for col in header[0])
        table_prompt += f"\nUse exactly these columns: {header_str}"
    if source is not None:
        source_str = json.dumps(source, indent=2)
        table_prompt += f"\n\nUse this source data to create the table:\n{source_str}"
    table_prompt += ("\nReturn ONLY a JSON object with a key 'items' whose value is a JSON array of arrays (2D array) with the table data. "
                     "The first row should contain column headers if not provided. "
                     "Each subsequent row should contain data that fits the columns. "
                     "Do not include any explanatory text, just the JSON object. "
                     "For example: {\"items\": [[\"Header1\", \"Header2\"], [\"Row1Col1\", \"Row1Col2\"]]}")
    payload = {
        "messages": [{"role": "user", "content": table_prompt}],
        "temperature": float(temperature),
        "model": model,
        "max_tokens": max_tokens,
        "response_format": {"type": "json_object"}
    }
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
        "Accept": "application/json"
    }
    try:
        response = requests.post(api_url, headers=headers, json=payload)
        if response.status_code == 429:
            return "Error: You have hit the rate limit for the API. Please try again later"
        response.raise_for_status()
        response_data = response.json()
        if not isinstance(response_data, dict) or "choices" not in response_data:
            err_msg = None
            for k in ("error", "message", "detail"):
                if k in response_data:
                    err_msg = response_data[k]
                    break
            if not err_msg:
                err_msg = str(response_data)
            return f"Error: {err_msg}"
        content = response_data["choices"][0]["message"]["content"]
        try:
            table_data = json.loads(content)
            if isinstance(table_data, dict) and "items" in table_data:
                table_data = table_data["items"]
            elif isinstance(table_data, dict):
                for key in ("data", "filled_data", "result"):
                    if key in table_data:
                        table_data = table_data[key]
                        break
            if isinstance(table_data, list) and all(isinstance(row, list) for row in table_data):
                return table_data
            else:
                return "Error: Unable to parse response. Expected a 2D array"
        except (json.JSONDecodeError, ValueError):
            return "Error: Unable to generate table. The AI response wasn't in the expected format"
    except Exception as e:
        msg = str(e)
        return f"Error: {msg}"

Online Calculator

The request describing what table content to generate
API key for authentication.
Optional 2D list with column headers as the first row
Optional 2D list of source data to transform into the table
Controls randomness in AI response (0.0 = deterministic, 2.0 = highly random)
Maximum tokens in the AI response (5 to 5000)
Model ID to use. Default is "codestral-2508".
OpenAI-compatible API endpoint URL. Default is "https://api.mistral.ai/v1/chat/completions".