Excel Formula Basic to Advanced Education Course

START HERE

How Excel Formulas Work

A formula starts with = and can contain functions, cell references, constants and operators. Excel recalculates formulas when referenced values change. citeturn477179search0turn477179search10

Microsoft documents Excel functions by category and notes that Excel has hundreds of built-in functions. This course therefore provides a broad practical Basic → Advanced library rather than claiming to reproduce every function in existence. citeturn477179search3turn477179search7

STEP 1

What is an Excel Formula?

Excel formulas calculate values or perform actions in worksheet cells. Every formula starts with =.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 2

Cell References

Use cell references instead of hard-coded values so a formula updates when the source cells change.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 3

Range References

A range such as A1:A10 lets a formula work across multiple cells.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 4

Relative References

Relative references change when a formula is copied to another cell.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 5

Absolute References

Use $ to lock a row, column, or both when copying formulas.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 6

Mixed References

Lock only the row or column when that is what the formula design requires.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 7

Arithmetic Operators

Use +, -, *, / and ^ for calculations.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 8

SUM and AVERAGE

SUM totals values; AVERAGE returns the arithmetic mean.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 9

MIN, MAX and MEDIAN

Find the smallest, largest, and middle value of a dataset.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 10

COUNT Family

COUNT counts numbers, COUNTA counts non-empty cells, and COUNTBLANK counts blank cells.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 11

ROUND Family

ROUND, ROUNDUP, and ROUNDDOWN control numeric rounding.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 12

ABS, MOD and QUOTIENT

Use ABS for absolute value, MOD for a remainder, and QUOTIENT for integer division.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 13

SUMIF and SUMIFS

Add values that meet one or multiple conditions.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 14

COUNTIF and COUNTIFS

Count values that meet one or multiple criteria.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 15

AVERAGEIF and AVERAGEIFS

Average values meeting one or multiple conditions.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 16

IF

IF returns one value when a condition is TRUE and another when it is FALSE.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 17

IFS and SWITCH

IFS handles ordered conditions; SWITCH is useful when matching an expression to cases.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 18

AND, OR and NOT

Combine logical tests with AND, OR, and NOT.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 19

IFERROR and IFNA

Replace error results with a controlled message or value.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 20

Text Cleaning

TRIM removes extra spaces; CLEAN removes many non-printing characters.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 21

Text Case

UPPER, LOWER, and PROPER adjust text case.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 22

LEFT, RIGHT and MID

Extract text from the left, right, or middle of a cell.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 23

FIND and SEARCH

FIND is case-sensitive; SEARCH is generally case-insensitive.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 24

CONCAT and TEXTJOIN

Combine text from cells and ranges.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 25

SUBSTITUTE and REPLACE

Replace matching text or characters at specific positions.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 26

TEXT Formatting

Convert values into formatted text.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 27

TODAY, NOW and DATE

Work with current dates/times and construct dates.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 28

YEAR, MONTH, DAY and WEEKDAY

Extract components from dates.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 29

EDATE and EOMONTH

Move dates by months or calculate month-end dates.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 30

NETWORKDAYS and WORKDAY

Calculate workdays and workday-based dates.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 31

XLOOKUP

XLOOKUP searches a range/array and returns a corresponding value. Microsoft documents exact matching as the default.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 32

VLOOKUP and HLOOKUP

Traditional lookup functions search a table vertically or horizontally.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 33

INDEX and MATCH

Combine INDEX and MATCH for flexible lookup patterns.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 34

XMATCH and CHOOSE

XMATCH returns a match position; CHOOSE returns a value by index.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 35

FILTER

FILTER returns rows that meet one or more criteria and spills the result into neighboring cells.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 36

SORT, SORTBY and UNIQUE

Sort or deduplicate dynamic arrays.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 37

SEQUENCE, TAKE and DROP

Generate sequences and select or remove rows/columns from arrays.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 38

LET

LET assigns names to intermediate results, improving readability and potentially avoiding repeated calculation.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 39

LAMBDA

LAMBDA creates reusable custom functions without VBA or JavaScript.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 40

BYROW, BYCOL, MAP, REDUCE and SCAN

Modern array helpers can calculate per row/column, transform arrays, or accumulate values.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 41

PMT and FV

Financial functions can calculate loan payments and future values.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 42

PV, NPV, IRR and XIRR

Evaluate present value and investment returns.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 43

Statistical Functions

Use STDEV, VAR, PERCENTILE, and RANK to summarize and compare data.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 44

Error and Information Functions

