Data visualization is a powerful way to analyze and communicate data trends. While Excel provides built-in tools for creating charts and graphs, Python offers additional capabilities for enhancing visualizations. In this article, we’ll explore how to use Python to create dynamic and interactive data visualizations in Excel.

Introduction to Data Visualization

Data visualization is the graphical representation of data to help users understand complex data sets. It allows users to identify trends, patterns, and outliers in data, making it easier to interpret and analyze.

Why Use Python for Data Visualization in Excel?

While Excel provides basic charting tools, Python offers more flexibility and customization options for creating visualizations. Python libraries such as pandas, matplotlib, and openpyxl allow you to manipulate data and create sophisticated charts that can be embedded directly into Excel.

Getting Started

To begin, you’ll need to install the necessary Python libraries. You can do this using pip, the Python package manager. Open a terminal and run the following commands:

pip install pandas matplotlib openpyxl

Example 1: Creating a Simple Line Chart

Let’s start with a simple example of creating a line chart in Excel using Python. We’ll use sample data to plot the trend of sales over time.

import pandas as pd
import matplotlib.pyplot as plt

# Sample data
data = {'Year': [2018, 2019, 2020, 2021, 2022],
        'Sales': [100000, 120000, 150000, 180000, 200000]}

df = pd.DataFrame(data)

# Create a line chart
plt.plot(df['Year'], df['Sales'])
plt.xlabel('Year')
plt.ylabel('Sales')
plt.title('Sales Trend Over Time')

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

# Close the plot
plt.close()

Output:

Now if you want to automate the same in an Excel sheet such that we have an excel sheet created inside which we have 2 columns for the sales and years respectively and data in respective rows and beside it, the line chart should come then the Python code to automate it is below:

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

# Sample data
years = [2015, 2016, 2017, 2018, 2019, 2020]
sales = [1000, 1500, 1200, 1800, 2000, 2500]

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

# Add the sample data to the Excel sheet
ws['A1'] = 'Year'
ws['B1'] = 'Sales'
for i, (year, sale) in enumerate(zip(years, sales), start=2):
    ws[f'A{i}'] = year
    ws[f'B{i}'] = sale

# Add a line chart
chart = LineChart()
chart.title = "Sales Trend"
chart.style = 13
chart.x_axis.title = 'Year'
chart.y_axis.title = 'Sales'

data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len(sales)+1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(years)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

ws.add_chart(chart, "D2")

# Save the Excel workbook
wb.save('sales_data_with_chart.xlsx')

Output:

Code Explanation:

1. from openpyxl import Workbook: Importing the Workbook class from the openpyxl module to create a new Excel workbook.
2. from openpyxl.chart import LineChart, Reference: Importing the LineChart and Reference classes from the openpyxl.chart module for creating a line chart.
3. years = [2015, 2016, 2017, 2018, 2019, 2020]: Creating a list of years as sample data.
4. sales = [1000, 1500, 1200, 1800, 2000, 2500]: Creating a list of sales data corresponding to each year.
5. wb = Workbook(): Creating a new Excel workbook object.
6. ws = wb.active: Getting the active sheet of the workbook.
7. Setting the headers for the data in the Excel sheet: ws['A1'] = 'Year' and ws['B1'] = 'Sales'.
8. Looping through the sample data and adding it to the Excel sheet:
   • for i, (year, sale) in enumerate(zip(years, sales), start=2): Using enumerate to iterate over both years and sales lists, starting from index 2.
   • ws[f'A{i}'] = year and ws[f'B{i}'] = sale: Adding the year and sale values to columns ‘A’ and ‘B’ respectively, at row i.
9. Creating a new LineChart object and setting its properties:
   • chart = LineChart(): Creating a new line chart object.
   • chart.title = "Sales Trend": Setting the chart title.
   • chart.style = 13: Setting the chart style.
   • chart.x_axis.title = 'Year' and chart.y_axis.title = 'Sales': Setting the titles for the X and Y axes.
10. Adding data to the chart:
    • data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len(sales)+1): Creating a reference to the data in the Excel sheet (sales data).
    • categories = Reference(ws, min_col=1, min_row=2, max_row=len(years)+1): Creating a reference to the categories (years).
    • chart.add_data(data, titles_from_data=True): Adding the data to the chart, with titles automatically extracted from the data.
    • chart.set_categories(categories): Setting the categories (X-axis values) for the chart.
11. Adding the chart to the Excel sheet at cell “D2”: ws.add_chart(chart, "D2").
12. Saving the Excel workbook: wb.save('sales_data_with_chart.xlsx').

This code creates a new Excel workbook with a sheet containing sample sales data and a line chart showing the sales trend over the years.

The above code demonstrates how Python can be used to automate the process of data visualization in Excel, offering several benefits for real-life applications:

