Basic Dashboards

Basic Dashboards

A dashboard is a visual interface that brings important data, key performance
indicators, charts, summaries, and filters together in one place. In data
analysis, dashboards help users understand what is happening, identify trends,
compare performance, and make better decisions quickly.

Microsoft Excel can be used to create practical analytical dashboards by
combining Excel Tables, formulas, Pivot Tables, Pivot Charts, slicers,
conditional formatting, and carefully designed layouts.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what an analytical dashboard is.
• Differentiate between a dashboard and a traditional report.
• Identify useful dashboard components.
• Select appropriate KPIs for a dashboard.
• Use Excel Tables as dashboard data sources.
• Use Pivot Tables and Pivot Charts in dashboards.
• Use slicers and filters to make dashboards interactive.
• Apply formulas and conditional formatting to dashboard elements.
• Design a dashboard that focuses attention on important information.
• Apply dashboard design and data-quality best practices.

1. What Is a Dashboard?

A dashboard is a visual summary of important information designed to help
people monitor performance and make decisions.

Instead of requiring a manager to examine hundreds or thousands of rows,
a dashboard can summarize the most important information into a small number
of charts, numbers, tables, and indicators.

For example, a sales dashboard might display:

• Total sales.
• Number of orders.
• Average order value.
• Top-selling products.
• Sales by region.
• Monthly sales trends.
• Sales representatives’ performance.

2. Why Dashboards Are Important

A well-designed dashboard turns raw or summarized data into information that
can be understood quickly.

Dashboards can help users:

• Monitor key performance indicators.
• Identify trends.
• Compare categories.
• Find unusual results.
• Track targets.
• Investigate problems.
• Support management decisions.
• Communicate results to stakeholders.

3. Dashboard vs Report

A report usually provides detailed information for reading, documentation,
or deeper analysis. A dashboard focuses more strongly on monitoring and
decision-making.

4. Start With the Decision

A common dashboard mistake is starting with charts before deciding what the
dashboard is supposed to accomplish.

Before building a dashboard, ask:

• Who will use this dashboard?
• What decisions should it support?
• What questions should it answer?
• What metrics are important?
• How frequently should the data be updated?

A dashboard should exist because someone needs information to make or monitor
a decision.

5. Dashboard Audience

Different users need different levels of information.

For example:

• A CEO may need revenue, profit, growth, and major KPIs.
• A sales manager may need sales by representative and region.
• A teacher may need student attendance and performance.
• A marketing officer may need leads, conversion rates, and campaign results.
• An operations manager may need productivity and service-level metrics.

6. Key Performance Indicators (KPIs)

A Key Performance Indicator, or KPI, is a measurable value used to evaluate
performance against an objective.

Examples include:

• Total Revenue.
• Total Profit.
• Number of Customers.
• Number of Orders.
• Conversion Rate.
• Average Order Value.
• Student Enrollment.
• Course Completion Rate.
• Attendance Rate.

Not every available metric belongs on a dashboard. Select metrics that are
relevant to the decision being supported.

7. KPI Cards

KPI cards are large visual indicators that display important summary values.

A dashboard might place four KPI cards at the top:

• Total Sales: ₦25,000,000
• Total Orders: 2,450
• Average Order Value: ₦10,204
• Growth: 12%

KPI cards should be easy to read and should not contain unnecessary
information.

8. Common Dashboard Components

A basic Excel dashboard may contain:

• KPI cards.
• Line charts.
• Column charts.
• Bar charts.
• Pie or doughnut charts where appropriate.
• Pivot Tables.
• Pivot Charts.
• Slicers.
• Timelines.
• Summary tables.
• Conditional formatting.
• Target indicators.

9. Excel Tables as Dashboard Data Sources

Excel Tables are useful dashboard sources because they provide structured
datasets that can expand as new records are added.

Suppose a sales dataset contains:

• Date
• Salesperson
• Region
• Product
• Quantity
• Revenue

Converting the dataset into an Excel Table makes it easier to reference the
data consistently and use it with Pivot Tables, formulas, and charts.

10. Pivot Tables in Dashboards

Pivot Tables are one of the most useful Excel tools for dashboard development.
They can quickly summarize large datasets.

For example, a Pivot Table can show:

• Revenue by region.
• Revenue by product.
• Revenue by salesperson.
• Monthly revenue.
• Average student score by course.

The summarized results can then become the source for charts and other
dashboard components.

11. Pivot Charts

A Pivot Chart provides a visual representation of Pivot Table results.

For example, a Pivot Table showing monthly revenue can be represented using
a line chart to make the trend easier to understand.

Pivot Charts are particularly useful when combined with slicers because users
can interactively change the information being displayed.

12. Slicers

A slicer is an interactive filtering control used with Excel Tables and
Pivot Tables.

For example, a sales dashboard could provide slicers for:

• Region.
• Product.
• Salesperson.
• Year.

Selecting a value in a slicer can filter connected dashboard information.

