Excel Formulas and Function Reference for Operational Use
Common formulas and function families used in Microsoft Excel and compatible spreadsheet software power operational reporting, reconciliation, and automation. This reference outlines core formula categories, representative examples, typical use cases, cross-version considerations, and testing cues to help teams evaluate and adopt patterns for production spreadsheets.
Scope and software/version considerations
Spreadsheet function names, argument order, and availability differ across Excel for Windows, Excel for Mac, Excel Online, and alternative engines like Google Sheets. Recent Excel builds add dynamic array behavior and new functions; older versions require legacy array formulas. Check the target environment and locale-specific function names before standardizing templates.
Basic arithmetic and cell references
Simple arithmetic uses operators and cell references to compute totals, margins, and unit conversions. Use A1-style addresses for relative references and $A$1 for absolute anchoring when copying formulas across ranges. Practical snippets include cumulative sums with simple addition, per-unit pricing with multiplication and division, and percentage change using (new-old)/old. Named ranges can improve readability for recurring inputs like tax rates or exchange rates.
Text manipulation functions
Text functions handle parsing, normalization, and concatenation of labels and identifiers. LEFT, RIGHT, MID extract substrings; TRIM removes extra spaces; TEXT formats numbers as strings for display; CONCAT or & joins values. For example, parsing a SKU like “ABC-123-01” often combines FIND to locate delimiters and MID to extract segments. Use CLEAN to remove nonprinting characters when importing CSVs from external systems.
Lookup and reference functions
Lookup functions resolve keys to rows or columns in tables. VLOOKUP and INDEX/MATCH are classic choices; XLOOKUP (newer Excel) unifies vertical and horizontal lookups with built-in not-found handling. INDEX returns a value from a row/column intersection, while MATCH returns position indexes for dynamic referencing. Structured table references (Table[Column]) improve resilience when rows shift during inserts or filtering.
Conditional and logical functions
IF, IFS, and SWITCH implement conditional logic; AND/OR combine tests. Conditional aggregation uses SUMIF(S)/COUNTIF(S) for single- or multi-criteria totals. For multi-branch logic, IFS reduces nested IFs but evaluate order carefully to avoid unexpected short-circuiting. Use boolean expressions inside FILTER or conditional columns to produce subsets without helper columns.
Date and time functions
Date arithmetic and period calculations depend on serial date systems. TODAY and NOW provide current anchors; DATE, YEAR, MONTH, DAY extract components; EOMONTH gives month-end dates. NETWORKDAYS and WORKDAY account for business calendars and can accept holiday lists. When converting imported timestamps, confirm time zone and text format parsing with DATEVALUE or parsing combinations.
Aggregation and statistical functions
SUM, AVERAGE, MEDIAN, MIN, and MAX address common rollups. AGGREGATE and SUBTOTAL offer behavior that can ignore filtered or error cells. For conditional statistics, use AVERAGEIFS or COUNTIFS. Moving-window statistics commonly use OFFSET or dynamic ranges with INDEX; newer rolling calculations can leverage dynamic arrays or the LET function for clearer intermediate naming.
Array formulas and dynamic arrays
Arrays produce and consume ranges as single formula results. Legacy array formulas use Ctrl+Shift+Enter semantics; modern Excel supports implicit dynamic arrays where functions like FILTER, UNIQUE, SORT, and SEQUENCE spill results into adjacent cells. Arrays enable vectorized operations (for example, multiplying two ranges element-wise) and can remove helper columns, but they change how references behave when spilled ranges grow or shrink.
Error handling and debugging techniques
Wrap operations with IFERROR or IFNA to manage expected missing values and present clear fallback values. Use ISNUMBER, ISBLANK, and ISTEXT to validate inputs before computation. For auditing, deploy formula tracing tools, evaluate formulas stepwise, and add temporary diagnostic columns that expose intermediate values. Keep error suppression conservative; hiding errors without investigation can mask data quality issues.
Organization: naming, templates, and copy-ready snippets
Consistent naming and modular templates reduce mistakes in operational spreadsheets. Use named ranges for key inputs, a single Inputs worksheet for manual overrides, and a Calculation worksheet for core logic. Store reusable snippets in a snippet library or template workbook so analysts can copy verified formulas into new models. Below is a compact table of copy-ready snippets with notes about version behavior.
| Formula | Purpose | Example |
|---|---|---|
| =SUMIF(C2:C100,”Region A”,D2:D100) | Conditional aggregation by region | Totals sales for Region A using SUMIF |
| =INDEX(Table1[Price],MATCH(G2,Table1[SKU],0)) | Robust lookup using INDEX/MATCH | Finds price for SKU in G2; works across versions |
| =TEXT(E2,”yyyy-mm-dd”) | Standardize date formatting as text | Useful for exports where ISO date strings required |
| =IFERROR(XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B),”Not found”) | Safe lookup with friendly fallback | XLOOKUP available in modern Excel; use INDEX/MATCH fallback if needed |
Compatibility, constraints, and testing checklist
Adopt functions consciously: dynamic array functions simplify formulas but change how references spill; older clients may break if they open workbooks with newer functions. International users may see function names translated and list separators swapped between commas and semicolons. Accessibility considerations include using clear labels, avoiding hidden formulas, and ensuring screen-reader-friendly structure in worksheets.
A short testing checklist improves rollout: validate formulas on representative data, compare results with a trusted baseline, test with blank and edge-case inputs, and keep backups before changing live sheets. Encourage formula reviewers to document assumptions in adjacent cells or a documentation sheet so future auditors can trace logic quickly.
How Excel templates improve formula reuse
Basic Excel formulas for auditing workflows
Spreadsheet training resources for formula mastery
Choosing appropriate functions depends on the environment, the dataset size, and maintenance expectations. Favor readable constructions—named ranges, LET for intermediate names, and structured tables—over compressed, hard-to-debug formulas. Regular validation against representative inputs and conservative error handling keep operational spreadsheets reliable and easier to hand off between team members.
