In this series of Excel tutorials, discover some of the most challenging of the 300+ formulas and functions in Excel, and learn about how to put them to their best use.
Overview
Syllabus
Introduction
- Welcome
- Using the exercise files
- Displaying and highlighting formulas
- Auditing tools
- Using entire row/column references
- Copying column formulas instantly
- Converting formulas to values with a simple drag
- Updating values without formulas
- Simplifying debugging formulas
- Enhancing readability with range names
- Creating 3D formulas to gather data from multiple sheets
- Understanding the hierarchy of operations in Excel formulas
- Using the Formulas tab on the Ribbon for locating functions
- Using the Insert Function button for guidance with unfamiliar functions
- Using and extending AutoSum button capabilities
- Using absolute and relative references in formulas
- Using mixed references in formulas
- Exploring IF logical tests and using relational operators
- Creating and expanding the use of nested IF statements
- Using the AND and OR 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
- Using VLOOKUP with large tables
- 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 using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
- Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding the middle value with MEDIAN
- Ranking data without sorting with RANK
- Finding the largest and smallest values with LARGE and SMALL
- Tabulating blank cells with COUNTBLANK
- Using COUNT, COUNTA, and the status bar
- Working with ROUND, ROUNDUP, and ROUNDDOWN
- Working with MROUND, CEILING, and FLOOR for specialized rounding
- Using the INT and TRUNC functions to extract integer data
- Finding the remainder with MOD and using MOD with conditional formatting
- Practical uses for the random number functions RAND and RANDBETWEEN
- Converting a value between measurement systems with CONVERT
- Using the powerful AGGREGATE function to bypass errors and hidden data
- Using the ROMAN and ARABIC functions to display different numeral systems
- Understanding Excel date/time capabilities in formulas
- Using TODAY and NOW functions for dynamic date/time entry
- Identifying the day of the week with WEEKDAY
- Counting working days with NETWORKDAYS
- Determining a completion date with WORKDAY
- Tabulating date differences with DATEDIF
- Calculating end-of-month and future/past dates with EDATE and EOMONTH
- Converting text entries into dates and times with DATEVALUE and TIMEVALUE
- 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
- Using array formula techniques with the MATCH function for complex lookups
- Getting data from remote cells with OFFSET
- Returning references with INDIRECT
- Using INDIRECT with data validation for two-tiered pick list scenarios
- Locating and extracting data with FIND, SEARCH, and MID
- Extracting specific data with LEFT and RIGHT
- Removing extra spaces with TRIM and removing hidden characters with CLEAN
- Using ampersands and CONCATENATE to combine data from different cells
- Adjusting the case within cells with PROPER, UPPER, and LOWER
- Adjusting character content with REPLACE and SUBSTITUTE
- Using other utility text functions: LEN, REPT, VALUE, TEXT
- Extracting information with the CELL and INFO functions
- Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER
- Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA
- Using the ISFORMULA function with conditional formatting
- Goodbye
Taught by
Dennis Taylor
