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")

Output:

Explanation of the above code:

1. from openpyxl import Workbook – This line imports a tool (Workbook) from a library (openpyxl) that helps Python work with Excel files.
2. from openpyxl.drawing.image import Image – This line imports another tool (Image) from the same library that helps Python handle images in Excel.
3. import matplotlib.pyplot as plt – This line imports a tool (pyplot) from the matplotlib library, which is used for creating plots and graphs.
4. wb = Workbook() – This line creates a new Excel workbook (a new Excel file) and assigns it to the variable wb.
5. ws = wb.active – This line selects the active sheet (the sheet you’re currently working on) in the Excel workbook and assigns it to the variable ws.
6. data = [...] – This block of code defines a list of lists, where each inner list represents a row of data in the Excel sheet. The first inner list contains column headers (Month, Product A, Product B), and the other inner lists contain the actual sales data for each month.
7. for row in data: ws.append(row) – This loop iterates over each inner list in the data list and appends it as a row in the Excel sheet.
8. plt.figure(figsize=(8, 6)) – This line creates a new figure (plot) with a specific size (8 inches wide, 6 inches tall) using matplotlib.
9. for i in range(1, len(data)): – This loop iterates over the rows of data (excluding the header row) in the data list.
10. plt.plot([1, 2], [data[i][1], data[i][2]], marker='o', markersize=10) – This line plots a data point for each product (Product A and Product B) for the current row of data. The [1, 2] specifies the x-values (Product A and Product B), and [data[i][1], data[i][2]] specifies the y-values (sales for Product A and Product B).
11. plt.xticks([1, 2], ['Product A', 'Product B']) – This line sets the labels for the x-axis (Product A and Product B).
12. plt.ylabel('Sales') – This line sets the label for the y-axis (Sales).
13. plt.title('Dumbbell Chart') – This line sets the title of the chart to “Dumbbell Chart”.
14. plt.grid(True) – This line adds a grid to the plot.
15. plt.tight_layout() – This line adjusts the layout of the plot to make it fit better.
16. plt.savefig('dumbbell_chart.png') – This line saves the plot as an image file named “dumbbell_chart.png”.
17. img = Image('dumbbell_chart.png') – This line creates an Image object from the saved image file.
18. ws.add_image(img, 'E1') – This line adds the image to the Excel sheet at cell ‘E1’.
19. wb.save("sales_data.xlsx") – This line saves the Excel workbook (with the data and chart) to a file named “sales_data.xlsx”.

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

Example:
Let’s say 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 visualize the change in sales for each product and a Stacked Bar Graph to compare the total sales of both products over the three months.

Code:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
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()
plt.savefig('dumbbell_chart.png')
img_dumbbell = Image('dumbbell_chart.png')

# Add the Dumbbell Chart image to the Excel sheet
ws.add_image(img_dumbbell, 'E1')

# Create a Stacked Bar Graph
chart = BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Stacked Bar Graph"
chart.y_axis.title = "Sales"
chart.x_axis.title = "Month"

data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
cats_ref = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.shape = 4
ws.add_chart(chart, "E15")

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

Output:

Explanation of the above code:

1. from openpyxl import Workbook – This line imports a tool (Workbook) from a library (openpyxl) that helps Python work with Excel files.
2. from openpyxl.chart import BarChart, Reference – This line imports two tools (BarChart and Reference) from the openpyxl library that help create charts in Excel.
3. from openpyxl.drawing.image import Image – This line imports a tool (Image) from the openpyxl library that helps handle images in Excel.
4. import matplotlib.pyplot as plt – This line imports a tool (pyplot) from the matplotlib library, which is used for creating plots and graphs.
5. wb = Workbook() – This line creates a new Excel workbook (a new Excel file) and assigns it to the variable wb.
6. ws = wb.active – This line selects the active sheet (the sheet you’re currently working on) in the Excel workbook and assigns it to the variable ws.
7. data = [...] – This block of code defines a list of lists, where each inner list represents a row of data in the Excel sheet. The first inner list contains column headers (Month, Product A, Product B), and the other inner lists contain the actual sales data for each month.
8. for row in data: ws.append(row) – This loop iterates over each inner list in the data list and appends it as a row in the Excel sheet.
9. plt.figure(figsize=(8, 6)) – This line creates a new figure (plot) with a specific size (8 inches wide, 6 inches tall) using matplotlib.
10. for i in range(1, len(data)): plt.plot([1, 2], [data[i][1], data[i][2]], marker='o', markersize=10) – This loop iterates over the rows of data (excluding the header row) in the data list and plots a data point for each product (Product A and Product B) for the current row of data.
11. plt.xticks([1, 2], ['Product A', 'Product B']) – This line sets the labels for the x-axis (Product A and Product B).
12. plt.ylabel('Sales') – This line sets the label for the y-axis (Sales).
13. plt.title('Dumbbell Chart') – This line sets the title of the chart to “Dumbbell Chart”.
14. plt.grid(True) – This line adds a grid to the plot.
15. plt.tight_layout() – This line adjusts the layout of the plot to make it fit better.
16. plt.savefig('dumbbell_chart.png') – This line saves the plot as an image file named “dumbbell_chart.png”.
17. img_dumbbell = Image('dumbbell_chart.png') – This line creates an Image object from the saved image file.
18. ws.add_image(img_dumbbell, 'E1') – This line adds the image to the Excel sheet at cell ‘E1’.
19. chart = BarChart() – This line creates a new BarChart object.
20. chart.type = "col" – This line sets the chart type to a column chart.
21. chart.style = 10 – This line sets the chart style to style 10.
22. chart.title = "Stacked Bar Graph" – This line sets the title of the chart to “Stacked Bar Graph”.
23. chart.y_axis.title = "Sales" – This line sets the label for the y-axis (Sales).
24. chart.x_axis.title = "Month" – This line sets the label for the x-axis (Month).
25. data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4) – This line creates a reference to the data in the Excel sheet (sales data for both products over three months).
26. cats_ref = Reference(ws, min_col=1, min_row=2, max_row=4) – This line creates a reference to the categories (months) in the Excel sheet.
27. chart.add_data(data_ref, titles_from_data=True) – This line adds the data to the chart and uses the first row of data as titles for the legend.
28. chart.set_categories(cats_ref) – This line sets the categories (months) for the chart.
29. chart.shape = 4 – This line sets the shape of the bars in the chart.
30. ws.add_chart(chart, "E15") – This line adds the chart to the Excel sheet at cell ‘E15’.
31. wb.save("sales_data.xlsx") – This line saves the Excel workbook (with the data, Dumbbell Chart, and Stacked Bar Graph) to a file named “sales_data.xlsx”.

In conclusion,

using Python, you can automate the creation of Dumbbell Charts and Stacked Bar Graphs in Excel. These charts help visualize data, making it easier to understand trends and comparisons. By leveraging libraries like openpyxl and matplotlib, you can generate these charts directly in Excel, saving time and effort. This automation can be particularly useful for analyzing and presenting sales data, as demonstrated in the examples.