LEADS Corporation Limited
HOME > LTCL > Training > Advanced MS Excel -Automated Dashboard Reporting

Advanced MS Excel -Automated Dashboard Reporting

Overview

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 within seconds 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.

 

Learning Methodologies

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

 

Who Should Attend?

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 Session

Exclusive Project Session

To Develop a Complete Live Interactive Dashboard Report Training 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

LOOKUP & REFERENCES

  • VLOOKUP function
  • MATCH function, INDEX function
  • Combination of MATCH & INDEX function to resolve complex data matching
  • CHOOSE function for dynamic matching

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

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
  • Containing customized data format that will not be changed on refresh
  • Sorting and filtering techniques in PivotTable data
  • Make your PivotTable report professional looking your
  • Dashboard techniques using PivotTable

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 other databases (SQL/Access….)
  • Inserting a calculated column using Data Analysis Expression (DAX)
  • Techniques to use fields and slicers from PowerPivot Field List

GET YOUR DASHBOARD’S LIVE RAW DATA FROM EXTERNAL DATA SOURCES

  • Connect Excel with web and get live real time 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)

  • Choosing the perfect chart for your data
  • Creating various types of Charts-Column, Pie, Line etc.
  • Chart customize for target vs achievement, category/subcategory
  • Customize the chart elements professionally
  • Use multiple charts type for different series within one chart
  • Data visualization using Sparklines
  • Play your chart by form controls (drop-down, option button etc.)

BULLET-PROOF YOUR AUTOMATED REPORTS AND WORKBOOK

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

ESTABLISHING ADVANCED CONDITIONAL FORMATTING

  • Multiple Conditional Formatting-manage rule
  • Customizing Data Bars and Icon sets
  • Formula based Conditional Formatting

PRESENT YOUR DASHBOARD WITH POWERPOINT PRESENTAION

  • Attaching the dashboard in PowerPoint professionally
  • Build live connection with Dashboard and PowerPoint

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 OR
  • Macro Project-Data consolidation from hundreds of excel files

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


Start Course