Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Add Borders to a Range with openpyxl

openpyxl sets borders per cell, and every assignment replaces the whole border. Build helpers that draw a light grid, an outline around a table, a heavy line under the header and a double line above totals — without wiping edges set earlier — and handle merged cells and immutable style objects correctly.
Cell styling & layoutopenpyxl

Borders make a table readable: a light grid to guide the eye, a line under the header, a box around the whole table and the classic double rule above a total. In Excel you select a range and click "Outside Borders". openpyxl has no such command — borders belong to individual cells, and each cell's border is replaced wholesale on every assignment. That is why border code so often erases itself. This guide, part of Styling Excel Cells with openpyxl, builds a few small helpers that apply borders to ranges the way Excel does, and checks the result by rendering the workbook.

A table with grid, header rule, outline and total rule A sales table with Region, Q1, Q2 and Total columns. Light grey grid lines separate all cells, a medium dark line runs under the header, a medium outline surrounds the table, and a double line sits above the Total row with its sums of 240, 285 and 525. A B C D 1 Region Q1 Q2 Total 2 North 120 140 260 3 South 80 90 170 4 East 40 55 95 5 Total 240 285 525 grid first, then the rules that sit on top of it

Prerequisites

Bash
pip install openpyxl

The sample table:

Python
from copy import copy

from openpyxl import Workbook
from openpyxl.styles import Border, Font, Side
from openpyxl.utils.cell import range_boundaries

wb = Workbook()
ws = wb.active
ws.title = "Sales"
for row in [("Region", "Q1", "Q2", "Total"), ("North", 120, 140, "=B2+C2"),
            ("South", 80, 90, "=B3+C3"), ("East", 40, 55, "=B4+C4"),
            ("Total", "=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)")]:
    ws.append(row)

Step 1 — Borders are four Sides, replaced together

A Border holds four Side objects — left, right, top, bottom — each with a style and a colour. Assigning a new Border to a cell replaces all four at once, and style objects cannot be edited in place:

Python
thin = Side(style="thin", color="CDD5E6")
ws["A1"].border = Border(left=thin, right=thin, top=thin, bottom=thin)
ws["A1"].border = Border(bottom=Side(style="medium"))
print(getattr(ws["A1"].border.left, "style", None), ws["A1"].border.bottom.style)     # None medium

try:
    ws["A1"].border.left = thin
except AttributeError as exc:
    print(exc)          # Style objects are immutable and cannot be changed.Reassign the style with a copy

The second assignment wiped the left, right and top edges — the classic self-erasing border. The fix is a helper that starts from the cell's current border and replaces only the edges you name.

Step 2 — A helper that keeps existing edges

set_edges reads the cell's current border, swaps in the given sides and assigns the result:

Python
def set_edges(cell, **sides):
    current = cell.border
    cell.border = Border(
        left=sides.get("left", current.left), right=sides.get("right", current.right),
        top=sides.get("top", current.top), bottom=sides.get("bottom", current.bottom),
    )

def grid(ws, ref, side):
    for row in ws[ref]:
        for cell in row:
            set_edges(cell, left=side, right=side, top=side, bottom=side)

def outline(ws, ref, side):
    min_col, min_row, max_col, max_row = range_boundaries(ref)
    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:
            edges = {}
            if cell.row == min_row: edges["top"] = side
            if cell.row == max_row: edges["bottom"] = side
            if cell.column == min_col: edges["left"] = side
            if cell.column == max_col: edges["right"] = side
            if edges:
                set_edges(cell, **edges)

outline touches only the cells on the edge of the range and only the edges that face outward, which is exactly what Excel's "Outside Borders" does. Because every change goes through set_edges, the helpers can be applied in any order without erasing each other.

Why set_edges does not erase earlier borders The helper reads the cell's current border, keeps every side that was not passed in, replaces only the named sides, and assigns a new Border. Assigning Border with only a bottom side would instead reset left, right and top to nothing. 1 def set_edges(cell, **sides): 2 1 current = cell.border 3 3 cell.border = Border( 4 2 left=sides.get("left", current.left), 5 bottom=sides.get("bottom", current.bottom), 6 ...) 1 read what is already there 2 keep an edge unless it was passed in 3 one new Border, assigned once

Step 3 — Layer the table's borders

Apply the light grid first, then the rules that should sit on top of it, then the outline last so nothing overwrites it:

Python
medium = Side(style="medium", color="1E293B")
double = Side(style="double", color="1E293B")

grid(ws, "A1:D5", thin)
for cell in ws[1]:
    set_edges(cell, bottom=medium)
for cell in ws[5]:
    set_edges(cell, top=double)
outline(ws, "A1:D5", medium)
wb.save("bordered.xlsx")
print(ws["A1"].border.left.style, ws["B3"].border.left.style, ws["B5"].border.top.style)
# medium thin double

Rendered, the table has a pale grid, a dark rule under the header, a double rule above the totals and a dark frame around everything. The double line above a total row is the accounting convention for "this is the sum of the figures above", and readers of financial reports look for it.

Step 4 — Bold headers without losing the font

