Excel Master - Advanced Level

Course details

Learning Outcome:

• Mastering Excel most important functions
  
• Analyzing Data and Alternative data sets
  
• Apply the key Excel functions to prepare data for analysis
  
• Apply advanced calculations 
  
• Create and customize pivot tables to reconcile and analyze accounts efficiently
  
• Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
  
• Creating Advanced charts and graphics
  
• Changing workbook Appearance

Workshop Content:

Advanced Calculations on Data

• Creating Formulas to calculate value with functions (CountIf, SumIf, HLookup, VLookup, Aggregate, Concatenate)
• Performing Advanced Date & Time Calculations.

Advanced Format Tools

• Creating workbook from Template
  
• Add Header, Footer, and Setup Pages
  
• Naming group of data
  
• Advanced formatting cells 
  
• Advanced page setup
  
• Conditional Formatting
  
• Linking to office documents from a workbook
  
• Creating Advanced Chart and Sparkline's
  
•  Protect workbook and worksheet with Password

Analysis Tools

• Conditional Formatting
  
• Using Data Validation to create a drop-down list
  
• Filtering and sorting in table with Slicers 
  
• Finding and correcting an error
  
• Using watch window tool

Reporting Tools

• Analyze data by Pivot table and Pivot chart
  
• Using function (Getpivotdata)
  
• Using Consolidate tool
  
• Key functions to prepare data for pivot table reporting
  
• Creating and customising pivot tables
  
• Performing calculations within pivot tables