Duration
2 days
Introduction
Elements of this syllabus are subject to change.
This training is suitable for intermediate and average of Excel 2013 user. This training required basic skill in Excel 2013. The required skills for this training include calculation, creating and formatting
Module 1: Using Basic Excel Tools
- Understanding Cell References and Formulas
- Using Basic Formulas
- Using Basic Functions
- Using Advanced Functions
Module 2: Advanced Formula Tasks
- Understanding Relative and Absolute Cell References
- Using Multiple Cell References
- Using 3D References
- Using Array Formulas
Module 3: Working with Named Ranges
- Understanding Named Ranges
- Defining Named Ranges
- Editing Named Ranges
- Deleting Named Ranges
- Using Named Ranges in Formulas
Module 4: Using Advanced Functions
- Using the PMT Function
- Using the FV Function
- Using VLOOKUP and HLOOKUP Functions
- Using IF, AND, OR Functions
Module 5: Resolving Formula Errors
- Tracing Formula Precedents and Dependents
- Showing Formulas
- Evaluating Formulas
- Setting Error Checking Options
- Using Error Option Buttons
- Running an Error Check
Module 6: Formatting the Workbook
- Using Cell Styles
- Formatting Data as a Table
- Changing the Theme
- Using Sort and Filter
Module 7: Managing Data
- Consolidating Information
- Removing Duplicates
- Configuring Data Validation
- Transposing Data
- Converting Text to Columns
Module 8: Outlining and Grouping Data
- Outlining Data
- Showing and Hiding Outline Details
- Grouping Data
- Creating Subtotals
- Removing Outlining and Grouping
Module 9: Working with Scenarios
- Creating Scenarios
- Loading Scenarios
- Merging Scenarios
- Editing Scenarios
- Creating a Scenario Summary Report
- Deleting Scenarios
Module 10: Using Solver
- Setting up the Worksheet
- Running Solver
- Generating Reports and Scenarios with Solver
- Modifying Constraints
- Setting Solver Options
Module 11: Analyzing Data
- Using Goal Seek
- Using Data Tables
Module 12: Advanced 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.