The same immutability applies to fonts, with a subtler trap: Font(bold=True) creates a whole new font with no name or size, so the cell falls back to the application default — in LibreOffice's rendering, a serif face. Copy the existing font and change one attribute instead:

Python
def make_bold(cell):
    font = copy(cell.font)
    font.bold = True
    cell.font = font

for cell in list(ws[1]) + list(ws[5]):
    make_bold(cell)
wb.save("bordered.xlsx")
print(ws["A1"].font.name, ws["A1"].font.sz, ws["A1"].font.b)       # Calibri 11.0 True

copy produces an editable duplicate of the style object; after changing it, assign it back. The same pattern works for fills, alignment and protection. For a report with many styled ranges, a named style is more efficient than per-cell copies — see Apply a Reusable Style Theme Across an Excel Report.

Border styles and where they fit Hair and thin lines suit inner grids. Medium lines mark the header rule and the table outline. Thick lines are for emphasis on a single edge. Double lines mark totals. Dashed and dotted lines suit input areas or annotations rather than data tables. Typical use hair, thin inner grid lines medium header rule, outline thick one strong edge double above a total row dashed, dotted input areas and notes

Step 5 — Merged cells

A merged range is drawn from its individual cells, so an outline around a merged title needs the edges on every cell in the block — setting a border on the top-left cell alone leaves the rest of the frame missing:

Python
ws.insert_rows(1)
ws["A1"] = "Quarterly sales"
ws.merge_cells("A1:D1")
outline(ws, "A1:D1", medium)
wb.save("bordered.xlsx")
print([ws.cell(1, c).border.top.style for c in range(1, 5)])      # ['medium', 'medium', 'medium', 'medium']

outline already walks every cell of the range, so it handles merged blocks without special cases. Inserting a row shifts the cells and their borders down with them, so the table keeps its formatting below the new title. Merging itself is covered in Merge Cells and Centre a Report Title with openpyxl.

Step 6 — Border a table written by pandas

In most reports the table comes from DataFrame.to_excel, and the borders go on afterwards. The range follows from the frame's shape, so compute it instead of hard-coding it — the report may have four rows today and forty next month:

Python
import pandas as pd
from openpyxl.utils import get_column_letter

report = pd.DataFrame({"Region": ["North", "South", "East"], "Q1": [120, 80, 40], "Q2": [140, 90, 55]})
with pd.ExcelWriter("pandas_bordered.xlsx", engine="openpyxl") as writer:
    report.to_excel(writer, sheet_name="Report", index=False, startrow=1)
    sheet = writer.sheets["Report"]
    first_row = 2                                            # header row after startrow=1
    last_row = first_row + len(report)
    last_col = get_column_letter(len(report.columns))
    table_ref = f"A{first_row}:{last_col}{last_row}"
    grid(sheet, table_ref, thin)
    for cell in sheet[first_row]:
        set_edges(cell, bottom=medium)
    outline(sheet, table_ref, medium)
print(table_ref)                                             # A2:C5

startrow is zero-based in pandas and leaves row 1 free for a title, so the header lands in row 2. The current pandas release writes a plain header — no bold, no border — so the grid and the header rule supply all the structure; make the header bold with the make_bold helper from Step 4. Older pandas versions styled the header themselves, bold with thin borders, and the helpers simply layer on top of that. Writing the table and styling it in the same ExcelWriter block means the file is saved once, with borders included.

Common pitfalls

  • Borders on the neighbour. The line between A1 and A2 can come from A1's bottom or A2's top. Pick one convention — this guide always sets both through the grid — or lines appear twice as thick in some viewers.
  • Borders that do not print. Very light colours such as E7EBEF look fine on screen and vanish on paper. Check a print preview, or use a slightly darker grey for grids in reports that are printed.
  • Styling huge ranges cell by cell. Hundreds of thousands of set_edges calls are slow. Style the header and totals explicitly and rely on a table style or banding for the body.
  • Colours with a #. Side(color="#CDD5E6") raises "Colors must be aRGB hex values"; pass the six hex digits without the hash, or an 8-digit ARGB value.

Conclusion

openpyxl borders are per cell and replaced as a whole, so build them through a helper that copies the current border and swaps only the named edges. With set_edges, grid and outline you can layer a light grid, a medium header rule, a double total rule and an outer frame in any order, border merged blocks cell by cell, and — by the same copy-then-assign pattern — make text bold without losing its font.

Frequently asked questions

How do I put a border around a range with openpyxl? openpyxl has no range-level border, so loop over the cells and set only the outer edges — top on the first row, bottom on the last, left on the first column and right on the last — while keeping each cell's other edges.

Why did my border disappear when I added another one? Assigning cell.border replaces all four edges. Setting a bottom border after a grid removes the grid's left, right and top lines. Build the new Border from the existing one, changing only the edges you want.

Why do I get "Style objects are immutable"? openpyxl style objects cannot be changed in place, so cell.border.left = Side(...) fails. Create a new Border — or copy the existing one with the changed edge — and assign it to cell.border.

How do I border merged cells? Set the border on every cell covered by the merge, not just the top-left one. Excel draws each edge from the cell that sits on it, so the outer cells of the merged block need the outline edges.

More on cell styling & layout

More on cell styling & layout →