BASIC_CHART

Overview

This function generates a simple matplotlib chart (e.g., a line or bar chart) from provided numeric data and returns the chart as a PNG image encoded as a base64 string suitable for embedding in Excel or web apps.

Usage

To insert the image in a viewable manner in Excel you need to use the function dialog and select Insert result, not formula option. Using the custom function below will only return the base64 string.


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

data: 2D list or range of numbers (required)
chart_type: ‘line’ or ‘bar’ (optional, default: ‘line’)
title: Chart title (optional)
xlabel: X-axis label (optional)
ylabel: Y-axis label (optional)

Parameters

Parameter	Type	Required	Description
data	2D list	Yes	Numeric data for the chart (rows/columns).
chart_type	string	No	’line’ or ‘bar’. Default is ‘line’.
title	string	No	Chart title.
xlabel	string	No	X-axis label.
ylabel	string	No	Y-axis label.

Return Value

Return Value	Type	Description
Chart Image	string	PNG image as a base64 string for embedding.

Limitations

Only supports 2D numeric data.
Only ‘line’ and ‘bar’ charts are supported.
Large datasets may result in slow rendering or large images.
Requires matplotlib and base64 support in the Python environment.

Benefits

Quickly visualize data from Excel using Python’s matplotlib.
Embeds charts directly in Excel or web dashboards.
Customizable chart type and labels.

Examples

Simple Line Chart

Sample Input Data (Range A1:B4):

X	Y
1	2
2	4
3	8
4	16


=BASIC_CHART(A1:B4, "line", "Growth Curve", "X Axis", "Y Axis")

Sample Output: A string starting with data:image/png;base64, followed by the base64-encoded PNG image.

Bar Chart Example

Sample Input Data (Range A1:B3):

Category	Value
A	10
B	20
C	15


=BASIC_CHART(A1:B3, "bar", "Category Values", "Category", "Value")

Sample Output: A string starting with data:image/png;base64, followed by the base64-encoded PNG image.

Source Code


import matplotlib
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.
    Args:
        data (list): 2D list of numbers (Excel range or similar)
        chart_type (str): 'line' or 'bar' (default: 'line')
        title (str): Chart title (optional)
        xlabel (str): X-axis label (optional)
        ylabel (str): Y-axis label (optional)
    Returns:
        str: PNG image as base64 string starting with 'data:image/png;base64,'
    """
    # Validate input data
    if not isinstance(data, list) or not data or not isinstance(data[0], list):
        raise Exception("Input data must be a 2D list.")
    try:
        arr = np.array(data, dtype=float)
    except Exception:
        raise Exception("Data must be numeric.")
    if arr.ndim != 2 or arr.shape[1] < 2:
        raise Exception("Data must have at least two columns (X and Y).")
    x = arr[:, 0]
    y = arr[:, 1]
    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()
    buf = io.BytesIO()
    plt.savefig(buf, format='png')
    plt.close()
    buf.seek(0)
    img_bytes = buf.read()
    img_b64 = base64.b64encode(img_bytes).decode('utf-8')
    return f"data:image/png;base64,{img_b64}"
# For backward compatibility
matplotlib_demo = basic_chart