Excel for Mac 2016: Advanced Formulas and Functions

Introduction

• Welcome
• Exercise files

1. Formula and Function Tools

• Write formulas using a hierarchy of operators
• Save time with AutoSum and extended features
• Determine when to use absolute references vs. relative
• Use mixed references in formulas
• Use function category buttons for locating functions
• Use the Formula Builder for unfamiliar functions
• Use AutoCalculate to get totals for selected cells

2. Formula and Function Tips and Shortcuts

• Display and highlight all worksheet formulas
• Track cell dependencies; locate formula sources
• Use entire row/column references in formulas
• Copy formulas down a column instantly
• Convert formulas to values with a simple drag
• Update values without formulas
• Simplify debugging formulas with the F9 key
• Enhance readability with range names
• Create 3D formulas that gather data from multiple sheets

3. IF and Related Functions

• Use relational operators and IF logical tests
• Create and expand nested IF functions
• Create compound logical tests: AND, OR, and NOT with IF

4. Lookup and Reference Functions

• Look up information with VLOOKUP and HLOOKUP
• Find approximate matches with VLOOKUP
• Find exact matches with VLOOKUP
• Use VLOOKUP with large tables
• Use nested lookup functions
• Find table-like information with CHOOSE
• Identify the presence of data with MATCH
• Retrieve information by location with INDEX
• Use MATCH and INDEX together

5. Statistical Functions

• Use MEDIAN for middle value, MODE for most frequent
• Rank data without sorting using the RANK function
• Find largest and smallest values with LARGE and SMALL
• Tabulate blank cells with COUNTBLANK
• Use COUNT, COUNTA, and the status bar

6. Power Functions

• Tabulate with COUNTIF, SUMIF, and AVERAGEIF
• Tabulate with COUNTIFS, SUMIFS, and AVERAGEIFS
• Use the SUBTOTAL function to prevent double counting

7. Selected Financial Functions

• Calculate monthly payment with PMT
• Calculate the future value of a series with FV
• Use PV to determine fixed-term borrow amount

8. Math Functions

• Change displayed result with ROUND, ROUNDUP, and ROUNDDOWN
• Change displayed result with MROUND, CEILING, and FLOOR
• Use the INT and TRUNC functions to extract integer data
• Find remainder with MOD; use MOD with conditional formatting
• Generate random values with RAND and RANDBETWEEN
• Convert values between measurement systems with CONVERT
• Bypass errors and hidden data with AGGREGATE
• Display different number systems with ROMAN and ARABIC

9. Date and Time Functions

• Use dates and times in Excel formulas
• Use TODAY and NOW for dynamic date/time entry
• Identify the day of the week with WEEKDAY
• Count working days with NETWORKDAYS
• Determine a completion date with WORKDAY
• Tabulate date differences with DATEDIF
• Calculate end-of-month with EOMONTH, future/past with EDATE

10. Text Functions

• Locate and extract data with FIND, SEARCH, and MID
• Extract specific data with LEFT and RIGHT
• Remove extra spaces with TRIM
• Use ampersands and CONCATENATE to combine select cell data
• Adjust case within cells using the PROPER, UPPER, and LOWER
• Adjust character content with REPLACE and SUBSTITUTE
• Use other utility text functions: LEN, REPT, VALUE, TEXT

11. Array Formulas and Functions

• Extend formula capabilities with arrays
• Count unique entries in a range with an array formula
• Determine frequency distributions with FREQUENCY
• Flip row/column orientation with TRANSPOSE
• Build analysis with TREND and GROWTH regression techniques
• Use MATCH function for complex lookups

12. Reference Functions

• Get data from remote cells with OFFSET
• Return references with INDIRECT
• INDIRECT with Data Validation for two-tiered pick list scenarios
• FORMULATEXT function for on-screen documentation

13. Information Functions

• Extract information with CELL and INFO
• Use ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER
• Use ISERR, ISERROR, IFERROR, and ISNA
• Document formulas nearby using ISFORMULA

Conclusion

• Next steps