# How to Handle Chart Creation with Trendlines and Error Bars in Excel Using OfficeCLI

> Master Excel chart creation with trendlines and error bars using OfficeCLI. Automate chart enhancements via command-line property assignments and simplify your workflow.

- Repository: [OfficeAI/OfficeCLI](https://github.com/iofficeai/OfficeCLI)
- Tags: how-to-guide
- Published: 2026-08-07

---

**OfficeCLI enables command-line manipulation of Excel charts by parsing property specifications into OOXML objects, allowing you to add trendlines and error bars through simple `--prop` assignments without manually editing XML.**

This guide demonstrates how to handle chart creation with trendlines and error bars in Excel using OfficeCLI, an open-source command-line interface maintained by iOfficeAI. The tool abstracts the complexity of the Open XML SDK by routing chart modification commands through specialized handlers that construct `C.Trendline` and `C.ErrBars` elements programmatically.

## Architecture of Chart Handling in OfficeCLI

OfficeCLI treats an Excel chart as a **ChartPart** contained within a worksheet’s **DrawingsPart**. When you execute a `set` command targeting a chart path (e.g., `/Sheet1/chart[1]`), the request flows through a pipeline of specialized handlers and builders.

### Path Resolution and ChartPart Extraction

The entry point in [`ExcelHandler.Set.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.cs) uses regular expressions to identify chart-related paths:

```csharp
// /SheetName/chart[N]/axis[@role=…]   → SetChartAxisByPath
// /SheetName/chart[N] or /chart[N]/series[K] → SetChartByPath

```

When a path resolves to a chart, [`ExcelHandler.Set.Charts.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Charts.cs) extracts the `ChartPart` (or extended part) and forwards the property dictionary to the core chart helper for processing.

### The Property Dispatch Pipeline

`ChartHelper.SetChartProperties` in [`ChartHelper.Setter.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.Setter.cs) serves as the central dispatcher. It iterates over the supplied property dictionary and routes keys based on their prefixes:

- **Trendline keys**: `trendline` or `seriesN.trendline`
- **Error-bar keys**: `errbars` or `errorbars`

The dispatcher invokes `ChartHelper.BuildTrendline` and `ChartHelper.ApplyTrendlineOptions` (implemented in [`ChartHelper.SetterHelpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.SetterHelpers.cs) lines 62-140) to construct the appropriate OOXML objects.

## Adding Trendlines to Excel Charts

Trendlines in OfficeCLI are built by parsing specification strings and applying optional configuration parameters.

### Supported Trendline Types and Specifications

`BuildTrendline` accepts spec strings that map to `C.TrendlineValues` enum values:

```bash

# Linear trendline

officecli set /Sheet1/chart[1] --prop series1.trendline=linear

# Polynomial with specific order (2-6)

officecli set /Sheet1/chart[1] --prop series1.trendline=poly:4

# Moving average with explicit period (minimum 2)

officecli set /Sheet1/chart[1] --prop series1.trendline=movingAvg:4

# Exponential with specific parameters

officecli set /Sheet1/chart[1] --prop series1.trendline=exp:2:1

```

The parser creates a new `C.Trendline()` object and injects auxiliary children such as `<c:order>` for polynomials or `<c:period>` for moving averages. If the period is omitted for moving averages, the code defaults to **2** (Excel’s standard default).

### Configuring Trendline Options

`ApplyTrendlineOptions` mutates the `C.Trendline` node based on additional property keys:

| Property | OOXML Element | Constraint |
|----------|--------------|------------|
| `forward` / `forecastforward` | `<c:forward>` | Double value for extrapolation |
| `backward` / `forecastbackward` | `<c:backward>` | Double value for backward projection |
| `order` | `<c:order>` | Clamped to **2-6** (polynomial only) |
| `period` | `<c:period>` | Minimum **2** (throws exception if lower) |
| `intercept` | `<c:intercept>` | Value for exponential/power trendlines |
| `displayrsquared` / `r2` | `<c:dispRSqr>` | Boolean to show R² value |
| `displayequation` / `eq` | `<c:dispEq>` | Boolean to show regression equation |
| `name` / `label` | `<c:trendlineLbl>` | Rich-text label display |

The implementation respects strict OOXML child ordering requirements: name → type → order → period → forward → backward → intercept → dispRSqr → dispEq → trendlineLbl.

## Configuring Error Bars in Excel Charts

