Excel 2013: Advanced Formulas and Functions

In this series of Excel tutorials, discover some of the most challenging of the 300+ formulas and functions in Excel, and learn about how to put them to their best use.

Introduction

• Welcome
• Using the exercise files

1. Formula and Function Tips and Shortcuts

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

2. Formula and Function Tools

• Understanding the hierarchy of operations in Excel formulas
• Using the Formulas tab on the Ribbon for locating functions
• Using the Insert Function button for guidance with unfamiliar functions
• Using and extending AutoSum button capabilities
• Using absolute and relative references in formulas
• Using mixed references in formulas

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 and OR 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
• Using VLOOKUP with large tables
• 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 using a single criterion with COUNTIF, SUMIF, and AVERAGEIF
• Tabulating information using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

6. Statistical Functions

• Finding the middle value with MEDIAN
• Ranking data without sorting with RANK
• Finding the largest and smallest values with LARGE and SMALL
• Tabulating blank cells with COUNTBLANK
• Using COUNT, COUNTA, and the status bar

7. Math Functions

• Working with ROUND, ROUNDUP, and ROUNDDOWN
• Working with MROUND, CEILING, and FLOOR for specialized rounding
• Using the INT and TRUNC functions to extract integer data
• Finding the remainder with MOD and using MOD with conditional formatting
• Practical uses for the random number functions RAND and RANDBETWEEN
• Converting a value between measurement systems with CONVERT
• Using the powerful AGGREGATE function to bypass errors and hidden data
• Using the ROMAN and ARABIC functions to display different numeral systems

8. Date and Time Functions

• Understanding Excel date/time capabilities in formulas
• Using TODAY and NOW functions for dynamic date/time entry
• Identifying the day of the week with WEEKDAY
• Counting working days with NETWORKDAYS
• Determining a completion date with WORKDAY
• Tabulating date differences with DATEDIF
• Calculating end-of-month and future/past dates with EDATE and EOMONTH
• Converting text entries into dates and times with DATEVALUE and TIMEVALUE

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
• Using array formula techniques with the MATCH function for complex lookups

10. Reference Functions

• Getting data from remote cells with OFFSET
• Returning references with INDIRECT
• Using INDIRECT with data validation for two-tiered pick list scenarios

11. Text Functions

• Locating and extracting data with FIND, SEARCH, and MID
• Extracting specific data with LEFT and RIGHT
• Removing extra spaces with TRIM and removing hidden characters with CLEAN
• Using ampersands and CONCATENATE to combine data from different cells
• Adjusting the case within cells with PROPER, UPPER, and LOWER
• Adjusting character content with REPLACE and SUBSTITUTE
• Using other utility text functions: LEN, REPT, VALUE, TEXT

12. Information Functions

• Extracting information with the CELL and INFO functions
• Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER
• Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA
• Using the ISFORMULA function with conditional formatting

Conclusion

• Goodbye