01934-453979 info@kaizenitbd.com
register

login now

forgot password?

register now

Advanced Excel Online and Offline Course

course

COURSE OVERVIEW

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

COURSE MODULE

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 analysing 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 Formatting of excel sheet
 Basic Formulas (Addition, Subtraction, Multiply, Division, average, average if, Max, Min,
Percentage)
 Create Custom series
 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
 Joining text in various way
 Text to column
 Direct Reference.
ADVANCED EXCEL COURSE OUTLINE
Shahnaz Excel Pathshala
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.
 Offset & Indirect formula
 Data Transpose with various way
Module 4: Methods of Summarizing Data
 Using COUNT, CountA, IF, Ifs, and blank formula
 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 Advanced 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
Some Practical project that will be done by this course:
1. Employee Attendance Sheet
2. Sales report
3. Salary sheet
4. Mark sheet
5. Electricity bill
6. Invoice
7. Data entry in different sheet by Macro
8. Inventory maintain
9. Sales dashboard
10.KPI Dashboard
11.Banking Financial Calculation
12. Solve ABC Company by multiple formulas.
* Broad concept of Google sheet, Advanced use of google
sheet (find some different formula in google sheet from
excel)
* Some exclusive Tips and Tricks.
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

COURSE DURATION

Course Free: 6000 TK

Course Duration: 3 months

Total Hours: 30 hours

( Weekly 3 Class, Every Class 2 Hours )

COURSE PREREQUISITES

Basic knowledge of computer operating.

Basic knowledge of internet browsing.

>