MS Excel 2013 (Intermediate – Advanced) (MDA140)

10 October 2017 - 11 October 2017

The participant should be able to analyze and summarize huge database using
• New features in excel 2013
• Intermediate data management tools such as filters and advanced filters.
• Formulas to validate data
• Analyze data using a number of tools such as a subtotal report and a pivot table.
• Import and export data and link to the Web.
• Create web pages of Excel data.
• Lookup the data using lookup function


Module F: Sign in to Office and OneDrive
• Sign In
• Saving a Workbook as OneDrive

Module G: Protecting Workbook
• Protecting Worksheets, Workbooks and File
• Exercise : Protecting Worksheets with Hidden Formulas
• Exercise : Protecting Workbooks and file

Module H: Analyze Your Data Instantly
• Analyse Your Data Instantly

Module I: Presentation Data in Charts
• Creating Chart
• Modified Chart
• Recommended Chart

Module J: Working with Sparklines
• Types of Sparklines
• Create Sparklines
• Changing the Appearance of Sparklines
• Change the display Range

Module K: Performing Calculation In Name Group
• Understand Relatives and Absolute Reference
• Named Method
• Creating and Re-defining Range names
• Creating Formulas with Range Names

Module L: Advance Function
• Using VLOOKUP Function
• Using IF Function
• Using PMT Function
• Using FV Function

Module M: Visualization Data Using Conditional Formatting
• Formatting Data Based on Its Value
• Using Conditional Format
• Create a New Conditional Formatting Rules
• Remote Conditional Formatting Rules

Module N: Enhancing Your Work with Pictures and Drawing
• Inserting ClipArt And Picture
• Modify a graphic
• Insert a Screenshot
• Add Background to a Worksheet
• Draw and modify Shapes
• Insert a SmartArt Graphics

Module O: Data Validation
• Create drop down list
• Creating Data Validation Formula

Module P: Managing Data In List
• Basic Sort
• Custom Sort
• Sorting database
• Filtering Data
• Using Functions with list

Module Q: Data Analysis
• Create Pivot table and chart
• Using Pivot table fields pane
• Adding calculation fields
• Scenario Manager

Module R: Using PowerPivot
• Enabling PowerPivot
• Creating Pivot table and PowerPivot Data

Module S: Simple Macro
• Converting formula to value
• Assign Macro To Quick Toolbar
• Attaching Macro to a Command Button View

2 days

Program 100% Claimable Under SBL Scheme
PSDC Members: RM 630/pax
Non-Members: RM 680/pax

16 - 17 May 2017
10 – 11 October 2017

*Training dates are subject to change