Overview
-
Provides formula examples and demystifies some of the nearly 400 functions in Excel.
Syllabus
-
Introduction
- Welcome
- Using the exercise files
- Using the entire row/column references
- Copying column formulas instantly
- Converting formulas to values with a simple drag
- Creating 3D formulas to gather data from multiple sheets
- Updating values without formulas
- Displaying and highlighting formulas
- Simplifying debugging formulas
- Enhancing readability with range names
- Reviewing function basics
- Using and extending AutoSum
- Using absolute and relative references
- Using mixed references
- Exploring IF logical tests and using relational operators
- Creating and expanding the use of nested IF statements
- Using the AND, OR, and NOT functions with IF to create compound logical tests
- Looking up information with VLOOKUP and HLOOKUP
- Finding approximate matches with VLOOKUP
- Finding exact matches with VLOOKUP
- Nesting LOOKUP functions
- Finding table-like information within a function with CHOOSE
- Locating data with MATCH
- Retrieving information by location with INDEX
- Using MATCH and INDEX together
- Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
- Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding the middle value with MEDIAN
- Ranking data without sorting with RANK
- Finding the magnitude data with LARGE and SMALL
- Tabulating blank cells with COUNTBLANK
- Understanding Excel date/time capabilities in formulas
- Identifying the day of the week with WEEKDAY
- Counting working days with NETWORKDAYS
- Determining a completion date with WORKDAY
- Tabulating date/time differences with DATEDIF
- Working with rounding functions
- Finding the remainder with MOD and using MOD with conditional formatting
- Building random number generators with RAND and RANDBETWEEN
- Converting a value between measurement systems with CONVERT
- Using the powerful new AGGREGATE function to bypass errors and hidden data
- Extending formula capabilities with arrays
- Counting unique entries in a range with an array formula
- Determining frequency distributions with FREQUENCY
- Flipping row/column orientation with TRANSPOSE
- Building analysis via regression techniques with TREND and GROWTH
- Combining multiple functions in arrays
- Locating and extracting data with FIND and MID
- Extracting specific data with LEFT and RIGHT
- Removing excess spaces with TRIM
- Using CONCATENATE with functions
- Adjusting case within cells with PROPER, UPPER, and LOWER
- Adjusting character content with REPLACE and SUBSTITUTE
- Reviewing additional text functions
- Calculating payments with PMT
- Finding future values with FV
- Determining total amount of future payments with PV
- Working with the IS information functions
- Using error-checking functions: ISERR, ISERROR, IFERROR
- Getting data from remote cells with OFFSET
- Returning references with INDIRECT
- Final thoughts
