Duration
Microsoft Excel (Intermediate)
1 day
Course Overview
This one-day Microsoft Excel Intermediate course is designed to help participants master complex formulas, functions, and data auditing techniques. The course focuses on using logical, statistical, lookup, and text functions, along with effective worksheet auditing and data management using tables.
Target Audience
Ideal for professionals, data analysts, finance personnel, and advanced Excel users who want to enhance their formula-building, data analysis, and auditing skills.
Learning Outcomes
Participants will be able to:
- Use named ranges and apply them in formulas.
- Implement logical functions like IF, AND, OR, IFS, and SWITCH.
- Utilize statistical and lookup functions for data analysis.
- Audit and evaluate formulas to troubleshoot errors.
- Convert data into tables and apply sorting, filtering, and calculations
Outline
9:00am-10:30am
Module 1: Working with Formulas
- Concept of Formulas
- Understanding Relative and Absolute Cell References
- Understanding Named Ranges
- Defining Named Ranges
- Editing Named Ranges
- Deleting Named Ranges
- Using Named Ranges in Formulas
10:30am-10:45pm Morning Break
10:45pm-12:30pm
Module 2: Working with Functions (I)
- Concept of Functions
- Using Advanced Functions
- Using Logical Function: IF
- Using Logical Function: AND
- Using Logical Function: OR
- Using Logical Function: IFS, SWITCH
12:30pm-1:30pm Lunch
1:30pm-3:30pm
Module 3: Working with Functions (II)
- Using Statistical functions: AVERAGEIF, COUNTIF
- Using Lookup Functions: VLOOKUP ,HLOOKUP, LOOKUP
- Using Text functions: TEXTJOIN, CONCAT
- Using Statistical Functions: MAXIFS and MINIFS
Module 4: Auditing Worksheets
- Tracing Formula Precedents and Dependents
- Showing Formulas
- Evaluating Formulas
- Using Error Option Buttons
- Running an Error Check
3:30pm-3:45pm Afternoon Break
3:45pm-5:00pm
Module 5: Working with Table
- Converting Data in range as a Table
- Total the Data in Table
- Removing Duplicates from Table
- Using Sort and Filter
Programme scope, delivery arrangements and suitability are confirmed during enquiry.