Python is an amazing programming language. It is easier to learn and adapt. The error messages in python are self-explainable. We don’t need to invest hours to find the solution to our error message. That’s why I love this programming language.
I think this should be the ideal programming language. A programming language’s goal should be to help us build exciting products, not wasting time in solving errors.
Recently, I have learned about automating excel tasks using python. I thought of sharing those amazing tricks with a wider audience. This automating strategy use python programming. The good thing is – each line of python code is self explainable that you don’t need to google anything.
You can use the code editor of your choice. In this article, I will be using the Jupyter Notebook for demonstration purpose.
Installing the required library
We will need to install a python library, openpyxl
. Type the below command in your command prompt.
pip install openpyxl
Loading an existing workbook
If you have an existing excel workbook and you want to read the data from a specific sheet or modify data to any sheet openpyxl
provides a function named load_workbook()
that can be used as follows.
from openpyxl import Workbook, load_workbook
wb = load_workbook('my_demo_sheet.xlsx')

Accessing the worksheet
If you want to access the content from the active sheet. Then workbook has an attribute named active
that can help us point to the active sheet.
ws = wb.active
print(ws)
But, if you want to point to any other sheet, then we can do it like this. Here, I am accessing the sheet2
from my excel sheet.
ws = wb["Sheet2"]
Accessing cell value
Once we have defined the worksheet, we can refer to any cell in the sheet.
cell_to_access= ws['A5']
To get the value of the cell, we have to use the value
attribute.
cell_value = ws['A5']

Changing the value of a cell
We can use the below line of code to change any value in the excel sheet.
ws['B1'].value="Second Tab"

We also need to save the changes, as follows.
wb.save("my_demo_sheet.xlsx")
Getting sheet names
If you want to know the name of available sheets in your workbook, you can use the below python line of code to get the sheet’s name in the list form.
print(wb.sheetnames)

Accessing other sheets
Using .active
attribute, we can access the default active sheet. If you want to access another sheet, i.e. summary, then we can do it as follows.
ws = wb["summary"]
Create new sheet
To create a new sheet in the same excel workbook, we can use create_sheet()
as shown below. As we are modifying the sheet – we need to save the workbook to apply the changes to the excel workbook.
wb.create_sheet("Index_sheet")
wb.save("test1.xlsx")

Create new workbook
Creating a new Excel workbook is pretty simple. We need to call the function Workbook()
. As this is a new workbook, we need to set the worksheet as the default worksheet. We can also change the name of the worksheet using the title
attribute.
wb = Workbook()
To define the default sheet.
ws = wb.active
ws.title = "Demo_data"
Adding data to the worksheet
Once we have defined the worksheet, we can add the data using the append
function. This will add the data row-wise.
ws.append(["This","Is", "A", "Header"])
ws.save("New_WB.xlsx")

Adding new rows
We can use the insert_rows()
to add any number of rows. This function takes row_number as the input.
for i in range(2):
ws.insert_rows(row_num)
Deleting rows
We can use the delete_rows()
function to delete any row. This function takes column_number as the input.
for i in range(3):
ws.delete_rows(col_num)
Adding new columns
If you want to insert any column, then insert_cols()
function can add any columns using the loop. This function takes column_number as the input.
ws.insert_cols(column_number)
Deleting columns
To delete any column, you can use delete_cols()
function with column number as the input.
ws.delete_cols(column_number)
Moving data in the excel sheet
Let’s say you have an excel sheet with data, and you want to move any portion of rows or column to another place.

We can use the move_range()
to move our data. This function takes three parameters – the selected data, how many rows to shift (+N,-N), and how many columns to shift (+N,-N).
ws.move_range("B2:D9", rows=0, cols=2 )

Conclusion
That’s all for this article. We have covered different scenarios to automate excel tasks. These tasks can also be done manually in excel. But, if there is some repetitive scenario, you can invest some time in this Automation strategy based on your requirement to save your daily time.
I hope you liked the article. Thanks for the reading!
Here are some of my best picks:
15 Ultimate Daily Hacks for Every Programmer
How a Single Mistake Wasted 3 Years of My Data Science Journey
Before you go…
If you liked this article and want to stay tuned with more exciting articles on Python & Data Science – do consider becoming a medium member by clicking here https://pranjalai.medium.com/membership.
Please do consider signing up using my referral link. In this way, the portion of the membership fee goes to me, which motivates me to write more exciting stuff on Python and Data Science.
Also, feel free to subscribe to my free newsletter: Pranjal’s Newsletter.