Move a Range of Cells with openpyxl move_range

Report templates change shape: a title row needs to go above the table, a new column has to squeeze in on the left, a block of inputs should sit further right. openpyxl's ws.move_range() shifts a rectangular block of cells by a number of rows and columns, and with one argument it keeps the formulas inside that block working. It does less than Excel's cut-and-paste, though, and the parts it leaves behind are exactly the ones that break reports quietly. This page, part of Using openpyxl for Excel File Manipulation, shows what moves, what does not, and how to move a table safely.

Prerequisites

pip install openpyxl

from openpyxl import Workbook
from openpyxl.styles import Font

def build():
    wb = Workbook()
    ws = wb.active
    ws.append(["item", "qty", "price", "total"])
    ws.append(["a", 2, 3, "=B2*C2"])
    ws.append(["b", 4, 5, "=B3*C3"])
    ws["A1"].font = Font(bold=True)
    ws["F1"] = "=SUM(D2:D3)"          # a formula outside the block that points into it
    return wb, ws

def show(ws, rows=6, cols=6):
    for row in ws.iter_rows(min_row=1, max_row=rows, max_col=cols, values_only=True):
        print(row)

Step 1 — Move a block with its formulas

move_range takes the block as an A1 range and offsets in rows and columns; positive numbers move down and right, negative numbers up and left. Pass translate=True so formulas inside the block are rewritten for their new position:

wb, ws = build()
ws.move_range("A1:D3", rows=2, cols=1, translate=True)
show(ws)

The table now sits at B3:E5, and its row formulas read =C4*D4 and =C5*D5 — each still multiplies its own row's quantity and price. The bold header moved with its cell, because styles belong to cells. Without translate=True, the moved formulas would keep their original text, =B2*C2, and multiply the wrong cells.

Step 2 — Fix formulas that point into the block

The last column of that output shows the catch. The formula in F1, =SUM(D2:D3), sat outside the moved block, so move_range did not touch it — and it now sums two empty cells. Excel's own cut-and-paste would have updated it; openpyxl does not track references from the rest of the sheet.

When other formulas refer to a block you move, rewrite their references with the same offset. openpyxl's formula tokenizer splits a formula into operands and operators, so a short helper can find every cell or range reference that lies wholly inside the moved block and shift it:

from openpyxl.formula.tokenizer import Tokenizer, Token
from openpyxl.utils.cell import range_boundaries, get_column_letter

def shift_refs(formula, block, rows, cols):
    bmin_c, bmin_r, bmax_c, bmax_r = range_boundaries(block)
    out = []
    for tok in Tokenizer(formula).items:
        text = tok.value
        if tok.type == Token.OPERAND and tok.subtype == Token.RANGE and "!" not in text:
            try:
                c1, r1, c2, r2 = range_boundaries(text.replace("$", ""))
            except ValueError:
                out.append(text)
                continue
            if bmin_c <= c1 and c2 <= bmax_c and bmin_r <= r1 and r2 <= bmax_r:
                start = f"{get_column_letter(c1 + cols)}{r1 + rows}"
                end = f"{get_column_letter(c2 + cols)}{r2 + rows}"
                text = start if (c1, r1) == (c2, r2) else f"{start}:{end}"
        out.append(text)
    return "=" + "".join(out)

print(ws["F1"].value)                                   # =SUM(D2:D3) — now stale
ws["F1"] = shift_refs(ws["F1"].value, "A1:D3", rows=2, cols=1)
print(ws["F1"].value)                                   # =SUM(E4:E5)
print(shift_refs("=SUM(D2:D9)", "A1:D3", 2, 1))         # partly outside: left alone

A range that is only partly inside the block — D2:D9 here — is left as it was, because there is no single right answer for it: the report's author has to decide whether it should grow, shrink or move. The helper also skips references to other sheets and writes relative addresses, so add $ markers back if your formulas relied on them. Run it over every formula on the sheet after a move, and review whatever it leaves alone.

For many dependent formulas, search the sheet for references into the old range before moving; Audit Every Formula in a Workbook with Python shows how to list them. If the dependent formulas are numerous and mechanical, it is often simpler to regenerate them than to patch them.

Step 3 — Know that translate means copy, not cut

translate=True shifts every relative reference in a moved formula by the same offset, the way copy-and-paste does. That is exactly right when a formula moves together with the cells it uses, as in Step 1. It is wrong when you move a formula away from inputs that stay put:

wb = Workbook()
ws = wb.active
ws["A1"] = 10
ws["B1"] = "=A1*2"

ws.move_range("B1", rows=1, translate=True)
print(ws["B2"].value)          # =A2*2 — now points at an empty cell

ws2 = Workbook().active
ws2["A1"] = 10
ws2["B1"] = "=A1*2"
ws2.move_range("B1", rows=1)   # translate=False keeps the text
print(ws2["B2"].value)         # =A1*2 — still points at the input

