Advanced Microsoft Excel - Data Analysis & Dashboard Reporting

Advanced Microsoft Excel - Data Analysis & Dashboard Reporting

MS Excel is widely used to prepare various type 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

course at a glance

  • Date : 5 Dec - 17 Dec 2019
  • No. of Classes/ Sessions : 6
  • Total Hours : 15
  • Last Date of Registration : 4 Dec 2019
  • Class Schedule :
    • Sunday - 7.00 PM - 9.30 PM
    • Tuesday - 7.00 PM - 9.30 PM
    • Thursday - 7.00 PM - 9.30 PM
  • venue : NRB Jobs Training Center BDBL Bhabhan, Level - 15 12 Kawran Bazar, Dhaka

Price: TK. 5,500
(Excluding VAT & TAX)

This training is jointly organized by BITM & NRB Jobs Training

Training will be held in NRB Jobs Training Center.

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 yours.; 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 rules.; 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

Curriculum

Microsoft Excel Advanced Microsoft Excel - Data Analysis & Dashboard Reporting 15 Hrs

Tentative Class Start

5th December, 2019

Available Seat

10 / 25

who can join

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.

Meet the Instructor