Using Python to Read Excel Files and Create Various Charts with Matplotlib

In many daily tasks we need to process data stored in Excel files and visualize it for better understanding and sharing. This guide shows how to read Excel files with Python and create various charts using pandas and matplotlib , optionally extending to seaborn for advanced plots.

Required libraries

We will use the following Python packages:

pandas – for reading and handling Excel files.

matplotlib – for creating plots.

openpyxl – required when reading .xlsx files.

Install them via:

pip install pandas matplotlib openpyxl

Example Excel template

The examples assume an Excel file named example_data.xlsx with columns A, B, C, D, E, F. Each column represents a variable that can be plotted.

Example 1: Line chart

import pandas as pd
import matplotlib.pyplot as pd

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.plot(df['A'], df['B'], label='Line AB')
plt.plot(df['A'], df['C'], label='Line AC')
plt.xlabel('A')
plt.ylabel('Values')
plt.title('Line Chart Example')
plt.legend()
plt.show()

This produces a line chart of columns B and C against A.

Example 2: Bar chart

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.bar(df['C'], df['D'])
plt.xlabel('C')
plt.ylabel('D')
plt.title('Bar Chart Example')
plt.show()

Creates a bar chart of D values for each C.

Example 3: Pie chart

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.pie(df['E'], labels=df.index, autopct='%1.1f%%')
plt.title('Pie Chart Example')
plt.show()

Displays a pie chart of column E.

Example 4: Scatter plot

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.scatter(df['F'], df['A'])
plt.xlabel('F')
plt.ylabel('A')
plt.title('Scatter Plot Example')
plt.show()

Shows the relationship between columns F and A.

Example 5: Box plot

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.boxplot([df['B'], df['C']], labels=['B', 'C'])
plt.ylabel('Values')
plt.title('Box Plot Example')
plt.show()

Compares the distribution of columns B and C.

Example 6: Histogram

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.hist(df['C'], bins=10)
plt.xlabel('C')
plt.ylabel('Frequency')
plt.title('Histogram Example')
plt.show()

Shows the frequency distribution of column C.

Example 7: Multiple subplots

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
plt.subplot(2, 2, 1)
plt.plot(df['A'], df['B'])
plt.title('Line AB')
plt.subplot(2, 2, 2)
plt.bar(df['C'], df['D'])
plt.title('Bar CD')
plt.subplot(2, 2, 3)
plt.scatter(df['E'], df['F'])
plt.title('Scatter EF')
plt.subplot(2, 2, 4)
plt.hist(df['A'], bins=5)
plt.title('Hist A')
plt.tight_layout()
plt.show()

Creates a 2×2 grid of different chart types.

Example 8: Heatmap with seaborn

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
plt.figure()
sns.heatmap(df.corr(), annot=True)
plt.title('Heatmap Example')
plt.show()

Displays a correlation heatmap of all numeric columns.

Example 9: Joint distribution plot with seaborn

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
sns.jointplot(data=df, x='D', y='E')
plt.show()

Shows a joint scatter and marginal histograms for columns D and E.

Example 10: Violin plot with seaborn

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

df = pd.read_excel('example_data.xlsx')
sns.violinplot(data=df, x='A', y='B')
plt.show()

Visualizes the distribution of B for each category in A.

Conclusion

These examples illustrate how Python’s powerful libraries can be combined with Excel data to perform a wide range of visual analyses. Adjust the code and data template as needed to fit specific analytical requirements.