# capspire-excel

> Excel spreadsheet creation, editing, and analysis with capSpire brand styling. This skill should be used when working with Excel files (.xlsx, .xlsm, .csv) for capSpire-branded deliverables. Combines technical Excel workflows (formulas, data analysis, formatting) with capSpire brand guidelines (Franklin Gothic typography, Orange/Charcoal colors). Includes support for formula recalculation, zero-error validation, and industry-standard financial modeling conventions with capSpire visual styling.

- Author: Tate Hasenclever
- Repository: Tate-Has/tates-claude
- Version: 20260125201803
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/Tate-Has/tates-claude
- Web: https://mule.run/skillshub/@@Tate-Has/tates-claude~capspire-excel:20260125201803

---

---
name: capspire-excel
description: Excel spreadsheet creation, editing, and analysis with capSpire brand styling. This skill should be used when working with Excel files (.xlsx, .xlsm, .csv) for capSpire-branded deliverables. Combines technical Excel workflows (formulas, data analysis, formatting) with capSpire brand guidelines (Franklin Gothic typography, Orange/Charcoal colors). Includes support for formula recalculation, zero-error validation, and industry-standard financial modeling conventions with capSpire visual styling.
---

# capSpire Excel Skill

## Overview

Create and edit Excel spreadsheets with capSpire brand styling while maintaining technical excellence and industry-standard best practices.

**Integration:** This skill works in conjunction with the `capspire-brand-guidelines` skill, which provides core brand identity (colors, fonts, tone). This skill adds Excel-specific implementation details.

**Industry Context:** Designed for ETRM/CTRM consulting deliverables including financial models, data analysis, dashboards, and client-facing reports.

---

## Brand Integration

### Reference capSpire Brand Guidelines

Use the `capspire-brand-guidelines` skill for:
- Core color palette and hex codes
- Franklin Gothic font family variants
- Brand voice and tone
- Accessibility standards

This skill provides Excel-specific implementation of those guidelines.

### Excel-Specific Brand Application

For detailed Excel brand implementation including typography specifications, color usage, and formatting examples, refer to:
- `references/excel-brand-guidelines.md` - Complete Excel brand implementation guide

