Overview
This advance Excel course is designed for current users of Microsoft Office and Excel. This course builds on the FUNdamentals, 5Fs of Excel, and Formulas and Functions Part 1 courses. We will expand on the VLOOKUP/HLOOKUP, 3-D References, Absolute/Mixed References, the Name Box/Name Manager and IF…THEN…ELSE functions introduced in the Formulas and Functions Part 1 course. This course teaches advanced functions including SUMIFS, COUNTIFS, AVERAGEIFS, DSUM, DCOUNT, and DSUM. We will learn and use Data Validation for our criteria in using the SUMIFS, AVERAGIFS, and so on.
Prerequisite: Formulas and Functions Part 1 and comfortable using the VLOOKUP, IF…THEN…ELSE, and the Name Box/Manager.
Learning Objectives
After completing this program, participants will be able to:
- Use Data Validation for function criteria,
- Develop results with functions based on criteria,
- Create multiple IF-THEN-ELSE statements,
- Develop functions that give results based on criteria: COUNTIFS, SUMIFS, AVERAGEIFS,
- Summarize data with database functions: DSUM, DCOUNT, and DAVERAGE.
Course Content
- Lesson 1: Data Validation
- Create rules and lists for data and functions,
- Develop error alerts and rules for text, numbers, and dates,
- Restrict users to specific choices in SUMIFS, AVERAGEIFS, and COUNTIF functions.
- Lesson 2: HLOOKUP/VLOOKUP
- Multiple VLOOKUP functions carrying data from one worksheet to another,
- Use VLOOKUP for quick searches in a worksheet,
- Use HLOOKUP to lookup information in rows.
- Lesson 3: Functions based on Criteria
- IF. . . THEN. . . ELSE (Multiple criteria),
- SUMIF and SUMIFS,
- AVERAGEIF and AVERAGEIFS,
- COUNTIF and COUNTIFS.
- Lesson 4: Database functions
- DSUM,
- DCOUNT,
- DAVERAGE.