TRAINING DETAILS

Microsoft Excel - 2019 Advanced Level

Microsoft Excel - 2019 Advanced Level

Overview of this Training:
Microsoft Excel is widely used to prepare various types of analytical interactive reports for the top management who are at the planning stage. The latest buzzword in the world of Excel is Dashboard Reporting. A dashboard report is a management tool that measures and presents critical data on the key business performance areas in a summarized easy-to-read format. To prepare a smart dashboard report, you have to use various form controls, appropriate charts, functions to fetch summary data, various formatting and few tricks.

This project-based professional workshop will help you through various functions, tools, tips and tricks to boost up your Excel productivity and you will learn how to map and handle big data in Excel that will work for you and generate the interactive reports describing the core information within seconds professionally.

Learning Methodologies

  • 100% PRACTICAL with trainer’s predesigned working files
  • Completely interactive and participative
  • Project based examples and workings
  • Training lecture sheet that will be interactively connected with working data
  • Problem solving and Brainstorming
  • Open discussion (Q & A session)

Who Should Attend?
Spreadsheet authors, Excel developers, regular Excel users and all kinds of analysts who want to gain skills in designing visual reports. Those who want to start getting creative with an interest in learning about dynamic charts and visually appealing data will benefit from this course

Training Contents

  • Cell Reference (Absolute Vs Relative):
    • Techniques To Use $ Sign In Formula
    • Different Types Of Use Of References (A4, $A4, A$4, $A$4)
    • Managing Cell Reference With Complex Formula In Easy Rule
  • Use Essential Functions For Restructuring Usable Data Table
    • Describing The New Functions In Excel 2019
    • Joining Multiple Cells
    • Extracting The Required Portion Of A Cell Value
    • Removing Unwanted Extra Spaces
    • Convert A Cell Text To Your Required Case
    • Converting A Text Number To A Computable Pure Number Or Date
    • Rounding A Cell Value In Different Ways
    • Join A Text With Formatted Number Or Date.
    • If & Ifs() Function
  • Lookup & References
    • General Vlookup & Hlookup Function
    • Complex Uses Of Vlookup/Hlookup
    • Match Function And Index Function
    • Combination Of Match And Index Function
  • Advanced Conditional Formatting
    • Using Multiple Conditional Formatting In A Range
    • Using Data Bars, Color Scales And Icon Set For Great Presentation
    • Advanced Formula Based Conditional Formatting
    • Aging Monitoring Using Conditional Formatting
  • Convert Number In Words Automatically
    • Procedure To Convert A Number Into Words
  • Reporting Techniques Using Pivottables, Pivotchart And Slicers
    • Describing The New Features In Excel 2019 Pivottable
    • Data Mapping To Prepare A Pivottable
    • Various Types Of Report Layout-Report In Compact Form, Report In Tabular Form
    • Customizing Subtotal At Any Row Label Data And Subtotal Category
    • Changing Pivottable Data Source
    • Inserting A Calculated Field And Calculated Item
    • Drill-Down To The Pivot Data
    • Grouping/Ungrouping Data By Year, Quarter, Month, Day
    • Sorting And Filtering Techniques In Pivottable Data
    • Slicer In Pivottable-Creating, Changing And Formatting
    • Make Your Pivottable Dynamic Using Slicer
    • Creating Pivotchart From Existing Workbook Data
    • Automated Dashboard Reporting Techniques Using Pivottable
  • Powerpivot – Use The Most Powerful Feature In Excel (Bi Tool)
    • What Is Powerpivot?
    • Activation Powerpivot In Ms Excel-2010, 2013, 2016 And 2019
    • Clear Concept About Excel Table
    • Building Relationships Among The Tables
    • Developing Data Model From Different Data Sources
    • Creating Report Using Powerpivot Linking With Excel
    • Creating Report Using Powerpivot Linking With Other External Sources (From Access/Sql Server)
    • Inserting A Calculated Column Using Data Analysis Expression (Dax)
    • Techniques To Use Fields And Slicers From Powerpivot Field List
  • Data Summarization & Aggregation Techniques From Large Data
    • New Summarization Functions In Excel 2019
    • Data Summarizing From A Large Dataset Using Functions
    • Countifs, Sumifs And Averageifs Functions
    • Use These Functions With Multiple Criteria And Conditions
    • Data Summarizing Using Indirect Linking
  • Data Sorting & Filtering
    • Multi-Level Data Sorting & Filtering Using Multiple Customizing Conditions
    • Finding Unique Records In A Range And Paste At Another Place
    • Removing Duplicates At The Same Range
    • Customized Filtering Using If () Condition
  • Date Functions:
    • Do You Know How Many Mistakes You Are Doing Every Day For Date??
    • Date Formatting Using Formula And Shortcut
    • Presenting Day Name And Month Name Of A Date
    • Combining Date With A Text (Report Date: 31/Dec/2014)
    • Calculating Future Date Adding 3 Years 8 Months And 18 Days
    • Calculating Date Difference Between Two Dates (Difference Of Days, Months And Years)
    • Preparing Age Calculator (Example: Your Age Is 25 Years 05 Months And 18 Days)
  • Error Handling And Cleaning Reports
    • Type Of Errors And When It Occurs
    • Circular Reference And Carefulness For Errors!
    • Error Handling Using Iserror, Iferror And Isna Function
    • Magic Tips To Find Error Cells And Cleaning
    • Formula Tracing And Debugging Techniques
  • Data Validation And File Protection
    • Protect Your Workbook So That It Can Be Used By Anyone With A Limited Use Of Excel
    • Locking And Protecting Cells
    • Restriction Incorrect Data Entry With Data Validations
    • Creating List With Static Values Or A Dynamic Range
    • Creating Error Validation Messages
    • Create Your Workbook Password Protected
  • Creating Chart And Visual Presentation
    • New Charts In Excel 2019
    • Data Visualization Using Sparklines
    • Choosing The Perfect Chart For Your Data
    • Creating Various Types Of Charts-Column, Bar, Pie, Line Etc.
    • Customize Your Chart’s Labels, Axes And Background
    • Chart For Target-Achievement Analysis
    • Chart For Main Group-Sub Group Analysis
    • Waterfall Chart And Funnel Chart
    • Geographical Map Chart
  • Macro & Vba: Automate Your Excel Action Or Report And Save Time
    • Overview Of Macro In Excel Step By Step
    • Building Macro Without Having Any Programming Knowledge
    • Macro Settings And Security
    • Trust Center And Trust Center Settings
    • Automation Techniques Using Macro
    • Creating Macro To Generate Report Using Criteria
    • Carefulness Of Macros!
  • Problem Solving Session
    • Share Your Excel Problem
    • The Facilitator Will Share Some Critical Real Cases
