How to Insert Columns in Excel using openpyxl (Python)

Inserting column in Excel by openpyxl library

It only requires a few lines of code in Python to insert a new column in MS Excel’s worksheet.

All you have to do is to specify the Excel Workbook (file name path), an active worksheet, and the placement of a new column – let us have a look at the methods and code required.

In this tutorial, we will show you how to add a single column, multiple non-adjacent columns, and multiple adjacent columns with screenshots.

Steps to insert a column

Step 1:

Import the required component by using this line of code:

from openpyxl import load_workbook

Step 2:

Secondly, load the Excel file (Workbook) where you want to insert the column. For example, we have an object “wb_col” object and our Excel file name is “test_sheet.xlsx”.

Add the following line of code:

wb_col = load_workbook(‘test_sheet.xlsx’)

Step 3:

Specify the sheet in the Workbook where you want to add the column:

sheet = wb_col.active

Step 4:

Use the insert method to add a new column as follows:

sheet.insert_cols(idx=3)

Step 5:

Save the operation:

wb_col.save(‘test_sheet.xlsx’)

That’s it. Now let us have a look at the complete code and sample output.

Full code and sample output

Following is the complete code that we executed and you can see the Workbook before and after executing the code below.

Worksheet before executing the code:

Our target is to add a new column at position 3 i.e. after the “Product Name” column.

The code:

Output after code execution:

Adding two non-adjacent columns example

You may add multiple columns by executing the code once. For adding non-adjacent columns, just add the line with the column number in the above code and you are done. For example:

sheet.insert_cols(idx=2)

sheet.insert_cols(idx=4)

Showing with example and output

Our example target is to add one column at position 2 i.e. after the Product ID column and the second one at position 4 i.e. after the Product Name column.

The code:

Excel sheet before code execution:

After executing the above code:

Note that, as the second insert code of line executed i.e.

sheet.insert_cols(idx=4)

it counted the first added column as well.

Adding multiple adjacent columns example

If you require to add multiple adjacent columns then it is even simple and can be done by just one statement.

You just need to provide the value for the amount keyword along with idx keyword. For example:

sheet.insert_cols(idx=4,amount=3)

See the example below where we will add three adjacent columns to our example Worksheet.

The code:

Excel sheets before and after code execution:

After code execution:

You can see, three columns after the Price column (number 3) are added.