xlsx
Comprehensive spreadsheet work including creation, editing, and analysis of Excel files (.xlsx, .xlsm, .csv, .tsv). When Claude needs to work with spreadsheets for data analysis, financial modeling, or any Excel-related tasks.
When & Why to Use This Skill
This Claude skill provides comprehensive spreadsheet capabilities, enabling the professional creation, editing, and advanced analysis of Excel files including .xlsx, .xlsm, .csv, and .tsv formats. It specializes in high-integrity data analysis and financial modeling, ensuring zero formula errors and strict adherence to industry-standard formatting. By leveraging powerful Python libraries like pandas and openpyxl, it maintains spreadsheet dynamism through formulas rather than hardcoded values, making it an essential tool for automated reporting and complex data management.
Use Cases
- Financial Modeling: Constructing dynamic, error-free financial models with professional color-coding (blue for inputs, black for formulas) and standardized number formatting.
- Automated Data Analysis: Utilizing pandas to process large datasets, generate pivot tables, and perform complex data cleaning and transformation tasks.
- Professional Report Generation: Creating formatted Excel reports from raw data, incorporating conditional formatting, custom styles, and complex cell formulas.
- Template Preservation & Updates: Modifying existing spreadsheets while strictly maintaining original styles, conventions, and organizational structures.
- Bulk File Conversion: Efficiently reading and processing multiple worksheets or converting between different data formats like CSV and XLSX.
| name | xlsx |
|---|---|
| description | Comprehensive spreadsheet work including creation, editing, and analysis of Excel files (.xlsx, .xlsm, .csv, .tsv). When Claude needs to work with spreadsheets for data analysis, financial modeling, or any Excel-related tasks. |
XLSX Processing
Overview
Work with Excel spreadsheets for creation, editing, data analysis, and financial modeling.
Key Requirements
Zero Formula Errors
All Excel deliverables must have no errors:
#REF!- Invalid reference#DIV/0!- Division by zero#VALUE!- Wrong value type#N/A- Value not available#NAME?- Unrecognized name
Template Preservation
When updating existing files, study and exactly match existing format, style, and conventions.
Financial Model Standards
Color Coding Convention
| Color | Usage |
|---|---|
| Blue text | Hardcoded inputs users will modify |
| Black text | All formulas and calculations |
| Green text | Links from other worksheets |
| Red text | External file links |
| Yellow background | Key assumptions requiring attention |
Number Formatting
- Years as text strings ("2024" not "2,024")
- Currency:
$#,##0with units in headers - Zeros displayed as "-"
- Percentages:
0.0%format - Negative numbers in parentheses, not minus signs
Python Libraries
pandas - Data Analysis
import pandas as pd
# Read Excel
df = pd.read_excel('input.xlsx', sheet_name='Sheet1')
# Process data
df['Total'] = df['Price'] * df['Quantity']
# Write Excel
df.to_excel('output.xlsx', index=False)
openpyxl - Complex Formatting
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
wb = Workbook()
ws = wb.active
# Add data with formatting
ws['A1'] = 'Revenue'
ws['A1'].font = Font(bold=True)
# Add formula
ws['B10'] = '=SUM(B1:B9)'
wb.save('output.xlsx')
Tool Selection
| Task | Tool |
|---|---|
| Data analysis | pandas |
| Bulk operations | pandas |
| Simple exports | pandas |
| Complex formatting | openpyxl |
| Formulas | openpyxl |
| Excel-specific features | openpyxl |
Critical Rules
Use Formulas, Not Hardcoded Values
Always employ Excel formulas instead of calculating in Python and embedding results. This maintains spreadsheet dynamism.
# Good - uses formula
ws['C1'] = '=A1+B1'
# Bad - hardcoded result
ws['C1'] = 15 # Don't do this
Documentation Requirements
Hardcoded values require comments citing:
- Source
- Date
- Location
Example: "Source: Company 10-K, FY2024, Page 45"
Common Operations
Reading Multiple Sheets
xlsx = pd.ExcelFile('workbook.xlsx')
for sheet_name in xlsx.sheet_names:
df = pd.read_excel(xlsx, sheet_name=sheet_name)
Conditional Formatting
from openpyxl.formatting.rule import ColorScaleRule
rule = ColorScaleRule(
start_type='min', start_color='FF0000',
end_type='max', end_color='00FF00'
)
ws.conditional_formatting.add('A1:A10', rule)
Pivot Tables with pandas
pivot = df.pivot_table(
values='Sales',
index='Region',
columns='Product',
aggfunc='sum'
)