Excel Formulas and Functions

Excel formula starting symbol

Always start with =

Relative cell reference

A reference that automatically updates when a formula is pasted or copied.

Absolute cell reference

A cell reference that remains constant using dollar signs when copied.

SUM function

Calculates the sum of a set of numbers.

COUNT function

Counts the number of cells containing numbers.

COUNTA function

Counts the number of cells that are not blank.

AVERAGE function

Calculates the simple average of a set of numbers.

MAX function

Returns the largest value in a set of numbers.

MIN function

Returns the smallest value in a set of numbers.

NPER financial function

Calculates the number of payments to be made for a loan.

PMT financial function

Calculates the payment amount for a loan.

PV financial function

Calculates the current present value of an investment.

FV financial function

Calculates the future value of an investment.

RATE financial function

Calculates the interest rate earned for an investment.

EFFECT financial function

Calculates the annual percentage rate considering compounding interest.

Time value of money

The concept that a dollar earned today is worth more than next year.

Trace Precedents

Shows which cells are referenced in a particular formula.

Trace Dependents

Shows which other cells reference a particular cell.

Show Formulas

Displays formulas in each cell instead of their calculated results.

Boolean logic outcomes

Can have only two outcomes represented as FALSE (0) or TRUE (1).

AND function

Determines if all of the combined expressions are TRUE: =AND(logical1,[logical2],...)

OR function

Determines if at least one of the combined expressions is TRUE: =OR(logical1,[logical2],...)

NOT function

Returns the opposite of a Boolean expression.

IF function

Returns one value if a condition is true and another if false: =IF(logical_test,[value_if_true],[value_if_false])

COUNTIF function

Calculates the number of cells within a range that contain a specific value.

SUMIF function

Calculates the total for a set of values that match a specific criterion.

AVERAGEIF function

Calculates the average for a set of values that match a specific criterion.

VLOOKUP function

Finds a specified value in one column of data and returns a corresponding value from a reference table.

HLOOKUP function

Finds a specified value in one row of data and returns a corresponding value from a reference table.

HOUR function

Returns the hour portion of a time as a number from 0 to 23.

MINUTE function

Returns the minute portion of a time as a number from 0 to 59.

SECOND function

Returns the second portion of a time as a number from 0 to 59.

Date and Time Functions

Used to effectively work with dates and times to make professional-looking worksheets.

Text Functions

Used to easily manipulate text in worksheets.

Column Charts

Used to compare different categories or the same category over time, limited to 5-10 categories.

Stacked Column Charts

Well suited to compare categories that can be decomposed into smaller common segments across columns.

Pie Charts

Express the parts of a data series as percentages of the total for that series.

Pie of Pie Chart

A special chart type used to further decompose small segments of a larger category.

Line Charts

Particularly useful for identifying trends in a dataset, typically used over time.

Excel Charting Environment

The Excel workspace for working with charts, including Design and Formatting.