• A
• B
• C
• D
• E
• F
• G
• H
• I
• J
• K
• L
• M
• N
• O
• P
• Q
• R
• S
• T
• U
• V
• W
• X
• Y
• Z

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.

Course Excel 2007: Advanced Formulas and Functions Author Dennis Taylor SKU ISBN Release Date 2009-11-25 Duration 5.17 hrs / 61 tutorials Work files Yes Price $ 19.95 Retail price $ 49.95 Language Tags Excel

1. Formula and Function Tips/Shortcuts Using the entire row/column references 04:01 Copying column formulas instantly 04:56 Converting formulas to values with a simple drag 04:39 Creating 3D formulas to gather data from multiple sheets 06:06 Updating values without formulas 03:34 Showing all formulas with a single command 04:52 Highlighting formulas with two clicks 04:25 Simplifying debugging formulas 05:36 Creating range names to enhance readability 06:20 2. Excel Functions Understanding and using basic Excel functions 09:43 Using common functions: SUM, AVERAGE, MAX, and MIN 05:58 Using and extending AutoSum 05:19 3. IF and Related Functions Exploring IF logical tests and using relational operators 05:46 Creating and expanding the use of nested IFs 05:43 Using the AND, OR, and NOT functions with IF to create compound logical tests 06:54 4. Lookup and Reference Functions Looking up information with VLOOKUP and HLOOKUP 05:56 Using VLOOKUP for approximate matches 07:38 Using VLOOKUP for exact matches 04:59 Nesting Lookup functions 03:28 Finding table-like information within a function with CHOOSE 04:02 Locating data with MATCH 05:35 Retrieving information by location with INDEX 02:33 Using MATCH and INDEX together 06:37 5. Power Functions Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF 06:14 Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 06:18 6. Statistical Functions Finding the middle value with MEDIAN 02:54 Ranking data without sorting with RANK 04:51 Finding the magnitude data with LARGE and SMALL 02:11 Tabulating blank cells with COUNTBLANK 03:16 7. Date Functions Understanding Excel date/time capabilities in formulas 10:36

Identifying the day of the week with WEEKDAY 04:16 Counting working days with NETWORKDAYS 03:12 Determining a completion date with WORKDAY 04:51 Tabulating date/time differences with DATEDIF 02:21 8. Math Functions Working with rounding functions 11:30 Finding the remainder with MOD 04:19 Building random number generators with RAND and RANDBETWEEN 05:52 Converting a value between measurement systems with CONVERT 03:59 9. Array Formulas and Functions Extending formula capabilities with Arrays 07:40 Counting unique entries in a range with an Array formula 04:44 Determining frequency distributions with FREQUENCY 03:39 Flipping row/column orientation with TRANSPOSE 03:28 Building analysis via regression techniques with TREND and GROWTH 02:38 Combining multiple functions in arrays 05:39 10. Text Functions Locating and extracting data with FIND and MID 07:49 Extracting specific data with LEFT and RIGHT 03:12 Removing excess spaces with TRIM 04:33 Using CONCATENATE with functions 04:59 Adjusting case within cells with PROPER, UPPER, and LOWER 03:48 Adjusting character content with REPLACE and SUBSTITUTE 05:51 Reviewing additional text functions 08:52 11. Financial Functions Calculating payments with PMT 06:52 Finding future values with FV 05:25 Determining total amount of future payments with PV 02:46 12. Information Functions Working with the IS information functions 05:40 Using error-checking functions: ISERR, ISERROR, IFERROR 08:14 Getting data from remote cells with OFFSET 05:50 Returning references with INDIRECT 10:54 Conclusion Goodbye 00:39