**Quick Reference:**
- **Column Headers**: Franklin Gothic Demi, 11pt, **White (FFFFFF)** on Medium Gray (#666666) background
  - **CRITICAL**: Headers with gray fill MUST always use white text - never colored text
- **Section Headers**: Franklin Gothic Heavy, 12pt, Orange (#F7941D)
- **Cell Data**: Franklin Gothic Book, 10-11pt, Charcoal (#333333)
- **Totals/Summaries**: Franklin Gothic Demi, 11pt, Charcoal (#333333)
- **Charts**: Use capSpire data visualization color order (Orange → Lime → Green → Amber → Red → Charcoal)

---

## Requirements for All Excel Files

### Zero Formula Errors (CRITICAL)

Every Excel file MUST be delivered with ZERO formula errors:
- No #REF! errors (broken references)
- No #DIV/0! errors (division by zero)
- No #VALUE! errors (wrong value type)
- No #N/A errors (value not available)
- No #NAME? errors (unrecognized formula name)

**Validation:** Use `scripts/recalc.py` to recalculate all formulas and verify no errors exist.

### Preserve Existing Templates

When modifying existing Excel files:
- Study and EXACTLY match existing format, style, and conventions
- Never impose standardized formatting on files with established patterns
- Existing template conventions ALWAYS override these guidelines
- Only apply capSpire branding to new sections or when explicitly requested

---

## Technical Excel Workflows

### Use Formulas, Not Hardcoded Values (CRITICAL)

**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures spreadsheets remain dynamic and updateable.

#### ❌ WRONG - Hardcoding Calculated Values
```python
# Don't calculate in Python and write static values
revenue_2024 = 1000000 * 1.15  # 1,150,000
ws['B2'] = revenue_2024  # Hardcoded value
```

#### ✅ CORRECT - Using Excel Formulas
```python
# Write formula that Excel will calculate
ws['B2'] = '=B1*1.15'  # Dynamic formula
```

### Reading and Analyzing Data

For data analysis, visualization, and basic operations, use **pandas**:

```python
import pandas as pd

# Read Excel
df = pd.read_excel('file.xlsx')  # First sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # All sheets

# Analyze
df.head()      # Preview data
df.info()      # Column info
df.describe()  # Statistics

# Write Excel
df.to_excel('output.xlsx', index=False)
```

### Creating and Formatting Spreadsheets

Use **openpyxl** for creating spreadsheets with formulas and formatting:

```python
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# Create or load workbook
wb = Workbook()
ws = wb.active

# Write data and formulas
ws['A1'] = 'Revenue'
ws['B1'] = '=SUM(B2:B10)'  # Formula

# Apply capSpire brand styling
from openpyxl.styles import Font, PatternFill

# Header style (White on Charcoal)
header_font = Font(name='Franklin Gothic Demi', size=11, color='FFFFFF', bold=True)
header_fill = PatternFill(start_color='333333', end_color='333333', fill_type='solid')

ws['A1'].font = header_font
ws['A1'].fill = header_fill

# Data style (Charcoal text)
data_font = Font(name='Franklin Gothic Book', size=10, color='333333')
ws['A2'].font = data_font

# Save
wb.save('output.xlsx')
```

### Formula Recalculation

After creating or modifying Excel files with formulas, use the `recalc.py` script to recalculate all formula values:

```bash
python scripts/recalc.py path/to/file.xlsx
```

**Important:** LibreOffice is required for formula recalculation. The script automatically configures LibreOffice on first run.

---

## Industry-Standard Financial Modeling Conventions

### Color Coding for Formula Types

Use industry-standard color conventions for financial models (these override capSpire brand colors for functional purposes):

- **Blue text (RGB: 0,0,255)**: Hardcoded inputs that users will change for scenarios
- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
- **Red text (RGB: 255,0,0)**: External links to other files
- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells requiring updates

**Note:** Apply capSpire brand colors (Orange, Charcoal, Lime) to headers, titles, and visual elements. Keep functional color coding for cell contents in financial models.

**CRITICAL RULE - Headers vs Data Cells:**
- **Header rows with gray fill**: ALWAYS use White (FFFFFF) text, regardless of functional color coding
- **Data cells (no fill/white fill)**: Use functional colors (blue, black, green, red) for formula coding
- **Never apply functional colors to header text** - headers must maintain white-on-gray for readability

### Number Formatting Standards

- **Years**: Format as text strings (e.g., "2024" not "2,024")
- **Currency**: Use $#,##0 format; specify units in headers ("Revenue ($mm)")
- **Zeros**: Use formatting to display zeros as "-" (e.g., "$#,##0;($#,##0);-")
- **Percentages**: Default to 0.0% format (one decimal)
- **Multiples**: Format as 0.0x for valuation multiples
- **Negative numbers**: Use parentheses (123) not minus -123

### Formula Construction Rules

**Assumptions Placement:**
- Place ALL assumptions (growth rates, margins, multiples) in separate cells
- Use cell references instead of hardcoded values in formulas
- Example: Use `=B5*(1+$B$6)` instead of `=B5*1.05`

**Formula Error Prevention:**
- Verify all cell references are correct
- Check for off-by-one errors in ranges
- Ensure consistent formulas across projection periods
- Test with edge cases (zeros, negative numbers)
- Verify no unintended circular references

**Documentation for Hardcoded Values:**
Add comments or notes for hardcoded data:
- Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
- Example: "Source: Company 10-K, FY2024, Page 45, [SEC EDGAR URL]"

### Additional Best Practices

For comprehensive Excel standards including structural rules, formula conventions, and version control, see:
- `references/excel-brand-guidelines.md` - Additional Best Practices section

**Key Highlights:**
- **One formula per row**: Maintain consistent formula logic across rows
- **Calculate left to right, top to bottom**: Organize for logical flow without circular references
- **Separate inputs, calculations, outputs**: Use distinct tabs/sections for each
- **Avoid merged cells**: They break sorting and formulas
- **Minimize array formulas**: Use helper columns for auditability
- **No hidden calculations**: All logic must be visible
- **Keep formulas simple**: Break complex calculations into steps
- **Border key sections**: Visually separate calculation blocks
- **Version control**: Include date stamps in filenames (e.g., `Model_v2.1_2025-11-07.xlsx`)

---

## Chart and Visualization Guidelines

### capSpire Data Visualization Colors

Use capSpire brand colors for charts in order:

1. **capSpire Orange** (#F7941D) - Primary data series
2. **capSpire Lime** (#A6CE39) - Positive trends, growth
3. **Success Green** (#2ECC71) - Secondary positive indicator
4. **Alert Amber** (#F39C12) - Warnings, caution
5. **Critical Red** (#E74C3C) - Risks, declines, urgent items
6. **capSpire Charcoal** (#333333) - Neutral data series

### Chart Typography

- **Chart Titles**: Franklin Gothic Medium, 14pt, Charcoal
- **Axis Titles**: Franklin Gothic Medium, 11pt, Charcoal
- **Axis Labels**: Franklin Gothic Book, 10pt, Charcoal
- **Data Labels**: Franklin Gothic Book, 9pt, Charcoal
- **Legends**: Franklin Gothic Book, 10pt, Charcoal

### Chart Best Practices

- Use simple, clear chart types (bar, column, line)
- Limit to 6 data series maximum for readability
- Remove gridlines or use light gray (#D9D9D9) if needed
- Ensure chart backgrounds are white or light gray (#F2F2F2)
- Test charts in grayscale for accessibility

### Automatic Chart Formatting with Python

For comprehensive Python code examples showing how to automatically apply capSpire brand colors to charts and prevent title/axis overlap, see:
- `references/excel-brand-guidelines.md` - Python Chart Formatting Examples section

**Key Capabilities:**
- Automatically apply capSpire color palette to data series in correct order
- Remove or style gridlines per brand preferences
- Set white backgrounds for plot areas
- Position legends properly (right or bottom)
- Format titles and axis labels to prevent overlap
- Includes complete working examples for bar, column, line, and pie charts

---

## Workbook Organization Best Practices

### Worksheet Structure Guidelines

**Separate Data from Analysis:**
- **Data Sheets**: Store raw data, records, and transactional information
- **Summary Sheets**: Place summary statistics, calculations, and aggregations on separate sheets
- **Dashboard Sheets**: Create visualizations and executive summaries on dedicated sheets

**Rationale:** Separating data from summaries allows for easy expansion of datasets without disrupting analysis. Users can add new records to data sheets without reformatting summary sections.

**Default Pattern:**
```
Workbook Structure:
├── Data (raw records, growing dataset)
├── Summary (statistics, aggregations, totals)
└── Dashboard (optional - charts and visualizations)
```

**When to Deviate:**
- Simple workbooks with <20 rows of static data may combine data and summary
- Ad-hoc analysis where the dataset won't grow
- User explicitly requests single-sheet layout

---

## Common Workflows

### Creating a Multi-Sheet Workbook with Separate Data and Summary

```python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()

# ===== DATA SHEET =====
ws_data = wb.active
ws_data.title = "Data"

# Apply brand styling to headers
header_font = Font(name='Franklin Gothic Demi', size=11, color='FFFFFF', bold=True)
header_fill = PatternFill(start_color='666666', end_color='666666', fill_type='solid')  # Medium Gray
header_alignment = Alignment(horizontal='center', vertical='center')
data_font = Font(name='Franklin Gothic Book', size=10, color='333333')

# Title
ws_data['A1'] = 'Project Revenue Data'
ws_data['A1'].font = Font(name='Franklin Gothic Heavy', size=14, color='F7941D')

# Data headers (row 3)
headers = ['Quarter', 'Revenue', 'Expenses', 'Profit']
for col_num, header in enumerate(headers, 1):
    cell = ws_data.cell(row=3, column=col_num)
    cell.value = header
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment

# Sample data
data = [
    ['Q1 2025', 1000, 700, '=B4-C4'],
    ['Q2 2025', 1100, 750, '=B5-C5'],
    ['Q3 2025', 1200, 800, '=B6-C6'],
    ['Q4 2025', 1300, 850, '=B7-C7'],
]

for row_num, row_data in enumerate(data, 4):
    for col_num, value in enumerate(row_data, 1):
        cell = ws_data.cell(row=row_num, column=col_num)
        cell.value = value
        cell.font = data_font

# ===== SUMMARY SHEET =====
ws_summary = wb.create_sheet("Summary")

# Title
ws_summary['A1'] = 'Summary Statistics'
ws_summary['A1'].font = Font(name='Franklin Gothic Heavy', size=14, color='F7941D')

section_font = Font(name='Franklin Gothic Heavy', size=12, color='F7941D')
total_font = Font(name='Franklin Gothic Demi', size=11, color='333333', bold=True)

# Section header
ws_summary['A3'] = 'Annual Totals'
ws_summary['A3'].font = section_font

# Summary calculations referencing Data sheet
ws_summary['A4'] = 'Total Revenue:'
ws_summary['B4'] = "=SUM(Data!B4:B7)"
ws_summary['A5'] = 'Total Expenses:'
ws_summary['B5'] = "=SUM(Data!C4:C7)"
ws_summary['A6'] = 'Total Profit:'
ws_summary['B6'] = "=SUM(Data!D4:D7)"

# Style summary cells
for row in range(4, 7):
    ws_summary.cell(row=row, column=1).font = data_font
    ws_summary.cell(row=row, column=2).font = total_font
    ws_summary.cell(row=row, column=2).number_format = '$#,##0'

wb.save('branded_workbook.xlsx')
```

**Key Points:**
- Data sheet allows unlimited growth without affecting summaries
- Summary sheet uses cross-sheet references (e.g., `=SUM(Data!B4:B7)`)
- Users can add rows to Data sheet; Summary automatically updates

### Modifying an Existing Workbook

```python
from openpyxl import load_workbook

# Load existing file
wb = load_workbook('existing.xlsx')
ws = wb.active

# Modify data
ws['B2'] = '=B1*1.15'  # Add formula

# Apply brand styling to new sections only
# (preserve existing formatting elsewhere)

# Save
wb.save('existing_updated.xlsx')

# Recalculate formulas
import subprocess
subprocess.run(['python', 'scripts/recalc.py', 'existing_updated.xlsx'])
```

---

## Extended Documentation

For comprehensive Excel brand guidelines including detailed formatting specifications, examples, and best practices:
- `references/excel-brand-guidelines.md`

---

**Experience a Better Way.**