Excel 2007: Advanced Formulas and Functions
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.
In Excel 2007: Advanced Formulas and Functions, author and trainer Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel and shows how to put them to their best use. Dennis starts with a review of the more basic, building-block functions, and a few critical keyboard shortcuts that will speed up working with Excel data, even on multiple sheets. He then covers how to perform advanced searching and data retrieval with Lookup functions, tabulate and sort data with counting and statistical functions, format data with text and math functions, and even work with financial data using advanced formulas. Dennis focuses on practical examples that will help users easily transition to using these formulas and functions in real-world scenarios. Exercise files accompany this course.
In Excel 2007: Advanced Formulas and Functions, author and trainer Dennis Taylor demystifies some of the most challenging of the 300+ formulas and functions in Excel and shows how to put them to their best use. Dennis starts with a review of the more basic, building-block functions, and a few critical keyboard shortcuts that will speed up working with Excel data, even on multiple sheets. He then covers how to perform advanced searching and data retrieval with Lookup functions, tabulate and sort data with counting and statistical functions, format data with text and math functions, and even work with financial data using advanced formulas. Dennis focuses on practical examples that will help users easily transition to using these formulas and functions in real-world scenarios. Exercise files accompany this course.
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
Taught by
Dennis Taylor
Never Stop Learning!
