Skip to content
v0.1.0latest release21 skills2 categories3 sub-categories22 tested scripts68 reference docsContact us
Vector Agentic Skills
Submit a skill

XLSX Formula Writing

Reference for excel-formulas · 309 lines

Productivity / Excelv1.0.0Updated OCT 20 20263 references1 script

Syntax Rules

Every formula must follow these rules:

RuleRequirementExample
Start with =All formulas begin with equal sign=SUM(A1:A10)
Balanced parenthesesEvery ( must have matching )=IF(A1>0, (B1+C1), 0)
Comma separatorsUse , not ; for arguments=SUM(A1, B1, C1)
English function namesAlways use English names=SUMME wrong, =SUM correct
Colon for rangesUse : between start and end cells=SUM(A1:A10)

Cell Reference Types

TypeSyntaxBehavior
RelativeA1Changes when copied
Absolute$A$1Never changes
Mixed (column locked)$A1Column fixed, row changes
Mixed (row locked)A$1Row fixed, column changes

Pre-Flight Validation Checklist

Before outputting any formula, verify:

text
- [ ] Starts with =
- [ ] Parentheses are balanced (count opening = count closing)
- [ ] Function names are English and correctly spelled
- [ ] Arguments separated by commas
- [ ] Cell references use valid notation (A1, not 1A)
- [ ] Column letters A-XFD, row numbers 1-1048576
- [ ] No circular references (formula doesn't reference its own cell)
- [ ] Data types match function expectations
- [ ] Ranges use colon notation (A1:B10)
- [ ] Text values wrapped in double quotes ("text")

Error Types and Prevention

ErrorCausePrevention
#REF!Invalid cell referenceCheck references exist before using
#VALUE!Data type mismatchEnsure numeric functions get numbers
#DIV/0!Division by zeroUse =IFERROR(A1/B1, 0)
#N/ALookup value not foundUse XLOOKUP with if_not_found parameter
#NAME?Unrecognized functionCheck function name spelling
#NUM!Invalid numeric valueValidate numeric inputs
#NULL!Incorrect range intersectionUse proper range operators
#SPILL!Spill range blockedClear cells in spill area

Error Handling Pattern

Wrap formulas with IFERROR when errors are possible:

excel
=IFERROR(formula, fallback_value)

For specific error handling:

excel
=IFNA(lookup_formula, "Not Found")

Function Categories

CategoryFunctionsPurpose
MathSUM, AVERAGE, ROUND, ABS, MOD, POWERArithmetic operations
LogicalIF, AND, OR, NOT, IFS, SWITCHConditional logic
LookupVLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, XMATCHData retrieval
TextCONCAT, LEFT, RIGHT, MID, TRIM, UPPER, LOWER, LENString manipulation
Date/TimeTODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIFDate calculations
StatisticalCOUNT, COUNTA, COUNTIF, COUNTIFS, SUMIF, SUMIFSConditional aggregation
Dynamic ArrayFILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAYModern spill formulas

For detailed function reference, see functions-reference.md.

Formula Templates

Basic Aggregation

Sum values:

excel
=SUM(range)
=SUM(A1:A100)

Average values:

excel
=AVERAGE(range)
=AVERAGE(B1:B50)

Count numbers:

excel
=COUNT(range)
=COUNTA(range)  // counts non-empty cells

Conditional Logic

Single condition:

excel
=IF(condition, value_if_true, value_if_false)
=IF(A1>100, "High", "Low")

Multiple conditions (modern):

excel
=IFS(condition1, value1, condition2, value2, TRUE, default)
=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", TRUE, "F")

Combined conditions:

excel
=IF(AND(condition1, condition2), result_if_both, result_otherwise)
=IF(OR(condition1, condition2), result_if_either, result_otherwise)

Conditional Aggregation

Sum with single condition:

excel
=SUMIF(criteria_range, criteria, sum_range)
=SUMIF(A:A, "Product A", B:B)

Sum with multiple conditions:

excel
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
=SUMIFS(C:C, A:A, "East", B:B, ">1000")

Count with conditions:

excel
=COUNTIF(range, criteria)
=COUNTIFS(range1, criteria1, range2, criteria2)

Lookup Patterns

Modern lookup (Excel 365/2021+):

excel
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP(E1, A:A, B:B, "Not Found", 0)

Legacy vertical lookup:

excel
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
=VLOOKUP(E1, A1:C100, 2, FALSE)

Flexible lookup (any direction):

excel
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(B:B, MATCH(E1, A:A, 0))

Two-dimensional lookup:

excel
=INDEX(data_range, MATCH(row_lookup, row_headers, 0), MATCH(col_lookup, col_headers, 0))

Text Operations

Combine text:

excel
=CONCAT(text1, text2, ...)
=CONCAT(A1, " ", B1)
=A1 & " " & B1

Extract text:

excel
=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)