Test cell content and errors with ISBLANK, ISNUMBER, ISTEXT, ISERROR, and ISNA.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 45

SUMPRODUCT

SUMPRODUCT multiplies corresponding array values and then sums the products; it is useful for weighted totals.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 46

Dynamic Array Combination

Combine FILTER, SORT, UNIQUE and other array functions to build dynamic reports.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 47

Lookup + Error Handling

Combine XLOOKUP with IFNA/IFERROR for user-friendly lookups.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 48

LET + Multiple Calculations

Use LET when the same expression appears several times.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 49

LAMBDA Custom Tax Function

Build reusable custom business logic.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 50

Mini Project: Student Result

Combine IF, AVERAGE, COUNTIF, XLOOKUP, and TEXT to build a small student report.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 51

Mini Project: Sales Dashboard

Combine SUMIFS, COUNTIFS, XLOOKUP, FILTER, SORT, and TEXT for an interactive sales summary.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 52

Mini Project: Loan Calculator

Combine PMT, IF, ROUND, and TEXT for a learner-friendly EMI/payment worksheet.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 53

Mini Project: Attendance

Use COUNTIF/COUNTIFS and IF to calculate attendance status.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

STEP 54

Final Formula Challenge

Build a formula from smaller formulas. Break complex tasks into named steps with LET.

Formula / Example

Ready.

Result / Explanation — Let’s Understand It

REFERENCE

Formula Library

Search the practical formula library below. Availability varies by Excel version; modern functions are marked in the course lessons.

