Charts

Charts in Microsoft Excel

Charts are visual tools used to represent data graphically.
Instead of examining rows and columns of numbers, an analyst can use
a chart to communicate patterns, comparisons, trends and relationships
more clearly.

In data analysis, charts are not simply decoration. A well-designed
chart helps people understand information and make better decisions.

1. Learning Objectives

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

• Explain the purpose of charts in data analysis.
• Identify common Excel chart types.
• Select appropriate charts for different analytical questions.
• Create charts from Excel datasets.
• Understand chart elements.
• Format charts professionally.
• Create column and bar charts.
• Create line charts for trends.
• Create pie and doughnut charts when appropriate.
• Use scatter charts to study relationships.
• Create combination charts.
• Work with Pivot Charts.
• Use charts in dashboards.
• Avoid common chart design mistakes.

2. What Is a Chart?

A chart is a graphical representation of data.

For example, a table containing monthly sales can be represented using
a line chart.

Month       Sales
January     500000
February    650000
March       720000
April       600000

A line chart can make the upward and downward movement easier to see.

3. Why Charts Matter in Data Analysis

Humans often recognize visual patterns faster than they interpret long
tables of numbers.

Charts can help analysts identify:

• Trends.
• Comparisons.
• Patterns.
• Outliers.
• Changes over time.
• Relationships.
• Distribution.
• Proportions.

4. Data Visualization

Data visualization is the process of representing information
graphically so that patterns and insights can be communicated clearly.

Excel provides many tools for creating visualizations without requiring
programming.

5. Choosing the Right Chart

The best chart depends on the question being asked.

6. Creating a Chart in Excel

A basic process is:

1. Prepare the dataset.
2. Select the relevant data.
3. Go to the Insert tab.
4. Select a suitable chart type.
5. Review the generated chart.
6. Add an appropriate title.
7. Format the chart.
8. Check that the chart communicates the intended message.

7. Chart Components

Important chart elements include:

• Chart title.
• Horizontal axis.
• Vertical axis.
• Axis titles.
• Data series.
• Data points.
• Legend.
• Gridlines.
• Data labels.
• Plot area.
• Chart area.

8. Chart Title

A chart title should clearly describe what the chart shows.

Weak title:

Chart 1

Better title:

Monthly Sales Revenue — January to December 2026

A meaningful title makes a visualization easier to understand.

9. Column Charts

Column charts use vertical bars to compare categories.

They are useful when comparing values across categories.

Example:

Course             Students
Web Development    120
Data Analysis      95
Cyber Security     80
Graphics Design    60

A column chart can quickly show which course has the highest number
of students.

10. Bar Charts

Bar charts use horizontal bars.

They are useful when category names are long or when there are many
categories.

For example, a bar chart can compare sales across many products.

11. Column Chart vs Bar Chart

• Column chart:
  vertical comparison.
• Bar chart:
  horizontal comparison.

Both can communicate category comparisons effectively.

12. Line Charts

Line charts are particularly useful for showing changes over time.

Examples include:

• Monthly sales.
• Daily website visitors.
• Annual revenue.
• Student enrollment trends.
• Monthly expenses.

13. Example of a Line Chart Dataset

Month       Enrollment
January     40
February    55
March       70
April       65
May         90
June        110

A line chart can make the enrollment trend immediately visible.

14. Pie Charts

Pie charts display parts of a whole.

For example, a business may use a pie chart to show the percentage
contribution of different product categories to total sales.

Pie charts are most effective when there are only a small number of
categories.

15. Problems With Pie Charts

Pie charts become difficult to interpret when there are too many
slices.

For example, a chart with 20 categories can become visually confusing.

In such situations, a bar chart may communicate the information more
effectively.

16. Doughnut Charts

A doughnut chart is similar to a pie chart but has a hole in the
center.

It can be used for proportion-based visualizations and dashboards.

17. Scatter Charts

A scatter chart displays the relationship between two numerical
variables.

For example:

Study Hours    Exam Score
2              45
3              52
4              60
5              68
6              75
8              88

A scatter chart may reveal whether increased study time is associated
with higher examination scores.

18. Correlation and Scatter Charts

A scatter chart can help visualize possible correlation between
variables.

However, remember that correlation does not automatically prove
causation.

19. Histogram

A histogram is used to show the distribution of numerical data across
ranges or bins.

For example, student scores can be grouped into:

0–49
50–59
60–69
70–79
80–89
90–100

The histogram shows how many observations fall within each range.

20. Area Charts

