Course Description
This Intermediate-Advanced Microsoft Excel training class is designed for students to gain the skills necessary to work withformulas, functions, worksheets and tables. Also, for students to gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros.
Target Audience
This training is suitable for intermediate of Excel 2019 user and is recommended for users to already have the basic knowledge of Excel 2019 usage. Also for students who have intermediate skills with Microsoft Excel 2019 who want to learn more advanced skills or students who want to learn the topics covered in this course in the 2019 interface.
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
Course Outline ::EXC19I-A::
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
Module 6: Managing Data
Consolidating Information
Configuring Data Validation
Converting Text to Columns
Using Flash Fill
Module 7: Outlining and Grouping Data
Outlining Data
Showing and Hiding Outline Details
Grouping Data
Creating Subtotals
Removing Outlining and Grouping
Course Outline ::EXC19I-A::
Module 8: Data Visualization
Understand Types of Charts
Creating Treemap Chart
Creating Waterfall Chart
Creating Map Chart
Creating Funnel Chart
Module 9: Analyzing Data (I)
Using Goal Seek
Using Data Tables
Using Solver
Module 10: Analyzing Data (II)
Analysis on Stocks Data Type
Analysis on Geography Data Types
Using Ideas
Module 11: PivotTable Features
Creating a Basic PivotTable
Creating a Basic PivotChart
Using the PivotTable Fields Pane
Adding Calculated Fields
Sorting Pivoted Data
Filtering Pivoted Data
Using Slicers
Creating Timelines
Programme scope, delivery arrangements and suitability are confirmed during enquiry.