Duration
2 days
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
- 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: Dynamic Arrays Functions
- Excel Dynamic Arrays
- UNIQUE Function
- SEQUENCE Function
- SORT Function
- SORTBY Function
- FILTER Function
- XLOOKUP Function
- LET Function
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
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 Analyze Data
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.