Quickly Delete or Remove Blank Rows, Columns or Cells from Excel with Python

4 min readJul 3, 2023

When conducting data analysis, it is essential to have a clean dataset. Blank rows, columns, or cells can disrupt calculations, formulas, and statistical analyses, potentially leading to inaccurate results. By removing these empty elements, you can ensure that your analysis is based on precise and comprehensive data. In this article, we will explore how to programmatically delete blank rows, columns or cells from an Excel file using Python.

Delete Blank Rows from Excel in Python
Delete Blank Columns from Excel in Python
Delete Blank Cells from Excel in Python

Python Library to Delete Blank Rows, Columns and Cells from Excel

In order to delete blank rows, columns and cells from Excel files, this article uses a Python Excel library: Spire.XLS for Python.

Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.

You can easily install Spire.XLS for Python from pypi by running the following commands in your terminal:

pip install Spire.Xls

For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.

Delete Blank Rows from Excel in Python

You can use the Worksheet.Rows[int index].IsBlank property to determine whether a row is blank or not. If the result is true, you can use the Worksheet.DeleteRow() method to delete that row.

The following code snippet shows how to find the blank rows in an Excel worksheet and delete them using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

#Specify the input and output file paths
inputFile = "FileWithBlankRows.xlsx"
outputFile = "DeleteBlankRows.xlsx"

#Create a workbook instance
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile(inputFile)
#Get the first worksheet
sheet = workbook.Worksheets[0]

#Delete blank rows from the worksheet
for i in range(sheet.Rows.Length - 1, -1, -1):
    if sheet.Rows[i].IsBlank:
        sheet.DeleteRow(i + 1)

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Delete Blank Columns from Excel in Python

You can use the Worksheet.Columns[int index].IsBlank property to determine whether a column is blank or not. If the result is true, you can use the Worksheet.DeleteColumn() method to delete that column.

The following code snippet shows how to find the blank columns in an Excel worksheet and delete them using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

#Specify the input and output file paths
inputFile = "FileWithBlankColumns.xlsx"
outputFile = "DeleteBlankColumns.xlsx"

#Create a workbook instance
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile(inputFile)

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Delete blank columns from the worksheet
for i in range(sheet.Columns.Length - 1, -1, -1):
    if sheet.Columns[i].IsBlank:
        sheet.DeleteColumn(i + 1)

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Delete Blank Columns from Excel in Python

Delete Blank Cells from Excel in Python

You can use the Worksheet.Cells[int index].IsBlank property to determine whether a cell is blank or not. If the result is true, you can use the Worksheet.DeleteRange() method to delete that cell.

The following code snippet shows how to find the blank cells in an Excel worksheet and delete them using Python and Spire.XLS for Python:

from spire.xls import *
from spire.xls.common import *

#Specify the input and output file paths
inputFile = "FileWithBlankCells.xlsx"
outputFile = "DeleteBlankCells.xlsx"

#Create a workbook instance
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile(inputFile)

#Get the first worksheet
sheet = workbook.Worksheets[0]

#Delete blank cells from the worksheet
for i in range(sheet.Cells.Length - 1, -1, -1):
    if sheet.Cells[i].IsBlank:
        sheet.DeleteRange(sheet.Cells[i], DeleteOption.MoveLeft)

#Save the result file
workbook.SaveToFile(outputFile, ExcelVersion.Version2013)
workbook.Dispose()

Conclusion

In this article, we have discussed how to delete blank rows, columns and cells from an Excel file with Python. We hope it’s helpful to you.