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 -o /tmp/officecli_install.sh && bash /tmp/officecli_install.sh && rm -f /tmp/officecli_install.sh # Windows: irm https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.ps1 -OutFile "$env:TEMP\officecli_install.ps1"; & "$env:TEMP\officecli_install.ps1" 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 -o /tmp/officecli_install.sh && bash /tmp/officecli_install.sh && rm -f /tmp/officecli_install.sh 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 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 and calcPr must be the LAST commands | | 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 | --- ## 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