Session 6:
Dashboarding in Excel
Our Goal
What does
Dashboards do ?
Search of a problem Search of a solution
Types of Dashboards
Operational
Functional
Analytical
Executive
Strategic
Tactical
Operational Analytical Strategic
Tactical Executive Functional
Analytical Purpose
Strategic
Operational
• Historical data for deeper insights
Executive
• Trend identification
Functional
Tactical
Analytical Use Case
Strategic
Operational
• Understanding patterns,
Executive
• Performing root cause analysis
Functional
• Identifying long-term trends
Tactical
Features
Analytical
Strategic
• Focus on in-depth data analysis
Operational
• Typically covers longer time periods ( months
Executive
or years)
Functional • May include drill-down capabilities to
investigate data further
Tactical
Analytical Purpose
Strategic
Operational • Provide high-level overviews
Executive • Long-term strategic insights for business
Functional leaders and executives
Tactical
Analytical Use Case
Strategic
Operational
• Setting and monitoring organizational goals
Executive • Tracking progress toward long-term objectives
Functional
• Informing key business decisions
Tactical
Features
Analytical
Strategic
• Summarized data, often focusing on critical KPIs
Operational
• Provides insights into the overall health of the
Executive
business
Functional • Typically updated less frequently (monthly or
quarterly)
Tactical
Analytical Purpose
Strategic
Operational
• Monitor real-time data
Executive
• Provide insights into daily operations
Functional
Tactical
Analytical Use Case
Strategic
Operational
• For day-to-day decision-making
Executive • Operational efficiency
Functional
• Tracking KPIs (Key Performance Indicators).
Tactical
Features
Analytical
Strategic
• Focus on real-time data updates
Operational
• Monitors metrics such as sales, marketing
Executive
performance, customer support activity, etc
Functional • Highlights areas that require immediate attention or
action
Tactical
Analytical Purpose
Strategic
Operational
• Monitor real-time data
Executive
• Provide insights into daily operations
Functional
Tactical
Analytical Use Case
Strategic
Operational
• For day-to-day decision-making
Executive • Operational efficiency
Functional
• Tracking KPIs (Key Performance Indicators).
Tactical
Features
Analytical
Strategic
• Focus on real-time data updates
Operational
• Monitors metrics such as sales, marketing
Executive
performance, customer support activity, etc
Functional • Highlights areas that require immediate attention or
action
Tactical
Analytical Purpose
Strategic
Operational • Focus on key metrics and performance
Executive indicators relevant to a particular area of the
Functional business
Tactical
Analytical Use Case
Strategic
Operational
• Employee turnover and retention rates
Executive • Sales quota vs. actuals
• Email open rates and click-through rates
Functional
Tactical
Analytical Purpose
Strategic
Operational
• Focus on specific goals or projects, often used
Executive
for short-term tracking
Functional
Tactical
Analytical Use Case
Strategic
Operational
• Used by department heads or project managers
Executive to track the progress of initiatives and compare
Functional
current performance against set targets
Tactical
Features
Analytical
Strategic
Operational • Granular data focused on specific business processes
Executive
or areas
• Tracks short- to mid-term goals, such as sales targets
Functional
or project milestones
Tactical
If you have a Department wise revenue and cost analysis dashboard ?
What type of dashboard it is
Analytical Strategic Tactical Executive Functional Operational
The Story of 4 Friends and their
business
Four friends are starting their own farming businesses.
Each has a special skill to help them, but they all
struggle with analyzing and making decisions. Our goal
is to help them run their businesses successfully.
PizzaBun
The friends decided to name their business
"PizzaBun"—a pizza store with buns as the
crust. They’ve also pooled their money to
get started.
A Full Year
After 12 successful months, the friends
want to review their business progress.
They need to create a report (or
dashboard) but don’t know how. Let’s help
them!
Why do we Need a
Dashboard
Why do we Need a
Dashboard
What is an Excel Dashboard ?
An Excel dashboard is a collection of summaries created using pivot tables
or formulas. Its main purpose is to reveal hidden insights from data. Some
dashboards focus on solving specific problems, tracking KPIs, or analyzing
performance. Dashboards are designed for third parties like managers,
clients, or executives. There are three main types:
• Formulated Dashboards (focused on numbers)
• Charted Dashboards (visually appealing)
• Pivot Table Dashboards (a mix of visuals and data)
Before Creating a Dashboard
● Understand the Data well
● Create a list of areas you wish to focus on the dashboard
● Create a list of Business Questions and Pick the most
insightful ones for your dashboard
● Create a list of High Level Summaries you wish to create for
the dashboard
Creating Business Questions
● Pick any one text Field from the data and write down the unique values of that field
● Repeat the same with other fields too
● Now manipulate the fields with each other
● Example: Order type has “Online” & “Physical Visit” so the question can be
“How Many Total Products sold via online Mode ?
Keep in Mind
Some of the
Best Practices
are below when
you create a
dashboard.
You
• Remove Distractions
• Keep the dashboard area clean without background
• Increase the font sizes and customize heavily for your users
• Keep the colours consistent
• Make it informative
• Gather the questions before inserting the first chart
• Always have a overall view and then the break down information
Eight Essentials
1. Setting up the Environment
2. Creating Business Answers
3. Creating Charts
4. Aligning the charts
5. Removing Distractions
6. Bringing the Insights out
7. Slicers
8. Colouring and theming
Customer Support
Dashboard
Setting up the
Environment
Setting up the Environment
1. Dedicated Sheets for Pivots & Dashboards
2. Disable Grid Lines
3. Zoom IN or OUT for Final View
4. Disable Formula Bar
5. Collapse the Ribbon
6. Disable Headers
7. Ensure the Final View is Good enough
8. Final Layout in mind
Layout
Dashboard Title
KPI's
Chart 1 Chart 2 Summary and Insights
Vertical Slicers Chart 3 Chart 4 Chart 5 Chart 6
Trend Chart 1 or Other Charts Trend Chart 2 or other Charts
Creating
Business
Answers
Creating
Charts
Choosing Charts
Bar Chart Funnel
Chart
Choosing Charts
Line Chart Pie Chart
Choosing Charts
Bullet Graph
Scatter Plot
Aligning the
charts
Removing
Distractions
Bringing the
Insights out
Slicers
Customizing
Everything
Colouring
& Theming
The
Creative
Touch
Let’s Create Individual Dashboards for
Each of them
Strawbia
Orangesingh
Pinapiya
Customer Support Financial Order Fulfilment
Dashboard Dashboard Dashboard
Financial Dashboard
Pivot Table
Grouping
Contextual
Charting
Slicers
Customizing
Everything
Colouring
& Theming
Basic
Summariza
tion
Let’s Create Individual Dashboards for
Each of them
Strawbia
Orangesingh
Pinapiya
Customer Support Financial Order Fulfilment
Dashboard Dashboard Dashboard
Order Fulfilment
Dashboard
KPI
Custom
Formatting
Logarithmi
c Scale
Customizing
Slicers
Customizin
g Numbers
Chart Level
Summariza
tion