{"id":9441,"date":"2023-05-24T10:09:51","date_gmt":"2023-05-24T10:09:51","guid":{"rendered":"https:\/\/www.excel-learn.com\/?p=9441"},"modified":"2023-11-21T19:13:00","modified_gmt":"2023-11-21T19:13:00","slug":"freeze-excel-columns-rows-by-openpyxl-freeze_panes","status":"publish","type":"post","link":"https:\/\/www.excel-learn.com\/freeze-excel-columns-rows-by-openpyxl-freeze_panes\/","title":{"rendered":"How to freeze Excel Columns\/Rows by openpyxl freeze_panes"},"content":{"rendered":"

Freezing columns in Excel by openpyxl<\/strong><\/h3>\n

In Excel, freezing columns or rows while scrolling through data can be very useful, especially for sheets with large data.<\/p>\n

By using the power of Python and its libraries (Pandas, openpyxl), we can create Excel files with large data.<\/p>\n

At the same time, we may perform formatting of data and other actions that are generally done directly in MS Excel.<\/p>\n

In this tutorial, we are going to show how to freeze panes\/columns in Excel<\/a>.<\/p>\n

We will use openpyxl<\/strong> library for this purpose.<\/p>\n

An example of freezing \u201cA\u201d column<\/strong><\/h3>\n

We will freeze \u201cA\u201d column, so that if you scroll toward the right to look at other columns \u2013 column A remains visible.<\/p>\n

Sample sheet:<\/p>\n

\"openpyxl-freeze-excel\"<\/p>\n

For freezing column A, we will use freeze_panes<\/strong> attribute of the sheet\u2019s object.<\/p>\n

Python program to free column:<\/strong><\/p>\n

from openpyxl import load_workbook\r\n\r\n#Loading an existing workbook\r\n\r\nwb_freeze = load_workbook('openpyxl_ex.xlsx')\r\n\r\n#Accessing Product Information sheet\r\n\r\nsheet = wb_freeze[\"Product Information\"]\r\n\r\n\r\n#Using free_panes attribute\r\n\r\nsheet.freeze_panes = 'B1'\r\n\r\n'Save the task\r\n\r\nwb_freeze.save('openpyxl_ex.xlsx')<\/pre>\n
Output:<\/strong><\/p>\n
\"openpyxl-freeze-column\"<\/p>\n
You can see in the above graphic that we scrolled columns till D. The B and C<\/strong> columns disappeared while A<\/strong> is still visible.<\/p>\n
Freezing two columns A and B<\/strong><\/h3>\n
Similarly, you may freeze two or more columns as you scroll right.<\/p>\n
For freezing A and B columns, specify the \u201cC\u201d column in the freeze_panes attribute as in the example below:<\/p>\n
from openpyxl import load_workbook\r\n\r\n\r\n#Loading an existing workbook\r\n\r\nwb_freeze = load_workbook('openpyxl_ex.xlsx')\r\n\r\n\r\n#Accessing Product Information sheet\r\n\r\nsheet = wb_freeze[\"Product Information\"]\r\n\r\n\r\n#Freezing two columns : A and B\r\n\r\nsheet.freeze_panes = 'C1'\r\n\r\n'Save the task\r\n\r\nwb_freeze.save('openpyxl_ex.xlsx')<\/pre>\n
Result:<\/strong><\/p>\n
\"openpyxl-freeze-2\"<\/p>\n
You can see that the C column disappeared as we scrolled while A & B are still visible i.e. Frozen.<\/p>\n
How to freeze\/lock top rows<\/strong><\/h3>\n
By specifying B1, C1, etc, we are basically freezing columns till that specific column. Where 1 in B1 means the first row.<\/p>\n
If you want to freeze the top 3 rows and A & B columns then you may use C4 in the freeze_panes<\/strong> attribute. See an example below:<\/p>\n
from openpyxl import load_workbook\r\n\r\n#Loading an existing workbook\r\n\r\nwb_freeze = load_workbook('openpyxl_ex.xlsx')\r\n\r\n#Accessing Product Information sheet\r\n\r\nsheet = wb_freeze[\"Product Information\"]\r\n\r\n#Freeze A & B columns. Also freeze top 3 rows\r\n\r\nsheet.freeze_panes = 'C4'\r\n\r\n#Save the task\r\n\r\nwb_freeze.save('openpyxl_ex.xlsx')<\/pre>\n
Output:<\/strong><\/p>\n
\"openpyxl-freeze-rows\"<\/p>\n
You saw we scrolled downwards and 4,5 and 6 rows disappeared while 1,2 and 3 rows are still visible.<\/p>\n
Similarly, as we scrolled to the right, the C column is hidden while A&B is still visible.<\/p>\n
How to unfreeze frozen columns<\/strong><\/h3>\n
There are two ways you can unfreeze the frozen columns:<\/p>\n