Lay Out a Dashboard Sheet on a Grid

Generated dashboards often look slightly wrong in a way that is hard to name: one chart a few pixels wider than the one beside it, KPI tiles that do not quite line up with the chart edges, a gap on the right that changes with the zoom level. The cause is usually that each element was positioned on its own — a chart anchored at H2 with a width in centimetres, a tile merged over whatever columns happened to be free — so nothing shares a common measure. Designers solve this with a grid, and an Excel sheet already is one. This page makes the sheet's columns and rows a uniform grid, describes every tile and chart by its position and span on that grid, snaps charts to cell boundaries with two-cell anchors so their edges coincide with the tiles', and checks the layout for overlaps before saving. It belongs to Building Multi-Sheet Excel Dashboards.

Prerequisites

pip install openpyxl

The examples use openpyxl because it exposes chart anchors directly. The layout ideas — a uniform grid, positions described in grid units, anchors on cell boundaries — apply equally to XlsxWriter, whose insert_chart accepts offsets and sizes in pixels you can compute from the same grid.

Step 1 — Decide the grid

A dashboard sheet laid out on 12 or 24 equal columns is easy to divide into halves, thirds and quarters. Set every grid column to the same width and every row to the same height, and keep column A as a narrow margin:

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

GRID_COLS, COL_WIDTH = 24, 4.7          # character units; about 37 px each in Calibri 11
GRID_ROWS, ROW_HEIGHT = 40, 15           # points; 20 px each
MARGIN = 1                               # column A and row 1 are a margin

def set_up_grid(ws):
    ws.column_dimensions["A"].width = 2
    ws.row_dimensions[1].height = 8
    for c in range(MARGIN + 1, MARGIN + GRID_COLS + 1):
        ws.column_dimensions[get_column_letter(c)].width = COL_WIDTH
    for r in range(MARGIN + 1, MARGIN + GRID_ROWS + 1):
        ws.row_dimensions[r].height = ROW_HEIGHT
    ws.sheet_view.showGridLines = False
    ws.sheet_view.zoomScale = 90

def px(width_chars):                       # Excel's conversion for the default font
    return int(width_chars * 7 + 5)

print("grid column:", px(COL_WIDTH), "px; full grid width:", GRID_COLS * px(COL_WIDTH), "px")

Column widths in Excel are measured in characters of the default font, not pixels, which is why "the same width" has to mean the same number in every column rather than an attempt to compute pixels per element. With 24 columns of 37 pixels each the grid is just under 900 pixels wide — comfortable on a laptop at 90% zoom. Rows are measured in points; 15 points is 20 pixels at standard resolution. Turning off gridlines removes the visual noise, and the grid remains in force invisibly. Column widths themselves are covered in Set Column Width and Row Height in openpyxl.

Step 2 — Describe the layout in grid units

Rather than scattering cell addresses through the code, write the layout as data: each element has a column, row, width and height in grid units. A helper turns that into cell ranges and checks that nothing overlaps:

from dataclasses import dataclass

@dataclass
class Box:
    name: str
    col: int      # 0-based grid column
    row: int      # 0-based grid row
    w: int        # width in grid columns
    h: int        # height in grid rows

    def cells(self):
        c1, r1 = MARGIN + 1 + self.col, MARGIN + 1 + self.row
        return c1, r1, c1 + self.w - 1, r1 + self.h - 1

    def ref(self):
        c1, r1, c2, r2 = self.cells()
        return f"{get_column_letter(c1)}{r1}:{get_column_letter(c2)}{r2}"

LAYOUT = [
    Box("title", 0, 0, 24, 2),
    Box("kpi_revenue", 0, 3, 6, 4), Box("kpi_orders", 6, 3, 6, 4), Box("kpi_margin", 12, 3, 6, 4), Box("kpi_returns", 18, 3, 6, 4),
    Box("chart_trend", 0, 8, 16, 14), Box("chart_mix", 16, 8, 8, 14),
    Box("table_top", 0, 23, 24, 12),
]

