Arithmetic Operation in excel file using openpyxl in Python

Openpyxl is a library in Python with which one can perform the different mathematic operations in an excel sheet. In this tutorial, we are going to learn the implementation of mathematical functions available in openpyxl library. Mathematical operations like sum, product, average, quotient, count, etc. are simple, straight forward, and easy to implement in Python using openpyxl library.

Arithmetic Operations in excel file with Python.

We can perform different operations like reading, writing, plotting graphs, etc. too with openpyxl.

Let’s see how we can implement these methods.

The first step is – importing openpyxl library

import openpyxl

The second step is to call a workbook() function and create a workbook object.

wb = openpyxl.Workbook()

Next, get the worksheet using an active attribute.

sheet = wb.active

After creating the empty worksheet, fill the empty cells with some random values to perform mathematical operation later.

sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = 400
sheet['A4'] = 500
sheet['A5'] = 600

Firstly, we will see SUM() method – ‘=SUM(cell1:cell2)’

sheet['A7'] = '= SUM(A1:A5)'

Here, the formula is set to the cell ‘A7’ that sums the values of ‘A1’, ‘A2’, ‘A3’, ‘A4’ and ‘A5’ and print the results in ‘A7’

To save the file

wb.save("sum.xlsx")

Second is the Product() method – ‘=PRODUCT(cell1:cell2)’ returns the product of the numbers in the cells.

wb2 = openpyxl.Workbook() 
sheet2 = wb2.active 
  
sheet2['A1'] = 2
sheet2['A2'] = 3
sheet2['A3'] = 4
sheet2['A4'] = 5
sheet2['A5'] = 6
  
sheet2['A7'] = '= PRODUCT(A1:A5)'
  
wb2.save("product.xlsx")

It sets the formula to the cell ‘A7’ and returns the multiplication of the numbers in all the cells. At the end of the code save the file.

Third is Average() method – ‘=AVERAGE(cell1:cell2)’ returns the average value of the given cells.

wb3 = openpyxl.Workbook() 
sheet3 = wb3.active 
  
sheet3['A1'] = 200
sheet3['A2'] = 300
sheet3['A3'] = 400
sheet3['A4'] = 500
sheet3['A5'] = 600
  
sheet3['A7'] = '= AVERAGE(A1:A5)'
  
wb3.save("average.xlsx")

Works similar to the above, it calculates the arithmetic mean of all the numbers in a given cell range.

Fourth is quotient() method – ‘=QUOTIENT(num1, num2)’

wb4 = openpyxl.Workbook() 
sheet4 = wb4.active 
  
sheet4['A1'] = '= QUOTIENT(64, 8)'
sheet4['A2'] = '= QUOTIENT(25, 4)'
  
wb4.save("quotient.xlsx")

It returns the quotient from the division operation where num1 is the dividend and num2 is the divisor.

Fifth is the MOD() method – ‘=MOD(num1, num2)’

wb5 = openpyxl.Workbook() 
sheet5 = wb5.active 
   
sheet5['A1'] = '= MOD(64, 8)'
sheet5['A2'] = '= MOD(25, 4)'
  
wb5.save("modulus.xlsx")

It returns the remainder of the division operation where num1 is dividend and num2 is the divisor.

and the last one is count() method – ‘=COUNT(cell1:cell2)’

wb6 = openpyxl.Workbook() 
sheet6 = wb6.active 
  
sheet6['A1'] = 200
sheet6['A2'] = 300
sheet6['A3'] = 400
sheet6['A4'] = 500
sheet6['A5'] = 600
  
sheet6['A7'] = '= COUNT(A1:A6)'
  
wb6.save("count.xlsx")

It counts the number of cells that contain the value.