Excel Formulas & Functions – Full Course
Summary
This course, taught by a former Amazon software engineer, provides a comprehensive guide to mastering Excel formulas and functions. It emphasizes practical application through interactive, browser-based exercises, covering core concepts from basic operators and error handling to advanced topics like dynamic arrays, logical operations, and lookup functions such as XLOOKUP. The curriculum is designed to build a strong understanding through a blend of video lessons, quizzes, and hands-on problem-solving, leading to genuine Excel mastery.
Key Insights
Excel evaluates formulas using the order of operations (PEMDAS/BODMAS).
Excel follows a specific order for calculations: Parentheses, Exponents, Multiplication and Division (left-to-right), Addition and Subtraction (left-to-right). This ensures consistent results even in complex formulas.
Nesting involves placing one expression or function inside another.
Excel evaluates nested formulas from the inside out. The inner expressions must resolve to a value before the outer function or expression can complete its calculation. Understanding this process is key to reading and debugging complex formulas.
Absolute referencing locks a cell reference to prevent it from changing when copied.
Using dollar signs ($) before the column letter and/or row number (e.g., $I$2) creates an absolute reference. This ensures the formula always points to the exact same cell, regardless of where it is copied.
Mixed referencing locks either the column or the row, but not both.
Mixed references (e.g., $C12 or C$12) allow one part of the reference to change while the other remains fixed. This is useful when a formula needs to align with a specific column but vary by row, or vice-versa.
Optional arguments must maintain their position even if skipped.
When using functions with multiple optional arguments, you cannot skip an earlier optional argument to set a later one. You must include placeholder commas for any omitted optional arguments in their correct sequence.
IFERROR catches any Excel error and returns a specified value.
The IFERROR function takes two arguments: the value to check and the value to return if the first argument is an error. It's a catch-all for any error type, useful for displaying a safe fallback.
IFNA specifically handles #N/A errors, preserving other error types.
IFNA is used when you only want to handle cases where a lookup or search value is not found. It takes the same pattern as IFERROR but only acts on #N/A errors, allowing other errors to surface for debugging.
IFS function handles multiple conditions sequentially.
IFS evaluates conditions in order and returns the value corresponding to the first TRUE condition encountered. IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...). A final TRUE condition acts as a catch-all.
SUMIFS sums values from a specified range where conditions across other ranges are met.
Requires the sum range as the first argument, followed by pairs of criteria ranges and criteria. It sums only the numbers in the sum range for rows meeting all specified conditions.
Criteria syntax includes exact text, numbers, operators, and wildcards.
Criteria can be exact text (in quotes), numbers (without quotes), logical operators combined with cell references (e.g., '>='&L4), or wildcards (* for any characters, ? for single characters).
XLOOKUP starts with a known value and returns a related value from another column.
It requires a lookup value, a lookup array (where to search), and a return array (what to return). It can search left or right and is more flexible than older lookup functions.
Match mode controls how XLOOKUP defines a match (exact, next smaller/larger, wildcard).
Match mode 0 (default) is exact match. -1 is exact or next smaller. 1 is exact or next larger. 2 is wildcard match. These modes allow for flexible searching beyond exact values.
INDEX and MATCH provide a powerful two-way lookup capability.
MATCH finds the relative position of a lookup value within an array. INDEX returns a value from an array at a specified row and column number. Together, they enable complex lookups.
Use EXACT for case-sensitive comparisons, '=' for case-insensitive.
The equals operator ('=') ignores case when comparing text, while the EXACT function performs a case-sensitive comparison.
Combine FIND/SEARCH with LEFT/RIGHT/MID for complex extraction.
A common pattern is to use FIND or SEARCH to locate a delimiter (like '@' in an email), then use LEFT/RIGHT/MID with LEN to extract specific parts of the text.
Dynamic array functions return entire ranges from a single formula.
Functions like FILTER, SORT, SORTBY, and UNIQUE spill results into adjacent cells, automatically adjusting the output size as source data changes.
FILTER returns rows or columns that meet specified criteria.
FILTER(array, include, [if_empty]) keeps data that meets the 'include' condition (a TRUE/FALSE array). It handles single or multiple conditions (using multiplication for AND logic).
SORTBY sorts a range based on corresponding values in another range.
SORTBY(array, sort_by1, [sort_order1], ... ) sorts the primary 'array' based on one or more 'sort_by' arrays. It's powerful for multi-criteria sorting and sorting by external data.
UNIQUE returns distinct values from a range.
UNIQUE(array, [by_col], [exactly_once]) extracts unique rows, columns, or values. Can find items appearing only once ('exactly_once'=TRUE) or just one instance of each distinct item.
Sections
Introduction to Formulas and Functions
Understand the distinction between formulas and functions in Excel.
A formula is any Excel expression starting with an equals sign that tells Excel what to calculate. A function is a built-in tool that can be used within a formula, like SUM or IF. For example, '=12+5' is a formula without a function, while '=SUM(2,5)' is a formula using a function.
Always start Excel formulas with an equal sign.
Beginning a formula with an equal sign ('=') tells Excel to perform a calculation. Omitting it causes Excel to treat the input as literal text rather than a mathematical expression.
Excel uses a variety of operators for calculations and comparisons.
Arithmetic operators include +, -, *, /, and ^ (exponent). Non-arithmetic operators include = (equals), > (greater than), < (less than), >= (greater than or equals to), <= (less than or equals to), and & (ampersand) for text concatenation.
Excel evaluates formulas using the order of operations (PEMDAS/BODMAS).
Excel follows a specific order for calculations: Parentheses, Exponents, Multiplication and Division (left-to-right), Addition and Subtraction (left-to-right). This ensures consistent results even in complex formulas.
Parentheses allow you to control the order of operations in formulas.
By enclosing parts of a formula in parentheses, you can force Excel to evaluate those specific calculations first, effectively carving out smaller sub-problems to solve before proceeding with the rest of the formula.
Nesting involves placing one expression or function inside another.
Excel evaluates nested formulas from the inside out. The inner expressions must resolve to a value before the outer function or expression can complete its calculation. Understanding this process is key to reading and debugging complex formulas.
Break down complex formulas by asking four key questions.
To understand any formula, ask: What are the inputs? Which rules apply first? What intermediate value should appear next? What final output should that lead to? This systematic approach aids in reading, writing, and debugging.
Cell Referencing: Relative, Absolute, and Mixed
Relative referencing allows formulas to adapt when copied.
By default, cell references are relative. When a formula is copied, Excel adjusts the references based on their position relative to the new cell. This is highly efficient for applying the same calculation across many rows or columns.
Absolute referencing locks a cell reference to prevent it from changing when copied.
Using dollar signs ($) before the column letter and/or row number (e.g., $I$2) creates an absolute reference. This ensures the formula always points to the exact same cell, regardless of where it is copied.
Mixed referencing locks either the column or the row, but not both.
Mixed references (e.g., $C12 or C$12) allow one part of the reference to change while the other remains fixed. This is useful when a formula needs to align with a specific column but vary by row, or vice-versa.
Choose the correct referencing type before copying formulas.
Decide which parts of a cell reference should move and which should stay anchored before copying formulas, then apply relative, absolute, or mixed references accordingly to ensure accurate calculations.
Understanding Excel Functions
Functions have a name and arguments enclosed in parentheses.
Every function has a name that specifies the action, followed by arguments (inputs) within parentheses, separated by commas if there are multiple. Example: ROUND(C4, 2), where ROUND is the name and C4, 2 are the arguments.
Arguments can be required or optional.
Required arguments are essential for the function to work. Optional arguments have default values if omitted. Optional arguments are often indicated in square brackets in function syntax help.
Formula builder ('FX') helps visualize and understand function arguments.
Clicking the 'FX' button next to the formula bar opens the Function Arguments dialog box. This clearly shows each argument, indicates which are optional, and provides a help link for detailed explanations.
Optional arguments must maintain their position even if skipped.
When using functions with multiple optional arguments, you cannot skip an earlier optional argument to set a later one. You must include placeholder commas for any omitted optional arguments in their correct sequence.
Some functions accept an indefinite number of arguments.
Functions like SUM can take a variable number of arguments. Excel indicates this capability in the syntax helper with three dots (...) after the last optional argument, signifying that more inputs of the same type can be added.
Functions without arguments, like TODAY(), still require parentheses.
Even functions that do not take any inputs require empty parentheses after the function name (e.g., =TODAY()). These are executed purely by their name to return a value.
Error Handling in Excel
Understand common Excel error types and their causes.
Key errors include #DIV/0! (division by zero), #VALUE! (wrong input type), #NAME? (unrecognized function or name), #REF! (broken reference), #N/A (value not found), and #NUM! (invalid numeric result).
IFERROR catches any Excel error and returns a specified value.
The IFERROR function takes two arguments: the value to check and the value to return if the first argument is an error. It's a catch-all for any error type, useful for displaying a safe fallback.
IFNA specifically handles #N/A errors, preserving other error types.
IFNA is used when you only want to handle cases where a lookup or search value is not found. It takes the same pattern as IFERROR but only acts on #N/A errors, allowing other errors to surface for debugging.
Choose error handling based on whether to hide all errors or specific ones.
Use IFERROR to replace any error with a consistent message, useful for user-facing reports. Use IFNA to specifically manage 'not found' scenarios in lookups, keeping other errors visible to identify underlying issues.
Errors can occur from formula issues or broken references.
Errors can stem from incorrect inputs, formulas that produce invalid results, or issues like deleting cells that are referenced by other formulas, leading to #REF! errors.
Logical Functions: IF, AND, OR, NOT, IFS
IF function performs a single true/false test to return one of two outcomes.
The IF function structure is IF(logical_test, value_if_true, value_if_false). It evaluates a condition and returns a specified value based on whether the condition is TRUE or FALSE.
AND function returns TRUE only if all its conditions are TRUE.
The AND function checks multiple logical tests and returns TRUE if every single test evaluates to TRUE. It's used when all criteria must be met for a certain outcome.
OR function returns TRUE if any of its conditions are TRUE.
The OR function checks multiple logical tests and returns TRUE if at least one test evaluates to TRUE. It's used when meeting any one of several criteria is sufficient.
NOT function reverses the logical state of its argument.
The NOT function takes a single logical value and returns its opposite: if the input is TRUE, it returns FALSE, and if the input is FALSE, it returns TRUE. It's used to invert a condition.
IFS function handles multiple conditions sequentially.
IFS evaluates conditions in order and returns the value corresponding to the first TRUE condition encountered. IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...). A final TRUE condition acts as a catch-all.
Nesting IF statements achieves multiple outcomes but is harder to read.
While nesting IF functions within the 'value_if_false' argument allows for multiple branching outcomes, the IFS function provides a cleaner and more readable alternative for handling numerous conditions.
Core Aggregation Functions
Aggregation functions summarize a range of data into a single value.
These functions include SUM, AVERAGE, MEDIAN, MODE, MIN, MAX, COUNT, COUNTA, and COUNTBLANK, turning raw data into quick insights like totals, averages, or counts.
SUM adds all numeric values, ignoring blanks and text.
The SUM function calculates the total of numbers within a specified range. Text and empty cells are disregarded in the calculation.
AVERAGE calculates the mean of numeric values, ignoring blanks and text.
AVERAGE sums numeric values and divides by the count of only those numeric entries. This makes it robust against missing or non-numeric data impacting the result.
MEDIAN returns the middle value in a sorted numeric dataset.
MEDIAN finds the central value of a set of numbers after they are sorted. It's less affected by extreme outliers than the average.
MODE identifies the most frequent value in a dataset.
MODE returns the number that appears most often in the range. If multiple values have the same highest frequency, it typically returns the first one encountered.
MIN and MAX find the smallest and largest numeric values in a range.
MIN and MAX are useful for establishing the range of data, identifying lowest and highest scores, sales, dates, or other numeric values.
COUNT counts only numeric values, ignoring blanks and text.
COUNT tallies the number of cells that contain numbers within the specified range.
COUNTA counts all non-empty cells, including text and numbers.
COUNTA tallies the total number of cells that contain any data, whether it's numbers, text, or error values, excluding only truly blank cells.
COUNTBLANK counts only empty cells within a range.
COUNTBLANK specifically tallies the number of cells that are completely empty.
Core problems combine multiple lesson concepts for practice.
The 'Score compared to median' problem requires using both aggregation (MEDIAN) and logical functions (IFS) with proper cell referencing to determine status based on score relative to the median.
Conditional Aggregation: SUMIFS, COUNTIFS, etc.
Conditional aggregation aggregates data based on defined criteria.
Functions like COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, and MINIFS allow you to summarize data only for rows that meet specific conditions, rather than the entire dataset.
COUNTIFS counts rows that meet all specified criteria.
Takes pairs of criteria ranges and criteria. It counts rows where the value in the first range matches the first criterion AND the value in the second range matches the second criterion, and so on.
SUMIFS sums values from a specified range where conditions across other ranges are met.
Requires the sum range as the first argument, followed by pairs of criteria ranges and criteria. It sums only the numbers in the sum range for rows meeting all specified conditions.
AVERAGEIFS, MAXIFS, MINIFS follow the SUMIFS pattern for their respective aggregations.
These functions begin with the range to average, find the maximum, or find the minimum, followed by the criteria range/criteria pairs. They perform conditional averaging, finding the maximum, or finding the minimum.
Older IF functions (SUMIF, COUNTIF) exist but IFS versions are preferred.
SUMIF, COUNTIF, and AVERAGEIF handle only a single criterion. While still functional and present in older files, the corresponding -IFS functions are more versatile and recommended for new formulas, as they handle single criteria too.
Criteria syntax includes exact text, numbers, operators, and wildcards.
Criteria can be exact text (in quotes), numbers (without quotes), logical operators combined with cell references (e.g., '>='&L4), or wildcards (* for any characters, ? for single characters).
Special criteria define blanks and non-blanks.
Criteria like '""' counts blank cells, while '<>""' counts non-blank cells. These are essential for analyzing data completeness.
Lookup Functions: XLOOKUP, INDEX/MATCH
XLOOKUP starts with a known value and returns a related value from another column.
It requires a lookup value, a lookup array (where to search), and a return array (what to return). It can search left or right and is more flexible than older lookup functions.
XLOOKUP can return multiple values or entire arrays at once.
By specifying a multi-cell range for the return array, XLOOKUP can spill multiple results into adjacent cells when a match is found.
The 'if not found' argument provides a fallback value.
This optional argument allows you to specify what XLOOKUP should return if no match is found, preventing #N/A errors and providing a user-friendly message or default value.
Match mode controls how XLOOKUP defines a match (exact, next smaller/larger, wildcard).
Match mode 0 (default) is exact match. -1 is exact or next smaller. 1 is exact or next larger. 2 is wildcard match. These modes allow for flexible searching beyond exact values.
Search mode controls the direction of the search (first-to-last or last-to-first).
Search mode 1 (default) searches from the first item to the last. -1 searches from the last item to the first. Binary modes (2 and -2) offer speed on sorted data but require careful setup.
INDEX and MATCH provide a powerful two-way lookup capability.
MATCH finds the relative position of a lookup value within an array. INDEX returns a value from an array at a specified row and column number. Together, they enable complex lookups.
XLOOKUP and XMATCH make older VLOOKUP, HLOOKUP, and MATCH obsolete.
XLOOKUP is more flexible (searches left/right, returns arrays, has fallback), and XMATCH offers better match and search options than the legacy functions. Use them for modern Excel development.
Text Normalization and Manipulation
Normalize text data to ensure consistency for formulas.
Functions like TRIM, LOWER, UPPER, and PROPER help clean up messy text by removing extra spaces, standardizing case, and formatting capitalization consistently.
TRIM removes leading, trailing, and excess internal spaces.
TRIM standardizes spacing in text, ensuring that comparisons and lookups aren't affected by invisible extra spaces.
LOWER, UPPER, and PROPER standardize text case.
LOWER converts text to all lowercase, UPPER to all uppercase, and PROPER capitalizes the first letter of each word. Useful for case-insensitive operations.
SUBSTITUTE replaces specific text within a string.
Use SUBSTITUTE when you know the exact text to find and replace. It can replace all occurrences or a specific instance.
REPLACE edits text based on character position, not content.
REPLACE substitutes a portion of a text string based on a starting position and number of characters to replace. Useful for standardizing codes by position.
TEXT converts values into text with custom formatting.
The TEXT function formats numbers or dates into specific text strings (e.g., 'mmmm d, yyyy'), useful for labels and exports, but the result is text, not a number or date.
Use EXACT for case-sensitive comparisons, '=' for case-insensitive.
The equals operator ('=') ignores case when comparing text, while the EXACT function performs a case-sensitive comparison.
Working with Text: Finding and Extracting
FIND and SEARCH locate the position of text within another string.
FIND is case-sensitive; SEARCH is case-insensitive. Both return the starting character position of the first occurrence. They have an optional 'start num' argument.
LEFT, RIGHT, and MID extract text based on position.
LEFT returns characters from the beginning of a string, RIGHT from the end, and MID from a specified starting position for a specific number of characters.
LEN counts the total number of characters in a text string.
The LEN function is crucial for creating adaptable formulas where the length of the text can vary, enabling dynamic extraction.
Combine FIND/SEARCH with LEFT/RIGHT/MID for complex extraction.
A common pattern is to use FIND or SEARCH to locate a delimiter (like '@' in an email), then use LEFT/RIGHT/MID with LEN to extract specific parts of the text.
Working with Dates in Excel
Excel stores dates as serial numbers, starting from January 1, 1900.
The underlying numeric value allows Excel to perform date arithmetic and comparisons. Formatting only changes how the number is displayed as a calendar date.
DATE function constructs a valid Excel date from year, month, and day components.
DATE(year, month, day) creates a date serial number. It safely handles input normalization (e.g., month 13 rolls into the next year).
YEAR, MONTH, DAY functions extract components from a date serial number.
These functions take a date serial number and return its year, month, or day component as a number.
WEEKDAY identifies the day of the week for a given date.
WEEKDAY(date, [return_type]) returns a number from 1-7 representing the day. Return type 2 is common for business use (Monday=1, Sunday=7).
WORKDAY calculates a date after a specified number of business days.
WORKDAY(start_date, days, [holidays]) moves forward or backward by business days, excluding weekends and optional holidays.
NETWORKDAYS calculates the number of business days between two dates.
NETWORKDAYS(start_date, end_date, [holidays]) counts the total business days (inclusive of start and end dates if they are business days), excluding weekends and holidays.
EOMONTH finds the end of the month for a given date and offset.
EOMONTH(start_date, months) returns the last day of the month that is a specified number of months before or after the start date.
EDATE calculates a date a specified number of months away, preserving the day.
EDATE(start_date, months) shifts the start date forward or backward by whole months. If the target month lacks the original day, it returns the last day of that month.
Dynamic Array Functions: FILTER, SORT, UNIQUE
Dynamic array functions return entire ranges from a single formula.
Functions like FILTER, SORT, SORTBY, and UNIQUE spill results into adjacent cells, automatically adjusting the output size as source data changes.
FILTER returns rows or columns that meet specified criteria.
FILTER(array, include, [if_empty]) keeps data that meets the 'include' condition (a TRUE/FALSE array). It handles single or multiple conditions (using multiplication for AND logic).
SORT arranges a range based on one or more columns/rows.
SORT(array, sort_index, sort_order, [by_column]) sorts the specified array by a given column or row index in ascending (1) or descending (-1) order. Can sort rows or columns.
SORTBY sorts a range based on corresponding values in another range.
SORTBY(array, sort_by1, [sort_order1], ... ) sorts the primary 'array' based on one or more 'sort_by' arrays. It's powerful for multi-criteria sorting and sorting by external data.
UNIQUE returns distinct values from a range.
UNIQUE(array, [by_col], [exactly_once]) extracts unique rows, columns, or values. Can find items appearing only once ('exactly_once'=TRUE) or just one instance of each distinct item.
Use the '#' suffix to reference entire spill ranges.
When referring to a spill range in another formula, append '#' to the anchor cell's reference (e.g., B17#) to include all cells that the dynamic array function occupies.
Spill errors occur when output space is blocked by existing data.
If cells below or to the right of a dynamic array formula's anchor cell are occupied, Excel returns a #SPILL! error, indicating it cannot place the results.
Ask a Question
*Uses 1 Wisdom coin from your coin balance
![Learn C Programming and OOP with Dr. Chuck [feat. classic book by Kernighan and Ritchie]](https://i.ytimg.com/vi/PaPN51Mm5qQ/hqdefault.jpg)
