How To Remove Blank Rows In Excel Using Python

If you’re working with Excel spreadsheets, it’s not uncommon to encounter blank rows that either contain absolutely no data or only have a few missing cells. Such blank rows can create confusion and make your data less organized. In this tutorial, we will use Python to remove blank rows from an Excel file with the help of the openpyxl library.

Note: Before diving into the tutorial, make sure that you have Python and openpyxl library installed on your system. If you don’t have the openpyxl library, you can install it using the following command:
pip install openpyxl
We’ll be using an example Excel file called example.xlsx with the following content:

Name    Age    Country
John     25     USA

Jane     26     Canada
Alice    24     Australia

Our objective is to remove the blank row between John and Jane.

Step 1: Read the Excel file

Load the Excel file using the load_workbook() function from the openpyxl library. Read the first sheet in the excel document.

1
2
3
4
5
import openpyxl
 
file_path = "example.xlsx"
wb = openpyxl.load_workbook(file_path)
sheet = wb.active

Step 2: Identify and delete blank rows

Iterate through each row in the spreadsheet, identify if all the cells in the row are empty, and delete the row if it’s blank.

1
2
3
4
5
6
7
empty_rows = []
for row in sheet.iter_rows():
    if all(cell.value is None for cell in row):
        empty_rows.append(row[0].row)
 
for row in reversed(empty_rows):
    sheet.delete_rows(row)

Step 3: Save the modified Excel file

Save the modified workbook to a new file. In this example, we’ll save it as output.xlsx.

1
2
output_file_path = "output.xlsx"
wb.save(output_file_path)

Now, let’s see the complete code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import openpyxl
 
file_path = "example.xlsx"
wb = openpyxl.load_workbook(file_path)
sheet = wb.active
 
empty_rows = []
for row in sheet.iter_rows():
    if all(cell.value is None for cell in row):
        empty_rows.append(row[0].row)
 
for row in reversed(empty_rows):
    sheet.delete_rows(row)
 
output_file_path = "output.xlsx"
wb.save(output_file_path)

After running the code, inspect the output.xlsx file:

Name    Age    Country
John     25     USA
Jane     26     Canada
Alice    24     Australia

As you can see, the blank row between John and Jane has been removed.

Conclusion

In this tutorial, we demonstrated how to remove blank rows from an Excel file using Python and openpyxl library. With this technique, you can easily clean up your spreadsheets and improve their readability.