Building Dynamic Dashboards For Business Intelligence with EXCEL

A business dashboard gives business leaders valuable information and provides a quick outlook of all the most valuable numbers, which allows them to perform an accurate business analysis and make actionable decisions.
Participants will learn about:
– Dashboard definition and design principles
– Fundamentals on Excel Dashboard
– buildingOutline the structure of a dashboard
– Design dashboard layout using frames

Slicers are an easy-to-use filtering component that give you the ability to quickly filter your Dashboard and its respective views as well as add an attractive and interactive user interface to your dashboards.
Participants will learn how to:
– Prepare dynamic data source using Excel Table
– Create relationship between two table
– Create PivotTables using Data Model
– Create and customize PivotChart
– Create slicer and Timeline, connect Slicer and Timeline to multiple PivotTable
– Create Combo chart and create new Slicer style

The benefit of using advanced charts in Excel is they enable you focus on a specific data and make it easily understandable. You can use them in your dashboards to provide visualizations that help focus attention on key trends, comparisons, and exceptions.
Participants will learn how to:
– Create Waterfall chart, Histogram and Pie of Pie charts
– Show forecasting trend with Trendline
– Compare different targets with chart
– Create in-cell data bar and icon setsInsert in-cell line Sparklines, column Sparklines and Win/Loss Sparklines

Excel and PowerPoint are perfect partners for presenting dashboard on big screen. Simple dashboard on multiple slides will work better than one complex dashboard on a single slide.
Participants will learn how to:
– Create an embedded Excel dashboard in PowerPoint slide
– Create a linked Excel dashboard in PowerPoint slide
– Convert chart into picture
– Capture a snapshot of chart with Screenshot
– Use Camera tool and Linked Picture option to take live picture of a cell range

Excel offers a set of Form controls such as option buttons, combo boxes and checkboxes that allow you to add interactivity to your dashboard and make it easier for users to interact with the charts and tables on dashboard.
Participants will learn how to:
– Set form controls properties
– Use various form controls such as button, combo box, check box, scroll bar, spin button, group box and option button to manipulate charts
– Define dynamic range names
– Create summary tables using OFFSET, INDEX and MATCH functions
– Create charts using names
– Control chart series using data validation drop-down list.

Analytical dashboards are designed to help decision makers, executives and senior leaders, establish targets based on insights into historical data, set goals and understand what and why something happened so that appropriate changes can be implemented. For example, a Sales dashboard enable you to monitor and investigate different aspects of sales process from the beginning until the end of its lifecycle and it’s useful to showcase sales data during annual meetings or quarterly meetings.
Participants will learn how to:
– Prepare dynamic data source using defined name and Table
– Create dynamic table in dashboard using CHOOSE, SUMIFS and other functions
– Create dynamic chart labelsUse slicer to control data labels
– Create slicer and format slicer

Dashboard automation in Excel can be achieved using macro buttons. Macro Recorder is a handy tool for a beginner who is unable to write the VBA (Visual Basic for Applications) code by hand to create macros.
Participants will learn how to:
– Record macro using Macro Recorder and edit macro using VBA (Microsoft Visual Basic for Application)
– Assign macro to a button on a worksheet and to a command button on the Quick Access Toolbar
– Record macro to sort and filter tables and charts on a dashboard

Participants will have chance to design a user-friendly dynamic dashboard. The case study helps participant to apply the skills and functions they learned from the training so that they know exactly how to build interactive and informative dashboards in their work.

Prerequisite:
You should be a confident Excel user who has experience in using and maintaining Excel reports and be able to create Excel table-driven reports and charts. A pre-requisite questionnaire will be provided to you to ensure the suitability and level is right for you.

Notice:
The course will actively be using Excel 365 subscription version. It will be running and taught based on the PC Windows software and not Mac versions. (All Laptops and materials will be provided)