# Dashboard & Report Best Practices
## The Professional Report Workflow
Every report or dashboard should follow this sequence:
```
1. Structure data → Excel Tables (never plain ranges)
2. Format values → Number formats by data type
3. Add visuals → Charts with explicit positioning
4. Verify layout → Screenshot to confirm no overlaps
5. Save and close → Persist changes
```
## Step 1: Structure Data as Excel Tables
**Always use Excel Tables for tabular data:**
```
range(set-values, rangeAddress='A1', values=[[headers + data]])
table(create, tableName='SalesData', rangeAddress='A1:D20')
```
**Why Tables matter:**
- Auto-filters on every column
- Banded rows for readability
- Structured references in formulas
- Required for Data Model / DAX / PivotTables
- Auto-expand when new rows are added
## Step 2: Format Values by Data Type
**Apply number formats AFTER setting values — not before:**
| Data Type | Format Code | Result |
|-----------|-------------|--------|
| Currency (USD) | `$#,##0.00` | $1,234.56 |
| Currency (EUR) | `€#,##0.00` | €1,234.56 |
| Percentage | `0.0%` | 12.3% |
| Date | `yyyy-mm-dd` | 2025-01-22 |
| Number (thousands) | `#,##0` | 1,235 |
| Accounting | `_($* #,##0.00_)` | $ 1,234.56 |
**Always use US format codes** — Excel translates automatically to the user's locale.
## Step 3: Position Charts with No Overlaps
**Charts have automatic collision detection and three positioning modes:**
### Single Chart (Auto-Position or targetRange)
```
# Option A: targetRange (explicit cell placement)
chart(create-from-range, sourceRange='A1:D20', targetRange='F2:K15')
# Option B: Omit position — auto-places below content
chart(create-from-range, sourceRange='A1:D20', chartType='Line')
# → Automatically positioned below the used range
```
### Multiple Charts (Dashboard) — Always Use targetRange
```
Place in a grid pattern below data:
Chart 1: targetRange='A22:F35' (top-left)
Chart 2: targetRange='G22:L35' (top-right)
Chart 3: targetRange='A37:F50' (bottom-left)
Chart 4: targetRange='G37:L50' (bottom-right)
```
### Collision Detection
All chart operations automatically warn about overlaps. If a result includes an `OVERLAP WARNING` message:
1. Use `chart(fit-to-range)` to reposition
2. Take `screenshot(capture-sheet)` to verify
**Rules:**
- **Use targetRange for multi-chart layouts** — auto-positioning stacks vertically
- Leave 1-2 rows/columns gap between charts
- Place charts BELOW the data area, not beside it (more room)
- Keep chart sizes consistent (same row/column span)
- **Always check result messages** for overlap warnings
## Step 4: Verify with Screenshot
**Always take a screenshot after creating charts or complex layouts:**
```
screenshot(capture-sheet)
→ Confirm: no overlaps, professional spacing, readable labels
→ If issues found: chart(fit-to-range) to reposition, then screenshot again
```
## Common Dashboard Layouts
### Summary Dashboard (Data + 2 Charts)
```
A1:D10 → Data table (formatted as Excel Table)
A12:F25 → Main chart (bar/column)
G12:L25 → Supporting chart (pie/line)
```
### Analytics Dashboard (4 Charts)
```
A1:D10 → Source data table
A12:F25 → Chart 1 (trend line)
G12:L25 → Chart 2 (distribution pie)
A27:F40 → Chart 3 (comparison bar)
G27:L40 → Chart 4 (detail scatter)
```
### Executive Report (Summary + Detail)
```
Sheet "Summary":
A1:D5 → KPI table (small, formatted)
A7:F20 → Summary chart
Sheet "Detail":
A1:H100 → Full data table
A102:H120 → Detail charts
```
## Formatting Checklist
- [ ] Data in Excel Tables (not plain ranges)
- [ ] Number formats applied (currency, dates, percentages)
- [ ] Column widths appropriate for content
- [ ] Chart titles are descriptive
- [ ] Chart axis labels formatted (currency, percentages)
- [ ] No chart overlaps with data or other charts
- [ ] Consistent chart sizes in dashboards
- [ ] Screenshot taken to verify final layout
