2013
Lynda
Dennis Taylor
5:26
English
Conquer some of the most daunting features in Microsoft Excel once and for all. In this 2013 update to his popular series, author 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. The course 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. Dennis 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 work with financial data using advanced formulas. Dennis focuses on practical examples that transition effortlessly to real-world scenarios.
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, OR, and NOT 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
http://www.lynda.com/Excel-tutorials/Excel-2013-Advanced-Formulas-Functions/126129-2.html

Download File Size:1.16 GB

---