BASIC_CHART

Overview

The BASIC_CHART function generates visualizations from 2D numeric data directly within Excel, using Python’s Matplotlib library. The function accepts paired X-Y data and renders either a line chart or a bar chart, returning the result as a base64-encoded PNG image that can be displayed inline in Excel cells.

Matplotlib is the foundational plotting library in Python’s scientific computing ecosystem. The pyplot module provides a MATLAB-like interface for creating a wide variety of static, animated, and interactive visualizations. This implementation uses the Agg backend, which renders graphics to a memory buffer without requiring a display, making it suitable for server-side and embedded environments like Python in Excel.

The function works by extracting the first column of the input data as X-axis values and the second column as Y-axis values. For line charts, data points are plotted and connected with lines, with circular markers ('o') at each point. For bar charts, vertical bars are drawn at each X position with heights corresponding to Y values. The chart is rendered at a fixed size of 6×4 inches using plt.tight_layout() to automatically adjust spacing and prevent label clipping.

After rendering, the figure is saved to an in-memory buffer in PNG format, encoded as a base64 data URI, and returned as a string. This approach enables embedding images directly in cells without requiring external file storage. For more information on Matplotlib’s plotting capabilities, see the pyplot API documentation and the Matplotlib GitHub repository.

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

Excel Usage

=BASIC_CHART(data, chart_type, title, xlabel, ylabel)

data (list[list], required): 2D numeric array with X values in column 1 and Y values in column 2
chart_type (str, optional, default: “line”): Type of chart to generate (“line” or “bar”)
title (str, optional, default: null): Optional title to display above the chart
xlabel (str, optional, default: null): Optional label for the X-axis
ylabel (str, optional, default: null): Optional label for the Y-axis

Returns (object): Matplotlib Figure object (standard Python) or PNG image as base64 string in a 2D array (Pyodide).

Examples

Example 1: Demo case 1

Inputs:

data		chart_type	title	xlabel	ylabel
1	2	line	Growth Curve	X Axis	Y Axis
2	4
3	8
4	16

Excel formula:

=BASIC_CHART({1,2;2,4;3,8;4,16}, "line", "Growth Curve", "X Axis", "Y Axis")

Expected output:

"chart"

Example 2: Demo case 2

Inputs:

data		chart_type	title	xlabel	ylabel
1	10	bar	Category Values	Category	Value
2	20
3	15

Excel formula:

=BASIC_CHART({1,10;2,20;3,15}, "bar", "Category Values", "Category", "Value")

Expected output:

"chart"

Example 3: Demo case 3

Inputs:

data
1	2
2	4
3	8
4	16

Excel formula:

=BASIC_CHART({1,2;2,4;3,8;4,16})

Expected output:

"chart"

Example 4: Demo case 4

Inputs:

data		chart_type
1	10	bar
2	20
3	15

Excel formula:

=BASIC_CHART({1,10;2,20;3,15}, "bar")

Expected output:

"chart"

Python Code

import sys
import matplotlib
IS_PYODIDE = sys.platform == "emscripten"
if IS_PYODIDE:
    matplotlib.use('Agg')
import matplotlib.pyplot as plt
import numpy as np
import io
import base64

def basic_chart(data, chart_type='line', title=None, xlabel=None, ylabel=None):
    """
    Generates a matplotlib chart from 2D numeric data and returns a PNG image as a base64 string.

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

    Args:
        data (list[list]): 2D numeric array with X values in column 1 and Y values in column 2
        chart_type (str, optional): Type of chart to generate ("line" or "bar") Default is 'line'.
        title (str, optional): Optional title to display above the chart Default is None.
        xlabel (str, optional): Optional label for the X-axis Default is None.
        ylabel (str, optional): Optional label for the Y-axis Default is None.

    Returns:
        object: Matplotlib Figure object (standard Python) or PNG image as base64 string in a 2D array (Pyodide).
    """
    if not isinstance(data, list) or not data or not isinstance(data[0], list):
        return "Error: Input data must be a 2D list."
    try:
        arr = np.array(data, dtype=float)
    except Exception:
        return "Error: Data must be numeric."
    if arr.ndim != 2 or arr.shape[1] < 2:
        return "Error: Data must have at least two columns (X and Y)."
    x = arr[:, 0]
    y = arr[:, 1]
    fig = plt.figure(figsize=(6, 4))
    if chart_type == 'bar':
        plt.bar(x, y)
    else:
        plt.plot(x, y, marker='o')
    if title:
        plt.title(title)
    if xlabel:
        plt.xlabel(xlabel)
    if ylabel:
        plt.ylabel(ylabel)
    plt.tight_layout()

    if IS_PYODIDE:
        buf = io.BytesIO()
        plt.savefig(buf, format='png')
        plt.close(fig)
        buf.seek(0)
        img_bytes = buf.read()
        img_b64 = base64.b64encode(img_bytes).decode('utf-8')
        return f"data:image/png;base64,{img_b64}"
    else:
        # Return the Matplotlib Figure object for Python in Excel or standard Python
        return fig

Overview

Excel Usage

Examples

Python Code

Online Calculator