Microsoft Excel Online Masterclass

Unlock the potential of spreadsheet. This is the ultimate course for beginners and intermediate users. 

Users: Beginners and intermediate

Number of hours: 21

Webinar will be conducted through ZOOM platform. If you don’t have it yet you can download here https://zoom.us/download (PC/laptop) . 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

BASIC AND USEFUL FEATURES OF EXCEL

• • Exploring Excel worksheet and workbook
  • Custom number formatting
  • Replicating and clearing of formats
  • Working and proper way of filtering data
  • Changing positions of cells
  • Automatic subtotal and grouping of data
  • Selecting group of cells based on contents
  • Changing the position of 
  • Techniques in printing worksheets
  • Set up restriction in encoding data
  • Creating dropdown list
  • Protecting the workbook and worksheet
• TYPES OF VALUE AND REFERENCE
  • Constant, relative and absolute reference
  • The flash fill data
  • Fixing cell reference from row and/or columns
  • Assigning name to cell reference or ranges
• FOUNDATION OF FUNCTIONS AND FORMULA
  • Basic functions and formula
  • Methods of combining cell data and value
  • The Insert Function
  • Common mistakes in syntax
• STATISTICAL / DATABASE FUNCTION
  • SUM, COUNT and COUNTA
  • AVERAGE, SUMPRODUCT, MAX, MIN, LARGE and SMALL
  • Working with SUBTOTAL function
  • Summation and count with criteria or condition
  • Summation and count with multiple criteria or condition
• LOOKUPS
  • VLOOKUP and HLOOKUP
  • Dynamic VLookup
  • INDEX-MATCH as dynamic lookup
  • Lookups with multiple critieria
• LOGICAL FUNCTIONS
  • Logical expression to test if value returns to true or false
  • IF statement with single condition
  • Nested IF when more than two possible conditions
  • IS function to test the data type of value
  • Multiple conditions in IF statement
  • Nested IF with multiple conditions
• TEXT FUNCTIONS
  • Format when combining date and numbers
  • Extract characters from text
  • Separate values into columns
  • Get the numbers of characters from text
  • Test the content of cell data
• DATE FUNCTION
  • Calculations in dates
  • Functions that return to current date and time
  • Extracting value from a date
  • Function that return to same date with different month
  • Function that return to end of the month and apply in sum nested function
  • Find the difference between date and/or time periods
  • Find the difference between periods excluding weekends and holidays
• ARRAY FORMULA
  • Why is array formula useful?
  • FREQUENCY Function – calculates how often values occur within range
  • Creating logical expression to total values
  • The AND/OR logic for summation/count
  • SUM Function with criteria or condition
  • SUMPRODUCT Function criteria or condition
  • Get the total value based on column header and row labels
• REFERENCE FUNCTIONS
  • CHOOSE – refer to values based on number of choices
  • INDIRECT – converting text to a reference.
  • Row/s and column/s functions
  • OFFSET function to locate the last nth record in database
• CONDITIONAL FORMATTING
  • Format based on scale, rank and content
  • Format if duplicate or unique values
• CHARTS AND GRAPH
  • Methods of inserting data in chart
  • Changing element, filter and format
• TABLES AND PIVOT TABLES
  • Features of tables
  • Pivot Table
  • Inserting Pivot Chart
  • Inserting Slicers
  • Pivot Table with OLAP, convert Pivot to ordinary cell with formula
• FORMULA-BASED DATA VALIDATION - Restricting value with condition
• FORMULA-BASED CONDITIONAL FORMATTING
• DATABASE FUNCTIONS WITH CRITERIA
  • DSUM, DCOUNT, DCOUNTA, DGET, DMAX, DMIN
• POWER QUERY
  • Merge and Append worksheet data or table
  • Import and consolidate external data source
  • Clean and transform database
• CONSOLIDATE DIFFERENT DATABASE OF TABLE USING RELATIONSHIPS
• CONSOLIDATE DIFFERENT SOURCES OF DATA USING POWER PIVOT
• ELEMENTS OF DASHBOARD
• WHAT IF-ANALYSIS  
  • Working with Goal Seek and Solver
  • Working with Scenario Manager
• TIPS AND TECHNIQUES
  • Simple approach to database function with multiple criteria using INDIRECT, formatted table and concatenation
  • Creating dependent dropdown list, where list will be dependent on the value of another cell
  • Dynamic dropdown list
  • Sum of unique values
• AUDITING FORMULA
  • Identiy the dependent and precedent cells
  • Resolving circular reference
  • Evaluating formula to check errors
• MAIL MERGE - automatically produce multiple documents