Advanced Microsoft Excel

Advanced Microsoft Excel

Delve into complex functions like nested formulas, data analysis tools such as pivot tables, and advanced features like macros and VBA (Visual Basic for Applications) for automating tasks and manipulating data more efficiently. Additionally, you’ll explore advanced data visualization techniques, data validation, and scenario analysis, empowering you to handle intricate data sets and create sophisticated spreadsheets for various purposes.

“SpecCon’s Advanced Microsoft Excel course is called ‘advanced’ for a reason; it’s not for amateurs! It is jam-packed with over 60 different functions and formulas. It teaches you to combine formulas and enables to take these to a new level. Most importantly, the course enables you to learn and discover further functions for yourself”

Course Content

  • Formula basics
  • Types of formula errors
  • Fixed referencing
  • Logical test
  • IF formula
  • OR formula
  • AND function
  • IFERROR formula
 
  • VLOOKUP 
  • MATCH formula
  • Using the MATCH formula to populate the column index number 
  • INDEX
  • OFFSET
  • Using the VLOOKUP to fix data with mapping tables
  • Using a True or 1 in a Vlookup
  • The address formula
  • Pivot tables
  • Count using pivot tables and using pivot tables to generate lists
  • Updating values in a pivot table and adding multiple values
  • Using calculated fields in pivot tables
  • Changing the look and feel of pivot tables (totals, classic view, formatting)
  • Splitting pivot tables into multiple sheets and grouping data
  • Retrieving the field list when lost
  • Creating dashboards from pivot tables (Graphs and slicers)
  • The sumifs function
  • Recording macros and using macros to improve reporting
  • The  INDIRECTformula
  • FIND formula
  • CONCATENATE formula
  • LEFT, RIGHT and MID formulas
  • TRIM functions
  • PROPER/ LOWER and UPPER formulas 
  • SUBSTITUTE formula
  • VALUE formula 
  • LEN formula
  • Understanding dates and dynamic dates
  • Converting text to dates with formulas
  • The NETWORKINGDAYS formula 
  • The END OF MONTH formula 
  • Working with time
  • The TEXT formula and dates
  • PMT function 
  • IPMT function
  •  FV function
  • NPV function
  • IRR function
  • Using the find and replace option to edit formulas
  • Use conditional formatting to do reconciliations and find duplicates
  • Countif formula and using countif to number data 
  • Goalseek
  • Google sheets 
  • More Google sheets
  • IMPORTRANGE function
  • Google forms 
  • Manipulating a table 
  • Adding a new record
  • Modify the design of a database table
  • Sorting and searching for records in a database table
  • Non-accredited: Short course only  
  • Duration: 1h 30m
  • Delivery: Classroom/Online/Blended
  • Access Period: 12 Months 
SpecCon Short Course
Scroll to Top