Advance Excel

Advance Excel

COURSE GOAL: This course is aimed at exposing participants to the use of Advanced Excel formulas and features in intensive data analyses. advanced knowledge of Excel means possessing the ability to us

course at a glance

  • Date : 11 Dec - 19 Jan 2022
  • No. of Classes/ Sessions : 15
  • Total Hours : 30
  • Last Date of Registration : 10 Dec 2021
  • Class Schedule :
    • Saturday - 6.00pm - 8.00pm
    • Monday - 6.00pm - 8.00pm
    • Wednesday - 6.00pm - 8.00pm
  • venue : 151/6,2nd Floor,Gazi Tower Panthapath Dhaka-1205. Contact No-01965501219. Kaizen IT Ltd.

Price: TK. 4,600
(Excluding VAT & TAX)
N/A

This Training is jointly organized by BITM & Kaizen IT Ltd

Training will be held in Kaizen IT Ltd

COURSE GOAL:

This course is aimed at exposing participants to the use of Advanced Excel formulas and features in intensive data analyses.

OBJECTIVES OF THE COURSE:
1. To teach participants the advanced formulas as well as how to use which formula for which occasion.
2. To equip participants with the knowledge on how to debug and audit the advanced formulas.
3. To explore the magic of analysis data using

Advanced Excel COURSE CONTENT:
The course is organized in modules and each module covers a reasonable content to enable participants connect and progress successively in the next modules.

Module 1: Basic Formulas and functions of advanced Excel

 Basic Formulas (Addition, Subtraction, Multiply, Division, average, average if, Max, Min, Percentage)
 Total Concept of Date & Time Formula
 Broad concept about cell reference
 Transpose Data in three way
 Sum Formula (Sum, Sum if, Sum Ifs)
 Data Filter
 Using Subtotal
 The Paste Special Function
 3D Sum
 Consolidating Data

Module 2: Data Validation
 Extended uses of Data Validation
 Working with validation formulae
 Other methods of tracking down invalid entries

Module 3:
Preparing your data for analysis
 Mastering lookup functions (INDEX, MATCH)
 Creating helper columns using DATE and TEXT functions
 Applying NESTED-IF, AND, OR to organize data
 Vlookup Formula in different way with creating Invoice.

Module 4:
Methods of Summarizing Data
 Using COUNTIFS
 Advanced uses of PIVOT-TABLE feature like Value Field Settings, Grouping Data and Slicers among others
 Identify Major Customers, Top Products, Top/Bottom Sales reps…LARGE, SMALL, MAX, MIN
 Advanced Range Names and Formula in Names
 Calculations and reporting in Power Pivot – an introduction to Data Analysis Expressions (DAX) Module 5: Report Visualization Techniques in Excel
 Dynamic charts (using CHOOSE & OFFSET functions) & Sparkline’s for trends
 Effectively using Conditional Formatting (formula-driven) for reporting
 Exchanging information with VB code
 Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)
 Report presentation with using DASHBOARD.

Module 6:
Decision Making with Excel
 Applications of Financial functions (Amortization table, FV, NPV, IRR, etc.);
 Sensitivity (“What-if”) analysis on models using Data Tables, Goal Seek, Scenarios; Reports.
 Using Excel to help you make decisions
 Create and manage alternative scenarios
 Make more profit or incur less expense by using Excel Solver to identify the best solution
 Use of External Data tools

Module 7:
Macro and Finalizing worksheet
 Understanding basic Macro
 Using Advanced Macro
 Protecting worksheet and workbook
 Sharing Worksheets to multiple user
 Data Encrypting and Finalizing Workbook

COURSE OUTCOMES:
At the end of the course, participants should be able to:
1. Apply advanced formulas to lay data in readiness for analysis
2. Use advanced techniques for report visualizations
3. Leverage on various methodologies of summarizing data

Curriculum

Advance Excel Advance Excel 30 Hrs

Tentative Class Start

11th December, 2021

Available Seat

10 / 20

who can join

This course is for those who know basic Excel work. Even then those who are interested in learning the course can do this course. Everyone should have knowledge on this course. So everyone can do this course.

Meet the Instructor