Open In App

How to Automate an Excel Sheet in Python?

Last Updated : 12 Sep, 2025
Comments
Improve
Suggest changes
9 Likes
Like
Report

Python provides openpyxl library using which you can automate Excel tasks programmatically. It allows you to read, write, edit, and format spreadsheets, apply formulas, and even generate charts directly from Python scripts saving both time and effort.

Installing Required Library

Install it using the following command:

pip install openpyxl

Example

  • We have an Excel sheet with product data:
    Column 3 → Prices (but they’re incorrect).
  • We need to
    1. Decrease each price by 10%
    2.Save corrected prices in Column 4
    3. Add a bar chart of the corrected prices
input_excel
input.png

Doing this manually for thousands of rows would take days - let's see how we can automate this using Python's openpyxl library:

1. Import Libraries

Python 
import openpyxl as xl
from openpyxl.chart import BarChart, Reference
  • openpyxl: Work with Excel.
  • Bar Chart, Reference: Used to create charts.

2. Open Workbook & Sheet

Python 
#loads the Excel file python-spreadsheet.xlsx and picks the worksheet "Sheet1" from it.
wb = xl.load_workbook("python-spreadsheet.xlsx")
sheet = wb["Sheet1"]

3. Loop Through Rows

Python 
#Iterating the sheet using for loop
for row in range(2, sheet.max_row + 1):
    cell = sheet.cell(row, 3)

4. Correct the Prices

Python 
corrected_price = float(cell.value.replace('$', '')) * 0.9
sheet.cell(row, 4).value = corrected_price
  • Removes the $ sign, converts value to number.
  • multiplies by 0.9 (10% discount)
  • writes corrected price in Column 4.

5. Add a Chart

Python 
values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4)
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "E2")
correct_chart
chart
  • Selects corrected prices (Column 4).
  • Creates a bar chart and places it at cell E2.

6. Save the File

Python 
#Saves everything into a new file so original data isn’t lost.
wb.save("python-spreadsheet2.xlsx")

7. Making It Reusable

If you want to process many spreadsheets, put the logic into a function:

Python 
def process_workbook(filename):
    wb = xl.load_workbook(filename)
    sheet = wb["Sheet1"]

    for row in range(2, sheet.max_row + 1):
        cell = sheet.cell(row, 3)
        corrected_price = float(cell.value.replace('$', '')) * 0.9
        sheet.cell(row, 4).value = corrected_price

    values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4)
    chart = BarChart()
    chart.add_data(values)
    sheet.add_chart(chart, "E2")

    wb.save(filename)

Now you can run:

Python 
process_workbook("python-spreadsheet.xlsx")

Output

output_excel
output.png

Complete Code

Python 
import openpyxl as xl
from openpyxl.chart import BarChart, Reference

# Load workbook & select sheet
wb = xl.load_workbook("python-spreadsheet.xlsx")
sheet = wb["Sheet1"]

# Loop through rows (starting from 2 to skip header)
for row in range(2, sheet.max_row + 1):
    cell = sheet.cell(row, 3)                   # Price column
    corrected_price = float(cell.value.replace('$', '')) * 0.9
    sheet.cell(row, 4).value = corrected_price  # Write to new column

# Select corrected prices (Column 4) for chart
values = Reference(sheet, min_row=2, max_row=sheet.max_row, min_col=4, max_col=4)

# Create bar chart
chart = BarChart()
chart.add_data(values)
sheet.add_chart(chart, "E2")   # Place chart at cell E2

# Save updated file
wb.save("python-spreadsheet2.xlsx")

A

anuupadhyay
Improve
Article Tags :

Explore

Lightbox