--- title: Transpose or rotate Excel cell data with OpenPyXL description: Transpose or rotate Excel cell data with OpenPyXL --- [Skip to content](https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl#main-content) ![Conny-Söderholm](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg) - [Home](https://www.connysoderholm.com/) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) Open main navigation Close main navigation - [Home](https://www.connysoderholm.com/) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) December 28, 2019 # Transpose or rotate Excel cell data with OpenPyXL ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) Share: [facebook-f icon](http://www.facebook.com/share.php?u=https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl) [linkedin-in icon](http://www.linkedin.com/shareArticle?mini=true&url=https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl) [Twitter icon](https://twitter.com/intent/tweet?url=https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl) [pinterest-p icon](http://pinterest.com/pin/create/link/?url=https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl) [envelope icon](mailto:?body=https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl) ## Introduction In Excel there is a neat Paste Special method named *Transpose*. The [transpose](https://support.office.com/en-us/article/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744) is done by copying your cells and then pasting the data to another place with the transpose option. We can easily do the same with [Python](https://python.org/) and [OpenPyXL](https://openpyxl.readthedocs.io/en/stable/). ![](https://static.hsstatic.net/BlogImporterAssetsUI/ex/missing-image.png) Transposed cells in Excel. There are currently (To the author's knowledge, December 2019) no transpose functions or methods written in OpenPyXL. I have made a small code sample that transposes rows to columns and columns to rows. Only cell data is transposed with these functions. The functions are divided into three main functions. ## The functions ### Transpose The first function is Transpose, which takes an OpenPyXL worksheet and which rows and columns to transpose the values to/from. The function uses two loops, which first traverses the rows and then the columns. We set the worksheet's cell values with `ws.cell(row=col,column=row).value = ws.cell(row=row,column=col).value`. To set the values, we just invert the column and row values. ### Transpose\_row\_to\_col and transpose\_col\_to\_row I have expanded these functions to accept also the target cell to paste the values in, and also the option to delete the transposed cells values. The functions are identical except for the usage of [iter\_rows](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.iter_rows) and [iter\_cols](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.iter_cols). The `target_cell_address` is a tuple consisting of the cell's absolute address. If `delete_source` is True the values in the transposed cells are deleted. First, an empty list called `cell_values` is created. After that we use the iter\_rows method to iterate through the worksheets rows, one by one. The iter\_rows produces cells from the worksheet, by row while the iter\_cols returns cells by column. The cells are yielded from a [generator](https://wiki.python.org/moin/Generators). We append the values of the cells to our `cell_ranges` list. We then use the `fill_cells` function to enter the values again to our transposed cells. I hope that this helps you in transposing and rotating your cell values. As always, if you have any questions or comments please let me know. ## Complete code ``` #!/usr/bin/env python3 """ Transpose row values to column values and vice versa """ import openpyxl def transpose(ws, min_row, max_row, min_col, max_col): for row in range(min_row, max_row+1): for col in range(min_col, max_col+1): ws.cell(row=col,column=row).value = ws.cell(row=row,column=col).value def transpose_row_to_col(ws, min_row, max_row, min_col, max_col, target_cell_address=(1,1), delete_source=False): cell_values = [] for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col): for cell in row: cell_values.append(cell.value) if delete_source: cell.value = "" fill_cells(ws, target_cell_address[0], target_cell_address[1], cell_values) def transpose_col_to_row(ws, min_row, max_row, min_col, max_col, target_cell_address=(1,1), delete_source=False): cell_values = [] for col in ws.iter_cols(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col): for cell in col: cell_values.append(cell.value) if delete_source: cell.value = "" fill_cells(ws, target_cell_address[0], target_cell_address[1], cell_values) def fill_cells(ws, start_row, start_column, cell_values): row = start_row column = start_column for value in cell_values: ws.cell(row=row,column=column).value = value row += 1 if __name__ == "__main__": # Open workbook file_name = "Transpose_cell_values.xlsx" wb = openpyxl.load_workbook(file_name) ws1 = wb.worksheets[0] transpose(ws1, min_row=1, max_row=1, min_col=1, max_col=5) #transpose_col_to_row(ws1, min_row=1, max_row=10, min_col=1, max_col=1, target_cell_address=(1,2)) wb.save(file_name) ``` [Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel) ## Related posts [Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel) ## [Copy Excel data to workbook with Python & OpenPyXL](https://www.connysoderholm.com/blog/copy-data-from-one-excel-workbook-to-a-new-workbook-with-python-openpyxl) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) December 16, 2019 [Read more](https://www.connysoderholm.com/blog/copy-data-from-one-excel-workbook-to-a-new-workbook-with-python-openpyxl) [![Excel table](https://www.connysoderholm.com/hs-fs/hubfs/image-png.png?height=200&name=image-png.png)](https://www.connysoderholm.com/blog/inserting-rows-into-excel-table-without-corrupting-the-table) [Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel) ## [Inserting rows into Excel table without corrupting the table](https://www.connysoderholm.com/blog/inserting-rows-into-excel-table-without-corrupting-the-table) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) August 1, 2023 Inserting rows With OpenPyXL you can insert rows into a worksheet with the worksheet method... [Read more](https://www.connysoderholm.com/blog/inserting-rows-into-excel-table-without-corrupting-the-table) [![Spreadsheet table](https://www.connysoderholm.com/hs-fs/hubfs/Table.png?height=200&name=Table.png)](https://www.connysoderholm.com/blog/iterating-over-tables-in-an-existing-excel-workbook-with-openpyxl) [Python](https://www.connysoderholm.com/blog/tag/python), [openpyxl](https://www.connysoderholm.com/blog/tag/openpyxl), [Excel](https://www.connysoderholm.com/blog/tag/excel) ## [Iterating over tables in an existing Excel workbook with OpenPyXL](https://www.connysoderholm.com/blog/iterating-over-tables-in-an-existing-excel-workbook-with-openpyxl) ![Picture of Conny Söderholm](https://app.hubspot.com/settings/avatar/c60eb763b898c1f4c2e54f9401f3aef1) [Conny Söderholm](https://www.connysoderholm.com/blog/author/conny-soderholm) July 31, 2023 There is a way to iterate over Excel tables with OpenPyXL. The functionality has changed in newer... [Read more](https://www.connysoderholm.com/blog/iterating-over-tables-in-an-existing-excel-workbook-with-openpyxl) [linkedin-in icon](https://www.linkedin.com/in/connysoderholm) [Follow us on Facebook](https://www.youtube.com/@connysoderholm1473) - [Home](https://www.connysoderholm.com) - [Courses](https://www.connysoderholm.com/courses) - [Articles](https://www.connysoderholm.com/blog) - [Contact Me](https://www.connysoderholm.com/contact-me) - [Privacy Policy](https://www.connysoderholm.com/privacy-policy) - [Disclaimer](https://www.connysoderholm.com/disclaimer) --- [![Conny Söderholm Logo](https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg "Conny Söderholm Logo")](http://Conny%20Söderholm) Copyright © 2026 Conny Söderholm ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "author" : { "@type" : "Person", "name" : "Conny Söderholm", "url" : "https://www.connysoderholm.com/blog/author/conny-soderholm" }, "dateModified" : "2023-07-26T14:39:10.900Z", "datePublished" : "2019-12-27T22:28:55.000Z", "headline" : "Transpose or rotate Excel cell data with OpenPyXL", "mainEntityOfPage" : { "@id" : "https://www.connysoderholm.com/blog/transpose-or-rotate-excel-cell-data-with-openpyxl", "@type" : "WebPage" }, "publisher" : { "@type" : "Organization", "logo" : { "@type" : "ImageObject", "url" : "https://www.connysoderholm.com/hubfs/Conny-S%C3%B6derholm.svg" }, "name" : "Conny Söderholm" } } ```