Md. Nazmul Muneer

Md. Nazmul Muneer

Excel Automation Expert & Professional Trainer


Md. Nazmul Muneer is one of the key contributors in the Accounts & Finance department of Beximco Pharmaceuticals Ltd. with 10 years job experience and has previous work experience in Energypac Power Generation Ltd. Mr. Muneer also works as a freelance Excel automation consultant and Facilitator. In the last few years he has conducted more than 100 professional Excel training programs and educated in Excel more than 2,000 participants through effective training.

 

He is a regular Excel Facilitator of some leading professional training institutes in Bangladesh and also conducted many special training programs in different organizations and Universities.

 

He has also been providing Excel based consultation and technical services to different companies in the UK, USA and Australia on Excel Automation and developed more than 35 projects, helping them to create new processes, reports and streamlines those are already in live environment.

 

He is expertise in developing automated formula based Excel dashboard, VBA/Macro based Excel application, strategic business model, various problem solving calculators, integration between Excel and other databases, What-if Analysis model, data analysis dashboard using Business Intelligence (BI) tools, KPI automation etc.

 

Mr. Muneer has obtained educational experience in two different disciplines. He is a post graduate in Accounting and has a Diploma in Professional Software Engineering. These have enabled him to acquire vast IT knowledge on developing Database Software, Advanced Excel with VBA, Access with programming, VB, SQL server, Crystal report, IBM AS/400, ORACLE E-Business Suit as well as various accounting packages.



Date
April 26 & 27, 2019
Time
09:30 am - 05:30 pm
Duration
2 Days
Reg. Before
April 21, 2019
Vanue
NRB Jobs Training Center
NRB Jobs Training reserves the right to alter dates, content, venue and trainer.
Per Perticipant
BDT 7,000/- 


Early Bird Discount
 
(Excluding All Tax & VAT)

N/A