Microsoft Excel
by ICN LEARNING PARTNER PLT SKU
Brand: No Brand
Last update: Mar 25, 2022
| Wholesale Price Available | |
|---|---|
| Buy 25 or more each | |
| MYR 7,000.00 each | |
| Guide |
|---|
| 1 each : 1 Unit |
|
|
Advanced Microsoft Excel 365 ProPlus
About this course
Students will build upon the essential Microsoft Office Excel 365 Pro Plus knowledge and skills already acquired and learn to create advanced workbooks and worksheets, including advanced formulas, tables and data analysing tools.
Audience profile
Students who have basic skills with Microsoft Excel 365 Pro Plus who want to learn more advanced skills or students who want to learn the topics covered in this course in the interface.
At course completion
After completing this course, students will be able to
- Manage big database effectively.
- Apply proper advanced formula and function.
- Troubleshooting complex errors
- Link and Embed Data from other sources.
- Working with Complex Analysis
Prerequisite
- Basic – Intermediate level of Excel.
Course Duration
- 2 Days (Online)
Course Outline
Lesson 1: Lookup, Reference, and Information Functions
- The Basic Lookup
- VLOOKUP(): Vertical Lookups
- HLOOKUP(): Horizontal Lookups
- XLOOKUP() vertical and horizontal lookups
- Advanced Lookups
- MATCH(): Finding the Position of Items in a Range
- INDEX(): Retrieving the Value from a Cell
- Performing a “Left Lookup”
- Performing a Double Lookup
- Double Match Function
- Information Functions
- The “IS” Functions: Checking the Value Inside a Cell
- Tutorial: Generating Invoices from a Product Catalogue
Lesson 2: Working with Logical Functions - Advanced Formula
Writing and Troubleshooting
-
- Working with Logical IF / IFS
- Working with Multiple Criteria in IF ( And / Or) Functions
- Working with Nested IF Functions
- Replacing Nested IF With Lookup
- MAXIFS
- MINIFS
- SWITCH
- UNIQUE
- #SPILL
Lesson 3: Analysing Data with Conditional Formatting
- Applying Conditional Formatting
- Working with Cell Rules
- Working with Top / Bottom Rules
- Working with Colour Scales and Data Bar Rules
- Working with Icons
- Working with Formula in Conditional Formatting
Lesson 4: Working with Complex Data Transformation
- The Important Reviews
- Power Query Editor
- E.T.L
- Combine and Append Data
- Combine and Merge Data
- Working with Folder
Lesson 5: Analysing Data Using Pivot Table
- Data Analytics with Pivot Table
- Updating and modifying Pivot Tables
- Working with Pivot Table Caches
- Creating Monthly Reports using Dates and Time Values
- Applying Pivot Table Designs and Layouts
- Sorting and Filtering Data
- Working with calculated fields in Pivot Tables
- Working with Calculated Items in Pivot Tables
Lesson 6: Data Analysis and Dashboard Reporting in Excel
- Simple Data Storytelling in Excel Using Pivot Tables
- Working with Multiple Pivot Tables in a Single Page
- Manipulating Pivot Charts and Sparkline in Dashboards
- Working with Speedo-Meter Chart in Dashboard
Lesson 7: working with Power Pivot
- Data modelling in Excel
Lesson 8 : Automating Tasks with Macros
- Macros 101
- Macro-Free and Macro-Enabled Workbooks
- The Macro Recorder
- Relative and Absolute Recording
- Where Macros Live
- Recording a macro
- Playing a Macro
- Macro Security
- Trusted Location
- Placing a Macro on the Quick Access Toolbar
- Attaching a macro to a button inside a worksheet.
Lesson 9: Working in the Cloud with Office 365
- What is the Cloud?
- What is Office 365?
- Choosing the Right Plan
- Signing in with Microsoft Accounts
- Exploring the Interface
- Understanding OneDrive
- Office Online Overview
- Opening Files
- Saving Files
Lab Assessments
Pre and Post Test
Group Discussion
Test
Quizes
Online Sharing After Training
Questions & Answers (0)
Delivery Information
Standard Delivery
5-7 working days