Arena Training 3 hours
Course Description
Do you want to become more efficient with formulas and functions? Have you inherited workbooks and need to understand the functions within? Do you work with huge amounts of data and need to parse the information? This may be the class for you. We explore advanced functions, work with named ranges and resolve errors. This class is a terrific lead-in to more advanced Data Analysis classes.
Course Objectives:
Upon successful completion of this course, you will be able to create advanced functions and formulas and apply range names so you can take advantage of advanced Excel functionality.
You will:
- Use Data Validation
- Create and Use Range Names
- Create Formulas using Relative and Absolute Addressing
- Create Array Formulas
- Use the IF, VLOOKUP, INDEX and MATCH
- Use Error Resolving Techniques
Target Student:
This course is intended for students who have experience with Excel 2016 desire to advance their skills in working with some of the more advanced Excel features. Students who need to construct more complex Excel functions, and use those functions to perform rigorous analysis of extensive, complex datasets will benefit from this course.
Prerequisites:
To ensure success, students should have practical, real-world experience creating and analyzing datasets using Excel 2016. Specific tasks students should be able to perform include: creating formulas and using Excel to create the sum and average functions.
Course Content
Lesson 1: Validating Your Data
- Configure Data Validation
- Validate Using Lists
Lesson 2: Advanced Formula Tasks
- Use Relative and Absolute Cell References
- Use 3D References
- Use Array Formulas
Lesson 3: Working with Named Ranges
- Defining Named Ranges
- Editing Named Ranges
- Deleting Named Ranges
- Using Named Ranges in Formulas
Lesson 4: Using Advanced Functions
- Use logical functions such as IF, CountIf and
- Use VLOOKUP and HLOOKUP Functions
- Use text and date functions