1. Efficiency: Automating the creation of charts in Excel using Python can save time compared to manually creating and updating charts, especially for large datasets or when frequent updates are needed.
2. Consistency: Python automation ensures that charts are generated consistently, eliminating the risk of human error that can occur with manual data entry.
3. Scalability: Python scripts can handle large datasets and complex charting requirements, making it suitable for a wide range of data visualization tasks.
4. Flexibility: Python offers more flexibility in customizing charts and layouts than Excel’s built-in charting tools, allowing for more creative and tailored visualizations.
5. Integration: Python can easily integrate with other data processing and analysis tools, enabling seamless data workflows from data processing to visualization.
6. Reproducibility: Since the code can be saved and reused, the same visualization can be reproduced at any time, ensuring reproducibility of results.

Let us take another example , this time lets go for bar graph and we are going to add it in subsheet for the excel we have already created above

P.S: If you are struggling to create an automated Excel follow step-by-step guidelines on how to create it

Creating a bar plot with data labels

# Sample data
data = {'Product': ['A', 'B', 'C', 'D'],
        'Sales': [50000, 70000, 90000, 60000]}

df = pd.DataFrame(data)

# Create a bar chart with data labels
ax = df.plot(kind='bar', x='Product', y='Sales', legend=None)
for p in ax.patches:
    ax.annotate(str(p.get_height()), (p.get_x() + p.get_width() / 2., p.get_height()), ha='center', va='center', xytext=(0, 10), textcoords='offset points')

plt.xlabel('Product')
plt.ylabel('Sales')
plt.title('Product Sales')

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

# Close the plot
plt.close()

Output:

Now let us automate in an Excel sheet that we have an excel sheet created inside which we have 2 columns for the sales and product respectively and data in respective rows and Besides it, the line chart should come then the Python code to automate it is below:

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

# Sample data
products = ["Product A", "Product B", "Product C", "Product D"]
sales = [100, 150, 120, 180]

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

# Add a sub-sheet named "sales_data"
ws2 = wb.create_sheet(title="sales_data")

# Add the sample data to the sub-sheet
ws2['A1'] = 'Product'
ws2['B1'] = 'Sales'
for i, (product, sale) in enumerate(zip(products, sales), start=2):
    ws2[f'A{i}'] = product
    ws2[f'B{i}'] = sale

# Add a bar chart
chart = BarChart()
chart.title = "Sales by Product"
chart.style = 13
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'

data = Reference(ws2, min_col=2, min_row=1, max_col=2, max_row=len(sales)+1)
categories = Reference(ws2, min_col=1, min_row=2, max_row=len(products)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

ws2.add_chart(chart, "D2")

# Save the Excel workbook
wb.save('sales_data_with_chart.xlsx')

Output:

Code Explanation:

1. from openpyxl import Workbook: Import the Workbook class from the openpyxl module to create a new Excel workbook.
2. from openpyxl.chart import BarChart, Reference: Import the BarChart class and the Reference class from the openpyxl.chart module for creating a bar chart in Excel.
3. products = ["Product A", "Product B", "Product C", "Product D"]: Define a list of product names.
4. sales = [100, 150, 120, 180]: Define a list of corresponding sales figures for each product.
5. wb = Workbook(): Create a new Excel workbook.
6. ws = wb.active: Get the active worksheet in the workbook.
7. ws2 = wb.create_sheet(title="sales_data"): Create a new worksheet (sub-sheet) with the title “sales_data”.
8. Add headers to the “sales_data” worksheet:
   • ws2['A1'] = 'Product': Set the value of cell A1 to “Product”.
   • ws2['B1'] = 'Sales': Set the value of cell B1 to “Sales”.
9. Add the sample data to the “sales_data” worksheet using a loop:
   • enumerate(zip(products, sales), start=2): Iterate over the products and sales lists, starting from index 2 (to skip the header row).
   • ws2[f'A{i}'] = product: Set the value of the current row and column A to the product name.
   • ws2[f'B{i}'] = sale: Set the value of the current row and column B to the sales figure.
10. Create a BarChart object for the bar chart:
    • chart = BarChart(): Create a new instance of the BarChart class.
    • Set chart properties like title, style, x-axis title, and y-axis title.
11. Define the data range and categories for the chart:
    • data = Reference(ws2, min_col=2, min_row=1, max_col=2, max_row=len(sales)+1): Define a reference to the data range (sales figures).
    • categories = Reference(ws2, min_col=1, min_row=2, max_row=len(products)+1): Define a reference to the categories (product names).
12. Add the data and categories to the chart:
    • chart.add_data(data, titles_from_data=True): Add the data to the chart, and use the data values as titles.
    • chart.set_categories(categories): Set the categories for the chart.
13. Add the chart to the “sales_data” worksheet:
    • ws2.add_chart(chart, "D2"): Add the chart to cell D2 of the worksheet.
14. Save the Excel workbook:
    • wb.save('sales_data_with_chart.xlsx'): Save the workbook to a file named “sales_data_with_chart.xlsx”.

This code creates an Excel workbook with a worksheet containing sales data and a bar chart representing the sales by product.

Now, that we have seen how you can automate data along with graphs in the Excel sheet let us create the above code in a sub-sheet in the same excel workbook. To combine the two examples above, creating a simple line chart and creating a bar chart with sample data, into a single Python script that adds both charts to an Excel workbook below is the code you can copy paste in your editor and try yourself with additions.

code:

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

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

# Example 1: Creating a simple line chart
# Sample data for the line chart
years = [2010, 2011, 2012, 2013, 2014, 2015]
sales = [500, 600, 700, 800, 900, 1000]

# Add the sample data to the worksheet
ws['A1'] = 'Year'
ws['B1'] = 'Sales'
for i, (year, sale) in enumerate(zip(years, sales), start=2):
    ws[f'A{i}'] = year
    ws[f'B{i}'] = sale

# Add a line chart
line_chart = LineChart()
line_chart.title = "Sales Over Years"
line_chart.style = 13
line_chart.x_axis.title = 'Year'
line_chart.y_axis.title = 'Sales'

data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len(sales)+1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(years)+1)
line_chart.add_data(data, titles_from_data=True)
line_chart.set_categories(categories)

