How to Visualize Data Using Charts

Using charts and graphs, you can visualize data for a more intuitive understanding.

For simple data, drawing charts directly in Excel is faster.

However, when dealing with large-scale data or frequently updated data, automating chart creation through programming is much more efficient.

openpyxl provides useful methods to create charts and graphs with code.

In this session, we’ll learn how to add charts to an Excel file using openpyxl.

Note: In this lesson, we’ll cover creating line charts and bar charts. However, openpyxl supports various chart types, including doughnut charts and pie charts.

A line chart is often used to visualize data changes over time.

Below is an example of Python code to create a line chart using openpyxl.

Copy
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Input data (e.g., monthly sales)
data = [
    ['Month', 'Sales'],
    ['January', 30],
    ['February', 40],
    ['March', 50],
    ['April', 20],
    ['May', 60],
]

for row in data:
    ws.append(row)

# Create a chart
line_chart = LineChart()

# Reference the chart data (B2 to B6)
data = Reference(ws, min_col=2, min_row=2, max_row=6)

# Reference the X-axis (category) labels (A2 to A6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

# Exclude titles from the data
line_chart.add_data(data, titles_from_data=False)
line_chart.set_categories(cats)
line_chart.title = "Monthly Sales"
line_chart.x_axis.title = "Month"
line_chart.y_axis.title = "Sales"

# Add the chart to the sheet (at cell D1)
ws.add_chart(line_chart, "A10")

# Save the file
wb.save("output_file.xlsx")

• line_chart = LineChart(): Creates a line chart object

• Reference: References the data range and categories (months) for the chart

• line_chart.add_data(data, titles_from_data=False): Adds data to the chart without including titles

• line_chart.set_categories(cats): Sets the X-axis (category) labels

• line_chart.title: Sets the chart title

• line_chart.x_axis.title: Sets the X-axis title

• line_chart.y_axis.title: Sets the Y-axis title

• ws.add_chart(line_chart, "A10"): Adds the chart to the sheet

For simplicity, the code defines monthly sales data in a data variable as a list.

Using openpyxl, you can also specify a file path (e.g., C:/Users/Username/Desktop/output_file.xlsx) to load an Excel file, read the data, and create a chart.

Copy
from openpyxl import load_workbook

# Load an Excel file
wb = load_workbook("C:/Users/Username/Desktop/input_file.xlsx")
ws = wb.active

# Read data
data = []

for row in ws.iter_rows(min_row=2, values_only=True):
    data.append(row)

A bar chart is commonly used to compare data sizes.

Below is an example of Python code to create a bar chart.

Copy
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Input data (e.g., monthly revenue)
data = [
    ['Month', 'Revenue'],
    ['January', 1000],
    ['February', 1500],
    ['March', 1200],
    ['April', 1700],
    ['May', 1300],
]

for row in data:
    ws.append(row)

# Create a chart
bar_chart = BarChart()

# Reference the chart data (B2 to B6)
data = Reference(ws, min_col=2, min_row=2, max_row=6)

# Reference the X-axis (category) labels (A2 to A6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

# Exclude titles from the data
bar_chart.add_data(data, titles_from_data=False)
bar_chart.set_categories(cats)
bar_chart.title = "Monthly Revenue"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Revenue"

# Add the chart to the sheet (at cell D1)
ws.add_chart(bar_chart, "A10")

# Save the file
wb.save("output_file.xlsx")

• bar_chart = BarChart(): Creates a bar chart object

• Reference: References the data range and categories (months) for the chart

• bar_chart.add_data(data, titles_from_data=False): Adds data to the chart without including titles

• bar_chart.set_categories(cats): Sets the X-axis (category) labels

• bar_chart.title: Sets the chart title

• bar_chart.x_axis.title: Sets the X-axis title

• bar_chart.y_axis.title: Sets the Y-axis title

• ws.add_chart(bar_chart, "A10"): Adds the chart to the sheet

Using openpyxl, you can create various chart types such as pie charts, doughnut charts, and histograms in addition to line and bar charts.

You can learn more about each chart type in the chart module.