officecli-data-dashboard

# Data Dashboard Skill

Create professional, formula-driven Excel dashboards from CSV or tabular data. The output is a single `.xlsx` file with a data sheet and a Dashboard sheet -- charts linked to live data, KPIs powered by formulas, and conditional formatting for visual insight.

---

## BEFORE YOU START (CRITICAL)

**Every time before using officecli, run this check:**

```bash
if ! command -v officecli &> /dev/null; then
    echo "Installing officecli..."
    curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh | bash
    # Windows: irm https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.ps1 | iex
else
    CURRENT=$(officecli --version 2>&1 | grep -oE '[0-9]+\.[0-9]+\.[0-9]+' | head -1)
    LATEST=$(curl -fsSL https://api.github.com/repos/iOfficeAI/OfficeCLI/releases/latest | grep '"tag_name"' | sed -E 's/.*"v?([0-9.]+)".*/\1/')
    if [ "$CURRENT" != "$LATEST" ]; then
        echo "Upgrading officecli $CURRENT -> $LATEST..."
        curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh | bash
    else
        echo "officecli $CURRENT is up to date"
    fi
fi
officecli --version
```

---

## Use When

- User wants to create a **dashboard** from CSV data or tabular data
- User asks for **KPI reports**, **analytics summaries**, or **metrics dashboards**
- User wants to **visualize data** in Excel with charts, sparklines, and conditional formatting
- User mentions "CSV to Excel", "executive dashboard", or "data visualization"

---

## What This Skill Produces

A single `.xlsx` file with:

| Component | Sheet | Description |
|-----------|-------|-------------|
| Raw data | Sheet1 | Imported CSV with frozen headers, AutoFilter, column widths, conditional formatting |
| Dashboard | Dashboard | KPI cards (formula-driven), sparklines, charts (cell-range-linked), preset styling |

The Dashboard sheet is **active on open**. All formulas **recalculate on open**.

---

## Core Concepts

### Formula-Driven KPIs
Every KPI value on the Dashboard is a formula referencing the data sheet. Never hardcode calculated values. When the underlying data changes, KPIs update automatically.

### Cell Range References for Charts
Every chart series references data sheet cells directly (`series1.values="Sheet1!B2:B13"`). Charts stay in sync with data. Never use inline data unless aggregation is impossible in Excel formulas.

### Chart Presets
Use `preset=dashboard` on charts for datasets with 10+ rows. For datasets with fewer than 10 rows, use `preset=minimal`. See the complexity table in A.3 of creating.md for the authoritative mapping -- **when any other text in this skill conflicts with that table, the table wins.** Presets are DeferredAddKeys -- they work on `add` only, NOT on `set`. A single preset replaces 5-8 manual styling properties with one consistent look.

### Data-Size-Aware Complexity
The number of KPIs, charts, sparklines, and CF rules scales with the input data size. A 5-row dataset gets 1 chart and no sparklines. A 200-row dataset gets 3-5 KPIs, 2-3 charts, sparklines, and multiple CF rules.

---

## Workflow Overview

### Phase 1: Analyze the Input Data
Count rows and columns. Identify column types (date, numeric, categorical). Determine the primary dimension (X-axis). Look up the data-size-to-complexity table.

### Phase 2: Plan Before Building
Decide how many KPIs, which chart types, which CF rules, and chart layout positions. Write out the plan before executing any commands.

### Phase 3: Build the Workbook
Follow the 11-step workflow: create + import, column widths, Dashboard sheet, KPIs, sparklines, charts, conditional formatting, tab colors, polish, raw-set, validate.

### Phase 4: QA
Run the QA checklist. Fix issues. Re-validate.

### Phase 5: Deliver
Deliver the `.xlsx` file. Tell the user the Dashboard sheet opens first and formulas recalculate automatically.

---

## Full Guide

Read [creating.md](creating.md) and follow it step by step. It contains the complete workflow, decision tables, command templates, a full runnable example, and the QA checklist.

