Quality Dynamics Consultancy Sdn Bhd presents
Microsoft Excel - Advanced
(by Mr. Chin Chee Chong )
objectives
At the end of the course participants are expected to:1. Create and use defined names in a workbook
2. Work with logical function in Excel
3. Use a variety of data validation techniques
4. Use a range of lookup and reference functions
5. Create summaries in your spreadsheets using subtotals
6. Use the Data consolidation feature to combine data from several
workbooks into one
7. Create and work with Scenarios and the Scenario Manager
8. Import data into Excel and Export date from Excel
9. Create and use a range of controls in a worksheet
10. Create recorded macros in Excel
11. Import Data in Excel and Export data from Excel
program content
Topic 1: Linking & Consolidate Data• Linking and Consolidating Data
• Linking Workbooks
• Creating a Link Formula by Paste Link
• Consolidating Data
• Creating a 3-D Formula
Topic 2: Tables and Sorting
• Managing Tables and Data
• Tables Overview
• Table Terms
• Tips for Setting up a Table
• Creating Tables
• Sorting Data
• Sorting Data: Sort Button
• Sorting Data: Sort Dialog Box
• To sort according to a custom list
• To sort according to a formatting criterion
Topic 3: Data Management
• Filtering Data
• Using Table Filters
• Using Custom Auto Filter
• Turning off the Auto Filter
• Advanced Filtering
• Using Advanced Filter
• Running an Advanced Filter
• Turning off Advanced Filter
• Copying an Advanced Filter to another Location
• Sub-totals
• Adding Sub-totals
• Adjusting views and sub-totals
• Removing Sub-totals
• Re-sorting the Data
• Data Validation
• Dealing with Text Files
• Text Format Files
• Text To Column
Topic 4: What –If Analysis
• What if Analysis
• Creating Data Tables
• Creating a One Input Data Table
• Creating a Two-Input Data Table
• Goal Seek
• Scenario Manager
• How to Create A Report from a Scenario
Topic 5: Using IF, VLOOKUP, HLOOKUP, Formula Auditing
and Protections
• Auditing a Worksheet
• Tracing Formulas and Errors
• Trace cells that provide data to a formula
• Trace formulas that reference a particular cell
• Watch Window
• Protecting your files and worksheets
• Protection Options
• Looking & Unlocking Cells
• Workbook Level Protection
• Worksheet Level Protection
• File Level Protection
Topic 6: Using Macros
• Automating Excel with Macros
• Creating & Recording a New Macro
• Running a Macro
• Suspending & running a Macro
• Deleting a Macro
• Saving a Workbook containing Macros
methodology
Lecture, discussion, individual/group exercise, Question and Answerwho must attend
Executives and non-executives who have an intermediate level of Excel knowledge.(KINDLY TAKE NOTE: PARTICIPANTS ARE REQUESTED TO BRING THEIR LAPTOP AND EQUIPPED WITH MICROSOFT EXCEL 2010 AND ABOVE VERSION)
LAPTOP IS AVAILABLE FOR RENTAL UPON REQUEST
investment fee / early bird by 14/03/2019
Standard: RM720.00 Nett PerDay / Not Available per participant
Group discount: Not Available / Not Available per participant for a minimum of 2 participants from the same company
(Fee inclusive of Refreshment, Buffet Lunch, Training Bag, Handouts, & Certificate of Completion) |
registration & payment
PROGRAMS ARE CLAIMABLE UNDER SBL SCHEME
Please register online through www.qdc.com.my or call 03-78054587 / 03-78044196. Our phone line operation hours are from 8:30am to 6:00pm from Monday to Friday.
All registration MUST be accompanied with PAYMENT.
Completed registration form with CHEQUES should be made in favor of "Quality Dynamics Consultancy Sdn Bhd" and sent to:
QUALITY DYNAMICS CONSULTANCY SDN BHD
No. 343, Block A, Kelana Centre Point, No.3 Jalan SS7/19,
Kelana Jaya, 47301 Petaling Jaya,
Selangor, Malaysia.
Tel : 03-78054587 / 03-78044196
Fax: 03-78054514
E-mail:
training@qdc.com.my
(
Wani/ Thang )
PSMB Reg: 620717-P
(FEE PAID IS NOT REFUNDABLE but replacement may be made at no additional cost. Quality Dynamics Consultancy Sdn Bhd reserves the right to cancel or reschedule the above course and shall inform participants of the changes)