How to freeze Excel Columns/Rows by openpyxl freeze_panes

In Excel, freezing columns or rows while scrolling through data can be very useful, especially for sheets with large data.

Freezing columns in Excel by openpyxl

In Excel, freezing columns or rows while scrolling through data can be very useful, especially for sheets with large data.

By using the power of Python and its libraries (Pandas, openpyxl), we can create Excel files with large data.

At the same time, we may perform formatting of data and other actions that are generally done directly in MS Excel.

In this tutorial, we are going to show how to freeze panes/columns in Excel.

We will use openpyxl library for this purpose.

An example of freezing “A” column

We will freeze “A” column, so that if you scroll toward the right to look at other columns – column A remains visible.

Sample sheet:

openpyxl-freeze-excel

For freezing column A, we will use freeze_panes attribute of the sheet’s object.

Python program to free column:

from openpyxl import load_workbook

#Loading an existing workbook

wb_freeze = load_workbook('openpyxl_ex.xlsx')

#Accessing Product Information sheet

sheet = wb_freeze["Product Information"]


#Using free_panes attribute

sheet.freeze_panes = 'B1'

'Save the task

wb_freeze.save('openpyxl_ex.xlsx')

Output:

openpyxl-freeze-column

You can see in the above graphic that we scrolled columns till D. The B and C columns disappeared while A is still visible.

Freezing two columns A and B

Similarly, you may freeze two or more columns as you scroll right.

For freezing A and B columns, specify the “C” column in the freeze_panes attribute as in the example below:

from openpyxl import load_workbook


#Loading an existing workbook

wb_freeze = load_workbook('openpyxl_ex.xlsx')


#Accessing Product Information sheet

sheet = wb_freeze["Product Information"]


#Freezing two columns : A and B

sheet.freeze_panes = 'C1'

'Save the task

wb_freeze.save('openpyxl_ex.xlsx')

Result:

openpyxl-freeze-2

You can see that the C column disappeared as we scrolled while A & B are still visible i.e. Frozen.

How to freeze/lock top rows

By specifying B1, C1, etc, we are basically freezing columns till that specific column. Where 1 in B1 means the first row.

If you want to freeze the top 3 rows and A & B columns then you may use C4 in the freeze_panes attribute. See an example below:

from openpyxl import load_workbook

#Loading an existing workbook

wb_freeze = load_workbook('openpyxl_ex.xlsx')

#Accessing Product Information sheet

sheet = wb_freeze["Product Information"]

#Freeze A & B columns. Also freeze top 3 rows

sheet.freeze_panes = 'C4'

#Save the task

wb_freeze.save('openpyxl_ex.xlsx')

Output:

openpyxl-freeze-rows

You saw we scrolled downwards and 4,5 and 6 rows disappeared while 1,2 and 3 rows are still visible.

Similarly, as we scrolled to the right, the C column is hidden while A&B is still visible.

How to unfreeze frozen columns

There are two ways you can unfreeze the frozen columns:

  • Specify “A1” column in the feeeze_panes attribute
  • Use None

The code:

from openpyxl import load_workbook

#Loading an existing workbook

wb_freeze = load_workbook('openpyxl_ex.xlsx')


#Accessing Product Information sheet

sheet = wb_freeze["Product Information"]

#Unfreeze all columns

sheet.freeze_panes = None

#Save the task

wb_freeze.save('openpyxl_ex.xlsx')

Result:

After executing this code, as you try to open the Excel file, it shows the error:

openpyxl-unfreeze

So, be careful as you unfreeze the columns.

Alternatively, you may use MS Excel to unfreeze the columns.

See its tutorial: Freeze/Unfreeze Panes/Columns in Excel