Two Days Exclusive Professional Development Workshop on Masterclass on Microsoft Excel-Data Analysis & Dashboard Reporting

Two Days Exclusive Professional Development Workshop on

Masterclass on 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?

Working with big volume data

Data analyzers

Business reporters

Reporting professionals

Decision makers

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

A Prezi presentation on various concepts on Dashboard report

• DATA MATCHING & LOOKUPS FOR PREPARING DASHBOARD’S RAW DATA

VLOOKUP & HLOOKUP function

Complex uses of VLOOKUP/HLOOKUP

XLOOKUP – the brand new function

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

Restriction for incorrect data entry/import with data validations
Various configuration to check invalid data

Setting notification for invalid data

• 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

DASHBOARD Project-1

• TOOLS TO DESIGN DASHBOARD REPORTS

Using various tools and objects in the report

• 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

DASHBOARD Project-2

• POWER QUERY – USE EXCEL’S BUILT-IN QUERY ENGINE

Connecting and importing external data

Data consolidation from multiple files (100 files) in one command

Data consolidation from multiple sheets

Creating custom column for complex calculation

DASHBOARD Project-3

• 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

Customizing 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

• EXCLUSIVE PROJECT SESSION

DASHBOARD Project-4

• OPEN DISCUSSION SESSION
• Problem Solving Session
• Share your Excel problem