How To Create Dumbbell Chart & Stacked Bar Graphs in Excel Automatically with Python

In the world of data visualization, Dumbbell Charts and Stacked Bar Graphs are powerful tools used to represent data trends and comparisons. These visualizations help convey complex information in a clear and concise manner, making it easier for viewers to understand the data. In this tutorial, we will explore how to create Dumbbell Charts and Stacked Bar Graphs in Excel automatically using Python.

Dumbbell Chart:

A Dumbbell Chart, also known as a Dumbbell Plot or Connected Dot Plot, is used to compare two sets of data points. It consists of two markers connected by a line, with each marker representing a data point. Dumbbell Charts are effective for showing changes between two points in time or comparing two different groups.

Data visualization is a crucial aspect of data analysis, helping to communicate insights effectively. Dumbbell Charts and Stacked Bar Graphs are popular visualization types for comparing data sets. In this tutorial, we’ll show you how to create these visualizations in Excel automatically using Python.

Stacked Bar Graph:

A Stacked Bar Graph is used to represent multiple data series in a single bar, where each segment of the bar represents a different category or subgroup. Stacked Bar Graphs are useful for comparing the total values across categories and seeing the contribution of each category to the total.

A Stacked Bar Graph is used to represent multiple data series in a single bar, with each segment of the bar representing a different category or subgroup. Stacked Bar Graphs are useful for comparing total values across categories and showing the contribution of each category to the total.

Example:

Let’s consider a scenario where we have data for the sales performance of two products, Product A and Product B, over three months. We want to create a Dumbbell Chart to show the change in sales for each product .

Code:

from openpyxl import Workbook
from openpyxl.drawing.image import Image
import matplotlib.pyplot as plt

# Create a new Excel workbook
wb = Workbook()
ws = wb.active

# Add data to the Excel sheet
data = [
    ["Month", "Product A", "Product B"],
    ["Jan", 100, 120],
    ["Feb", 110, 130],
    ["Mar", 120, 140]
]

for row in data:
    ws.append(row)

# Create a Dumbbell Chart
plt.figure(figsize=(8, 6))
for i in range(1, len(data)):
    plt.plot([1, 2], [data[i][1], data[i][2]], marker='o', markersize=10)

plt.xticks([1, 2], ['Product A', 'Product B'])
plt.ylabel('Sales')
plt.title('Dumbbell Chart')
plt.grid(True)
plt.tight_layout()

# Save the chart as an image
plt.savefig('dumbbell_chart.png')

# Add the image to the Excel file
img = Image('dumbbell_chart.png')
ws.add_image(img, 'E1')

# Save the Excel workbook
wb.save("sales_data.xlsx")

Read More