def check(layout):
    taken = {}
    for b in layout:
        assert b.col + b.w <= GRID_COLS and b.row + b.h <= GRID_ROWS, f"{b.name} leaves the grid"
        for c in range(b.col, b.col + b.w):
            for r in range(b.row, b.row + b.h):
                assert (c, r) not in taken, f"{b.name} overlaps {taken[(c, r)]}"
                taken[(c, r)] = b.name
    return len(taken)

print("cells used:", check(LAYOUT), "of", GRID_COLS * GRID_ROWS)
for b in LAYOUT[:3] + LAYOUT[5:7]:
    print(f"  {b.name:<12} {b.ref()}")

Four KPI tiles of six columns fill the width exactly; the trend chart takes two thirds and the mix chart one third; a table spans the full width below. Changing the design is now a matter of editing numbers in LAYOUT, and the check catches a tile that overlaps a chart or runs off the right edge before the workbook is written — far cheaper than discovering it in Excel.

Step 3 — Draw tiles into their boxes

Tiles are merged cell ranges with a label and a formula. Because the box gives the range, the drawing code needs no addresses of its own:

from openpyxl.styles import Alignment, Border, Font, PatternFill, Side

edge = Side(style="thin", color="D0D7DE")

def draw_tile(ws, box, label, formula, fmt):
    c1, r1, c2, r2 = box.cells()
    ws.merge_cells(start_row=r1, start_column=c1, end_row=r1, end_column=c2)
    ws.merge_cells(start_row=r1 + 1, start_column=c1, end_row=r2, end_column=c2)
    ws.cell(r1, c1, label).font = Font(size=9, color="57606A")
    value = ws.cell(r1 + 1, c1, formula)
    value.font, value.number_format = Font(size=20, bold=True), fmt
    for r in range(r1, r2 + 1):
        for c in range(c1, c2 + 1):
            cell = ws.cell(r, c)
            cell.fill = PatternFill("solid", fgColor="F6F8FA")
            cell.alignment = Alignment(horizontal="center", vertical="center")
            cell.border = Border(top=edge if r == r1 else None, bottom=edge if r == r2 else None,
                                 left=edge if c == c1 else None, right=edge if c == c2 else None)

wb = Workbook()
ws = wb.active
ws.title = "Dashboard"
set_up_grid(ws)
boxes = {b.name: b for b in LAYOUT}
c1, r1, c2, r2 = boxes["title"].cells()
ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2)
ws.cell(r1, c1, "Sales dashboard — September 2026").font = Font(size=16, bold=True)
for name, label, formula, fmt in [("kpi_revenue", "Revenue", 1284500, "#,##0"), ("kpi_orders", "Orders", 3412, "#,##0"),
                                  ("kpi_margin", "Gross margin", 0.384, "0.0%"), ("kpi_returns", "Return rate", 0.021, "0.0%")]:
    draw_tile(ws, boxes[name], label, formula, fmt)
print("merged ranges:", len(ws.merged_cells.ranges))

Borders are drawn only on a tile's outer edge, so the merged block reads as one card. In a real dashboard the values are formulas pointing at the data sheets, as in Add a KPI Summary Block to an Excel Dashboard; numbers are used here to keep the example short.

Step 4 — Snap charts to the grid with two-cell anchors

By default openpyxl anchors a chart at one cell and gives it a size in centimetres (chart.width, chart.height), so its right and bottom edges land wherever that size happens to fall — rarely on a cell boundary. A two-cell anchor fixes both corners to cells instead:

from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.drawing.spreadsheet_drawing import AnchorMarker, TwoCellAnchor

data = wb.create_sheet("Data")
for row in [["Month", "Revenue", "Share"], ["Jul", 1180, 0.42], ["Aug", 1215, 0.31], ["Sep", 1284, 0.27]]:
    data.append(row)

def anchor_to(box):
    c1, r1, c2, r2 = box.cells()
    anchor = TwoCellAnchor(editAs="twoCell")
    anchor._from = AnchorMarker(col=c1 - 1, row=r1 - 1)          # markers are 0-based
    anchor.to = AnchorMarker(col=c2, row=r2)                     # the cell after the box's last cell
    return anchor

