Skip to content

Charts

Tools

  • chart: Create charts, manage positioning and data sources
  • chart_config: Configure chart appearance, formatting, and analysis features

Chart Creation

From Range

chart(create-from-range, chartType, sourceRange, sheetName)
Best for: Simple data in worksheet ranges

From PivotTable (PivotChart)

chart(create-from-pivottable, pivotTableName)
Best for: Data Model data - creates a single PivotChart object (don't create separate PivotTable + Chart)

From Table

chart(create-from-table, tableName, chartType)
Best for: Excel Tables with structured references

Chart Types

Common types: ColumnClustered, Line, Pie, Bar, Area, XYScatter, Doughnut

Specialized: Waterfall, Funnel, Treemap, Sunburst, BoxWhisker, Histogram, Pareto

Configuration Actions (chart_config)

Series Management

  • add-series: Add data series with valuesRange and optional categoryRange
  • remove-series: Remove series by index (1-based)
  • set-source-range: Replace entire chart data source
  • set-series-chart-type: Assign a chart type to one regular-chart series for combo charts

Plot Behavior

  • get-plot-options: Read row/column orientation, blank-cell display, and hidden-cell plotting
  • set-plot-options: Configure plotBy, displayBlanksAs, and plotVisibleOnly

Titles and Labels

  • set-title: Set chart title (empty string hides)
  • set-axis-title: Set axis labels (Category, Value, CategorySecondary, ValueSecondary)
  • set-data-labels: Configure data labels (position, showValue, showCategory, showPercentage, showSeriesName, showLegendKey)

Axis Formatting

  • get-axis-scale: Get min, max, majorUnit, minorUnit, and auto flags
  • set-axis-scale: Configure scale properties
  • get-axis-number-format: Get current tick label format
  • set-axis-number-format: Format axis numbers (e.g., "$#,##0,,\"M\"" for millions)

Gridlines

  • get-gridlines: Check visibility state
  • set-gridlines: Show/hide major/minor gridlines

Series Formatting

  • set-series-format: Configure markers plus material fill, transparency, line color, and line weight

Trendlines

  • list-trendlines: View all trendlines on a series
  • add-trendline: Add Linear, Exponential, Logarithmic, Polynomial, Power, or MovingAverage trendline
  • delete-trendline: Remove trendline by index
  • set-trendline: Configure display (equation, R² value) and forecasting (forward, backward periods)

Styling

  • show-legend: Control legend visibility and position (Bottom, Corner, Top, Right, Left)
  • set-style: Apply Excel chart styles (1-48)
  • set-area-format: Format the chart area or plot area fill and border
  • set-placement: Configure cell anchoring plus print, lock, and rounded-corner behavior

Trendline Details

Types

Type Use Case Requirements
Linear Straight-line trends None
Exponential Growth/decay patterns Positive values
Logarithmic Rapid initial change Positive values
Polynomial Curves with peaks/valleys Order parameter (2-6)
Power Accelerating rates Positive values
MovingAverage Smooth fluctuations Period parameter (2+)

Parameters

  • order: Required for Polynomial (2-6, default 2)
  • period: Required for MovingAverage (2+, default 2)
  • forward/backward: Forecast periods ahead/behind data
  • intercept: Force trend through specific Y value
  • displayEquation: Show formula on chart
  • displayRSquared: Show R² goodness-of-fit value

Common Workflows

Create Chart with Formatting

1. chart(create-from-range) → chartName
2. chart_config(set-title, title="Monthly Sales")
3. chart_config(set-axis-title, axis="Value", title="Revenue ($)")
4. chart_config(set-axis-number-format, axis="Value", numberFormat="$#,##0")
5. chart_config(set-data-labels, position="OutsideEnd", showValue=true)

Add Analysis

1. chart_config(add-trendline, trendlineType="Linear", displayEquation=true, displayRSquared=true)
2. chart_config(set-trendline, forward=3) # Forecast 3 periods ahead

Best Practices

  1. PivotCharts for Data Model: Use create-from-pivottable not PivotTable + separate chart
  2. Format numbers: Set axis number format for readability
  3. Use gridlines sparingly: Minor gridlines often add clutter
  4. Trendlines for insights: Add R² to show fit quality
  5. Data labels placement: OutsideEnd for bar charts, Center for pie charts

Chart Positioning

Charts support three positioning modes, listed in order of preference:

1. targetRange (PREFERRED - One Step)

chart(create-from-range, sourceRange='A1:B10', chartType='Line', targetRange='F2:K15')
Creates chart AND positions it to the cell range in one call. No point math needed.

2. Auto-Positioning (No Position Specified)

When you omit both targetRange and left/top, the chart is automatically placed below all existing content (data ranges + other charts) with 10pt padding. This prevents overlap automatically. 

chart(create-from-range, sourceRange='A1:B10', chartType='Line')
## → Chart auto-positioned below the used range and any existing charts

3. Manual Coordinates

chart(create-from-range, sourceRange='A1:B10', left=360, top=20)
## left/top in points (72 points = 1 inch)

Collision Detection (Automatic)

All chart create, move, and fit-to-range operations automatically check for overlaps with data and other charts. If collisions are detected, the result includes an OVERLAP WARNING message. Always check the result message and fix overlaps before proceeding.

Result example with collision warning:
{
  "success": true,
  "chartName": "Chart 1",
  "message": "OVERLAP WARNING: Chart overlaps data area $A$1:$D$20. Use chart fit-to-range to reposition, then screenshot capture with an explicit range to verify layout."
}

If you see an overlap warning: 1. Use chart(fit-to-range, chartName, rangeAddress='F2:K15') to reposition 2. Or use chart(move, chartName, left=..., top=...) to adjust 3. Always follow up with screenshot(capture, rangeAddress='A1:M25') to include and verify the chart

Position Estimates

  • Rows: ~15 points per row (varies with row height)
  • Columns: ~60 points per column (varies with column width)
  • Default chart: 400×300 points

Positioning Workflow

  1. Preferred: Use targetRange='F2:K15' in create call — avoids all overlap issues
  2. Alternative: Omit position — auto-positioning places chart below content
  3. Manual: get-used-range → calculate coordinates → specify left/top
  4. Always verify: Use screenshot(capture, rangeAddress='A1:M25') to visually confirm layout

Multi-Chart Layout (CRITICAL)

When creating dashboards with multiple charts, every chart needs explicit positioning:

Grid Layout Pattern

Data at A1:D10. Place 4 charts in a 2×2 grid below data:

chart(create-from-range, ..., targetRange='A12:F25')   # Top-left
chart(create-from-range, ..., targetRange='G12:L25')   # Top-right
chart(create-from-range, ..., targetRange='A27:F40')   # Bottom-left
chart(create-from-range, ..., targetRange='G27:L40')   # Bottom-right
screenshot(capture, rangeAddress='A1:M40') → Verify no overlaps

Rules

  • Use targetRange for every chart in multi-chart layouts — auto-positioning stacks vertically
  • Leave at least 1-2 rows/columns gap between charts
  • If any chart result includes an overlap warning, fix it before creating the next chart
  • Take a final screenshot(capture, rangeAddress='A1:M40') to verify the complete layout