Control Excel via Python With OpenPyXL

In my formal job, as an analyst (Real estate), I used VBA to control Excel spreadsheets. It is quite convenient to use VBA macro to control Excel. No Env-setting, just save the file with .xlsm and you can simply share the code with the file to your colleagues or friends. However, sometimes I found out that VBA is not good at sorting data lists such as concatenation or format, list method…etc.

So I decided to use Python to control Excel with the library Openpyxl.


  • Python installed
  • any text editor / or use jupyter notebook
  • library “openpyxl” installed — by command line: pip install openpyxl (or pip3 install openpyxl on mac)
  • Of course, MS-Excel program


from openpyxl.workbook import Workbook
from openpyxl import load_workbook
# create a workbook obj
wb = Workbook()
#create an active worksheet
ws =
#load existing spreadsheet, here we are loading the file in the same folder
wb = load_workbook('youFileName.xlsx')
ws =

First, we need to import the methods. (the first 2 lines) Then create a wb object and active worksheet.

Grab a cell from a spreadsheet

#print something from our spreadsheet

#use python build-in function to play with spreadsheet
print(f'Eng: {ws["A2"].value}, Jap: {ws["B2"].value}')


Eng: accountant, Jap: 会計士

Grab a whole column

We can see that the col is saved as tuples. So we can loop it and append them to a list as the image below.

Grab ranges

We can notice that the output is tuples inside a tuple… there are double parentheses.

So we need to for-loop it twice to get the value of the cells.

Iterate Thru Rows(or Cols) via “.iter_rows()”

We are using the method .iter_rows() to iterate. So there are a couple parameters we could put in.

Here is my example, it would loop thru row 1 to 5 on col 1 and getting “.value” only.

  • min_row=1
  • max_row=5
  • min_col=1
  • max_col=1
  • values_only=True

As you can see from the image above, the output is “value” but in the tuple data type. So we need to for-loop it again.

Change cells and save file

It is really easy to change cells’ value, just simply assign value to the cell. For example:

ws['A2'] = "Hi"
ws.cell(row=3,column=1).value = "Hello"

Save the file just simply use “‘SavedFileName.xlsx’) ” and it would save the file under the same folder. If you want to save it to another folder, just simply add the absolute path to the folder.

Use Formulas with Python code

It is really simple. Just put the formula including “=” and assign it to the target cell. Such as:

ws['C6'] = "=SUM(C2:C5)"

Change Style of the cell

FIrst, we need to import the font, then, use Font() method with the properties we want to change.

#import Font, Border, Side
from openpyxl.styles import Font, Border, Side

c = ws['A1']
c.font = Font(size=30, bold=True, italic=False, color="226622")

On the other hand, to play with the border, we need to define a border first by Side() method, then, we use Border() to apply the side we defined to the target cell as the example below:

mybd = Side(style="thick", color="d80d0d")
ws['B3'].border = Border(left=mybd,right=mybd,top=mybd,bottom=mybd)

So in practice, we could use if-condition to decide which cell should have what kind of style. Very easy to customize, right?


I guess the basic knowledge of openpyxl will let us cover most of the VBA functions. Especially, we can use powerful Python build-in methods that can let us work much more efficiently.

Leave a Comment

Your email address will not be published. Required fields are marked *

Follow by Email