Course Overview:
In this training you will effectively learn how to use Excel's data tools, formulas and visual tool to crunch business data and to build displays. The trainees will learn the advanced Excel skills that apply to creating smart adaptive Excel business tools. This will cover topics on how to create advanced logic that allows these tools to operate autonomously and adapt to changing data. It also provides an introduction to macros using VBA-Visual Basic for Application in MS Excel, fundamentals of automating frequently repeated tasks.
Course Outline:
Section 1: Advanced Data Analysis Tools
Topic B: Data Validation Techniques
Topic C: Using Data Consolidation
Topic D: Using Goal Seek
Topic E: Using the Scenario Manager
Topic F: Using Solver
Topic G: Using Advanced Filter
Topic H: Importing External Data
Topic I: Using Pivot Table
Topic J: Using Pivot Chart and the appropriate Chart Types for reporting needs.
Section 2: Advanced Formulas
Topic A: Three Types of Formula
1. Standard Formula
2. Array Formula
3. 3-D Reference Formula
Topic B: Nesting Functions
Topic C: Advance Formulas
Topic C.1: Date & Time
• Datevalue
• Edate
• Eomonth
• Networkdays
• Networkdays.i
• Ntl
• Timevalue
• Weekday
• Weeknum
• Workday
• Workday.intl
Topic C.2: Statistical
• Large
• Small
• Max
• Min
• Maxa
• Mina
• Stdev.p
• Stdev.s
• Stdeva
• Stdevpa
• Trend
• Var.p
Topic C.3: Database
• Daverage
• Dcount
• Dcounta
• Dget
• Dmax
• Dmin
• Dproduct
• Dsum
• Dstdev
• Dstdevp
• Dvar
• Dvarp
Schedule:
February 4-5, 2021
Thursday-Friday
9AM-12NN, 2PM-5PM
