| 03 days – (Office 365 – Advanced Excel 2016 – 24 hrs) -Perform Advanced Spreadsheet
Functions (ICDL Certification)-[TGS-2018502696] |
Course Duration
24 Hours
Target Audience
For beginner and professional who in need of Advanced Spreadsheets in order to produce higher quality management information.
Pre-requisites
Need to possess basic knowledge of Office 365 Excel 2016
Certification
Successful trainees will receive the internationally recognized ICDL Certificate upon completion of the course. This certificate is administered by ICDL Singapore. This is a joint certification between SSG and ICDL Singapore. The student must take 60 minutes of online assessment at the end of the course.
Entry Requirement
- Primary education without PSLE/PSPE Certificate or their equivalent
ICDL Advanced Spreadsheets is a high-level certification program that presents candidates with the opportunity to bring their spreadsheet skills to an expert level. This allows them to use the full potential of the spreadsheet application to produce higher quality management information.
Individuals with ICDL Advanced Spreadsheets certified skills can:
- Produce higher quality information
- Pinpoint key information quicker and more easily
- Provide more sharply-defined analysis
- Produce more sophisticated reports
- Use advanced editing, data handling, functions, and analysis features
- Use macros within the spreadsheets application
| SECTION |
DESCRIPTION |
| Section 1: Introduction |
- Spreadsheet Design
- Identify the Different Techniques to Use Audience
|
| Section 2: Formatting |
- Cell Style, Table Style
- Conditional Formatting Move Worksheet to another Workbook, Hide, Unhide
- Special Copy – Multiply, Add, Transpose
- Flash Fill
- Spilt Bar
|
| Section 3: Manage Formulas |
- Relative, Absolute, Mixed Cell References
- Show/Hide Formulas
- Custom Number Format – placeholder
- Date and Time Functions, understand Serial Number
- NetworkDays.Intl, WorDays.Intl, Exclude Holidays
- Vlookup and Hlookup
- Index and Match- a better lookup
- Mathematical and Statistical functions – CountA, Round
- Financial Functions – PMT, FV
- Text Functions – Days, TextJoin, Concat
- Logical Functions – If, Nested If, Or and And
- Create 3D references
|
| Section 4: Charting |
- Column, Bar, Combination Chart
- Secondary Axis, Add image to chart, chart elements
- Standalone Chart, Add Image, Change Scales
|
| Section 5: Pivot Table |
- Characteristics of a Pivot Table, Anatomy
- Pivot Tools, Drag and Drop, Refresh, Filter
- Group and Ungroup
- Pivot Table Report Layout
- Subtotal and Grand Total
- Pivot with Filter Pages – populate
- Dates Grouping
- Pivot Chart and Slicers
- Excel Slicers
- Clear Slice Filter
- Resize a slicer, Lock slicer, Disconnect slicer, remove
- Slicer settings, Style
- Pivot Table with 2nd Details
- Connect slicer to multiple pivot tables
- Create DashBoard
|
| Section 6: Sorting and Filtering |
- Simple Sort, Multiple Sort
- Sort with Custom List
|
| Section 7: Excel Table |
- Understand Elements of a Table
- Sizing handle, Convert Table to Range
- Sorting and Filtering in Excel Table
- Text, Number, Date Filters
|
| Section 8: Advanced Filter |
- Advanced Filter with 2 conditions
- With Not Equal to conditions
|
| Section 9: Database Functions |
- DSum, DAverage,
- DMax, DMin,
- DCount, DCountA
|
| Section 10: What-If-Analysis |
- One-Input, Two-Input Table
- Scenarios
- Use, Edit and Delete Scenarios
- Create Scenario Summary Reports
|
| Section 11: Group and Ungroup Data |
- Group Manually
- Group using Auto Outline
- Ungroup all using Clear Outline
|
| Section 12: SubTotal |
- Create Subtotal
- Collapse and Expand Subtotal
|
| Section 13: Data Validation |
- Direct Entry
- Refer to a List
- Refer to Range Name
- Circle Invalid data
- Clear All Validation
|
| Section 14: Auditing Data |
- Evaluate Formulas
- Audit data – Trace Precedents and Dependents
- Error Checking
- Troubleshoot Excel Errors
- Show Watch Window
- Circular References
|
| Section 15: Manage Name Ranges |
- Name Manager
- Define New Name
- Apply Name
- Create Range Names from NameBox
- Delete Range Name
|
| Section 16: Templates |
- Viewing Templates
- Creating a Template from a Workbook
|
| Section 17: Linking |
- HyperLinks, Linking Objects, Embedding ObjectsNaming Convention, Update and Break a Link
- Importing delimited data into a spreadsheet
|
| Section 18: Macros Automation Code |
- Recording a Macro
- Assign macro to a toolbar
- Stop Macro
|
| Section 19: Cell Comment |
- Add, Hide, Show, Delete Comment
|
| Section 20: Track Changes |
- Turn on Track Changes
- Accepting and Rejecting Track Changes
- Compare and Merge
|
| Section 21: Protection |
- Protecting a Worksheet
- unlock a range only, the rest as locked
- Lock a range only, the rest as unlocked
- Hiding a formula
- Workbook Protection
|