ws.add_chart(line_chart, "D2")

# Example 2: Creating a bar chart in a sub-sheet
# Sample data for the bar chart
products = ["Product A", "Product B", "Product C", "Product D"]
sales_data = [100, 150, 120, 180]

# Add a sub-sheet named "sales_data"
ws2 = wb.create_sheet(title="sales_data")

# Add the sample data to the sub-sheet
ws2['A1'] = 'Product'
ws2['B1'] = 'Sales'
for i, (product, sale) in enumerate(zip(products, sales_data), start=2):
    ws2[f'A{i}'] = product
    ws2[f'B{i}'] = sale

# Add a bar chart to the sub-sheet
bar_chart = BarChart()
bar_chart.title = "Sales by Product"
bar_chart.style = 13
bar_chart.x_axis.title = 'Product'
bar_chart.y_axis.title = 'Sales'

data = Reference(ws2, min_col=2, min_row=1, max_col=2, max_row=len(sales_data)+1)
categories = Reference(ws2, min_col=1, min_row=2, max_row=len(products)+1)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)

ws2.add_chart(bar_chart, "D2")

# Save the Excel workbook
wb.save('sales_data_with_charts.xlsx')

Let’s see the output – it should have an excel sheet with two sub sheet one with a line graph and the other with bar plot

Code Explanation:

1. from openpyxl import Workbook: Import the Workbook class from the openpyxl module to create a new Excel workbook.
2. from openpyxl.chart import LineChart, BarChart, Reference: Import the LineChart, BarChart, and Reference classes from the openpyxl.chart module for creating charts in Excel.
3. wb = Workbook(): Create a new Excel workbook.
4. ws = wb.active: Get the active worksheet in the workbook.
5. Example 1: Creating a simple line chart
   • Sample data for the line chart (years and sales lists).
   • Add the sample data to the worksheet using a loop.
   • Create a line chart (line_chart), set its title and axis titles.
   • Define the data range (data) and categories (categories) for the chart.
   • Add the data and categories to the chart.
   • Add the chart to the worksheet at cell D2.
6. Example 2: Creating a bar chart in a sub-sheet
   • Sample data for the bar chart (products and sales_data lists).
   • Create a new sub-sheet (ws2) named “sales_data”.
   • Add the sample data to the sub-sheet using a loop.
   • Create a bar chart (bar_chart), set its title and axis titles.
   • Define the data range (data) and categories (categories) for the chart.
   • Add the data and categories to the chart.
   • Add the chart to the sub-sheet at cell D2.
7. wb.save('sales_data_with_charts.xlsx'): Save the Excel workbook to a file named “sales_data_with_charts.xlsx”.

Similarly, you can perform data visualization with various data and various plotting

3D Scatter plot

import pandas as pd
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

# Sample data
data = {'Study Hours': [2, 3, 4, 5, 6, 7, 8, 9, 10, 11],
        'Exam Scores': [85, 90, 88, 92, 95, 85, 92, 98, 75, 80],
        'Grade': [1, 2, 1, 2, 3, 2, 3, 3, 1, 2]}

df = pd.DataFrame(data)

# Create a 3D scatter plot
fig = plt.figure()
ax = fig.add_subplot(111, projection='3d')
ax.scatter(df['Study Hours'], df['Exam Scores'], df['Grade'])
ax.set_xlabel('Study Hours')
ax.set_ylabel('Exam Scores')
ax.set_zlabel('Grade')
plt.title('Relationship between Study Hours, Exam Scores, and Grade')

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

# Close the plot
plt.close()

You can automate the above plot with your Excel and add data

Data visualization in Excel using Python offers a flexible and powerful way to create interactive and informative visualizations. By leveraging Python libraries, you can enhance your Excel charts and graphs with advanced features and customization options. Experiment with different visualization techniques to effectively communicate your data insights.