{"id":8439,"date":"2023-01-19T15:50:42","date_gmt":"2023-01-19T15:50:42","guid":{"rendered":"https:\/\/www.excel-learn.com\/?p=8439"},"modified":"2023-01-19T15:50:42","modified_gmt":"2023-01-19T15:50:42","slug":"insert-columns-openplxl","status":"publish","type":"post","link":"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/","title":{"rendered":"How to Insert Columns in Excel using openpyxl (Python)"},"content":{"rendered":"

Inserting column in Excel by openpyxl library<\/strong><\/h3>\n

It only requires a few lines of code in Python to insert a new column in MS Excel\u2019s worksheet.<\/p>\n

All you have to do is to specify the Excel Workbook<\/strong> (file name path), an active worksheet<\/strong>, and the placement<\/strong> of a new column - let us have a look at the methods and code required.<\/p>\n

In this tutorial, we will show you how to add a single column, multiple non-adjacent columns, and multiple adjacent columns with screenshots.<\/p>\n

Steps to insert a column<\/strong><\/h3>\n

Step 1:<\/strong><\/p>\n

Import the required component by using this line of code:<\/p>\n

from openpyxl import load_workbook<\/p><\/blockquote>\n

Step 2:<\/strong><\/p>\n

Secondly, load the Excel file (Workbook) where you want to insert the column. For example, we have an object \u201cwb_col<\/em>\u201d object and our Excel file name is \u201ctest_sheet.xlsx<\/em>\u201d.<\/p>\n

Add the following line of code:<\/strong><\/p>\n

wb_col = load_workbook('test_sheet.xlsx')<\/p><\/blockquote>\n

Step 3:<\/strong><\/p>\n

Specify the sheet in the Workbook where you want to add the column:<\/p>\n

sheet = wb_col.active<\/p><\/blockquote>\n

Step 4:<\/strong><\/p>\n

Use the insert<\/strong> method to add a new column as follows:<\/p>\n

sheet.insert_cols(idx=3)<\/p><\/blockquote>\n

Step 5:<\/strong><\/p>\n

Save the operation:<\/strong><\/p>\n

wb_col.save('test_sheet.xlsx')<\/p><\/blockquote>\n

 <\/p>\n

That\u2019s it. Now let us have a look at the complete code and sample output.<\/p>\n

Full code and sample output<\/strong><\/h3>\n

Following is the complete code that we executed and you can see the Workbook before and after executing the code below.<\/p>\n

Worksheet before executing the code:<\/strong><\/p>\n

\"Excel-openpyxl-add-colu\"<\/p>\n

Our target is to add a new column at position 3 i.e. after the \u201cProduct Name\u201d<\/strong> column.<\/p>\n

The code:<\/strong><\/p>\n

# Insert Column in Excel\r\n\r\n# excel-python.py\r\n\r\nfrom openpyxl import load_workbook\r\n\r\nwb_col = load_workbook('test_sheet.xlsx')\r\n\r\nsheet = wb_col.active\r\n\r\nsheet.insert_cols(idx=3)\r\n\r\nwb_col.save('test_sheet.xlsx')<\/pre>\n
Output after code execution:<\/strong><\/p>\n
 <\/p>\n

\n\"openpyxl-add-column-add\"Adding two non-adjacent columns example<\/strong><\/h3>\n
You may add multiple columns by executing the code once. For adding non-adjacent<\/strong> columns, just add the line with the column number in the above code and you are done. For example:<\/p>\n
sheet.insert_cols(idx=2)<\/p>\n
sheet.insert_cols(idx=4)<\/p><\/blockquote>\n
Showing with example and output<\/strong><\/h3>\n
Our example target is to add one column at position 2 i.e. after the Product ID<\/strong> column and the second one at position 4 i.e. after the Product Name<\/strong> column.<\/p>\n
The code:<\/strong><\/p>\n
# Insert Column in Excel\r\n\r\n# excel-python.py\r\n\r\nfrom openpyxl import load_workbook\r\n\r\nwb_col = load_workbook('test_sheet.xlsx')\r\n\r\nsheet = wb_col.active\r\n\r\n#Specifying first column\r\n\r\nsheet.insert_cols(idx=2)\r\n\r\n\r\n#Specifying second column\r\n\r\nsheet.insert_cols(idx=4)\r\n\r\nwb_col.save('test_sheet.xlsx')<\/pre>\n
Excel sheet before code execution:<\/strong><\/p>\n
\"Excel-openpyxl-add-colu\"<\/p>\n
After executing the above code:<\/strong><\/p>\n
\"openpyxl-add-column-mul\"<\/p>\n
Note<\/strong> that, as the second insert code of line executed i.e.<\/p>\n
sheet.insert_cols(idx=4)<\/p><\/blockquote>\n
it counted the first added column as well.<\/p>\n
Adding multiple adjacent columns example<\/strong><\/h3>\n
If you require to add multiple adjacent columns then it is even simple and can be done by just one statement.<\/p>\n
You just need to provide the value for the amount<\/strong> keyword along with idx<\/strong> keyword. For example:<\/p>\n
sheet.insert_cols(idx=4,amount=3)<\/p><\/blockquote>\n
See the example below where we will add three adjacent columns to our example Worksheet.<\/p>\n
The code:<\/strong><\/p>\n
# Insert multiple Columns in Excelsheet\r\n\r\n# excel-python.py\r\n\r\n\r\nfrom openpyxl import load_workbook\r\n\r\nwb_col = load_workbook('test_sheet.xlsx')\r\n\r\nsheet = wb_col.active\r\n\r\n\r\n#Adding three adjacent columns\r\n\r\nsheet.insert_cols(idx=4,amount=3)\r\n\r\n\r\nwb_col.save('test_sheet.xlsx')<\/pre>\n
Excel sheets before and after code execution:<\/strong><\/p>\n
\"Excel-openpyxl-add-colu\"<\/p>\n
After code execution:<\/strong><\/p>\n
\"openpyxl-columns-adjace\"<\/p>\n
You can see, three columns after the Price<\/strong> column (number 3) are added.<\/p>\n","protected":false},"excerpt":{"rendered":"
Inserting column in Excel by openpyxl library It only requires a few lines of code…<\/p>\n","protected":false},"author":1,"featured_media":8442,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[145],"tags":[],"yoast_head":"\nHow to Insert Columns in Excel using openpyxl (Python)<\/title>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Insert Columns in Excel using openpyxl (Python)\" \/>\n<meta property=\"og:description\" content=\"Inserting column in Excel by openpyxl library It only requires a few lines of code...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-01-19T15:50:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/01\/6_3-openpyxl-add-column-mul.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"739\" \/>\n\t<meta property=\"og:image:height\" content=\"271\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.excel-learn.com\/#website\",\"url\":\"https:\/\/www.excel-learn.com\/\",\"name\":\"Excel Tutorials\",\"description\":\"Tips, Solutions, Tricks\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.excel-learn.com\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.excel-learn.com\/wp-content\/uploads\/2023\/01\/6_3-openpyxl-add-column-mul.jpg\",\"width\":739,\"height\":271,\"caption\":\"openpyxl-add-column-mul\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/#webpage\",\"url\":\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/\",\"name\":\"How to Insert Columns in Excel using openpyxl (Python)\",\"isPartOf\":{\"@id\":\"https:\/\/www.excel-learn.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/#primaryimage\"},\"datePublished\":\"2023-01-19T15:50:42+00:00\",\"dateModified\":\"2023-01-19T15:50:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.excel-learn.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.excel-learn.com\/insert-columns-openplxl\/\"]}]},{\"@type\":[\"Person\"],\"@id\":\"https:\/\/www.excel-learn.com\/#\/schema\/person\/c3f75f86b1c65bf0b93fc46f629fcad3\",\"name\":\"excelmaster\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.excel-learn.com\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e2a3743b4da71bd52984e02bd5c3280c?s=96&d=mm&r=g\",\"caption\":\"excelmaster\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/posts\/8439"}],"collection":[{"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/comments?post=8439"}],"version-history":[{"count":1,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/posts\/8439\/revisions"}],"predecessor-version":[{"id":8445,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/posts\/8439\/revisions\/8445"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/media\/8442"}],"wp:attachment":[{"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/media?parent=8439"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/categories?post=8439"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.excel-learn.com\/wp-json\/wp\/v2\/tags?post=8439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}