Excel Formulas & Functions

SUM

A basic function with the syntax $=SUM(\text{number1, number2, ...})$ that adds numbers together.

COUNT

A function that counts cells containing numbers but does NOT count text.

COUNTA

A function with the syntax $=COUNTA(\text{value1, value2, ...})$ that counts all non-empty cells, including both numbers and text.

AVERAGE

A function that calculates the arithmetic mean/average of a range of numbers.

MEDIAN

A function that returns the middle value when data is arranged from lowest to highest.

MODE.SNGL

A function with the syntax $=MODE.SNGL(\text{number1, number2, ...})$ that returns the value occurring most frequently in a data set.

STDEV.S

A function that measures how widely sample data is dispersed from the mean.

RANK.EQ

A function used to find the relative position or rank of a number within a specified data set.

MAX

A basic function that returns the largest value in a data set.

LARGE

A function with the syntax $=LARGE(\text{array, k})$ that returns the k-th largest value, such as the $10\text{th}$-largest value.

COUNTIFS

A function with the syntax =COUNTIFS(\text{criteria_range1, criteria1, ...}) used to count cells that meet one or more conditions.

Wildcard ?

A wildcard character that represents exactly one character in text search criteria.

Wildcard *

A wildcard character that represents any number of characters in text search criteria.

ROUND

A mathematical function that rounds a number normally to a specified number of decimal places.

ROUNDUP

A function that rounds a number away from zero.

ROUNDDOWN

A function that rounds a number toward zero.

TRUNC

A function with the syntax =TRUNC(\text{number, [num_digits]}) that removes decimal digits without rounding the value.

ODD

A function that rounds a number away from zero to the nearest odd integer.

EVEN

A function that rounds a number away from zero to the nearest even integer.

IF

A logical function with the syntax =IF(\text{logical_test, value_if_true, value_if_false}) that tests a condition and returns different results based on whether that test is TRUE or FALSE.

AND

A logical function that returns TRUE only if ALL conditions are TRUE.

OR

A logical function that returns TRUE if AT LEAST ONE condition is TRUE.

NOT

A function that reverses the logical value, turning TRUE into FALSE and FALSE into TRUE.

Relational Operators

Operators such as $=$, $<>$, $>$, $<$, $>=$ and $<=$ that return Boolean values of TRUE or FALSE.

VLOOKUP

A vertical lookup function that searches down the FIRST COLUMN of a table and returns a value from a specified column index number ($col_index_num$).

HLOOKUP

A horizontal lookup function that searches across the TOP ROW of a table and returns a value from a specified row index number ($row_index_num$).

LOOKUP

A function with the syntax =LOOKUP(\text{lookup_value, lookup_vector, [result_vector]}) that searches a row or column for a value and returns a corresponding value.

INDEX

A function with the syntax =INDEX(\text{array, row_num, [column_num]}) that retrieves a value based on its specific row and column position.

ISERROR

An error-checking function that returns TRUE if a value produces ANY Excel error (e.g., \text{#REF!}, \text{#DIV/0!}, \text{#N/A}).

ISERR

A function that checks whether a value produces an error EXCEPT for the \text{#N/A} error.

ISNA

A function that checks specifically for the \text{#N/A} (value not available) error.

ISBLANK

A function that returns TRUE if a cell is actually empty and FALSE if it is not.

ISNONTEXT

A function that returns TRUE if the value provided is NOT text.

Relative Reference

A cell reference (e.g., $A1$) where both the column and row can change when the formula is copied.

Absolute Reference

A cell reference (e.g., $\text{\textdollar A\textdollar 1}$) where both the column and row are locked and will not change when copied.

Mixed Reference

A cell reference where either the column is locked (e.g., $\text{\textdollar A1}$) or the row is locked (e.g., $\text{A\textdollar 1}$).

Operator Precedence

The order Excel evaluates formulas: 1. Parentheses, 2. Exponentiation, 3. Multiplication and Division, 4. Addition and Subtraction, 5. Comparisons.

NAME?

An Excel error indicating that something in the formula is not recognized, such as a misspelled function or text without quotation marks.

VALUE!

An Excel error indicating the wrong type of value or operand is being used in a calculation.

REF!

An Excel error indicating an invalid cell reference.

DIV/0!

An Excel error indicating an attempt to divide a number by zero.

N/A

An Excel error indicating that a value is not available or could not be found.

#

A display issue that occurs when a column is too narrow to display a value; it is generally not a formula error.

Custom Format #

A custom number formatting code that functions as an optional digit placeholder, displaying a digit only when needed.

Custom Format 0

A custom number formatting code that forces a digit or zero to display.

F2

The keyboard shortcut used to edit the active cell or formula.

F4

The keyboard shortcut used to cycle through cell reference types ($A1$, $\text{\textdollar A\textdollar 1}$, $\text{A\textdollar 1}$, $\text{\textdollar A1}$).

Ctrl + `

The keyboard shortcut used to toggle between displaying formulas and displaying formula results.

Ctrl+Shift+~

The keyboard shortcut used to apply the General number format.

Ctrl+Shift+$

The keyboard shortcut used to apply the Currency format.