Training on Advanced Microsoft Excel

About the Course

Microsoft Excel is the most commonly used analytics, reporting and strategy software. Becoming an expert data analyst requires you to have mastery of advanced Excel skills. This course is aimed at taking your excel skills to the next level. You will be able to apply complex functions within Excel to manage, manipulate, analyze and visualize data.

Target Participants

This course is aimed at professionals who have basic to intermediate Excel skills and want to advance those skills.

Course duration

Online

7 Days

Classroom-based

5 Days

What you will learn

By the end of this training the participants will be able to:

• Master advanced formulas and functions
  
• Consolidate data, link and export data
  
• Create sophisticated outputs including tables, charts, and graph
  
• Use What-If Analysis tools such as Goal Seek, Solver and Scenarios
  
• Increase interactivity by automating spreadsheets with macros and VBA
  
• Turn raw data into ‘must make’ decisions using PivotTables and PowerPivots.
  

Course Outline

Data Validation

• Whole numbers and decimals
  
• Date and time validation
  
• Text length
  
• Excel data validation list (drop-down)
  
• Custom data validation rules
  

Referencing in Excel

• Absolute referencing
  
• Relative referencing
  
• Structured reference
  

Working with Structured tables

• Why you should use tables
  
• Elements of an Excel table
  
• Managing data in an Excel table
  
• Using structured references with Excel tables
  

Formulas and Functions

• VLOOKUP
  
• HLOOKUP
  
• INDEX and MATCH
  
• SUMPRODUCT
  
• SUMIF & SUMIFS
  
• COUNTIF & COUNTIFS,
  
• OFFSET & INDIRECT
  
• Concatenating Functions
  
• IF and nested IF Functions
  
• AND, OR, NOT Functions
  
• Date Formulas and Functions
  

What If Analysis

• Scenarios
  
• Custom Views
  
• Reports
  
• Goal Seek
  
• Data Tables
  

Pivot Tables and Pivot Charts

• Preparing data for use with Pivot Table
  
• Formatting a PivotTable
  
• Working with Calculated Fields
  
• Grouping Data
  
• Filters and Advanced Filter
  
• Advanced Data Field Settings
  
• Creating Pivot Chart
  
• Power Query
  

Excel Macros

• Recording Macros
  
• Copy a Macro from Workbook to Workbook
  
• Exercise: Recording a Macro
  
• Running Macros
  
• Editing Macros
  
• Adding Macros to the Quick Access Toolbar
  
• Managing Macro Security
  

Charts and Visualizations

• Preparing data for charts
  
• Choosing appropriate chart
  
• Creating Different Charts
  
• Dashboards
  

Working External Data

• Linking Workbooks
  
• Working with non-legacy databases
  
• Extracting Data from the Web
  
• Creating Collector Models
  

Training Approach

This course is delivered by our seasoned trainers who have vast experience as expert professionals in the respective fields of practice. The course is taught through a mix of practical activities, theory, group works and case studies.

Training manuals and additional reference materials are provided to the participants.

Certification

Upon successful completion of this course, participants will be issued with a certificate.

Tailor-Made Course

We can also do this as a tailor-made course to meet organization-wide needs. Contact us to find out more: [email protected]

Payment

The training fee covers tuition fees, learning materials, and training venue. Accommodation and airport transfer are arranged for our participants upon request.

Payment should be sent to our bank account before start of training and proof of payment sent to: [email protected]

Host or Publisher Devimpact Institute