Course Overview:
This intermediate-level course is designed to help users enhance their Excel skills by exploring more advanced features, including data analysis, visualization, automation, and collaboration tools. Participants will learn to work efficiently with formulas, functions, charts, PivotTables, and conditional formatting to improve data management and reporting.
Course Objectives:
Use intermediate Excel functions to analyze and manipulate data
Create dynamic reports with PivotTables and PivotCharts
Automate tasks using basic macros
Improve collaboration through Excel’s sharing and security features
Enhance data visualization with charts and conditional formatting
Who Should Attend?
Professionals who use Excel regularly and want to improve efficiency
Data analysts, accountants, and business professionals
Individuals looking to enhance their reporting and analytical skills
Anyone with a basic understanding of Excel who wants to learn advanced features
Course Prerequisites
Participants should have a basic understanding of Microsoft Excel, including
Creating and formatting worksheets
Basic formulas and functions (SUM, AVERAGE, MIN, MAX)
Simple data entry, sorting, and filtering
Course Content:
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
- Using Array Formulas
- Using 3D References
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
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
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