How to Delete Rows and Columns from Excel in Python: Quick Guide

When working with Excel files, it's common to delete rows or columns that are either unnecessary or empty. While Excel does offer built-in options for deleting data, handling this manually can be time-consuming and prone to error.

To streamline the process and ensure precision, this article will show you how to delete rows and columns in Excel using Python. This approach not only saves time but also ensures that the data you remove is exactly what you intended, making your workflow more efficient.

Python Library to Delete Rows and Columns from Excel

In this guide, the steps and examples will be presented by Spire.XLS for Python. It is one of the best Microsoft Office alternatives, with powerful features. This method enables developers to delete both rows and columns simultaneously, overcoming MS Office's limitation on overlapping selections. With this functionality, there's no need to perform separate operations to delete rows and then columns, making the process more efficient and streamlined.

You can install it using the pip command: pip install Spire.XLS

How to Delete a Single Row and Column in Three Steps

Now that you have the necessary tools, let’s dive into the process of deleting a single row or column in just three steps. Spire.XLS for Python offers the Worksheets.DeleteRow() and Worksheets.DeleteColumn() methods, making it quick and easy to remove unnecessary data and streamline your work. Let’s go over the detailed steps below.

Steps to delete a row and a column from Excel:

• Create a Workbook object and load an Excel document from local storage using the Workbook.LoadFromFile() method.

• Get the specified worksheet with the Workbook.Worksheets[] method.

• Delete a certain row or a column from Excel by calling the Worksheets.DeleteRow() and the Worksheets.DeleteColumn() methods.

• Save the updated Excel spreadsheets as a new file using the Workbook.SaveToFile() method, and release the memory.

Here is the code example of deleting the 9th row and the 3rd column from a worksheet:

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


# Create a Workbook object
workbook = Workbook()

# Load an Excel file from the disk
workbook.LoadFromFile("/sample.xlsx")

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

# Delete the 9th row
sheet.DeleteRow(9)
# Delete the 3rd column
sheet.DeleteColumn(3)

# Save the resulting document
workbook.SaveToFile("/DeleteSpecificRowAndColumn.xlsx", ExcelVersion.Version2016)

# Release the resource
workbook.Dispose()

How to Delete Multiple Rows and Columns from Excel Quickly

When working with Excel documents, it's not just individual rows or columns that may need deletion; sometimes multiple consecutive rows or columns contain unnecessary information. In such cases, deleting them one by one isn't convenient or efficient. Fortunately, by adjusting the parameters in the Worksheets.DeleteRow() and Worksheets.DeleteColumn() methods, you can delete rows and columns from x to y in Excel​. Let’s explore the specific steps and code examples.

Detailed steps to delete multiple rows and columns:

• Create an instance of the Workbook class and specify the file path of the source Excel spreadsheets with the Workbook.LoadFromFile() method.

• Retrieve a worksheet using the Workbook.Worksheets[] method.

• Delete rows or columns from the Excel worksheet by calling the Worksheets.DeleteRow() and the Worksheets.DeleteColumn() methods.

• Write the modified Excel document as a new file.

Below is a code example of removing the 5th, 6th, 7th rows and the 3rd, 4th columns:

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


# Create a Workbook object
workbook = Workbook()

# Load an Excel file from the disk
workbook.LoadFromFile("/sample.xlsx")

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

# Delete the 5th, 6th and 7th rows
sheet.DeleteRow(5, 3)
# Delete the 3rd and 4th columns
sheet.DeleteColumn(3, 2)

# Save the modified file
workbook.SaveToFile("/DeleteMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016)

# Clean up
workbook.Dispose()

How to Delete Blank Rows or Columns from Excel

In addition to the previous tasks, another common task is removing blank rows or columns from Excel​. These empty spaces can disrupt the coherence of the information in your table, making it difficult for readers to find key details. Therefore, it’s essential to delete these blank areas. Still, you can use the Worksheets.DeleteRow() or Worksheets.DeleteColumn() methods. However, unlike the previous part, you’ll need to traverse the table first to ensure that no blank rows or columns are overlooked. Let’s take a closer look.

Steps to delete blank rows and columns from Excel:

• Instantiate a Workbook class and read an Excel file with the Workbook.LoadFromFile() method.

• Get a worksheet from the Excel workbook using the Workbook.Worksheets[] method.

• Loop through every row in the worksheet to check if there is a blank row, if it is, remove the blank row from Excel by calling the Worksheets.DeleteRow() method, same with columns.

• Save the updated Excel document.

Here is the code example of deleting blank rows and columns from Excel:

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


# Create a Workbook object
workbook = Workbook()

# Load an Excel file from the disk
workbook.LoadFromFile("/sample.xlsx")

# Get the third worksheet
sheet = workbook.Worksheets[2]

# 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)

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

# Save the result file
workbook.SaveToFile("/DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016)

# Release the memory
workbook.Dispose()

The Bottom Line

This article provides a comprehensive guide on how to delete rows and columns from Excel with Python, covering single rows and columns, multiple ranges, and blank spaces. Each section includes step-by-step instructions and code examples, making it easy to follow along. By the end, you’ll find that removing rows and columns from Excel is a piece of cake!