Excel 2010: Advanced Formulas and Functions – Dennis Taylor

Contents

Introduction – 01:49

• Welcome 1:25
• Using the exercise files 0:24

1. Formula and Function Tips and Shortcuts – 47:12

• Using the entire row/column references 5:43
• Copying column formulas instantly 4:35
• Converting formulas to values with a simple drag 5:10
• Creating 3D formulas to gather data from multiple sheets 5:17
• Updating values without formulas 4:25
• Displaying and highlighting formulas 7:15
• Simplifying debugging formulas 6:29
• Enhancing readability with range names 8:18

2. Formula and Function Tools – 22:32

• Reviewing function basics 7:12 29.1 MB
• Using and extending AutoSum 4:55 10.3 MB
• Using absolute and relative references 4:52 9.0 MB
• Using mixed references 5:33 10.8 MB

3. IF and Related Functions – 16:39

• Exploring IF logical tests and using relational operators 4:43
• Creating and expanding the use of nested IF statements 4:47
• Using the AND, OR, and NOT functions with IF to create compound logical tests 7:09

4. Lookup and Reference Functions – 41:01

• Looking up information with VLOOKUP and HLOOKUP 4:27
• Finding approximate matches with VLOOKUP 6:22
• Finding exact matches with VLOOKUP 4:21
• Nesting LOOKUP functions 5:51
• Finding table-like information within a function with CHOOSE 3:51
• Locating data with MATCH 5:03
• Retrieving information by location with INDEX 4:51
• Using MATCH and INDEX together 6:15

5. Power Functions – 12:27

• Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF 4:49
• Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 7:38 15.0 MB

6. Statistical Functions – 11:35

• Finding the middle value with MEDIAN 1:35
• Ranking data without sorting with RANK 4:50
• Finding the magnitude data with LARGE and SMALL 1:46
• Tabulating blank cells with COUNTBLANK 3:24

7. Data Functions – 23:27

• Understanding Excel date/time capabilities in formulas 7:18
• Identifying the day of the week with WEEKDAY 6:02
• Counting working days with NETWORKDAYS 3:05
• Determining a completion date with WORKDAY 2:24
• Tabulating date/time differences with DATEDIF 4:38

8. Math Functions – 25:36

• Working with rounding functions 8:03
• Finding the remainder with MOD and using MOD with conditional formatting 4:42
• Building random number generators with RAND and RANDBETWEEN 4:42
• Converting a value between measurement systems with CONVERT 3:07
• Using the powerful new AGGREGATE function to bypass errors and hidden data 5:02

9. Array Formulas and Functions – 27:16

• Extending formula capabilities with arrays 7:12
• Counting unique entries in a range with an array formula 4:46
• Determining frequency distributions with FREQUENCY 3:06
• Flipping row/column orientation with TRANSPOSE 4:38
• Building analysis via regression techniques with TREND and GROWTH 3:07
• Combining multiple functions in arrays 4:27

10. Text Functions – 36:11

• Locating and extracting data with FIND and MID 6:18
• Extracting specific data with LEFT and RIGHT 3:02
• Removing excess spaces with TRIM 5:42
• Using CONCATENATE with functions 4:24
• Adjusting case within cells with PROPER, UPPER, and LOWER 4:17
• Adjusting character content with REPLACE and SUBSTITUTE 5:56
• Reviewing additional text functions 6:32

11. Financial Functions – 13:30

• Calculating payments with PMT 4:34
• Finding future values with FV 4:03
• Determining total amount of future payments with PV 4:53

12. Information Functions – 11:13

• Working with the IS information functions 4:42
• Using error-checking functions: ISERR, ISERROR, IFERROR 6:31

13. Reference Functions – 14:29

• Getting data from remote cells with OFFSET 4:46
• Returning references with INDIRECT 9:43

Conclusion – 00:41

• Final thoughts

Author:

Dennis Taylor is an Excel expert who has 25+ years of experience in spreadsheet authoring and training.

Dennis has experience working as an author, speaker, seminar leader, and facilitator. Since the mid-90s, he has been the author/presenter of numerous Excel video and online courses and has traveled throughout the U.S. and Canada presenting over 300 seminars and classes. He has authored or co-authored multiple books on spreadsheet software and has presented over 500 Excel webinars to a diversity of audiences. Dennis has worked with hundreds of different corporations and governmental agencies as well as colleges and universities. He lives in Boulder, Colorado.