Error bar handling follows a parallel implementation pattern in [`ChartHelper.SetterHelpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.SetterHelpers.cs), constructing `C.ErrBars` objects and attaching them to series nodes.

### Error Bar Directions and Types

The dispatcher recognizes `errbars` or `errorbars` keys to trigger error bar construction:

```bash

# Basic symmetric error bars

officecli set /Sheet1/chart[1] --prop series1.errbars=both

# Direction-only specification

officecli set /Sheet1/chart[1] --prop series1.errbars=plus

```

Valid directions map to `<c:errDir>` values: `Both`, `Minus`, or `Plus`.

### Fixed Value vs. Percentage-Based Error Bars

Specify the error bar calculation method and magnitude using type-specific properties:

```bash

# Fixed value error bars (absolute units)

officecli set /Sheet1/chart[1] \
    --prop series1.errbars=both \
    --prop series1.errbars.plus=5 \
    --prop series1.errbars.minus=5

# Percentage-based error bars

officecli set /Sheet1/chart[1] \
    --prop series2.errbars=plus \
    --prop series2.errbars.type=percent \
    --prop series2.errbars.plus=10

```

The `errbars.type` property maps to `<c:errBarType>` values (`FixedVal`, `Percent`, `StdDev`, etc.), with validation ensuring numeric children exist where required by the OOXML schema.

## Practical Command-Line Examples

Combine trendlines and error bars in single commands for efficient batch updates:

**Linear trendline with equation display:**

```bash
officecli set /Sheet1/chart[1] \
    --prop series1.trendline=linear \
    --prop series1.trendline.displayequation=true

```

**Polynomial trendline with R-squared:**

```bash
officecli set /Sheet1/chart[1] \
    --prop series1.trendline=poly:3 \
    --prop series1.trendline.displayrsquared=true

```

**Combined trendline and symmetric error bars:**

```bash
officecli set /Sheet1/chart[1] \
    --prop series1.trendline=linear \
    --prop series1.trendline.displayeq=true \
    --prop series1.errbars=both \
    --prop series1.errbars.plus=3 \
    --prop series1.errbars.minus=3

```

**Moving average with custom period:**

```bash
officecli set /Sheet1/chart[1] --prop series2.trendline=movingAvg:5

```

## Key Implementation Details and Validation

Understanding the internal validation rules prevents runtime exceptions when scripting chart modifications.

### OOXML Child Ordering Constraints

The source code in [`ChartHelper.SetterHelpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.SetterHelpers.cs) (lines 58-64) enforces the exact element sequence required by the Open XML validator. Altering the order of children within `C.Trendline` or `C.ErrBars` causes Excel to reject the file as corrupted. The builder functions automatically insert elements in the compliant sequence.

### Extended Chart Support (ChartExBuilder)

For modern Excel chart types (funnel, pareto, treemap), the path resolves to an extended chart part (`cx` schema). [`ChartExBuilder.Setter.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartExBuilder.Setter.cs) mirrors the same `SetChartProperties` logic, ensuring that trendline and error bar commands work identically across both legacy and contemporary chart implementations.

After processing all properties, [`ExcelHandler.Set.Charts.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Charts.cs) persists changes via:

```csharp
ChartHelper.SetChartProperties(chartInfo.StandardPart, chartProps);
chartInfo.StandardPart.ChartSpace?.Save();

```

## Summary

- **OfficeCLI** routes chart commands through [`ExcelHandler.Set.Charts.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Charts.cs) to construct OOXML objects programmatically.
- **Trendlines** are specified via `seriesN.trendline` properties, supporting linear, polynomial, exponential, and moving average types with automatic validation of periods and orders.
- **Error bars** use `seriesN.errbars` properties to create `C.ErrBars` elements with configurable directions (plus/minus/both) and calculation types (fixed, percent, standard deviation).
- **Validation** enforces OOXML constraints, including minimum moving average periods of **2** and polynomial orders clamped to **2-6**.
- **Extended charts** (funnel, pareto) utilize [`ChartExBuilder.Setter.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartExBuilder.Setter.cs) with identical command syntax.

## Frequently Asked Questions

### What file handles the routing of chart-related set commands in OfficeCLI?

[`ExcelHandler.Set.Charts.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ExcelHandler.Set.Charts.cs) serves as the primary router, parsing chart paths (like `/Sheet1/chart[1]`) and extracting the `ChartPart` before delegating property processing to `ChartHelper.SetChartProperties` in [`ChartHelper.Setter.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.Setter.cs).

### How does OfficeCLI validate trendline periods and polynomial orders?

The validation occurs in [`ChartHelper.SetterHelpers.cs`](https://github.com/iOfficeAI/OfficeCLI/blob/main/ChartHelper.SetterHelpers.cs). Polynomial orders are clamped to the **2-6** range as per the OOXML specification, while moving average periods throw an `ArgumentException` if set below **2**, enforcing Excel’s minimum requirement.

### Can I combine trendlines and error bars on the same series using OfficeCLI?

Yes. You can combine multiple `--prop` assignments in a single command, such as `--prop series1.trendline=linear --prop series1.errbars=both`, and the dispatcher will batch both modifications before saving the chart part.

### What is the default period for moving average trendlines in OfficeCLI?

If you specify `movingAvg` without a period (e.g., `series1.trendline=movingAvg`), the code automatically injects a default period of **2**, matching Excel’s native behavior for moving average trendlines.