2013
Lynda
Dennis Taylor
5h 26m
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 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

www.lynda.com/Excel-tutorials/Excel-2013-Advanced-Formulas-Functions/126129-2.html

Download File Size:682.81 MB

---