In Excel, cutting B1 and pasting it at B2 keeps =A1*2, because a cut formula keeps pointing at its inputs. openpyxl has no cut mode, so choose per move: translate when the inputs travel with the formula, do not translate when they stay behind. Absolute references ($A$1) are never shifted, which is another way to keep a formula anchored.

Step 4 — Protect the destination

move_range overwrites whatever is at the destination without warning — values, styles and all. Check the target area first:

from openpyxl.utils.cell import range_boundaries

def move_range_safely(ws, cell_range, rows=0, cols=0, translate=True):
    min_col, min_row, max_col, max_row = range_boundaries(cell_range)
    source = {(r, c) for r in range(min_row, max_row + 1) for c in range(min_col, max_col + 1)}
    for r in range(min_row + rows, max_row + rows + 1):
        for c in range(min_col + cols, max_col + cols + 1):
            if (r, c) in source:
                continue                     # overlapping part of the block itself
            if ws.cell(row=r, column=c).value is not None:
                raise ValueError(f"destination {ws.cell(row=r, column=c).coordinate} is not empty")
    ws.move_range(cell_range, rows=rows, cols=cols, translate=translate)

wb, ws = build()
ws["C6"] = "keep me"
try:
    move_range_safely(ws, "A1:D3", rows=4, cols=1)
except ValueError as exc:
    print(exc)
move_range_safely(ws, "A1:D3", rows=2, cols=1)
print(ws["E4"].value)

Cells inside the original block are allowed to overlap the destination — moving a table down by one row overlaps itself, and move_range handles that correctly. Merged cells deserve a special look: if the destination includes part of a merged range, the moved value can land in a covered cell and disappear from view. Unmerge first, move, then merge again at the new position; Fix "MergedCell object attribute value is read-only" in openpyxl has a helper that shifts merged ranges.

Step 5 — Move a whole table with its surroundings

Putting the pieces together, a table move in a real report has four parts: move the cells, update formulas elsewhere, shift the ranges that formatting rules and merges use, and copy column widths if the table moved sideways:

wb, ws = build()
ws.column_dimensions["D"].width = 14
ws.move_range("A1:D3", rows=2, cols=1, translate=True)
ws["F1"] = "=SUM(E4:E5)"                                      # dependent formula
ws.column_dimensions["E"].width = ws.column_dimensions["D"].width  # widths follow columns
ws["A1"] = "Order summary"                                    # the freed space is now usable
ws["A1"].font = Font(bold=True, size=14)
show(ws)
wb.save("moved.xlsx")

When a report needs space rather than a specific offset — a new first row for a title, a new column on the left — insert_rows and insert_cols are the simpler tools, though they have their own formula limitations; Insert and Delete Rows and Columns with openpyxl compares them.

Common pitfalls

• Forgetting translate=True. Moved formulas keep their text and compute the wrong cells.
• Using translate=True when inputs stay put. Relative references shift away from their inputs.
• Assuming dependent formulas follow. Totals, lookups and charts that referred to the old range keep the old address.
• Moving into merged or occupied cells. Data is overwritten or hidden without warning.
• Charts. A chart's data references are not updated by move_range; rebuild the reference after moving its source data.

Performance notes

move_range works cell by cell and is fast for report-sized blocks — thousands of cells move in milliseconds. For very large shifts (hundreds of thousands of cells), it is usually quicker and clearer to write the data at the right position in the first place, for example with startrow and startcol in pandas' to_excel.

Conclusion

ws.move_range(range, rows=, cols=, translate=True) moves values and styles and rewrites formulas inside the block as a copy-paste would. It does not update formulas elsewhere that point into the block, does not move merged ranges, conditional formatting, validation, widths or heights, and overwrites the destination silently. Check the destination, translate only when inputs move with their formulas, and fix dependent references and ranges as part of every move.

Frequently asked questions

Does move_range update formulas that refer to the moved cells? No. With translate=True it rewrites formulas inside the moved block, but a formula elsewhere on the sheet that pointed at the block keeps its old address and now reads the empty cells left behind. Update those formulas yourself.

Is translate=True the same as cut and paste in Excel? No, it behaves like copy and paste. Relative references in the moved formulas shift by the same offset. That is right when a formula moves together with its inputs, and wrong when a formula moves away from inputs that stay put.

What happens to cells at the destination? They are overwritten without warning, including cells that belong to a merged range. Check that the destination is empty before moving.

Do styles move with the cells? Yes. Fonts, fills, borders and number formats travel with each cell. Row heights, column widths, merged ranges, data validation and conditional formatting do not.

Related

• Up one level: Using openpyxl for Excel File Manipulation — editing workbooks cell by cell.
• Copy a Formula Down a Column in openpyxl — the Translator on its own.
• Write a DataFrame to a Specific Cell in Excel with pandas — placing data where it belongs from the start.
• Clear Cell Contents and Formatting with openpyxl — tidying the area a block leaves behind.