Excel 2010: Advanced Formulas and Functions

Introduction

• Welcome
• Using the exercise files

1. Formula and Function Tips and Shortcuts

• Using the entire row/column references
• Copying column formulas instantly
• Converting formulas to values with a simple drag
• Creating 3D formulas to gather data from multiple sheets
• Updating values without formulas
• Displaying and highlighting formulas
• Simplifying debugging formulas
• Enhancing readability with range names

2. Formula and Function Tools

• Reviewing function basics
• Using and extending AutoSum
• Using absolute and relative references
• Using mixed references

3. IF and Related Functions

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

4. Lookup and Reference Functions

• Looking up information with VLOOKUP and HLOOKUP
• Finding approximate matches with VLOOKUP
• Finding exact matches with VLOOKUP
• Nesting LOOKUP functions
• Finding table-like information within a function with CHOOSE
• Locating data with MATCH
• Retrieving information by location with INDEX
• Using MATCH and INDEX together

5. Power Functions

• Tabulating information on a single criterion with COUNTIF, SUMIF, and AVERAGEIF
• Tabulating information on multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

6. Statistical Functions

• Finding the middle value with MEDIAN
• Ranking data without sorting with RANK
• Finding the magnitude data with LARGE and SMALL
• Tabulating blank cells with COUNTBLANK

7. Date Functions

• Understanding Excel date/time capabilities in formulas
• Identifying the day of the week with WEEKDAY
• Counting working days with NETWORKDAYS
• Determining a completion date with WORKDAY
• Tabulating date/time differences with DATEDIF

8. Math Functions

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

9. Array Formulas and Functions

• Extending formula capabilities with arrays
• Counting unique entries in a range with an array formula
• Determining frequency distributions with FREQUENCY
• Flipping row/column orientation with TRANSPOSE
• Building analysis via regression techniques with TREND and GROWTH
• Combining multiple functions in arrays

10. Text Functions

• Locating and extracting data with FIND and MID
• Extracting specific data with LEFT and RIGHT
• Removing excess spaces with TRIM
• Using CONCATENATE with functions
• Adjusting case within cells with PROPER, UPPER, and LOWER
• Adjusting character content with REPLACE and SUBSTITUTE
• Reviewing additional text functions

11. Financial Functions

• Calculating payments with PMT
• Finding future values with FV
• Determining total amount of future payments with PV

12. Information Functions

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

13. Reference Functions

• Getting data from remote cells with OFFSET
• Returning references with INDIRECT

Conclusion

• Final thoughts