Business Intelligence – Creating Interactive Dashboards Using Excel

Business Intelligence (BI) is what you get when you analyze raw data and turn that information into actionable knowledge. Dashboards and reports are ideal mechanisms for delivering this targeted information in a graphical, user-friendly form. 

What is a Dashboard?
Dashboard Types & Examples
Overview: Dashboard Creating Process

Defining The Purpose Of The Dashboard
Requirement of End Users
Scope: Domain /Department
Frequency
Appropriate Metrics or KPIs as per Domain/Department

Data for Dashboards
Excel Files
Text Import Utility
Web Data
Using Access Databases for BIG data
Power Query
Tables
Raw Data Dimensions

Pivot Tables & Power Pivot Tables
Beyond SUMIF & COUNTIF
Calculating of Total, YTD, Variance & Growth Rates
Convertng a Pivot to a Range
Using “Table” to Create Pivot Table

Named Ranges
Static Named Range
Constant Named Range
Dynamic Named Range (with Formulas)

Macros – The Copy-Paste Way
To Create Dynamic Named Range

Formulas & Functions
CHOOSE() MATCH() SUBSTITUTE()
CONCATENATE() MAX() TEXT()
FIND() MID() TRIM()
INDEX()   OFFSET()   LEN()
LEFT() RIGHT() VLOOKUP()


Charts and Sparklines
WaterFall Charts
Sparklines
Thermometer Charts
Speedometer Charts
Combination Charts

Conditional Formatting
Visualization without Charts 

Interactivity - Giving Life To Dashboard
Radio Buttons
Check Boxes
Scrollbar 
Slicers

Dashboard Design Tips & Tricks
Layouts
Color, Fonts and other Design Elements
Dashboard Message 
Using Right Charts According to the Message

Getting Hands On : Making Sample Dashboards
Finance Dashboard
Sales Dashboard
Inventory Dashboard

Securing Your Dashboards
Protecting Dashboard 

Bonus: Other BI Tools Demo
Demo: Dashboard with Power BI