Overview
-
Demystifies some of the most useful and challenging of the 300+ formulas and functions in Excel and shows how to put them to their best use.
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
- Showing all formulas with a single command
- Highlighting formulas with two clicks
- Simplifying debugging formulas
- Creating range names to enhance readability
- Understanding and using basic Excel functions
- Using common functions: SUM, AVERAGE, MAX, and MIN
- Using and extending AutoSum
- Exploring IF logical tests and using relational operators
- Creating and expanding the use of nested IFs
- Using the AND, OR, and NOT functions with IF to create compound logical tests
- Looking up information with VLOOKUP and HLOOKUP
- Using VLOOKUP for approximate matches
- Using VLOOKUP for exact matches
- 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
- Building random number generators with RAND and RANDBETWEEN
- Converting a value between measurement systems with CONVERT
- 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
- Goodbye
