Use Python to Delete Excel Rows & Columns

Last Updated on July 14, 2022 by Jay

This tutorial will show you how to use the Python openpyxl library to delete rows or columns from existing Excel files.

Library

To install the openpyxl library, type the following in a command prompt window:

pip install openpyxl

Sample Dataset

Copy and run the following code to create a sample Excel file to follow the tutorial.

from openpyxl import load_workbook, Workbook

wb = Workbook()
ws = wb.create_sheet('eg')

data = [('id', 'name', 'country'),
        (1, 'Trudeau','Canada'),
        (2,'Zelenskyy', 'Ukraine'),
        (3,'Putin', 'Russia'),
        (4,),
        (5,'Biden', 'US'),
        (6, 'Xi', 'China'),
        (7,'Johnson', 'UK'),
        (8, 'Castex', 'France'),
        (9,),
        (10, 'Steinmeier', 'Germany'),
        (11, 'Rutte', 'Netherlands'),
        (12,'Loong', 'Singapore')]

for row in data:
    ws.append(row)

wb.save('names.xlsx')

Python Delete Excel Rows and Columns

In openpyxl, we can use delete_rows() and delete_cols() methods from the Worksheet object to remove entire rows and columns. The syntax is straightforward:

delete_rows(row_id, number_of_rows)
delete_cols(col_id, number_of_cols)

Delete at Known Position

Since we need to delete from existing Excel files, let’s first read the file content into Python. Then we can use the delete_rows() or delete_cols() to remove rows and columns.

wb2 = load_workbook('names.xlsx')
ws2 = wb2['eg']
for i in ws2.values:
    print(i)

('id', 'name', 'country')
(1, 'Trudeau', 'Canada')
(2, 'Zelenskyy', 'Ukraine')
(3, 'Putin', 'Russia')
(4, None, None)
(5, 'Biden', 'US')
(6, 'Xi', 'China')
(7, 'Johnson', 'UK')
(8, 'Castex', 'France')
(9, None, None)
(10, 'Steinmeier', 'Germany')
(11, 'Rutte', 'Netherlands')
(12, 'Loong', 'Singapore')

Delete 1 Row or Column

If we want to delete just 1 row, just call delete_rows(n), which will delete the nth row from the top. We can ignore the second argument, which equals 1 by default. Similarly, to delete just 1 column, use delete_cols(n) and skip the second argument.

Delete Multiple Rows or Columns

To delete multiple rows or columns, we’ll need to use both arguments. The following code will delete the 2nd row to the 5th row. It means to delete row 2, plus the 3 rows underneath it, for a total of 4 rows.

ws2.delete_rows(2,4)
for i in ws2.values:
    print(i)
('id', 'name', 'country')
(5, 'Biden', 'US')
(6, 'Xi', 'China')
(7, 'Johnson', 'UK')
(8, 'Castex', 'France')
(9, None, None)
(10, 'Steinmeier', 'Germany')
(11, 'Rutte', 'Netherlands')
(12, 'Loong', 'Singapore')

Similarly, to delete multiple columns we need to use both arguments. The below deletes from column 2 (B), and for a total of 2 columns (B and C).

ws2.delete_cols(2,2)
for i in ws2.values:
    print(i)

('id',)
(1,)
(2,)
(3,)
(4,)
(5,)
(6,)
(7,)
(8,)
(9,)
(10,)
(11,)
(12,)

Unfortunately, there’s no easy way to delete multiple non-consecutive rows or columns. We have to delete one by one and figure out the new position of the rows/cols after each delete.

Delete Based on Cell Values

Our dataset contains 2 empty rows at id 4 and 9. Let’s say if the empty rows can appear randomly anywhere in a file, then we don’t know the exact location of those rows (or columns) so we can’t use the delete methods directly.

We need to first find where those rows are. Basically, we need to know which rows have empty values.

In the below code, using ws.iter_rows() we can get tuples of Cell objects. Then from those Cell objects, we can access the cell values and row/column positions.

any() returns True if at least one of the elements is not None, and returns False if all elements are None. We can use this function to locate the empty rows.

Of course, don’t forget to save the changes back to the Excel file!

any([1,2,None])
True

any([None,None,None])
False

for row in ws2.iter_rows():
    if not any([cell.value for cell in row[1:]]):
        ws2.delete_rows(row[0].row)

for i in ws2.values:
    print(i)
('id', 'name', 'country')
(1, 'Trudeau', 'Canada')
(2, 'Zelenskyy', 'Ukraine')
(3, 'Putin', 'Russia')
(5, 'Biden', 'US')
(6, 'Xi', 'China')
(7, 'Johnson', 'UK')
(8, 'Castex', 'France')
(10, 'Steinmeier', 'Germany')
(11, 'Rutte', 'Netherlands')
(12, 'Loong', 'Singapore')

wb.save('names.xlsx)

Additional Resources

Python & Excel – Number Format

Python openpyxl – How to Insert Rows, Columns in Excel

Adjust Excel Fonts using Python openpyxl

Write Data to Excel using Python