Advanced Microsoft Excel - Data Analysis & Dashboard Reporting
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
- Ice Breaking and Discussion for Dashboard Reporting
- What is Dashboard report in Excel and requirement?
- What to do and what not to do for developing Dashboard report
- Data Matching & Lookups for Preparing Dashboard’s Raw Data
- VLOOKUP & HLOOKUP function
- Complex uses of VLOOKUP/HLOOKUP
- MATCH & INDEX function
- Combination of MATCH and INDEX function
- Logical Function & Formula Nesting
- IF() condition with operators
- Using IF() with formula
- Naming in Excel
- Defining names of a cell/range/formula
- Using name in a formula
- Data Summarization from Large Data
- Data summarizing using multiple conditions
- Basic & advanced SUMPRODUCT function
- COUNTIFS, SUMIFS and AVERAGEIFS functions
- Data summarizing using INDIRECT linking
- Techniques to Validate Raw Data
- Locking and protecting cells/area
- Restriction for incorrect data entry/import with data validations
- Creating error validation messages
- PivotTable, PivotChart and Slicers
- Prepare your fist dynamic PivotTable
- Row Labels, column labels, values area, report filter
- Examples of various types of PivotTable in different angles
- Various types of report layout-report in compact form, report in tabular form
- Changing PivotTable data source
- Make your PivotTable’s data source dynamic
- Inserting a dynamic calculated field and calculated item
- Sorting and filtering techniques in PivotTable data
- Make your PivotTable report professional looking your
- Preparing a Dashboard using PivotTable, PivotChart and Slicers within 5 Minutes
- Make Protected Your Dashboard
- Create workbook/worksheet password protected
- Assign users to use your Excel workbook/sheet within a limited area
- PowerPivot – Use Excel’s Built-in Self Service BI Tool
- Activation PowerPivot in MS Excel-2010, 2013 and 2016
- Building relationships among the tables
- Developing Data Model from different data sources
- Creating report using PowerPivot linking other databases (SQL/Access….)
- Inserting a calculated column using Data Analysis Expression (DAX)
- Use fields and slicers from PowerPivot Field List
- Using Objects & Form Controls to Control Your Dashboard
- Get introduced with Form Controls
- Use Button, Check Box, Option Button, List Box, Combo Box
- Where will you use form controls?
- Tabular and graphical data interaction using form controls
- Using Charts & Graphs in Dashboard
- Data visualization using Sparklines
- Creating various types of Charts-Column, Bar, Pie, Line etc.
- Special charts for Group-Sub group analysis
- Special charts for Target-Achievement analysis
- Customize the Chart Elements
- Control your chart by form controls (drop-down, option button etc.)
- Applying Advanced Conditional Formatting on Dashboard
- Multiple Conditional Formatting-manage rule
- Using Data Bars and icons for great presentation
- Customizing Data Bars and Icon set’s parameter
- Use Formula in Conditional Formatting
- Macro & VBA: Automate Your Excel Action or Report
- Overview of macro in Excel step by step
- Macro settings and security
- Trust center and trust center settings
- Automation techniques using macro
- Carefulness of macros!
- Building your first macro without having any programming knowledge
- Consolidate data from all excel files in a Folder using VBA/Macro
- Exclusive Project Session
- To Develop a Complete Live Interactive Dashboard Report
- Open Discussion Session
- Problem Solving Session
- Share your Excel problem
- The facilitator will share some common problems
- Brainstorming and open discussion
Md. Nazmul Muneer
Microsoft Certified Trainer (MCT)
Md. Nazmul Muneer is a Microsoft Certified Professional “Microsoft Excel (Excel and Excel 2019)” and also obtained the prestigious Certificate from Microsoft “Microsoft Certified Trainer (MCT)”.
Md. Nazmul Muneer is one of the key contributors in the Accounts & Finance department of Beximco Pharmaceuticals Ltd. with 12+ 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 300 professional Excel training programs in many national and multinational corporate houses and educated in Excel more than 7,000 participants through effective training.
He is a regular Excel Facilitator and online course author of few leading training institutes and platforms. He also conducted Excel presentation in few educational institutes including Dhaka University, Jagannath University, United University, ICMAB, ICAB and more. He has also conducted exclusive corporate professional training programs for many Bangladeshi and MNCs organizations.
He is expertise in developing automated formula-based Excel dashboard, VBA/Macro based Excel application, strategic business model, various problem-solving calculators, 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 functional and implementation, etc.
|
Date December 05 - 17, 2019
|
Time 07:00 pm - 09:30 pm |
|
Duration 6 Days
|
Reg. Before December 2, 2019 |
|
Vanue NRB Jobs Training Center
|
|
BITM