MS Excel 2010 (Intermediate – Advanced) (MDA136)

03 August 2017 - 04 August 2017

OBJECTIVE
Participants will be able to gain advanced formula, calculating data using functions and formulas, create and modify charts, inserting and modify graphic objects in a worksheet, customize and enhance workbook and the Microsoft Excel environment

COURSE OUTLINE

INTERMEDIATE LEVEL
Module I: Work Protection
• Protecting Worksheets, Workbooks and File
• Protecting Worksheets with Hidden Formulas
• Protecting Workbooks and File with Passwords

Module J: Performing Calculation on Named Groups
• Understand Relative And Absolute Reference
• Naming Method
• Create Multisheet Names
• Calculate Data Across Worksheet
• Create Formulas With Range Names

Module K: Advanced Function
• Logical Functions
• VLOOKUP and HLOOKUP Functions

Module L: Presenting Data In Charts
• Overview of Excel Charting Features
• Creating Chart
• Charts Modifications
• Change Appearance of Charts
• Creating Combination Chart with Secondary Axis

Module M: Working with Sparklines
• Overview of Sparklines
• Creating Sparklines
• Change Appearance of Sprklines

Module N: Apply Conditional Formatting
• The conditional Formatting Rules Manager Dialog Box
• Using Conditional Format
• Create a new Conditional Formatting
• Remove Conditional Formatting Rules

Module O: Enhancing Your Work with Picture and Drawing
• Graphic Object
• Pictures and Clip Art
• The Screen Capping Tool
• Picture Formatting Options
• Format Artistic Effects Feature
• Draw and Modify Shapes
• Types of Shapes
• Illustrate Workflow Using SmartArt Graphics
• Layer and Group Graphic Object

ADVANCED LEVEL

OBJECTIVE
In this course, participants will be able to Managing data, work with various tools to analyze data using Pivot Tables, Slicers and Pivot Charts You will enhance productivity and efficiency by streamlining the workflow.

Module P: Data Validation
• Types Of Data Validation
• Create Drop-Down List
• Create Data Validation Formula

Module Q: Managing Data in List
• Basic And Custom Sort
• Use Auto Filter For Database
• Subtotal Data

Module R: Analyzing Data Using PivotTables, Slicers and PivotCharts
• Create a Pivot Table
• Use Slicers for Filtering data
• Analyze data using PivotCharts
• Create a trendline
• Create sparklines for data
• Scenario Manager
• Goal Seeking

Module S: Retrieving External Data
• Import Text Files
• Import From Another Excel File
• Import Using Microsoft Query

Module T: Simple Macro
• Record Macro
• Edit a simple
• Macro
• Attach Macro To Command Button
• Attach Macro To Quick Access Toolbar

DURATION
2 days

COURSE FEE
Program 100% Claimable Under SBL Khas Scheme
PSDC Members: RM 580/pax
Non-Members: RM 630/pax

DATES
13 – 14 April 2017
3 – 4 August 2017

*Training dates are subject to changes