Area charts are similar to line charts but emphasize the magnitude of
values over time.

They can be useful for showing cumulative or volume-related trends.

21. Combination Charts

A combination chart combines two chart types.

For example:

Sales → Column
Growth Rate → Line

This allows two related measures with different scales or meanings to
be displayed together.

22. Secondary Axis

A secondary axis can be used when two series have significantly
different scales.

For example:

Revenue: ₦0 – ₦10,000,000

Growth Rate: 0% – 20%

A secondary axis can make both measures readable.

Use secondary axes carefully because they can sometimes make charts
misleading if the viewer is not aware of the different scales.

23. Data Labels

Data labels display the actual values directly on the chart.

For example, a sales chart may display:

Laptop     ₦5,000,000
Keyboard   ₦1,250,000
Mouse      ₦900,000

Data labels can be helpful when exact values are important.

24. Legends

A legend identifies different data series.

For example, a chart comparing sales across three regions may have
three series.

The legend tells the viewer which series represents each region.

25. Axis Titles

Axis titles explain what each axis represents.

Example:

Horizontal Axis:
Month

Vertical Axis:
Sales Revenue (₦)

Clear axis titles reduce ambiguity.

26. Gridlines

Gridlines can help readers estimate values from a chart.

However, excessive gridlines can make a visualization look cluttered.

Use them only when they improve readability.

27. Formatting Charts

Excel provides many formatting options.

You can change:

• Chart title.
• Font.
• Text size.
• Data labels.
• Axis format.
• Gridlines.
• Legend position.
• Chart size.
• Chart layout.

28. Professional Chart Design

A professional chart should be simple, readable and focused on the
message.

Avoid:

• Too many unnecessary elements.
• Excessive visual effects.
• Too many categories.
• Unreadable labels.
• Misleading scales.
• Unnecessary 3D effects.

29. Avoid 3D Charts When Possible

Three-dimensional charts can look attractive but may make accurate
comparison more difficult.

For analytical reporting, simple two-dimensional charts are often
clearer.

30. Sorting Data Before Charting

Sorting can improve the readability of category comparison charts.

For example, sales can be sorted from highest to lowest before
creating a bar chart.

This makes rankings easier to identify.

31. Charting Monthly Sales

Suppose you have:

Month       Sales
January     500000
February    650000
March       720000
April       680000
May         850000
June        900000

A line chart is suitable because the primary question is how sales
change over time.

32. Charting Course Enrollment

Suppose Dwallnet Technologies has:

Course              Students
Web Development     120
Data Analysis       95
Cyber Security      80
Graphics Design     65
Python Programming  75

A bar or column chart can compare student enrollment across courses.

33. Charting Student Performance

If you want to compare average scores by course, a column chart can
be useful.

Course              Average Score
Web Development     78
Data Analysis       82
Cyber Security      75
Graphics Design     88

34. Charts and Pivot Tables

Pivot Tables and charts work together very effectively.

A Pivot Table summarizes the data while a Pivot Chart visualizes the
summary.

This combination is particularly useful for reporting and dashboards.

35. Pivot Charts

A Pivot Chart is linked directly to a Pivot Table.

When the Pivot Table is filtered, the connected chart can update to
reflect the selected information.

36. Slicers and Charts

Slicers can be connected to Pivot Tables and Pivot Charts.

For example, a dashboard may contain:

• Sales chart.
• Region slicer.
• Product slicer.
• Salesperson slicer.

Users can select filters and immediately explore different views of
the data.

37. Charts in Dashboards

Charts are an important component of Excel dashboards.

A dashboard may contain:

• Total Revenue KPI.
• Total Customers KPI.
• Monthly Sales Line Chart.
• Sales by Product Bar Chart.
• Regional Sales Chart.
• Slicers.

38. Choosing Charts Based on Analytical Questions

Always ask what you want the audience to understand.

39. Avoid Misleading Charts

A chart can technically display correct numbers while still creating
a misleading impression.

Examples include:

• Manipulating the axis scale.
• Removing important context.
• Using inappropriate chart types.
• Comparing values with different units incorrectly.
• Using 3D effects that distort perception.

40. Zero Baseline

For many column and bar charts, starting the numerical axis at zero
makes comparisons easier and less misleading.

An axis that begins at a high value can exaggerate small differences.

41. Outliers in Charts

Charts can help identify unusual observations.

For example, if most monthly sales are between ₦500,000 and
₦1,000,000 but one month has ₦5,000,000, the chart makes the unusual
value visible.

