Excel Online Master Class

MICROSOFT EXCEL MASTERCLASS 2.0

Unlock the potential of the spreadsheet. This is the ultimate course for beginners and intermediate users. This is an extended and comprehensive version of the popular MS Excel Masterclass.

Who can participate?: Those who have basic skills in Excel and/or those who want to improve skills in Excel

Level of topics: Basic to Advanced

Total Number of hours: 24 

Number of sessions: 12

Hours per session:  2 hours

Batch: 01

Schedule: November 7 - December 2, 2022 (Monday, Wednesday, Friday) | 8:00 PM - 10:00 PM

Webinar will be conducted through ZOOM platform You can also download the app through Playstore and Appstore in your mobile/tablet device. 

Requirements:

• Zoom and STABLE INTERNET. If you don't have stable internet you may opt to receive recorded session instead.
• Must have Microsoft Excel with versions  2010 / 2016 / 2019 / 365. If you have lower versions, kindly message us for assistance. 
• It is highly recommended that you run MS Excel during the session. 

INCLUSIONS:

• Live interactive training with Q& A
• Lifetime access to modules and learning materials
• Lifetime access to copy of recorded session of the training
• One month support for trainee in application of Excel
• Certificate of Completion

• TIME-SAVING TOOLS
  • Quick Access Toolbar
  • Useful Shortcuts
  • Flash Fill
  • Selecting a group of cells based on contents
  • Replicating and clearing formats
• MANAGING WORKSHEET DATA
  
  • Custom number formatting
  • Advanced Filter
  • Changing positions of cell values
  • Grouping of column or row data
  • Auto outline
  • Nested Subtotal
  • Techniques in printing worksheets
  • Set up restrictions in encoding data
  • Creating dropdown list
  • Protecting the workbook and worksheet
• TYPES OF VALUE AND REFERENCE
  • Constant, relative, and absolute reference
  • Fixing cell reference from row and/or columns
  • Assigning a name to cell reference or ranges
• STATISTICAL / DATABASE FUNCTION
  
  • AVERAGE, SUMPRODUCT, MAX, MIN, LARGE and SMALL
  • Working with the SUBTOTAL function
  • Working with AGGREGATE Function
  • Summation and count with criteria or condition
  • Summation and count with multiple criteria or condition
• CONCATENATION
  • Joining cell reference, number, string, and formula
• EXCEL TABLES
  • Features of Excel Tables
  • Working with Structured Reference
  • Insert Slicers
• LOOKUPS
  • VLOOKUP and HLOOKUP
  • INDEX-MATCH as dynamic lookup
  • Two-way Lookup
  • Lookups with multiple criteria
  • XLOOKUP
• LOGICAL FUNCTIONS
  • Logical expression to test if value returns to true or false
  • IS function to try the data type of value
  • IF statement with a single condition
  • Nested IF when more than two possible conditions
  • IFS Function
  • Multiple conditions in IF statement
• TEXT FUNCTIONS
  • Format when combining date and numbers
  • Extract characters from the text
  • Separate values into columns
  • Get the number of characters from the text
  • Test the content of cell data
• DATE FUNCTION
  
  • Functions that return to the current date and time
  • Extracting value from a date
  • Dynamic Date 
  • A function that returns to the same date with a different month
  • A function that returns to the end of the month
  • Find the date adding or deducting the number of days  excluding weekends and holidays
  • Find the number of days between two dates excluding weekends and holidays
    
  • Find the difference between two dates or exclude weekends and holidays
• ARRAY FORMULA
  • Why is the array formula useful?
  • Calculates how often values occur within the range
  • Condition to get the total values or count
  • The AND/OR logic for summation/count
  • SUMPRODUCT Function criteria or condition
  • Get the total value based on the column header and row labels
• DYNAMIC ARRAY FORMULA
  
  • Understanding Dynamic Array
  • Working with Unique Function
  • Working with Sort  Function
  • Working with Filter Function
• REFERENCE FUNCTIONS
  • CHOOSE – refer to values based on the number of choices
  • INDIRECT – converting text to a reference.
• CONDITIONAL FORMATTING
  • Format based on scale, rank, and content
  • Format if duplicate or unique values
• CHARTS AND GRAPH
  • Methods of inserting data into a chart
  • Changing element, filter, and format
•  PIVOT TABLES AND PIVOT CHART
  • Inserting PivotTable
  • Insert Row and Column
  • Compound labels
  • Dynamic source data for PivotTable
  • Inserting PivotChart
  • Inserting Slicers
• FORMULA-BASED DATA VALIDATION 
  • Restrict data if number or text only
  • Restrict data if data from another cell is blank
• FORMULA-BASED CONDITIONAL FORMATTING
  • Format entire rows/columns based on cell value
  • Format cell based on value from another cell
• POWER QUERY
  
  • Import and consolidate external data source
• POWER QUERY
  • Combining different tables
• WHAT IF-ANALYSIS 
  • Working with Goal Seek and Solver
• APPLICATION OF  EXCEL TOOLS
  
  • Applying INDIRECT in structured reference
  • Dynamic dropdown list
  • Assigning name for the formula
  • Dynamic subtotal with name range
  • Creating dashboard navigation
• AUDITING FORMULA
  • Identify the dependent and precedent cells
  • Resolving circular reference
  • Evaluating formula to check errors
• MAIL MERGE - automatically produce multiple documents 
• BASIC MACRO
  • Understanding automation in Excel
  • Enable Developer tab
  • Recording Macro
  • Absolute vs Relative
  • Assigning Macro
  • Macro Security
  • Editing Macro