Python openpyxl - How to Insert Rows, Columns in Excel

Last Updated on July 14, 2022 by Jay

This tutorial will show you how to use the Python openpyxl library to insert rows, columns and merge cells in an Excel workbook. Formatting an Excel file can be a tedious and time-consuming task, so it’s a good task to automate away!

Library

We are going to use openpyxl library to build a simple budgeting Excel spreadsheet. To install the library, type the following into a command prompt window:

pip install openpyxl

Mock-up Data

The below code creates an Excel file named “formatting.xlsx” with some data inside. Copy and run the following code if you want to follow along:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = 'formatting'

income = [('Income', ''),
          ('Salary', 1000),
          ('Investment', 500),
          ('Side hustle', 500),
          ('Total', 2000)]

expense = [('Expense',''),
           ('Housing', 1200),
           ('Insurance', 200),
           ('Grocery', 500),
           ('Entertainment', 500),
           ('Total', 2400)]

for row in income+expense:
    ws.append(row)

wb.save('formatting.xslx')

You should get an unformatted Excel file that looks like this:

Insert Rows and Columns in Excel using Python openpyxl

Let’s start formatting by moving the cells (with data) around to make the workbook look a little nicer. Add the following code after the for loop block, but before saving the file:

ws.insert_rows(1,6)  
ws.insert_cols(1)
ws.move_range("B12:C17", rows = 2, cols = 0)

##Add more stuff
ws['B2'] = 'Monthly Budget'
ws['E7'] = 'Income'
ws['F7'] = '=sum(C8:C10)'
ws['E9'] = 'Expense'
ws['F9'] = '=sum(C15:C18)'
ws['E11'] = 'Net'
ws['F11'] = '=F7-F9'
ws['E13'] = 'Exp/Income Ratio'
ws['F13'] = '=F9/F7'

The above code block did the following:

The 1st line – Insert 6 rows, from row 1-6
The 2nd line – Insert 1 column at column A (1)
And the 3rd line – Moved the Expense section (with the previous 2 steps, this section is now at B12:C17) down by 2 rows
Rest of the code – added a bunch of other things in B2, and cells around E7:F13

insert rows and columns using python openpyxl

Merge Cells using openpyxl

Continue with the above, add the following right after the previous code block. The below code merges several cells together. Note the two ways of merging cells using ws.merge_cell() method:

Using the normal Excel range notation, e.g. ‘B2:F2’
Use 4 arguments to select boundaries for the cells to be merged

ws.merge_cells('B2:F2')

ws.merge_cells('E7:E8')
ws.merge_cells('F7:F8')

for r in range(9,14,2):
    for c in range(5,7):
        ws.merge_cells(start_row = r, start_column = c, end_row=r+1, end_column = c)