13. Timelines

A Timeline is an interactive filtering tool designed primarily for date
fields in Pivot Tables.

It can allow users to filter information by:

• Years.
• Quarters.
• Months.
• Days.

Timelines can make time-based dashboards much easier to explore.

14. Dashboard Layout

A good dashboard should have a clear visual hierarchy.

A common layout is:

1. Dashboard title.
2. Important filters.
3. KPI cards.
4. Main trend chart.
5. Category comparison charts.
6. Detailed summary table.

Important information should generally receive more visual attention than
secondary information.

15. Dashboard Grid

A dashboard should use a consistent layout. Charts and KPI cards should align
with one another rather than being placed randomly across the worksheet.

Using an invisible grid or consistent cell boundaries can help maintain
alignment.

16. Choosing the Right Chart

17. Avoiding Too Many Charts

A dashboard is not a collection of every chart that can be created from a
dataset.

Too many charts can make a dashboard difficult to understand.

Each visual should answer a useful question.

18. Using Formulas in Dashboards

Excel formulas can calculate values displayed in KPI cards and summary areas.

Common dashboard formulas include:

• SUM
• COUNT
• COUNTA
• AVERAGE
• SUMIF
• SUMIFS
• COUNTIF
• COUNTIFS
• IF
• IFERROR
• XLOOKUP

19. Example Dashboard KPI Formulas

Suppose revenue values are stored in cells E2:E1000.

=SUM(E2:E1000)

This calculates total revenue.

To calculate the average revenue:

=AVERAGE(E2:E1000)

To count records:

=COUNT(E2:E1000)

20. Conditional Formatting

Conditional formatting can make dashboard indicators easier to interpret.

For example, performance against a target could be represented using
different visual indicators.

Conditional formatting can be used for:

• High and low values.
• Performance targets.
• Negative results.
• Positive growth.
• Data bars.
• Color scales.
• Icon sets.

21. Example Target Indicator

Suppose a business target is ₦10,000,000 and actual revenue is stored in
cell B2.

The dashboard could calculate achievement percentage using:

=B2/10000000

The result can then be formatted as a percentage.

22. Sales Dashboard Example

Imagine a company wants to monitor sales performance.

The dashboard could contain:

• Total Revenue.
• Total Orders.
• Total Customers.
• Average Order Value.
• Monthly Sales Trend.
• Sales by Region.
• Top Products.
• Sales by Representative.
• Region slicer.
• Year or month timeline.

23. Student Performance Dashboard Example

An educational institution can use Excel to create a student performance
dashboard.

Possible KPIs include:

• Total Students.
• Average Score.
• Pass Rate.
• Attendance Rate.
• Number of Courses.

Charts could show performance by course, department, class, or academic
period.

24. Dwallnet Training Dashboard Example

A training organization such as Dwallnet Technologies could create a
dashboard to monitor training activities.

Possible metrics include:

• Total registered students.
• Active students.
• Completed students.
• Course enrollment.
• Average assessment score.
• Attendance rate.
• Course completion rate.
• Student performance by course.

The dashboard could use filters for course, cohort, trainer, and training
period.

25. Dashboard Color and Design

Colors should support communication rather than distract the user.

Good dashboard design should:

• Use a consistent color scheme.
• Maintain good contrast.
• Use readable fonts.
• Avoid excessive decoration.
• Use emphasis only where necessary.
• Keep similar information visually consistent.

26. Avoiding Dashboard Clutter

Dashboard clutter occurs when too many objects, colors, labels, charts, and
controls compete for attention.

To reduce clutter:

• Remove unnecessary charts.
• Remove unnecessary borders.
• Reduce repeated labels.
• Use concise titles.
• Group related information.
• Keep adequate spacing.
• Focus on the most important KPIs.

27. Dashboard Interactivity

Interactive dashboards allow users to explore data without manually changing
the underlying dataset.

Common interactive controls include:

• Slicers.
• Timelines.
• Drop-down lists.
• Pivot Table filters.
• Form controls where appropriate.

28. Dashboard Data Quality

A beautiful dashboard can still provide incorrect information if its source
data is inaccurate.

Before publishing a dashboard, check:

• Missing values.
• Duplicate records.
• Incorrect dates.
• Incorrect numbers.
• Inconsistent category names.
• Incorrect formulas.
• Incorrect filters.
• Outdated source data.

29. Dashboard Refresh

Dashboards are useful only when their information remains current.

If the underlying dataset changes, Pivot Tables, Pivot Charts, formulas, and
other dashboard components may need to be refreshed or recalculated.

Users should understand the dashboard’s refresh process and update schedule.

30. Dashboard Testing

Before sharing a dashboard, test every major component.

• Check KPI calculations.
• Test every slicer.
• Test date filters.
• Check chart labels.
• Check totals against the source data.
• Check formulas.
• Check whether filters affect the correct visuals.
• Check for blank or misleading results.

31. Basic Dashboard Development Workflow

