Transpose a Range of Cells with openpyxl

Data often arrives the wrong way round for the next step: months across the top when a chart or a pivot wants them down the side, one column per store when a database load wants one row per store, a questionnaire with questions across and respondents down. Excel's Paste Special → Transpose fixes it by hand. openpyxl has no transpose method, but because a range is just rows of cells, swapping the row and column index is a few lines — the extra work is in keeping the formatting and deciding whether the result should be a copy or stay linked to the source. This page is part of Using openpyxl for Excel File Manipulation.

Prerequisites

pip install openpyxl pandas

monthly.xlsx has a small, formatted table on the Monthly sheet: months across the top in white on indigo, three metrics down the side on a pale fill, and a different number format on each row — currency-style for revenue, whole numbers for orders, percentages for margin. After transposing, those formats have to follow the values into columns.

Step 1 — Transpose the values

iter_rows(values_only=True) yields each row as a tuple. zip(*rows) regroups those tuples column by column, which is exactly the transpose. Write the result to a new sheet with append:

from openpyxl import load_workbook

wb = load_workbook("monthly.xlsx")
src = wb["Monthly"]
rows = list(src.iter_rows(min_row=1, max_row=4, min_col=1, max_col=5, values_only=True))
for new_row in zip(*rows):
    print(new_row)

dst = wb.create_sheet("By month")
for new_row in zip(*rows):
    dst.append(new_row)
wb.save("monthly_values.xlsx")

The four source rows of five cells become five rows of four. This is enough when only the numbers matter — feeding another script, say — but the new sheet has lost every font, fill, border and number format: margin shows as 0.312 instead of 31.2%.

Step 2 — Carry the formatting across

To keep the look, copy each cell's style along with its value, writing source cell (r, c) to target cell (c, r). Styles in openpyxl are immutable objects shared between cells, so assigning a copy of each one is safe:

from copy import copy
from openpyxl.utils import get_column_letter

def transpose_range(src, min_row, max_row, min_col, max_col, dst, top=1, left=1):
    for r in range(min_row, max_row + 1):
        for c in range(min_col, max_col + 1):
            s = src.cell(r, c)
            t = dst.cell(top + (c - min_col), left + (r - min_row), s.value)
            if s.has_style:
                t.font = copy(s.font)
                t.fill = copy(s.fill)
                t.border = copy(s.border)
                t.alignment = copy(s.alignment)
                t.number_format = s.number_format
                t.protection = copy(s.protection)

wb = load_workbook("monthly.xlsx")
dst = wb.create_sheet("By month")
transpose_range(wb["Monthly"], 1, 4, 1, 5, dst)
print(dst["B2"].value, dst["B2"].number_format, "|", dst["D3"].value, dst["D3"].number_format)

Each value keeps its own number format, so after transposing the revenue column is currency-style and the margin column shows percentages — the formats moved with the cells rather than staying with a row position. The border sides are copied as they are; for a uniform grid that looks right, but a table that has a thick bottom border under its header row will, after transposing, have a thick bottom border on the header column's cells, not a thick right border. Swap top/left and bottom/right on the copied Border if that matters.

Step 3 — Swap column widths and row heights

Widths and heights live on the sheet, not on the cells, so the copy above does not touch them. Column A was 12 wide and row 1 was 22 tall; after transposing, the old header row is the first column and the old first column is the header row. Carry the sizes that make sense across:

src = wb["Monthly"]
dst.column_dimensions["A"].width = max(src.column_dimensions[get_column_letter(c)].width or 8.43 for c in range(1, 6))
for c in range(2, 5):
    dst.column_dimensions[get_column_letter(c)].width = 12
dst.row_dimensions[1].height = src.row_dimensions[1].height
wb.save("monthly_transposed.xlsx")
print({k: v.width for k, v in dst.column_dimensions.items()})

A column width is measured in characters and a row height in points, so there is no exact conversion between the two. Set the new widths from the content — the longest value in each new column — as in Set Column Width and Row Height in openpyxl, rather than trying to translate the old heights.

Step 4 — Merged cells

A merged range has to be merged again in the transposed position, with its height and width swapped. Collect the merges inside the source range and re-create them after copying:

from openpyxl.worksheet.cell_range import CellRange

def transposed_merges(src, min_row, max_row, min_col, max_col, top=1, left=1):
    area = CellRange(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col)
    for m in src.merged_cells.ranges:
        if m.issubset(area):
            yield CellRange(min_row=top + m.min_col - min_col, max_row=top + m.max_col - min_col,
                            min_col=left + m.min_row - min_row, max_col=left + m.max_row - min_row).coord

src.merge_cells("B1:C1")
print(list(transposed_merges(src, 1, 4, 1, 5)))

