Standard Excel formula writing skill covering syntax, functions, error handling, and common patterns.
Overview
This skill provides guidance for writing error-free Excel formulas with proper syntax and best practices. Use when creating spreadsheet calculations, data analysis formulas, or any formula-based operations in Excel.
What's Covered
| Topic | Description |
|---|---|
| Syntax Rules | Equal sign, parentheses, separators, cell references |
| Validation | Pre-flight checklist before outputting formulas |
| Error Handling | Common errors and prevention strategies |
| Function Categories | Math, Logical, Lookup, Text, Date/Time, Statistical |
| Formula Patterns | Templates for common calculations |
Function Categories Quick Reference
| Category | Key Functions | Use Cases |
|---|---|---|
| Math | SUM, AVERAGE, ROUND, ABS | Arithmetic operations |
| Logical | IF, AND, OR, IFS, SWITCH | Conditional logic |
| Lookup | VLOOKUP, INDEX, MATCH, XLOOKUP | Data retrieval |
| Text | CONCAT, LEFT, RIGHT, MID, TRIM | String manipulation |
| Date/Time | TODAY, NOW, DATE, DATEDIF | Date calculations |
| Statistical | COUNT, COUNTIF, SUMIF, SUMIFS | Conditional aggregation |
| Dynamic Array | FILTER, SORT, UNIQUE, SEQUENCE | Modern spill formulas |
Common Formula Patterns
Aggregation:
=SUM(range)
=AVERAGE(range)
=COUNT(range)Conditional:
=IF(condition, true_value, false_value)
=IFS(cond1, val1, cond2, val2, TRUE, default)Lookup:
=XLOOKUP(lookup, search_range, return_range)
=INDEX(range, MATCH(value, lookup_range, 0))Conditional Sum/Count:
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
=COUNTIF(range, criteria)Error Handling:
=IFERROR(formula, fallback_value)
=IFNA(lookup_formula, "Not Found")Validation Checklist
Before outputting any formula:
- Starts with
= - Parentheses are balanced
- Function names are English
- Arguments use commas (not semicolons)
- Cell references are valid (A1 notation)
- No circular references
- Data types match function expectations
Error Prevention
| Error | Cause | Solution |
|---|---|---|
| #REF! | Invalid reference | Verify cell exists |
| #VALUE! | Type mismatch | Check data types |
| #DIV/0! | Division by zero | Use IFERROR wrapper |
| #N/A | Not found | Add fallback value |
| #NAME? | Unknown function | Check spelling |
Scripts
Lint formulas before giving them to the user:
python scripts/check_formula.py '=IFERROR(VLOOKUP(A2,Data!A:C,3,FALSE),"Not found")'
python scripts/check_formula.py --file formulas.txt --jsonReports missing =, unbalanced parentheses, unterminated strings, smart quotes, nesting over 64 levels, unknown function names (#NAME? risk), volatile functions and VLOOKUP/HLOOKUP calls without an explicit match mode. Exit code 1 means at least one formula has errors.
Skill Files
| File | Content |
|---|---|
| formulas.md | Complete syntax rules, templates, and patterns |
| functions-reference.md | Detailed function documentation by category |
| examples.md | Production-ready formula examples |
When to Use
Use this skill when:
- Writing Excel formulas for calculations
- Creating lookup or conditional logic
- Aggregating data with criteria
- Handling formula errors
- Building formula templates
For advanced features (LAMBDA, LET, GROUPBY), see the excel-advanced-formulas skill.
excel-advanced-formulas
Modern Excel 365/2021+ formulas — LAMBDA and LAMBDA helpers (MAP, REDUCE, SCAN, BYROW, BYCOL), LET, dynamic arrays (FILTER, SORT, UNIQUE, HSTACK, VSTACK, TAKE, DROP), TEXTSPLIT, REGEX functions, GROUPBY and PIVOTBY.
excel-fundamentals
Excel workbook fundamentals — cells, ranges, absolute/relative references, named ranges, number formats, conditional formatting, sheets and views, sorting, filtering, tables and keyboard shortcuts.