1. Define the dashboard audience.
2. Identify the decisions to support.
3. Define the questions the dashboard should answer.
4. Select appropriate KPIs.
5. Collect the required data.
6. Clean and validate the data.
7. Convert source data into an Excel Table where appropriate.
8. Create Pivot Tables and summaries.
9. Create charts.
10. Add slicers and filters.
11. Design the dashboard layout.
12. Test calculations and interactions.
13. Review the dashboard with a user.
14. Publish and maintain the dashboard.

32. Example Dashboard Structure

---------------------------------------------------------
|              SALES PERFORMANCE DASHBOARD              |
---------------------------------------------------------
| Total Sales | Orders | Customers | Growth            |
---------------------------------------------------------
|              Monthly Sales Trend                      |
|                                                       |
|                    LINE CHART                         |
---------------------------------------------------------
| Sales by Region       |       Top Products             |
|                       |                                |
|      BAR CHART        |       BAR CHART                |
---------------------------------------------------------
| Region Slicer | Product Slicer | Date Timeline        |
---------------------------------------------------------

33. Dashboard Best Practices

• Design for the intended audience.
• Start with business questions.
• Use trustworthy data.
• Keep KPIs meaningful.
• Use appropriate charts.
• Keep the layout consistent.
• Use filters carefully.
• Do not overload the dashboard.
• Make important information easy to find.
• Test calculations before publishing.
• Document important data sources.
• Keep the dashboard maintainable.

34. Common Dashboard Mistakes

• Using too many charts.
• Using inappropriate chart types.
• Displaying unnecessary KPIs.
• Using excessive colors.
• Ignoring data quality.
• Building the dashboard without a clear purpose.
• Using misleading scales.
• Failing to test formulas.
• Using outdated data.
• Making filters difficult to understand.
• Making the dashboard difficult to maintain.

35. Practical Exercise

Create a basic sales dashboard in Microsoft Excel using a dataset containing
at least the following fields:

• Date
• Product
• Salesperson
• Region
• Quantity
• Revenue

Your dashboard should contain:

1. Total Revenue KPI.
2. Total Orders KPI.
3. Average Revenue KPI.
4. Monthly revenue chart.
5. Revenue by region chart.
6. Top five products chart.
7. At least one slicer.
8. A clear dashboard title.

Compare the dashboard totals against the original dataset to verify that your
calculations are correct.

36. Practical Challenge

Create a student performance dashboard containing:

• Student ID.
• Student Name.
• Course.
• Score.
• Attendance.
• Completion Status.

Create KPI cards for total students, average score, pass rate, and attendance
rate. Then create charts showing performance by course.

Add a course filter so that the dashboard can be used to inspect individual
courses.

37. Knowledge Check

1. What is a dashboard?
2. What is the difference between a dashboard and a report?
3. What does KPI mean?
4. Give three examples of dashboard KPIs.
5. Why are Pivot Tables useful in dashboards?
6. What is a slicer?
7. What is a Timeline in Excel?
8. Why should dashboards avoid excessive charts?
9. Why is data quality important when building a dashboard?
10. Why should a dashboard be tested before publication?

38. Key Terms

• Dashboard: A visual summary of important information.
• KPI: A Key Performance Indicator used to measure performance.
• Pivot Table: An Excel tool for summarizing and analyzing data.
• Pivot Chart: A chart connected to a Pivot Table.
• Slicer: An interactive filtering control.
• Timeline: An interactive date-filtering control.
• Data Source: The dataset from which dashboard information is generated.
• Visualization: A graphical representation of data.
• Metric: A measurable value used for analysis.
• Interactivity: The ability of a user to explore information using controls.

39. Key Takeaways

• Dashboards provide visual summaries for monitoring and decision-making.
• A good dashboard begins with a clear business question.
• KPIs should be relevant to the dashboard’s purpose.
• Excel Tables provide useful structured data sources.
• Pivot Tables help summarize large datasets.
• Pivot Charts turn summaries into visual information.
• Slicers and Timelines make dashboards interactive.
• Good layout and visual hierarchy improve usability.
• Data quality determines the reliability of dashboard results.
• Dashboards should be tested and maintained regularly.

40. Summary

Basic dashboards combine the analytical capabilities of Excel into a visual
environment designed for monitoring and decision-making. A successful
dashboard does not simply display data; it communicates the information that
matters most to its intended audience.

Excel provides several tools that can be combined to create dashboards,
including Tables, formulas, Pivot Tables, Pivot Charts, slicers, timelines,
and conditional formatting.

The most important principle is to build the dashboard around the questions
and decisions that users need to address. When the data is reliable, the KPIs
are meaningful, and the design is clear, an Excel dashboard can become a
powerful data-analysis and communication tool.

Next Lesson

You have completed the Microsoft Excel for Data Analysis section.
The next stage of the Data Analysis curriculum will build on these Excel
skills and move into more advanced analytical techniques and tools.