Duration
2 days
Introduction
This training is suitable for intermediate of Excel 2013 user. This training required basic skill in Excel 2013.
Contents
In this module you will learn to use calculation in Excel. Beside calculation, you also will learn about Formatting Table.
Module 1: Using Basic Excel Tools
- Using Basic Formulas
- Using Basic Functions
- Using Advanced Functions
- Understanding Cell References and Formulas
- Understanding Relative and Absolute Cell References
- Using Multiple Cell References
- Using 3D References
- Using Array Formulas
Module 2: Working with Named Ranges
- Understanding Named Ranges
- Defining Named Ranges
- Editing Named Ranges
- Deleting Named Ranges
- Using Named Ranges in Formulas
Module 3: Using Functions
- Using VLOOKUP and HLOOKUP Functions
- Using IF, AND, OR Functions
- Summarize data using SUMIF
- Using SUMIFS for multiple conditions
- Get median arithmetic value with AVERAGEIF
- Using AVERAGEIFS for multiple conditions
- Using COUNTIF to count no of Records
- Working with multiple criteria with COUNTIFS Analyst Data in Database
Module 4: Formatting the Workbook
- Using Cell Styles
- Formatting Data as a Table
- Using Sort and Filter
Introduction
This training required user that have average skill in Excel 2013. The required skills for this training include calculation, creating and formatting.
Contents
In this module, you will learn to manage and massage data into your on format.
Module 1: Managing Data
- Consolidating Information
- Removing Duplicates
- Configuring Data Validation
- Transposing Data
- Converting Text to Columns
Module 2: Outlining and Grouping Data
- Outlining Data
- Showing and Hiding Outline Details
- Grouping Data
- Creating Subtotals
- Removing Outlining and Grouping
Module 3: Working with Scenarios
- Creating Scenarios
- Loading Scenarios
- Merging Scenarios
- Editing Scenarios
- Creating a Scenario Summary Report
- Deleting Scenarios
Module 4: Using Solver
- Setting up the Worksheet
- Running Solver
- Generating Reports and Scenarios with Solver
- Modifying Constraints
- Setting Solver Options
Module 5: Analyzing Data
- Enabling the Analysis ToolPak
- Using Data Analysis Tools
- Using Goal Seek
- Using Data Tables
Module 6: 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
Module 7: Using Macros
- Recording a Macro
- Writing a Macro using the Visual Basic Editor
- Editing a Macro
- Running a Macro
Programme scope, delivery arrangements and suitability are confirmed during enquiry.