---

## Quick Reference: Key Warnings

| Warning | Detail |
|---------|--------|
| Batch invocation | `cat <<'EOF' \| officecli batch dashboard.xlsx` — file argument is **required**. `officecli batch` alone fails with "Required argument missing". |
| Batch JSON values | ALL values must be strings: `"true"` not `true`, `"24"` not `24` |
| Chart preset | Add-only. `preset=dashboard` for 10+ rows, `preset=minimal` for < 10 rows |
| Scatter charts | Use `series1.xValues` NOT `series1.categories` (causes validation error) |
| Reference lines | Format is `value:color:label:dash` (color BEFORE label) |
| Cell range refs | Always `series1.values="Sheet1!B2:B13"`, never inline data |
| raw-set ordering | activeTab raw-set must be the LAST command. Use `set / --prop calc.fullCalcOnLoad=true` for calcPr (not raw-set). |
| formulacf | Do NOT use `font.bold`. Use `fill` + `font.color` only |
| Column widths | `import --header` does NOT auto-size. Set widths manually on **ALL sheets including Dashboard** |
| Dashboard ### | KPI cells at 24pt bold WILL show ### if Dashboard columns are not set to width=22. See Step 4b |
| pie chart | `chartType=pie` has a known rendering issue in LibreOffice — chart renders blank (only legend visible). Use `chartType=doughnut` or `chartType=column` instead for category breakdowns. |
| Dashboard KPI placement | KPI labels and values MUST be on the Dashboard sheet itself, not on Summary/Data sheets. Dashboard must show: KPI area (rows 1-2) + charts (rows 5+). See Step 3 CRITICAL box in creating.md. |
| Helper columns | After using helper columns on Dashboard for chart data sources, hide them with `officecli set file.xlsx '/Dashboard/col[N]' --prop hidden=true`. Visible helper columns are unprofessional clutter. |
| **Chart data source (CRITICAL)** | **Chart series MUST reference visible cells on the Summary sheet or Data sheet — NEVER reference hidden columns or helper columns.** LibreOffice does not recalculate hidden columns on render; charts referencing hidden columns will display blank/empty data. Correct pattern: aggregate all chart data into visible rows on the Summary sheet, then reference those cells as chart data sources. |
| **Charts within print area** | After setting the print area, verify all charts are positioned inside it. Chart `x + width` must not exceed the print area column boundary. Recommended: chart width ≤ 90% of print area width. |
| Summary sheet col widths | Summary sheet columns are NOT auto-sized. Always set widths after writing formulas: text/label cols 15-20, numeric cols 12-15. See Step 2 in creating.md. |
| Percentage numFmt on Summary | `AVERAGEIFS`/ratio formula results on Summary sheet display as raw floats (0.083) unless `numFmt="0.0%"` is set at the same time as the formula. Never skip numFmt on percentage cells. |
| **Header row fill (MANDATORY)** | Every sheet with column headers MUST have a dark fill on row 1: `officecli set dashboard.xlsx '/Sheet1/A1:F1' --prop fill=1F3864 --prop font.color=FFFFFF --prop font.bold=true`. Omitting header fill is a Quality Bar violation (Q2 fail). See Step 2b in creating.md. |
| **Conditional formatting (MANDATORY for 10+ rows)** | At least 1 CF rule (colorscale or databar) MUST be applied to a numeric column in the Data sheet for datasets with 10+ rows. Verify presence with `officecli query dashboard.xlsx 'conditionalformatting'`. Zero CF rules is a Q4 quality failure. |
| **KPI card background fill (Quality Bar)** | Dashboard KPI card area (rows 1-2) SHOULD have a light background fill, e.g., `fill=F0F4FF`. Omitting this is a P2 deduction. See Step 4b in creating.md. |

---

## References

- [creating.md](creating.md) -- Complete dashboard creation guide (the main skill file)
- [xlsx SKILL.md](../xlsx/SKILL.md) -- General xlsx reading, editing, and QA reference