The analyst should investigate whether the outlier is genuine or the
result of a data error.

42. Trend Lines

Trendlines can help visualize the general direction of numerical data
in supported Excel chart types.

Common trendline types include:

• Linear.
• Exponential.
• Logarithmic.
• Polynomial.
• Moving average.

The appropriate trendline depends on the underlying data and analytical
purpose.

43. Moving Average

A moving average smooths short-term fluctuations and helps reveal
underlying trends.

It can be useful for time-series analysis such as sales, website
traffic or enrollment.

44. Combo Chart Example

Suppose you have monthly revenue and growth percentage:

Month       Revenue       Growth
January     500000        5%
February    600000        8%
March       720000        12%
April       680000        -6%

A combination chart can display Revenue as columns and Growth as a
line.

45. Chart Accessibility

Charts should be understandable to as many users as possible.

Good practices include:

• Use readable text.
• Use descriptive titles.
• Do not rely only on color to communicate meaning.
• Use clear labels.
• Provide context for unusual values.

46. Common Chart Mistakes

• Choosing a chart type that does not match the question.
• Adding too many data series.
• Using too many colors.
• Using 3D effects unnecessarily.
• Creating overly complicated charts.
• Leaving the title unclear.
• Removing important axis information.
• Using misleading scales.
• Failing to sort categories where appropriate.
• Showing too many decimal places.

47. Practical Chart Workflow

1. Define the analytical question.
2. Prepare and clean the dataset.
3. Select the relevant fields.
4. Choose the correct chart type.
5. Create the chart.
6. Add a meaningful title.
7. Format axes and labels.
8. Remove unnecessary elements.
9. Check for misleading presentation.
10. Interpret the chart.
11. Communicate the insight.

48. Practical Exercise

Create an Excel workbook containing the following sales dataset:

Date
Product
Category
Region
Salesperson
Quantity
Sales

Create the following visualizations:

1. Column chart showing sales by product.
2. Bar chart showing sales by region.
3. Line chart showing monthly sales.
4. Pie or doughnut chart showing category contribution.
5. Scatter chart comparing quantity and sales.
6. Combination chart comparing revenue and growth.

Add meaningful titles and labels to each chart.

49. Dashboard Exercise

Build a simple Excel dashboard containing:

• Total Sales KPI.
• Total Transactions KPI.
• Sales by Product chart.
• Monthly Sales trend.
• Regional Sales chart.
• At least one slicer.

The dashboard should be easy to read and should answer important
business questions.

50. Knowledge Check

1. What is data visualization?
2. Why are charts important in data analysis?
3. When should you use a line chart?
4. When is a bar chart useful?
5. What is a scatter chart used for?
6. What is a histogram?
7. What is a Pivot Chart?
8. What is the purpose of a chart title?
9. Why should charts avoid unnecessary 3D effects?
10. What is a secondary axis?
11. Why can misleading scales be dangerous?
12. What role do charts play in dashboards?

51. Key Terms

• Chart: A graphical representation of data.
• Data Visualization: The graphical presentation of information.
• Data Series: A related group of values displayed in a chart.
• Axis: A reference line used to organize chart values.
• Legend: An element identifying chart series.
• Data Label: A label displaying a data value.
• Scatter Chart: A chart showing the relationship between numerical variables.
• Histogram: A chart showing the distribution of numerical data.
• Pivot Chart: A chart connected to a Pivot Table.
• Trendline: A line representing the general direction of data.

52. Key Takeaways

• Charts transform numerical information into visual information.
• Choose charts according to the analytical question.
• Column and bar charts are useful for comparisons.
• Line charts are useful for trends over time.
• Pie and doughnut charts can show proportions.
• Scatter charts can show relationships between numerical variables.
• Histograms show distributions.
• Combination charts can compare different measures.
• Pivot Charts work with Pivot Tables.
• Good chart design improves communication.
• Charts should never intentionally distort information.

53. Summary

Charts are an essential part of Excel data analysis because they help
turn numerical information into understandable visual stories.

The most important skill is not simply knowing how to insert a chart.
The analyst must understand which visualization is appropriate for the
question being asked.

Professional charts should be accurate, simple, readable and
purpose-driven. When combined with Pivot Tables, Slicers and
dashboards, Excel charts can become powerful tools for communicating
analytical insights.

Next Lesson

In the next lesson, we will learn about
Basic Dashboards and how to combine Excel tables,
Pivot Tables, charts, KPIs and filters into an interactive analytical
report.