Certificate Course on: Advance Excel 1500

  • Duration: 30 Hours

  • Per Day: 1-2 Hours
Advance Excel

The Training session will be live *Recorded lectures will be available

Enjoy the learning with trainer led certification Course. This course covers creating Pivot Tables as well as using calculated fields and filters. You will learn to connect to external data sources such as Access and web queries. You will learn to use lookup functions and data tables to allow more sophisticated data conversion functions. This course will also show how to record and execute simple macros. This course is designed for students who need to use Pivot Tables and more advanced functions in Microsoft® Office Excel

Week Duration Topic Contents
Week 1 1-2 Hours Introduction
  • Introduction
  • The ROUND
  • Using ROUND Function
  • TEXT
  • Using TEXT
  • The LEN Function
  • The REPLACE Function
  • Using REPLACE And LEN
Week 1 1-2 Hours What-If Analysis
  • The IF Function
  • Using IF
  • More On IF
Week 1 1-2 Hours The CONCATENATE Function
  • Using LEFT, RIGHT & MID
  • The CONCATENATE Function
  • Using UPPER, LOWER & PROPER
  • The TRIM Function
  • Using VALUE
Week 1 1-2 Hours Working With Multiple Sheets
  • Working With Multiple Sheets
  • Drop Down
  • A Real World Scenario
  • Using COUNTIF & COUNTA
  • COUNT Together
Week 1 1-2 Hours Financial Solution
  • Relative & Absolute
  • Multiplying Text
  • The PMT Function
Week 2 1-2 Hours Introduction to Macros in Excel
  • Recording and Playing Macros
  • Copying and Deleting Macros
  • Visual Basic and Macros
Week 2 1-2 Hours Creating HLOOKUP and VLOOKUP Functions
  • Using VLOOKUP
  • Creating a VLOOKUP Invoice
  • VLOOKUP & IF
  • SUBTOTAL
  • Using HLOOKUP
Week 2 1-2 Hours Conditional Formatting
  • Conditional drop-down lists (data validation)
  • Conditional formatting based on formula
Week 2 1-2 Hours PivotTables
  • Getting Started with PivotTables
  • Displaying Data in a PivotTable
  • Formatting a PivotTable
  • Using the Classic PivotTable Layout
  • Slicers
Week 2 1-2 Hours Charting Pivoted Data
  • Introduction To Pivot Tables
  • Pivot Table Options
  • Pivot Charts
  • Goal Seek
Week 3 1-2 Hours Working on Google Sheets Google sheets
Week 3 1-2 Hours Conditional Logic
  • Logical Functions I: IF
  • Logical Functions II: AND, OR
  • Combining Logical Functions I: IF, AND, OR
  • Combining Logical Functions II: Nested IFs
  • Handling Errors: IFERROR, IFNA
Week 3 1-2 Hours Automating Lookups
  • Introduction to Lookups: CHOOSE
  • Approximate Matches: Range VLOOKUP
  • Exact Matches: Exact Match VLOOKUP
  • Finding a Position: MATCH
  • Dynamic Lookups: INDEX, MATCH
Week 3 1-2 Hours Dashboard Dashboarding
Week 3 1-2 Hours Conclusion Conclusion