Advanced Courses


Advanced Excel Formulas and Functions

There are seven units for Excel 365 Functions and Formulas

Unit 1: Dynamic Array Functions
1.1. Dynamic calculation using SEQUENCE and Spilled range operator
1.2. Flip the data using Transpose Function
1.3. Filter a range of data based on criteria using FILTER function
1.4. Sort the contents of a range using SORT and SORTBY functions
1.5. Get a list of unique values in a list

2.1.  IFS
2.2.  SWITCH
2.3.  IFERROR  

3.1. VLOOKUP/ HLOOKUP
3.2. INDEX
3.3. MATCH
3.3. XLOOKUP

4.1. Use Conditional Functions to Sum and Count

5.1.  Calculate Measures of Central Tendency

6.1. Extract data
6.2. Manipulating text
6.3. Remove extra spaces
6.4. Convert Text
6.5. Substitute Text

7.1. Dynamic date and time: TODAY, NOW
7.2. Extract month and year from date: MONTH, YEAR
7.3. Calculate Date Differences: DATEDIF

In this course, the participants will leverage on the new Excel 365 dynamic functions and formulas.  With Dynamic Arrays, the participants can extract unique values, sort and filter data, lookup multiple values, and build dynamic lists that instantly respond to new information.  They will work on various scenarios examples using 

  • XLOOKUP, a successor of VLOOKUP, helps user to look up a value vertically and horizontally and also to the left columns with insertions and deletions.
  • Use Statistical Function to calculate measures of central tendency.
  • IFS, SWITCH, CONCAT, TEXTJOIN, and other useful functions.

Course Code: MSO-EXCEL365-FF

Duration : Two Full Day

Time : 9:00 am to 5:00 pm

Special rates will be given to a group of above 5 participants.

Venue: Virtual Learning / In-House

 

Training Schedule:

Please contact us for the course fee and schedule.

Please click the link below to register.

Interested participants from the respective Ministries, HRMS agencies or ACE, please register with your Training Coordinator the Course Code, Course Title, Course Date and the Training Provider.

Microsoft Excel Table with Power Query, Power Pivot, Pivot Table and Chart

There are six units for Advanced Microsoft Excel with Power Query and Power Pivot.   

1.1.  Structured References
1.2.  Dynamic Ranges with Formula
1.3 Totals without Formulas
1.4  Create Dynamic Chart
1.5  Convert to Range

2.1.  Connect:   import data source, such as an Excel workbook, CSV, Text.
2.2.  Data Cleaning e.g. Replace, Calculated columns, Merge, Unpivot, etc
2.3.  Load   Complete your query, Load and Add to Data Model

3.1.   Add Multiple Tables into Data Model.
3.2.  Create Relationship
3.3.  Calculate using DAX
3.4   Create Pivot Table from PowerPivot

4.1.  Customize Pivot Table
4.2.  Change Field Summary
4.3.  Group and Ungroup Pivot Table
4.4    Custom Calculations
4.5.   Create Pivot Charts

In this course, the participants will work on the following powerful tools to create dashboard:

  • Harness the Power of Tables.
  • Use Power Query to Import External Data Source and Shape the data
  • Load your query into Excel to create Pivot Tables
  • Merge Data from Multiple Tables Without Using VLOOKUP
  • Data Modeling Using PowerPivot

Course Code: MSO-EXCEL365-FF

Duration : One Full Day

Time : 9:00 am to 6:00 pm

Special rates will be given to a group of above 5 participants.

Venue: Virtual Learning / In-House

 

Training Schedule:

Please contact us for the course fee and schedule.

Interested participants must be an Excel users that use Excel frequently.

Please click the link below to register.

Interested participants from the respective Ministries, HRMS agencies or ACE, please register with your Training Coordinator the Course Code, Course Title, Course Date and the Training Provider.

Data Analysis and Building Interactive Dashboard Using Power Bi Desktop

You will work on case studies using Power Bi to build data models i.e. clean and combine different data sources effectively, and create visualizations for analysis.

There are five units in analyzing and building interactive dashboard using Power Bi Desktop.

At the end of the course, participants will be able to:

  • connect to different data sources
  • extract, profile, clean, transform and model the data
  • build a relational data model using Power Query and DAX
  • create charts and graphs using different visualization techniques.
  • design and create reports and interactive dashboards to get meaningful insights

Course Code: PowerBi

Duration : Two Full Day

Time : 9:00 am to 6:00 pm

Special rates will be given to a group of above 5 participants.

Venue: Virtual Learning / In-House

 

Training Schedule:

Please contact us for the course fee and schedule.

Please click the link below to register.

Interested participants from the respective Ministries, HRMS agencies or ACE, please register with your Training Coordinator the Course Code, Course Title, Course Date and the Training Provider.

Contact Us