Category	Function	Purpose
Math & Basic	`SUM`	Adds numbers.
Math & Basic	`SUMIF`	Sums with one condition.
Math & Basic	`SUMIFS`	Sums with multiple conditions.
Math & Basic	`AVERAGE`	Arithmetic mean.
Math & Basic	`AVERAGEIF`	Conditional average.
Math & Basic	`AVERAGEIFS`	Multi-condition average.
Math & Basic	`MIN`	Smallest value.
Math & Basic	`MAX`	Largest value.
Math & Basic	`MEDIAN`	Middle value.
Math & Basic	`COUNT`	Counts numeric cells.
Math & Basic	`COUNTA`	Counts non-empty cells.
Math & Basic	`COUNTBLANK`	Counts blanks.
Math & Basic	`COUNTIF`	Counts one condition.
Math & Basic	`COUNTIFS`	Counts multiple conditions.
Math & Basic	`ROUND`	Rounds to a specified number of digits.
Math & Basic	`ROUNDUP`	Rounds away from zero.
Math & Basic	`ROUNDDOWN`	Rounds toward zero.
Math & Basic	`INT`	Rounds down to integer.
Math & Basic	`ABS`	Absolute value.
Math & Basic	`MOD`	Remainder after division.
Math & Basic	`QUOTIENT`	Integer portion of division.
Math & Basic	`PRODUCT`	Multiplies values.
Math & Basic	`SUMPRODUCT`	Weighted/multi-array multiplication and sum.
Math & Basic	`POWER`	Raises to a power.
Math & Basic	`SQRT`	Square root.
Math & Basic	`RAND`	Random decimal.
Math & Basic	`RANDBETWEEN`	Random integer.
Logical	`IF`	Conditional result.
Logical	`IFS`	Multiple ordered conditions.
Logical	`AND`	TRUE if all conditions are true.
Logical	`OR`	TRUE if any condition is true.
Logical	`NOT`	Reverses a logical value.
Logical	`IFERROR`	Replacement for any error.
Logical	`IFNA`	Replacement for #N/A.
Logical	`SWITCH`	Matches an expression to cases.
Text	`LEFT`	Text from left.
Text	`RIGHT`	Text from right.
Text	`MID`	Text from middle.
Text	`LEN`	Character count.
Text	`TRIM`	Removes extra spaces.
Text	`CLEAN`	Removes non-printing characters.
Text	`UPPER`	Uppercase.
Text	`LOWER`	Lowercase.
Text	`PROPER`	Capitalizes words.
Text	`CONCAT`	Combines text.
Text	`TEXTJOIN`	Combines text with delimiter.
Text	`TEXT`	Formats value as text.
Text	`SUBSTITUTE`	Replaces matching text.
Text	`REPLACE`	Replaces by position.
Text	`FIND`	Case-sensitive search.
Text	`SEARCH`	Case-insensitive search.
Text	`EXACT`	Case-sensitive comparison.
Text	`CHAR`	Character from code.
Text	`CODE`	Code of first character.
Date & Time	`TODAY`	Current date.
Date & Time	`NOW`	Current date/time.
Date & Time	`DATE`	Creates date.
Date & Time	`YEAR`	Extracts year.
Date & Time	`MONTH`	Extracts month.
Date & Time	`DAY`	Extracts day.
Date & Time	`WEEKDAY`	Day-of-week number.
Date & Time	`WEEKNUM`	Week number.
Date & Time	`EDATE`	Moves by months.
Date & Time	`EOMONTH`	Month-end date.
Date & Time	`DATEDIF`	Date difference.
Date & Time	`NETWORKDAYS`	Working days.
Date & Time	`WORKDAY`	Date after working days.
Lookup & Reference	`XLOOKUP`	Modern lookup.
Lookup & Reference	`VLOOKUP`	Vertical lookup.
Lookup & Reference	`HLOOKUP`	Horizontal lookup.
Lookup & Reference	`INDEX`	Returns value by position.
Lookup & Reference	`MATCH`	Returns match position.
Lookup & Reference	`XMATCH`	Modern match.
Lookup & Reference	`CHOOSE`	Value by index.
Lookup & Reference	`OFFSET`	Shifted reference.
Lookup & Reference	`INDIRECT`	Text-to-reference.
Dynamic Arrays	`FILTER`	Returns rows meeting criteria.
Dynamic Arrays	`SORT`	Sorts an array.
Dynamic Arrays	`SORTBY`	Sorts by another array.
Dynamic Arrays	`UNIQUE`	Returns unique values.
Dynamic Arrays	`SEQUENCE`	Generates sequences.
Dynamic Arrays	`TRANSPOSE`	Switches rows and columns.
Dynamic Arrays	`TAKE`	Returns selected leading/trailing rows/columns.
Dynamic Arrays	`DROP`	Drops leading/trailing rows/columns.
Dynamic Arrays	`CHOOSECOLS`	Selects columns.
Dynamic Arrays	`CHOOSEROWS`	Selects rows.
Dynamic Arrays	`TOCOL`	Flattens to a column.
Dynamic Arrays	`TOROW`	Flattens to a row.
Advanced	`LET`	Names intermediate calculations.
Advanced	`LAMBDA`	Creates reusable custom functions.
Advanced	`BYROW`	Calculates per row.
Advanced	`BYCOL`	Calculates per column.
Advanced	`MAP`	Transforms array items.
Advanced	`REDUCE`	Accumulates to one result.
Advanced	`SCAN`	Returns intermediate accumulations.
Financial	`PMT`	Periodic payment.
Financial	`FV`	Future value.
Financial	`PV`	Present value.
Financial	`NPV`	Net present value.
Financial	`IRR`	Internal rate of return.
Financial	`XIRR`	IRR with irregular dates.
Statistical	`STDEV.S`	Sample standard deviation.
Statistical	`STDEV.P`	Population standard deviation.
Statistical	`VAR.S`	Sample variance.
Statistical	`PERCENTILE.INC`	Inclusive percentile.
Statistical	`RANK.EQ`	Ranks a value.
Information & Errors	`ISBLANK`	Tests blank.
Information & Errors	`ISNUMBER`	Tests numeric type.
Information & Errors	`ISTEXT`	Tests text type.
Information & Errors	`ISERROR`	Tests error.
Information & Errors	`ISNA`	Tests #N/A.
Database	`DSUM`	Database conditional sum.
Database	`DCOUNT`	Database conditional count.
Engineering	`CONVERT`	Unit conversion.
Engineering	`DEC2BIN`	Decimal to binary.
Engineering	`HEX2DEC`	Hexadecimal to decimal.

FINAL QUIZ

Excel Formula Knowledge Check

Question 1

What character begins an Excel formula?

Question 2

Which function is the modern lookup example in this course?

Question 3

Which function returns unique values?

Question 4

Which function can create a reusable custom Excel function?

Question 5

Which function can name intermediate calculations inside one formula?

Choose an answer to start the score.

OFFICIAL REFERENCES

Microsoft Excel Formula Documentation

Excel functions by category

Excel functions alphabetical reference

Overview of formulas in Excel

XLOOKUP function

LET function

LAMBDA function

PROTECTION NOTE

Protected Learning Mode

This page blocks common right-click, selection, copy, print, save-shortcut and drag actions as browser-side deterrence while keeping formula editors and Run buttons usable.

A standalone HTML file cannot provide absolute DRM because the browser must receive the page and its source to display it.