XLSX Formulas
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:
code
=SUM(range) =AVERAGE(range) =COUNT(range)
Conditional:
code
=IF(condition, true_value, false_value) =IFS(cond1, val1, cond2, val2, TRUE, default)
Lookup:
code
=XLOOKUP(lookup, search_range, return_range) =INDEX(range, MATCH(value, lookup_range, 0))
Conditional Sum/Count:
code
=SUMIF(range, criteria, sum_range) =SUMIFS(sum_range, range1, criteria1, range2, criteria2) =COUNTIF(range, criteria)
Error Handling:
code
=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 |
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 advanced-formulas skill.