Clean text:

excel
=TRIM(text)
=CLEAN(text)
=SUBSTITUTE(text, old_text, new_text)

Date Calculations

Current date/time:

excel
=TODAY()
=NOW()

Extract date parts:

excel
=YEAR(date)
=MONTH(date)
=DAY(date)

Date difference:

excel
=DATEDIF(start_date, end_date, "Y")  // years
=DATEDIF(start_date, end_date, "M")  // months
=DATEDIF(start_date, end_date, "D")  // days

Add months:

excel
=EDATE(start_date, months)
=EOMONTH(start_date, months)  // end of month

Dynamic Array Formulas (Excel 365/2021+)

Filter data:

excel
=FILTER(array, include, [if_empty])
=FILTER(A2:C100, B2:B100>1000, "No results")

Sort data:

excel
=SORT(array, [sort_index], [sort_order], [by_col])
=SORT(A2:C100, 2, -1)  // sort by column 2 descending

Unique values:

excel
=UNIQUE(array, [by_col], [exactly_once])
=UNIQUE(A2:A100)

Generate sequence:

excel
=SEQUENCE(rows, [cols], [start], [step])
=SEQUENCE(10, 1, 1, 1)  // 1 to 10

Formula Construction Workflow

text
1. IDENTIFY   -> Determine what calculation is needed
2. SELECT     -> Choose appropriate function(s) from categories
3. DRAFT      -> Build formula using template patterns
4. VALIDATE   -> Run pre-flight checklist
5. PROTECT    -> Add error handling (IFERROR/IFNA) if needed
6. TEST       -> Consider edge cases (empty, zero, missing data)

Common Pitfalls

PitfallWrongCorrect
Text in numbers=SUM(A1, "5")=SUM(A1, 5)
Missing quotes=IF(A1=Yes, 1, 0)=IF(A1="Yes", 1, 0)
Wrong separator=SUM(A1;B1;C1)=SUM(A1, B1, C1)
Hardcoded values=A1*0.15=A1*$B$1 (tax rate in B1)
Nested IF overuse10+ nested IFsUse IFS, SWITCH, or XLOOKUP
VLOOKUP last columnNot foundUse INDEX/MATCH or XLOOKUP

Operator Precedence

Evaluated in order (highest to lowest):

  1. : (range), (intersection), , (union)
  2. - (negation)
  3. % (percent)
  4. ^ (exponentiation)
  5. * and / (multiplication, division)
  6. + and - (addition, subtraction)
  7. & (concatenation)
  8. =, <, >, <=, >=, <> (comparison)

Use parentheses to override precedence.

Comparison Operators

OperatorMeaning
=Equal to
<>Not equal to
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal

Wildcard Characters (in COUNTIF, SUMIF, etc.)

WildcardMatches
*Any number of characters
?Any single character
~*Literal asterisk
~?Literal question mark

Example: =COUNTIF(A:A, "A*") counts cells starting with "A"

Additional Resources

More in Excel

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.