cloud93421.monster

Excel Function Dictionary: Quick Reference for Every Formula

Written by

admin

in

Excel Function Dictionary: Find the Right Formula FastExcel is a powerful tool for organizing, analyzing, and presenting data — but its real strength comes from formulas. With hundreds of built‑in functions across categories like math, text, date/time, lookup, logical, and statistical, choosing the right formula can save time and reduce errors. This comprehensive dictionary-style guide helps you find the right Excel function quickly, explains common use cases, and shows practical examples to make each function easier to adopt.

How to use this dictionary

  • Scan categories to narrow down functions by task (e.g., text handling, lookups, dates).
  • Each entry includes a short description, typical use cases, a concise syntax line, and an example.
  • Use the examples as templates — replace cell references with your own data.
  • For complex needs, combine functions; many examples show simple combinations to solve real problems.

Basic arithmetic and aggregation

SUM

  • Description: Adds numbers.
  • Syntax: =SUM(number1, [number2], …)
  • Use case: Totaling sales or quantities.
  • Example: =SUM(B2:B10)

AVERAGE

  • Description: Calculates the arithmetic mean.
  • Syntax: =AVERAGE(number1, [number2], …)
  • Use case: Finding average score or rate.
  • Example: =AVERAGE(C2:C50)

COUNT / COUNTA / COUNTIF / COUNTIFS

  • COUNT — counts numeric cells. Syntax: =COUNT(range)
  • COUNTA — counts non-empty cells. Syntax: =COUNTA(range)
  • COUNTIF — counts cells meeting one condition. Syntax: =COUNTIF(range, criteria)
  • COUNTIFS — counts cells meeting multiple conditions. Syntax: =COUNTIFS(range1, crit1, [range2, crit2], …)
  • Example: =COUNTIF(D2:D100, “Completed”)

SUMIF / SUMIFS

  • Description: Sums values that meet one or more criteria.
  • Syntax: =SUMIF(range, criteria, [sum_range]) or =SUMIFS(sum_range, criteria_range1, criteria1, …)
  • Example: =SUMIFS(E2:E100, B2:B100, “East”, C2:C100, “>1000”)

Logical functions

IF / IFS

  • IF — returns one value if condition is true, another if false. Syntax: =IF(logical_test, value_if_true, value_if_false)
  • IFS — evaluates multiple conditions in order. Syntax: =IFS(condition1, value1, condition2, value2, …)
  • Example: =IF(F2>0, “Profit”, “Loss”)

AND / OR / NOT

  • AND — returns TRUE if all arguments are TRUE. Syntax: =AND(condition1, condition2, …)
  • OR — returns TRUE if any argument is TRUE. Syntax: =OR(condition1, condition2, …)
  • NOT — reverses logical value. Syntax: =NOT(condition)
  • Example: =AND(G2>0, H2<100)

Text functions

CONCAT / CONCATENATE / TEXTJOIN

  • CONCAT — joins text from multiple cells. Syntax: =CONCAT(text1, [text2], …)
  • CONCATENATE — older form, same purpose.
  • TEXTJOIN — joins text with a delimiter and option to ignore empty cells. Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
  • Example: =TEXTJOIN(” “, TRUE, A2, B2)

LEFT / RIGHT / MID

  • LEFT — returns leftmost characters. Syntax: =LEFT(text, [num_chars])
  • RIGHT — returns rightmost characters. Syntax: =RIGHT(text, [num_chars])
  • MID — returns characters from the middle. Syntax: =MID(text, start_num, num_chars)
  • Example: =MID(A2, 4, 3)

TRIM / CLEAN / UPPER / LOWER / PROPER

  • TRIM — removes extra spaces. Syntax: =TRIM(text)
  • CLEAN — removes non-printable characters. Syntax: =CLEAN(text)
  • UPPER / LOWER / PROPER — change case. Syntax: =UPPER(text)
  • Example: =PROPER(B2)