A title merged across B1:C1 — two columns in one row — becomes A2:A3, two rows in one column. Call dst.merge_cells(coord) for each after the values are in place; only the top-left cell of a merge keeps its value, which is also what Excel's own transpose does.

Step 5 — When styles do not matter: pandas

For data rather than presentation, pandas transposes a whole sheet in one expression. Read with the first column as the index, take .T, and write back:

import pandas as pd

df = pd.read_excel("monthly.xlsx", sheet_name="Monthly", index_col=0)
flipped = df.T
flipped.index.name = "Month"
print(flipped)
flipped.to_excel("monthly_long.xlsx")

Look closely at the Orders column: 1284.0, not 1284. Before the flip, each month was a column holding revenue, orders and margin together, so pandas stored it as floats; transposing moves those floats into place, and the whole-number column comes out as float. Fix it with flipped = flipped.astype({"Orders": "int64"}), or call .infer_objects() after reading a sheet with mixed columns. The month labels, which were column headers, become the index, and each metric becomes a column with a single type — which is what charts, pivots and databases want. That shape is closer to the "one row per record" layout described in Unpivot a Wide Excel Sheet with pandas melt, which is often what the transpose was really for.

Step 6 — Keep it linked with a TRANSPOSE formula

Every method above produces a copy: change a number in the source table and the transposed version is stale. If the workbook should keep the two in step, write Excel's TRANSPOSE function instead, as an array formula over the target range:

from openpyxl.worksheet.formula import ArrayFormula

wb = load_workbook("monthly.xlsx")
ws = wb["Monthly"]
ws["H1"] = ArrayFormula("H1:K5", "=TRANSPOSE(A1:E4)")
wb.save("monthly_linked.xlsx")

The target range must have the transposed shape — five rows by four columns for a four-by-five source. Excel and LibreOffice calculate it on open; openpyxl does not calculate formulas, so check it by letting LibreOffice recalculate a copy:

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir recalculated monthly_linked.xlsx > /dev/null 2>&1
ls recalculated

ws = load_workbook("recalculated/monthly_linked.xlsx", data_only=True)["Monthly"]
for row in ws.iter_rows(min_row=1, max_row=5, min_col=8, max_col=11, values_only=True):
    print(row)

The formula produces values only — TRANSPOSE does not carry formats — so format the target range once, by column. In Excel 365, =TRANSPOSE(A1:E4) in a single cell spills automatically; the array formula form above works in every version.

Common pitfalls

• Overlapping source and target. Transposing in place over the same cells overwrites values before they are read. Read everything first, or write to a new sheet or a clear area.
• Formulas in the source. Copying a formula string moves it without adjusting its references; =B2*1.2 still points at B2. Load with data_only=True to copy values, or rewrite references deliberately.
• Borders that suddenly look wrong. Directional borders keep their side; swap them if the table has a header rule.
• Wrong target shape for the array formula. An ArrayFormula range smaller than the transposed source truncates it; a larger one fills the spare cells with #N/A.
• Tables and conditional formats. An Excel table object or a conditional-format range is not moved by cell copying; re-create them over the new range.

Conclusion

To transpose values, zip(*rows) over iter_rows(values_only=True). To keep the look, copy each cell's value and style from (r, c) to (c, r), then set widths from the new content and re-create merged ranges with their dimensions swapped. Use pandas .T when only the data matters, and a TRANSPOSE array formula when the transposed range should keep following the source.

Frequently asked questions

Does openpyxl have a transpose function? No. Read the range with iter_rows(values_only=True), regroup it with zip(*rows) and append the new rows to a sheet. To keep formatting, copy each cell's value and style to the swapped position instead.

How do I transpose and keep the formatting? Loop over the source range and write source cell (r, c) to target cell (c, r), copying font, fill, border, alignment, protection and number_format with copy(). Column widths and row heights are sheet settings and must be set separately.

How do I write a TRANSPOSE formula with openpyxl? Assign an ArrayFormula to the top-left target cell: ws"H1" = ArrayFormula("H1:K5", "=TRANSPOSE(A1:E4)"). The target range must have the transposed shape. Excel calculates it when the file is opened.

Can pandas transpose an Excel sheet? Yes: read it with index_col=0 so the first column becomes the index, call .T and write the result with to_excel. Formatting is not preserved, so use it for data rather than presentation.

Related

• Up one level: Using openpyxl for Excel File Manipulation — the cell-level toolkit.
• Move a Range of Cells with openpyxl — shifting a block without turning it.
• Copy Cell Styles with openpyxl — the style-copy pattern in detail.
• Write Dynamic Array Formulas with Python — spilling formulas in Excel 365.