Learn several of Excel's essential functions and techniques for putting those functions to work in your worksheet formulas. Techniques include Formula AutoComplete, the Insert Function command and nesting multiple functions in a single formula, and more.
This class is part two of a two-part series designed to move the accomplished Excel beginner to the next level. It's designed for people who use Excel actively to do their jobs.
Class outline/topics
- Using the function arguments dialog box
- Understand function arguments and syntax
- Get help for functions
- Logic functions: IF, AND, OR
- Simple IF formulas
- Nested IF formulas
- Expanding IF function¿s capability with AND and OR
- Using VLOOKUP
- Exact and approximate match
- Conditional functions: SUMIFS, COUNTIFS
- Calculations based on multiple conditions
- Database functions: DSUM, DCOUNT
- Understanding criteria range concepts and techniques
Prerequisites
Participants should have already taken and put to work the skills covered in Intermediate Excel Part 1. Ideally, participants will be active Excel users.
To get the most out of this class, participants should be proficient in and comfortable with at least seven out of the following 10 Excel skills:
- Manually create a named range and use it in an Excel formula
- Use the Select Current Region command to select all cells in a range of adjacent cells
- Use Excel¿s Create from Selection command to create named ranges automatically
- Use the SUM function in a formula to add a column of numbers
- Create a formula using an absolute cell reference (for example: =$A$2 * C5)
- Use AutoFilter with various criteria including greater than, less than and begins with
- Convert a normal range of worksheet cells to a table
- Add a table total row and add to it formulas that sum and count visible table rows
- Create a formula that references cells in a table using the table name and column name rather than simple cell references
- Add and remove command shortcuts from the Quick Access toolbar
