It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change The Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change The Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change The Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change The Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change The Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change the Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change the Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change the Way of Your Life
It's all to do with the training: you can do a lot if you're properly trained.
An Institution That Can Change the Way of Your Life

MS Excel - Advanced Data Analysis and Dashboard Reporting

This training is jointly organized by BITM & Leads Training & Consulting Ltd.

 

Microsoft Excel is the most essential and powerful application of Microsoft Office package. It is widely used to prepare various types of analytical interactive reports for the top management who are at the planning stage. Sometimes you may struggle and kill huge times to prepare the reports to fulfill your management’s requirements.

This 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 professionally. You will also learn how to produce information into a logical framework, summarize it into a meaningful format, and then display the summary into easy-to-read tables and graphs. Then you will be able to build up various types of data model to analyze business more critically using various tools, objects and controls.

 

You can prepare any kind of report very easily within short time if you have sound knowledge in data mapping, various formula building techniques, direct and indirect linking, data connection with other databases, data consolidation and summarization, visual presentation, use of object & form controls, dashboard techniques, Business Intelligence (BI) tools, techniques to handle millions of data and Excel’s amazing productive tricks. These wonderful features will definitely enhance your productivity and make your life easy.

 

Thinking about the above necessities, this professional workshop schedule has been designed so that you can learn the techniques to prepare various types of interactive reports from different types of data sources.

 

Training will be held in Leads Training & Consulting Ltd.

FEE - Tk 6000

Prerequisite

Regular Excel users, data analyzers, management reporters, project managers, decision makers, financial modelers, trainers and the professionals who are engaged to prepare periodical reports from various data sources.

Project Oriented Course

Certificate will be provided after the completion of the course.

Course Outline

Introduction of Dashboard Report in MS Excel

  • What is Dashboard report in Excel and how to develop?
  • Essential rules should be maintained to develop dashboard in Excel
  • Summary report interaction using various parameters
  • What to do and what not to do for developing Dashboard report
  • A Prezi presentation on Dashboard Reporting

Data Summarization and Aggregation from Large Data

  • Data summarizing using multiple parameters and conditions
  • Basic & advanced SUMPRODUCT function
  • COUNTIFS, SUMIFS and AVERAGEIFS functions
  • Data summarizing using INDIRECT linking

Naming in Excel

  • Defining names of a cell/range/formula
  • Using name in a formula

Essential Functions-Organize Your Unorganized Data

  • Extracting the required portion(left/right/middle) of a cell value
  • Removing unwanted extra spaces
  • Making a function as a variable
  • Replace the existing text with new text using functions
  • Use counting functions for query
  • Join a text with a formatted number or date.

Logical Function & Formula Nesting

  • IF() condition with operators
  • Using IF() with formula

Bullet-Proof Your Automated Reports and Workbook

  • Create your Workbook password protected
  • Assign users to use your Excel workbook/sheet within a limited area

Validate and Dump Your Dashboard’s Raw Data

  • Locking and protecting cells/area
  • Restriction for incorrect data entry/import with data validations
  • Creating error validation messages

Describe Your Data Using PivotTable, PivotChart and Slicers

  • Data mapping to prepare a PivotTable
  • Prepare your fist dynamic PivotTable
  • Understanding PivotTable Field List
  • 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
  • Containing customized data format that will not be changed on refresh
  • Applying conditional formatting to a PivotTable that will not be changed on refresh
  • Sorting and filtering techniques in PivotTable data
  • Filling empty cells with zero (0)

Dashboard Techniques Using PivotTable

  • Create dynamic PivotTable and fixed the area
  • Containing customized column width that will not be changed on refresh
  • Creating and linkingPivotChart with an existing PivotTable
  • Creating and linking slicer with an existing PivotTable& PivotChart
  • Interact your PivotTable dynamically using slicers

PowerPivot–Use the Most Powerful Feature in Excel (BI Tool)

  • What is PowerPivot?
  • Activation PowerPivot in MS Excel-2010, 2013 and 2016
  • 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

Get Live Datafrom External Data SourcesUsing Live Connections

  • Connect Excel with web (dsebd.org) and get live updated data
  • Create connection and get data from MS Access, SQL Server, ORACLE
  • Get all live data of a specific table from the above databases
  • Get processed/filtered/queried data from the above databases
  • Create schedule for auto refresh

Make Your Report Interactive Using Objects &Form Controls

  • Get introduced with Form Controls
  • Inserting and Controlling 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

Dynamic Graphical Presentation (Charts & Graphs)

  • 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
  • Use multiple charts type for different series within one chart
  • Play your chart by form controls (drop-down, option button etc.)

 

Decorate Your Automated Report Using Advanced Conditional Formatting

  • Multiple Conditional Formatting-manage rule
  • Using Data Bars and icons for great presentation
  • Changing Data Bars and Icons parameter
  • Formula based Conditional Formatting

Macro& VBA: Automate Your Excel Action or Report

  • Overview of macro in Excel step by step
  • Building your first macro without having any programming knowledge
  • Macro settings and security
  • Trust center and trust center settings
  • Automation techniques using macro
  • Carefulness of macros!
  • Macro Project-Automated Filtering with criteria

Exclusive Project Session

  • To Developa CompleteLive Interactive Dashboard Report

Open Discussion Session

  • Problem Solving Session
  • Share your Excel problem
  • The facilitator will share some critical real cases
  • Brainstorming and open discussion

Used Tools

100% PRACTICAL with trainer’s predesigned 12-16 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)

COURSE SUMMARY

Course Duration : 5 Days
Total Hour : 16 Hours
Number of Batch : 1 Batch

Class Starting Tentative Date 07 April, 2017
Application Last Date : 07 April, 2017

Class Schedule

Day & Time : Friday 6:00 pm - 9:00 pm
: Saturday 6:00 pm - 9:00 pm
: Tuesday 6:00 pm - 9:00 pm
: Friday 6:00 pm - 9:00 pm
: Saturday 6:00 pm - 9:00 pm
Duration : 3 Hours per class

Project:

N/a


Certificate:

Certificate will be provided after the course completion.