Introduction 1m 23s
Welcome 58s
Using the exercise files 25s
1. Formula and Function Tips and Shortcuts 37m 48s
- Displaying and highlighting formulas 5m 10s
- Auditing tools 4m 38s
- Using entire row/column references 3m 43s
- Copying column formulas instantly 3m 11s
- Converting formulas to values with a simple drag 3m 32s
- Updating values without formulas 2m 55s
- Simplifying debugging formulas 3m 51s
- Enhancing readability with range names 5m 50s
- Creating 3D formulas to gather data from multiple sheets 4m 58s
2. Formula and Function Tools 24m 12s
- Understanding the hierarchy of operations in Excel formulas 3m 19s
- Using the Formulas tab on the Ribbon for locating functions 3m 14s
- Using the Insert Function button for guidance with unfamiliar functions 4m 36s
- Using and extending AutoSum button capabilities 5m 6s
- Using absolute and relative references in formulas 3m 30s
- Using mixed references in formulas 4m 27s
3. IF and Related Functions 15m 42s
- Exploring IF logical tests and using relational operators 3m 33s
- Creating and expanding the use of nested IF statements 4m 22s
- Using the AND and OR functions with IF to create compound logical tests 7m 47s
4. Lookup and Reference Functions 41m 58s
- Looking up information with VLOOKUP and HLOOKUP 3m 57s
- Finding approximate matches with VLOOKUP 7m 27s
- Finding exact matches with VLOOKUP 4m 47s
- Nesting lookup functions 4m 11s
- Using VLOOKUP with large tables 3m 23s
- Finding table-like information within a function with CHOOSE 3m 34s
- Locating data with MATCH 4m 35s
- Retrieving information by location with INDEX 2m 46s
- Using MATCH and INDEX together 7m 18s
5. Power Functions 10m 22s
- Tabulating information using a single criterion with COUNTIF, SUMIF, and AVERAGEIF 5m 9s
- Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS 5m 13s
6. Statistical Functions 18m 57s
- Finding the middle value with MEDIAN 2m 46s
- Ranking data without sorting with RANK 4m 24s
- Finding the largest and smallest values with LARGE and SMALL 2m 19s
- Tabulating blank cells with COUNTBLANK 5m 43s
- Using COUNT, COUNTA, and the status bar 3m 45s
7. Math Functions 36m 59s
- Working with ROUND, ROUNDUP, and ROUNDDOWN 6m 5s
- Working with MROUND, CEILING, and FLOOR for specialized rounding 5m 21s
- Using the INT and TRUNC functions to extract integer data 3m 2s
- Finding the remainder with MOD and using MOD with conditional formatting 5m 26s
- Practical uses for the random number functions RAND and RANDBETWEEN 6m 8s
- Converting a value between measurement systems with CONVERT 3m 17s
- Using the powerful AGGREGATE function to bypass errors and hidden data 4m 55s
- Using the ROMAN and ARABIC functions to display different numeral systems 2m 45s
8. Date and Time Functions 39m 44s
- Understanding Excel date/time capabilities in formulas 9m 2s
- Using TODAY and NOW functions for dynamic date/time entry 3m 55s
- Identifying the day of the week with WEEKDAY 4m 8s
- Counting working days with NETWORKDAYS 3m 32s
- Determining a completion date with WORKDAY 3m 38s
- Tabulating date differences with DATEDIF 5m 43s
- Calculating end-of-month and future/past dates with EDATE and EOMONTH 5m 0s
- Converting text entries into dates and times with DATEVALUE and TIMEVALUE 4m 46s
9. Array Formulas and Functions 28m 34s
- Extending formula capabilities with arrays 8m 27s
- Counting unique entries in a range with an array formula 5m 46s
- Determining frequency distributions with FREQUENCY 3m 54s
- Flipping row/column orientation with TRANSPOSE 3m 27s
- Building analysis via regression techniques with TREND and GROWTH 2m 31s
- Using array formula techniques with the MATCH function for complex lookups 4m 29s
10. Reference Functions 16m 38s
- Getting data from remote cells with OFFSET 6m 16s
- Returning references with INDIRECT 3m 50s
- Using INDIRECT with data validation for two-tiered pick list scenarios 6m 32s
11. Text Functions 34m 3s
- Locating and extracting data with FIND, SEARCH, and MID 5m 4s
- Extracting specific data with LEFT and RIGHT 2m 29s
- Removing extra spaces with TRIM and removing hidden characters with CLEAN 5m 5s
- Using ampersands and CONCATENATE to combine data from different cells 5m 38s
- Adjusting the case within cells with PROPER, UPPER, and LOWER 3m 6s
- Adjusting character content with REPLACE and SUBSTITUTE 5m 56s
- Using other utility text functions: LEN, REPT, VALUE, TEXT 6m 45s
12. Information Functions 19m 55s
- Extracting information with the CELL and INFO functions 5m 47s
- Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER 5m 20s
- Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA 6m 18s
- Using the ISFORMULA function with conditional formatting 2m 30s
- Conclusion 26s
- Goodbye 26s
======================================================================
This DVDs are only suitable for a PC/laptop/Mac; it WILL NOT play on a TV
=======================================================================
