[01] INTRODUCTION
This course is for participants that would like to automate some common tasks, apply advanced analysis techniques to more complex data sets, collaborate with others and use Excel data in other applications.
[02] COURSE OBJECTIVES
Upon completion of this course, participants will be able to:
▪ Master lookup functions, use relevant text functions to clean up data
▪ Protect valuable information
▪ Analyze data with Pivot Table
▪ Tweak charts
▪ Automate tasks with macro and VBA
[03] REQUIREMENTS
Attendees who has Excel Intermediate or equivalent skills, or have completed Microsoft Excel Level 2 training. The course can be performed in either Excel 2016 or Office 365 Excel Application.
Delegates are required to bring along a laptop with sufficient battery power and/or power charger
[04] WHO SHOULD ATTEND
• People who are ambitious and want to grow faster than their peers
• Anyone who wants to use Excel at its best, e.g. HR managers, project managers, revenue managers, finance managers and administrative personnel who are in the basic to intermediate group wishing to improve their competencies
• This course is helpful for increasing personal productivity, efficiency at work or even developing skills to obtain a better job.
[05] METHODOLOGY
Interactive workshop and practical exercises and quizzes using unique, proven learning methods.
[06] DATE | TIME | FEE
Date : 23 - 24 February 2023 (Thu - Fri)
Time : 9.00am - 5.00pm
Mode: Online/ Interactive
[HRDF CLAIMABLE - SBL KHAS]
Fee:
- Early Bird Offer (before 4 Feb 2023) – RM 720
- Special Offer (before 18 Feb 2023) – RM 780
- Normal Fee – RM 840
>> Fee includes Course Notes & Certificate of Completion and 6% Government Service Tax
** OFFER **
- 10% Cashback for SST2u Learner Card+ (SLC+) members. T&Cs applied.
HRDCorp Claimable Course:
Trainer MyCoiD: 429594X
Course Scheme: HRDCorp Claimable Course
Course Type: Remote Online Training (Public)
Course Code: 10001260482
[07] COURSE SCOPE
Day 1
Lesson 1: Warmup Session
• Explore essential shortcuts
• Quick overview of must know tools in Excel
Lesson 2: INDIRECT
• Link data from various sheets
• Use INDIRECT and mixed references to automate linking process
Lesson 3: Data Validation
• Data validation settings & options
• Create drop-down list
• Custom Validation
Lesson 4: Protecting Data & Structure
• Keynote on password
• Protect Worksheet
• Multiple range/ user password
• Protect Workbook structure
• File Encryption
Lesson 5: More Functions
• Text functions
o CONCATENATE,CONCAT,TEXTJOIN, UPPER, LOWER, PROPER, LEFT, RIGHT, MID, REPLACE, SUBSTITUTE, REPT, TRIM, VALUE, TEXT
• Lookup functions
o VLOOKUP, RangeLookup
o MATCH, INDEX
• Combining VLOOKUP & IF
• Combining VLOOKUP & INDIRECT
• IF, AND, OR
• Database functions
o DSUM,DAVERAGE,DCOUNT
Day 2
Lesson 6: Pivot Tables
• Preparing data and construct a PivotTable
• Understand PivotTable terms and layouts
• Adding field row, column, values
• Explore ‘Summarize Values By’
• Explore ‘Show Values As’
o % Grand Total, % Parent Total
o Running Total, Difference From
• Filter Top/Bottom ‘N’ values
• Grouping text, number, date fields
• Insert Slicers, Timeline, Report Connectivity
• Show Report filter pages
• GETPIVOTDATA function
• Pivot Chart
• Link/Embed data/chart to other applications
Lesson 7: Tweak Charts
• Linking cells to chart
• Conditionally format a series
• Using custom data labels
• Identifying the highest/lowest range
• Using shapes/images in a series
• Linking data to PivotChart
Lesson 8: Basic Macros & VBA
• Record and run simple macros
• Macro security level
• Save as Macro enabled file type
• Accessing VBA
• Running sample VBA
Lesson 9: Data Analysis Tools
• Scenario
• What-if Analysis
• Goal Seek
• Statistical Analysis
[08] SPEAKER PROFILE
UMA DEVI A/P SUBRAMANIAM
HRDF Trainer Senior Microsoft Office Trainer/ Consultant
Uma Devi has 30 years of experience in training Excel, Data Analysis, Customized Database Programming and Presentation. She holds a NCC Diploma in Computer Studies and completed programs including CVET, Digital Citizenship, Microsoft Office Specialist Excel 2016 etc.
Her key areas of training expertise are:
• Windows & File Management
• Microsoft Outlook
• Microsoft Word, Excel, PowerPoint, and Access (Basic, Intermediate, Advance levels)
• Microsoft Excel – Power Pivot, Formula & Functions, Dynamic Charts, Dashboard
Over the years, she has trained thousands of local and overseas attendees from more than 8 countries. She is also appointed Microsoft Office trainer for some large corporation. Her ability to supplement her trainings with real-life scenarios and examples, as well as training methodology that is easy to follow make her a popular trainer.
[09] ENQUIRY & REGISTRATION
Enquiry: yes@sst2u.com
Contact:
- Teo (011 - 3178 9203)
- Logesh (012 - 503 0346)
- Siti (012 - 383 8603)
- Han (019 - 323 0507)