Advance Excel Workshop

Key Learning of Advance Excel Training Program

  • 1. Warm Up Exercise
  • 2. Data Look Up
  • 3. Data Analysis and Filtration
  • 4. Statistical Function and Essential Formulas
  • 5. MIS Reporting -Pivot Table | Subtotal
  • 6. What If & Sensitivity Analysis
  • 7. Dashboard Techniques
  • 8. Creating Special Charts
  • 9. Data | Time Functions
  • 10. Macros
  • 11. Financial Mathematics Through Excel
  • 12. Data Cleanup & Extraction

For : U/G & P/G Students, Professionals, MIS Executives, CA, CS, Engineers, MBA

Duration: Alternate Day Classes 24 hours program, 1 hours per day

Key Learning: Understanding Adv. Excel Tools, Analyze Data, Prepare MIS Report

Fees: INR 4500 + 18% GST per participant

Batch Size 16 students

Note: 100% practical case studies | Embedded Tips-n-Tricks | Excel Version 2007 / 2010 / 2013 / 2016 / 2019 / 2021 Certified Trainers with extensive industry experience kindly bring your laptop along with you

Contact Us

Content Of the Program

MACROS

Goal Seek Why and How are they used Recording and Running Macros

WHAT- IF & SENSITIVITY ANALYSIS

Goal Seek Scenario Manager Data Table

MIS REPORTING - PIVOT TABLE / SUBTOTAL

Creating customized Pivot Table, Applications with % Computation, Grouping How to use Subtotal Formula

LOGICAL FUNCTIONS

Logical formulas - IF, And, Or, Not, Nested IF Logical formulas II - Iserror, Isblank, Isnumber, Istext, Iferror Conditional Formatting

CREATING SPECIAL CHARTS

Customization of Charts & Basic Concepts Basic Charts - Bar, Column, Pie Special Charts - Thermometer Charts, Multi-Axis Charts, Exploded Pie Charts Clip art, Pictures, Smart Art & Shapes

STATISTICAL FUNCTIONS & ESSENTIAL FORMULAS

Sum, Autosum, Max, Min, Average, Count Product, Sumproduct, Power, Round, Abs, Sqrt SumIifs, Countifs, Averageifs Concatenate, Transpose

DATA LOOKUP

Concepts, Issues & Applications - VLookup, HLookup, 2-way Lookup - Index with Match

DATE / TIME FUNCTIONS

Dates - Concepts, Issues & Related Formulas Day, Month, Year, Date, Edate, Eomonth

DATA ANALYSIS & FILTERATION

Sort - Custom, Multi-level Sort, Color Sorting Data Filter, Differential Filter & Advanced Filter

WARM-UP EXERCISES

Important Keyboard Shortcuts & Data Navigation Format Painter, Auto Fill options Paste Special (Advance) Go To (Special) Toggling - Absolute ($) & Relative referencing Creating shortcuts with the quick access toolbar Operator precedence

DASHBOARD TECHNIQUES

Grouping, Activate - Deactivate Gridlines Drop Down List - Data Validation Cell - Range Naming Form Controls - Spin Button & Scroll Bar Freeze - Unfreeze panes Hide - Unhide Columns & Rows Hyper Linking

FINANCIAL MATHEMATICS THROUGH EXCEL

Fv, Pv, Rate, Nper, Pmt Npv, XNpv, IRR, XIRR, Perpetuity, Inflation & Real Rate of Return Effective Rate (AER) vs Nominal (APR) Build Loan Amortization Table, Calculate EMI, IPMT, PPMT, CUMIPMT, CUMPRINC Bond Valuation : Calculate Bond Price & Yield Create Corpus after adjusting Inflation

DATA CLEAN-UP & EXTRACTION

Data Validation - Text, Number, Dates Text to Columns - Applications & Tricks Text Formulas & deal with Wildcard Characters Removing Duplicate values Comments writing & tricks Data Protection & Password Security