FIND / SEARCH / SUBSTITUTE

  • FIND — locates substring (case-sensitive). Syntax: =FIND(find_text, within_text, [start_num])
  • SEARCH — similar but case-insensitive. Syntax: =SEARCH(find_text, within_text, [start_num])
  • SUBSTITUTE — replaces occurrences of text. Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
  • Example: =SUBSTITUTE(C2, “Inc”, “LLC”)

Date & time functions

TODAY / NOW

  • TODAY — returns current date. Syntax: =TODAY()
  • NOW — returns current date and time. Syntax: =NOW()
  • Use cases: Dynamic timestamps for reports.

DATE / DATEVALUE / TIME

  • DATE — constructs a date from year, month, day. Syntax: =DATE(year, month, day)
  • DATEVALUE — converts date in text to serial number. Syntax: =DATEVALUE(date_text)
  • TIME — constructs time from hour, minute, second. Syntax: =TIME(hour, minute, second)
  • Example: =DATE(2025, 9, 2)

NETWORKDAYS / WORKDAY

  • NETWORKDAYS — counts working days between two dates (can exclude holidays). Syntax: =NETWORKDAYS(start_date, end_date, [holidays])
  • WORKDAY — returns a date after adding working days. Syntax: =WORKDAY(start_date, days, [holidays])
  • Example: =NETWORKDAYS(A2, B2, Holidays!A:A)

EDATE / EOMONTH / YEAR / MONTH / DAY

  • EDATE — adds months to a date. Syntax: =EDATE(start_date, months)
  • EOMONTH — last day of month offset. Syntax: =EOMONTH(start_date, months)
  • YEAR/MONTH/DAY — extract components. Syntax: =YEAR(date)
  • Example: =EOMONTH(TODAY(), 0)

Lookup & reference

VLOOKUP / HLOOKUP / XLOOKUP

  • VLOOKUP — vertical lookup; approximate or exact match. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • HLOOKUP — horizontal equivalent.
  • XLOOKUP — modern, flexible lookup (exact match default, supports searching left). Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
  • Example: =XLOOKUP(A2, Products[ID], Products[Price], “Not found”)

INDEX / MATCH

  • INDEX — returns value at specified row/column in an array. Syntax: =INDEX(array, row_num, [column_num])
  • MATCH — finds position of a value. Syntax: =MATCH(lookup_value, lookup_array, [match_type])
  • Combined: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

OFFSET / INDIRECT / ADDRESS

  • OFFSET — returns reference offset from a starting cell. Syntax: =OFFSET(reference, rows, cols, [height], [width])
  • INDIRECT — returns reference specified by text. Syntax: =INDIRECT(ref_text, [a1])
  • ADDRESS — creates a cell address as text. Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
  • Use case: Dynamic ranges for charts or formulas.

Financial functions

PMT / PV / FV / NPV / IRR

  • PMT — payment for loan based on constant payments and interest. Syntax: =PMT(rate, nper, pv, [fv], [type])
  • PV — present value. Syntax: =PV(rate, nper, pmt, [fv], [type])
  • FV — future value. Syntax: =FV(rate, nper, pmt, [pv], [type])
  • NPV — net present value of cash flows. Syntax: =NPV(rate, value1, [value2], …)
  • IRR — internal rate of return. Syntax: =IRR(values, [guess])
  • Example: =PMT(0.05/12, 60, -20000)

Statistical & analysis

MEDIAN / MODE / STDEV.P / STDEV.S / VAR.P / VAR.S

  • MEDIAN — middle value. Syntax: =MEDIAN(range)
  • MODE.SNGL — most frequent value. Syntax: =MODE.SNGL(range)
  • STDEV.P / STDEV.S — population/sample standard deviation. Syntax: =STDEV.S(range)
  • VAR.P / VAR.S — variance.
  • Example: =STDEV.S(B2:B100)

PERCENTILE.EXC / PERCENTILE.INC / QUARTILE.EXC / QUARTILE.INC

  • Use for percentile and quartile calculations. Syntax: =PERCENTILE.INC(array, k)
  • Example: =PERCENTILE.INC(Scores, 0.9)