trend = LineChart()
trend.add_data(Reference(data, min_col=2, min_row=1, max_row=4), titles_from_data=True)
trend.set_categories(Reference(data, min_col=1, min_row=2, max_row=4))
mix = BarChart()
mix.add_data(Reference(data, min_col=3, min_row=1, max_row=4), titles_from_data=True)
mix.set_categories(Reference(data, min_col=1, min_row=2, max_row=4))
for chart, name in [(trend, "chart_trend"), (mix, "chart_mix")]:
    chart.x_axis.delete = chart.y_axis.delete = False
    chart.anchor = anchor_to(boxes[name])
    ws.add_chart(chart)
wb.save("grid_dashboard.xlsx")

The from marker is the top-left cell and the to marker is the cell just past the bottom-right one, both counted from zero, so the chart covers exactly the box's cells. editAs="twoCell" tells Excel to move and resize the chart with its cells: widen a grid column and the chart widens with it, keeping every edge aligned with the tiles above. The trend chart's right edge and the mix chart's left edge now share a cell boundary, so there is neither a gap nor an overlap between them.

Step 5 — Check the saved anchors

openpyxl does not read charts back when it loads a workbook, but the anchors are plain XML in the drawing part, so they can be checked directly:

import re
import zipfile

with zipfile.ZipFile("grid_dashboard.xlsx") as z:
    drawing = z.read("xl/drawings/drawing1.xml").decode()
anchors = re.findall(r"<(?:xdr:)?twoCellAnchor.*?<(?:xdr:)?from><(?:xdr:)?col>(\d+)</(?:xdr:)?col>.*?<(?:xdr:)?row>(\d+)</(?:xdr:)?row>"
                     r".*?<(?:xdr:)?to><(?:xdr:)?col>(\d+)</(?:xdr:)?col>.*?<(?:xdr:)?row>(\d+)</(?:xdr:)?row>", drawing, re.S)
for (fc, fr, tc, tr), name in zip(anchors, ["chart_trend", "chart_mix"]):
    print(f"{name:<12} from col {fc} row {fr} to col {tc} row {tr}  -> cells {boxes[name].ref()}")

Both charts start and end exactly on the cells their boxes describe, and the trend chart's to column is the mix chart's from column — the shared edge. A check like this belongs in the test suite of any report that is supposed to look the same every month; Test Excel Output with pytest shows the wider testing approach.

Common pitfalls

• Charts sized in centimetres. Their edges fall between cells; use two-cell anchors.
• Columns of mixed widths. Elements cannot share edges unless the grid is uniform.
• Off-by-one markers. from is the first cell, to the cell after the last, both 0-based.
• Addresses scattered through code. Keep the layout in one list and check it.
• Charts that do not resize. Use editAs="twoCell".

Conclusion

Make the dashboard sheet a uniform grid — equal column widths and row heights, gridlines hidden — and describe every tile and chart as a box in grid units, checked for overlaps before anything is written. Draw tiles as merged ranges over their boxes and give charts two-cell anchors on the same boxes, so every edge lands on a cell boundary and charts resize with their columns. Read the anchors back from the drawing XML to confirm the layout in a test.

Frequently asked questions

How do I align charts exactly with cells in openpyxl? Give the chart a TwoCellAnchor whose from marker is the top-left cell and whose to marker is the cell after the bottom-right cell, both zero-based, and set editAs to twoCell.

Why do my openpyxl charts not line up with each other? By default a chart is anchored at one cell with a size in centimetres, so its other edges fall between cells. Anchoring both corners to cells on a uniform grid aligns them.

What grid should an Excel dashboard use? A 12- or 24-column grid of equal widths is easy to divide into halves, thirds and quarters, with a narrow first column as a margin.

Can I check chart positions in a saved workbook? openpyxl does not load charts, but the anchors are in the drawing XML inside the xlsx zip file and can be read with zipfile.

Related

• Up one level: Building Multi-Sheet Excel Dashboards — dashboards from Python.
• Build a Dashboard Sheet with Charts from Multiple Tabs — the charts themselves.
• Build a Rolling 12-Month Chart on an Excel Dashboard — a chart that follows the data.
• Merge Cells and Centre a Report Title with openpyxl — merged tiles.