Excel 2016: Advanced Formulas and Functions

Learn how to create efficient formulas and use some of the 450+ functions in Excel to tabulate and analyze numerical, date, and text data.

Introduction

• Welcome
• Using the exercise files

1. Formula and Function Tips and Shortcuts

• Displaying and highlighting formulas
• Using Auditing tools
• Using entire row and column references
• Copying column formulas instantly
• Converting formulas to values with a drag
• Updating values without formulas
• Debugging formulas with the F9 key
• Enhancing readability with range names
• Tabulating data from multiple sheets

2. Formula and Function Tools

• Hierarchy of operations in formulas
• Formulas tab for locating functions
• Insert Function for learning about unfamiliar functions
• Extending the capabilities of AutoSum
• Absolute and relative references
• Using mixed references in formulas
• Using autocalculate in the status bar

3. IF and Related Functions

• IF logical tests
• Expanding nested IF statements
• AND, OR, and NOT functions with IF
• Using IFS for multiple conditions

4. Lookup and Reference Functions

• Looking up information with VLOOKUP and HLOOKUP
• Finding approximate matches with VLOOKUP and HLOOKUP
• Finding exact matches with VLOOKUP
• Nesting lookup functions
• Using VLOOKUP with large tables
• Finding table-like information within a function using the CHOOSE function
• Using the SWITCH function for formula-embedded selection
• Locating data with the MATCH function
• Retrieving information by location
• Using MATCH and INDEX functions together

5. Power Functions

• Tabulating data using a single criterion
• Tabulating data using multiple criteria
• Using MAXIFS and MINIFS
• Preventing double counting

6. Statistical Functions

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

7. Math Functions

• Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
• 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
• Exploring practical uses for the RAND and RANDBETWEEN functions
• 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 number systems

8. Date and Time Functions

• Understanding Excel date and time capabilities in formulas
• Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
• Using the TODAY and NOW functions for dynamic date and time entry
• Identifying the day of the week with the WEEKDAY function
• Counting working days with the NETWORKDAYS function
• Determining a completion date with the WORKDAY function
• Tabulating date differences with the DATEDIF function
• Calculating end-of-month and future and past dates with EDATE and EOMONTH

9. Array Formulas and Functions

• Extending formula capabilities with array formulas
• Counting unique entries in a range with an array formula
• Determining frequency distributions with the FREQUENCY function
• Flipping row and column orientation with TRANSPOSE
• Building analysis via regression techniques with TREND and GROWTH
• Using array formulas and the MATCH function for complex lookups

10. Reference Functions

• Getting data from remote cells with the OFFSET function
• Returning references with the INDIRECT function
• Using INDIRECT with Data Validation for two-tiered pick list scenarios

11. Text Functions

• Locating and extracting data with the FIND, SEARCH, and MID functions
• Extracting specific data with the LEFT and RIGHT functions
• Using the TRIM function to remove unwanted spaces in a cell
• Using ampersands and CONCATENATE to combine data from different cells
• Using the CONCAT and TEXTJOIN to combine data from different cells
• Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
• Adjusting character content with the REPLACE and SUBSTITUTE functions
• Using the utility text functions: TEXT, REPT, and LEN

12. Information Functions

• Extracting information with the CELL and INFO functions
• Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
• Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
• Tracking and highlighting formula cells with the ISFORMULA function

Conclusion

• Next steps