UNIQUE / SORT / FILTER

  • UNIQUE — returns unique values from a range. Syntax: =UNIQUE(array, [by_col], [exactly_once])
  • SORT — sorts an array. Syntax: =SORT(array, [sort_index], [sort_order], [by_col])
  • FILTER — returns array filtered by condition. Syntax: =FILTER(array, include, [if_empty])
  • Example: =FILTER(Table, Table[Status]=“Active”)

Array & dynamic functions

SEQUENCE / RANDARRAY / SORTBY

  • SEQUENCE — generates a sequence of numbers. Syntax: =SEQUENCE(rows, [columns], [start], [step])
  • RANDARRAY — produces random numbers in an array. Syntax: =RANDARRAY([rows], [columns], [min], [max], [whole_number])
  • SORTBY — sorts based on corresponding array. Syntax: =SORTBY(array, by_array, [sort_order])
  • Example: =SEQUENCE(5,1,1,1)

Information & error handling

ISBLANK / ISNUMBER / ISTEXT / ISERROR / IFERROR / IFNA

  • IS* functions — test cell types or errors. Syntax: =ISNUMBER(value)
  • IFERROR — returns alternate value if formula returns an error. Syntax: =IFERROR(value, value_if_error)
  • IFNA — handles #N/A specifically. Syntax: =IFNA(value, value_if_na)
  • Example: =IFERROR(VLOOKUP(A2,Range,2,FALSE), “Not found”)

Text & number formatting

TEXT

  • Description: Formats a number and converts it to text with a format. Syntax: =TEXT(value, format_text)
  • Use case: Concatenate formatted dates or currencies.
  • Example: =TEXT(TODAY(),“yyyy-mm-dd”)

VALUE / NUMBERVALUE

  • Converts text to number. Syntax: =VALUE(text)
  • NUMBERVALUE allows custom decimal and group separators. Syntax: =NUMBERVALUE(text, decimal_separator, group_separator)

Tips for finding the right function fast

  • Use Excel’s Insert Function (fx) to search by description.
  • Search online for “X function + example” when unsure about syntax.
  • Start with the goal (e.g., “combine columns”, “exclude weekends”) and pick category: text, date, lookup, or logical.
  • For lookup needs prefer XLOOKUP or INDEX+MATCH for flexibility.
  • Use dynamic array functions (FILTER, UNIQUE, SORT) to simplify many older formula patterns.

  1. Combine first and last name, trimming extra spaces:

    • =TEXTJOIN(” “, TRUE, TRIM(A2), TRIM(B2))

  2. Sum sales for a product in a region over a quarter:

    • =SUMIFS(Sales[Amount], Sales[Product], “Widget”, Sales[Region], “North”, Sales[Date], “>=2025-01-01”, Sales[Date], “<=2025-03-31”)

  3. Create a dynamic list of active customers:

    • =FILTER(Customers[Name], Customers[Status]=“Active”)

  4. Replace nested VLOOKUPs across sheets:

    • Use XLOOKUP with optional if_not_found and arrays:
    • =XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, “Not found”)

  5. Calculate loan monthly payment:

    • =PMT(annual_rate/12, years*12, -loan_amount)

Common pitfalls

  • Confusing relative vs absolute references — use $ when locking ranges.
  • VLOOKUP’s default approximate match — set fourth argument to FALSE or use XLOOKUP.
  • Dates stored as text — convert with DATEVALUE or Text to Columns.
  • Performance issues with volatile functions (OFFSET, INDIRECT, TODAY, NOW, RAND) in very large workbooks.

Quick reference by task

  • Summing with conditions: SUMIF / SUMIFS
  • Counting with conditions: COUNTIF / COUNTIFS
  • Exact lookups: XLOOKUP / INDEX+MATCH
  • Unique lists: UNIQUE
  • Dynamic filtering: FILTER
  • Text joining: TEXTJOIN
  • Date arithmetic: EDATE / WORKDAY / NETWORKDAYS
  • Error handling: IFERROR / IFNA

If you want, I can convert this into a printable cheat sheet, an A–Z index of every Excel function with one-line examples, or provide step‑by‑step walkthroughs for any of the example scenarios.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts