Chart & KPI Regions
Charts and KPI cards are drawn over the sheet after everything else has calculated, reading their data from cell ranges — so they can chart table regions, financial reports and formula results alike. Both fill their whole range rectangle.
Chart Regions
Setup
| Field | Meaning |
|---|---|
| Range | The rectangle the chart is drawn over, for example B2:J18. |
| Chart Type | Column, Bar, Line, Area, Pie, Donut or Waterfall. |
| Chart Title | Drawn above the chart. Accepts [Address] tokens: Sales for [B1]. |
| Stacked | For column and bar charts, stacks the series instead of grouping them. |
| Data Range | The rectangular cell range holding the values, for example B2:D13. |
| Series In | Whether each column or each row of the data range is one series. |
| Categories Range | Optional — one label per data point (for example A2:A13). Unlabelled points are numbered. |
| Series Names Range | Optional — one name per series (for example B1:D1). Unnamed series are called Series 1, Series 2, … |
| Show Legend | On by default; the legend is hidden automatically for a single series. |
| Show Values | Data labels next to each value. |
| Show Grid Lines | Value-axis grid lines (no effect on pie and donut). |
| Value Decimals | Decimals in data labels (0–5). |
| Color / Color Scheme | See below. |
Behaviour worth knowing
- Blank points drop off: a point is charted only when its category cell has text or some series has a numeric value there — so trailing empty rows under a shrinking source range disappear instead of charting as zeroes.
- Waterfall charts the first series as deltas (positive and negative movements building on each other).
- The drawing's aspect ratio follows the actual cell sizes of its rectangle, including columns and rows you have resized.
Worked example: actual vs. budget by month
The month axis comes from the period slicer, not from typed dates: with ?RANGE->2026-01-01..2026-12-31 in C2, a date range region at AD6 (Start Date [C2], Interval <1M>, 12 dates, Date Range Filter) puts the twelve month filters in AD6:AD17, and a second one at AA6 in One Date mode with the number format {"k":"d","p":"mmm yyyy"} gives the labels. Actual revenue per month is then =-GL.GLSum('40000..49999', AD6) down AB6:AB17 and budget =-GL.GLBudget('40000..49999', AD6, 'BUDGET') down AC6:AC17 (see Functions). Pick another year in C2 and the whole chart follows. The chart region:
| Field | Value |
|---|---|
| Range | B9:H21 (the rectangle on the dashboard) |
| Chart Type | Column |
| Data Range | AB6:AC17 |
| Series In | Columns — column AB is series 1, AC series 2 |
| Categories Range | AA6:AA17 |
| Series Names Range | AB5:AC5 (cells containing Actual and Budget) |
| Chart Title | Revenue [C2] — shows the period slicer's pick |
Two grouped columns per month, a legend, and a title that follows the slicer. Switch the type to Line for a trend look, or turn on Stacked to show composition instead of comparison. For a net cash movement by month chart, use Waterfall with the monthly deltas as the (first) series.
Colour schemes
The Color Scheme field decides how the series palette derives from the region's colour:
| Scheme | Effect |
|---|---|
| Default (multi-hue) | The classic multi-colour palette. |
| Monochromatic | Shades of the one hue — best for a single series. |
| Analogous / Complementary / Split Complementary / Triadic | Related-hue palettes for a few series. |
| Colorblind Safe | The Okabe-Ito palette — distinguishable for every kind of colour vision. |
| Diverging (+/-) | Pairs the colour with its complement; for a single series, colours bars by sign. |
KPI Card Regions
A KPI card presents one number large, with an optional delta, sparkline, target progress or status badge. The region's Description is the card's label.
Card types
| Type | Layout |
|---|---|
| Stat | The value plus an optional delta line. |
| Trend | Adds a sparkline over the Trend Range. |
| Target | Adds progress toward the Target Cell — a bar or a donut. The fill clamps at 100% but the true percentage is always shown, with a "Target …" line. |
| Status | Adds an On track / Attention / Critical badge driven by the threshold cells. |
| Auto (default) | Infers the richest type the filled-in cells support: thresholds → Status, target → Target, trend range → Trend, else Stat. |
Setup
| Field | Meaning |
|---|---|
| Range | The rectangle the card is drawn over, for example B2:E8. |
| Value Cell | The cell holding the number the card presents. A non-numeric cell shows its text; an empty one shows a dash. |
| Compare Cell | Optional — a cell to compare against (budget, last year). The card shows the delta with an up/down arrow. |
| Comparison Label | Optional grey text after the delta: "vs last year". |
| Delta Mode | Percent (change relative to the compare cell), Absolute (the difference itself), or Percentage Points (the raw difference suffixed pp — for KPIs that are themselves percentages: margin 42% vs 40% reads +2.0 pp, not +5%). |
| Favorable Direction | Which way is good. Lower is Better flips the delta colours (a falling cost shows green) and the threshold comparisons; Neutral keeps the delta grey. |
| Trend Range | Trend cards: numeric cells drawn as a sparkline. |
| Target Cell | Target cards: the goal. |
| Warning / Critical Threshold Cell | Status cards: the thresholds, as cell references (not literals) so formulas and slicers can drive them. The badge shows Attention/Critical when the value is at or beyond the threshold on the unfavourable side. |
| Value Decimals | Decimals in the big number. |
| Progress Style | Target cards: a thin horizontal bar (default) or a donut ring; cards too small for a readable donut fall back to the bar. |
| Color | The accent colour for the card's strip and sparkline. |
Behaviour worth knowing
- The delta's arrow always agrees with its text: the sign is derived from the delta as displayed, so a movement smaller than the rounding resolution renders neutral — never "▲ +0.0%".
- Percent mode against a zero comparison shows the absolute difference instead (a ratio against zero is meaningless, but the movement still shows).
- A Status card whose configured threshold cell holds no readable number shows no badge at all rather than asserting a health it could not verify.
Worked examples
Revenue vs. budget (Stat/Target). Value Cell AB3 (=GL.GLSum('40000..49999', C2)), Compare Cell AB4 (=GL.GLBudget('40000..49999', C2)), Comparison Label vs budget, Delta Mode Percent. Add Target Cell AB4 too and the card gains a progress bar toward budget — "92%, Target 1,200,000".
Days sales outstanding (Status, lower is better). Value Cell points at your DSO formula; Favorable Direction Lower is Better; Warning Threshold Cell → a cell holding 45, Critical → 60. At DSO 38 the badge reads On track; at 47, Attention; at 63, Critical. A DSO that falls week over week shows a green downward delta — the direction setting flips the colours, not the arrow.
Gross margin % (Trend, percentage points). Value Cell = this month's margin %, Compare Cell = last month's, Delta Mode Percentage Points (42% vs 40% reads +2.0 pp), Trend Range = the last twelve months' margin cells, drawn as a sparkline.