[A Complete Guide] How to Insert a Row and a Column in Excel with Python

Inserting new rows or columns is a common task when dealing with Excel spreadsheets, for example, you need to expand a data set. While manually adding blank rows and columns can be effort-taking, this page offers a quick solution.

We will guide you through how to insert a row and column in Excel using Python. The programming way allows you to finish the task automatically.

Python Library to Insert Rows and Columns

In this article, to effectively insert rows and columns, we recommend Spire.XLS for Python. It is a powerful Python library that enables users to create, edit, and convert Excel spreadsheets without initiating Microsoft Excel.

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

How to Insert a Row and Column in Excel with Python

If you need to add scattered data or insert new content at various locations, you can easily insert a row or column into your table. Spire.XLS for Python provides flexibility in managing your Excel tables by allowing you to insert blank rows or columns anywhere within the table. So, this section will explain how to insert a row and a column in Excel, including steps and a code example.

Steps to insert a new row or column and set the width and height in Excel:

• Create an object of the Workbook class, and use the Workbook.LoadFromFile() method to open an Excel document.

• Get a certain worksheet with the Workbook.Worksheets[] method.

• Insert a row in Excel by calling the Worksheet.InsertRow() method, and insert a column with the Worksheet.Column() method.

Save the updated Excel spreadsheet as a new Excel document using the Workbook.SaveToFile() method, then release the resource.

The following is a code example of inserting a row and a column while setting the width and height in Excel:

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


# Create a Workbook instance
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/sample.xlsx")

# Get a specified worksheet
worksheet = workbook.Worksheets[1]

# Insert a blank row as the 5th row in the worksheet
worksheet.InsertRow(5)
# set the row height
worksheet.SetRowHeight(5, 30)

# Insert a blank column as the 4th column in the worksheet
worksheet.InsertColumn(4)
# Set the column width
worksheet.SetColumnWidth(4,15)

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

# Close the file
workbook.Dispose()

How to Insert Multiple Rows and Columns in Excel

When you need to insert multiple rows or columns of blank cells, such as when adding large amounts of data, doing it manually can be tedious. Although adding one row or column at a time offers flexibility, there are situations where bulk insertion is more efficient. Spire.XLS simplifies this process with its Worksheet.InsertRow() method. Although the basic steps for inserting multiple rows are similar to inserting a single row, you'll need to adjust the method's parameters to specify the number of rows to be inserted.

Steps to insert multiple rows or columns in Excel:

• Import essential modules from Spire.XLS.

• Create an instance of the Workbook class, and specify the file path to load an Excel spreadsheet with the Workbook.LoadFromFile() method.

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

• Insert multiple rows in Excel with the Worksheet.InsertRow(rowIndex, rowCount) method.

• Insert columns in Excel with the Worksheet.InsertColumn(columnIndex, columnCount) method.

• Write the Excel document to the disk using the Workbook.SaveToFile() method, then release resources.

Below is the code example of inserting three rows and two columns in an Excel spreadsheet:

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


# Create a Workbook instance
workbook = Workbook()

# Load an Excel document
workbook.LoadFromFile("/sample.xlsx")

# Get a specified worksheet
worksheet = workbook.Worksheets[1]

# Insert three blank rows into the worksheet
worksheet.InsertRow(5, 3)

#Insert two blank columns into the worksheet
worksheet.InsertColumn(4, 2)

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

# Release the resources
workbook.Dispose()

The Conclusion

This page introduces how to insert rows or columns in Excel spreadsheets. The guide is divided into two main sections: inserting a single row or column, and inserting multiple rows or columns. Each chapter provides detailed steps and code examples for easy reference. After going through this guide, you'll be able to effortlessly add blank rows or columns in Excel.