Convert Several Ranges into Excel Tables at Once

Excel tables — the banded ranges created with Insert › Table — bring filters, structured references, automatic expansion and a name that Power Query, pivot tables and pandas can find. Workbooks that grew by hand rarely use them: a typical one has a dozen sheets, some with two or three separate blocks of data side by side or one above the other, and none of them is a table. Converting them one at a time in Excel is tedious, and doing it carelessly produces files Excel refuses to open, because tables have rules — headers must be unique text, names must be valid and unique across the workbook, and tables must not overlap. This page converts every data block in a workbook into a table in one pass with openpyxl, repairing headers and generating names as it goes. It belongs to Creating Excel Tables and Autofilters with Python.

Prerequisites

pip install openpyxl

The example workbook has three sheets. Sales 2026 has a quarterly block and, separated by an empty column, a block of reps and targets, plus a note below. Stock has a title, then a block whose header has a duplicated name and a blank. 2026 Targets has a number as one of its headings. Each of these is something Excel would reject in a table header if it were written as is.

Step 1 — Find the blocks of data on each sheet

from openpyxl import load_workbook

def find_blocks(ws):
    """Rectangles of non-empty cells separated by fully empty rows or columns."""
    filled = {(c.row, c.column) for row in ws.iter_rows() for c in row if c.value not in (None, "")}
    seen, blocks = set(), []
    for start in sorted(filled):
        if start in seen:
            continue
        stack, cells = [start], set()
        while stack:                                    # flood fill over touching cells (incl. diagonals)
            r, c = stack.pop()
            if (r, c) in cells:
                continue
            cells.add((r, c))
            stack += [(r + dr, c + dc) for dr in (-1, 0, 1) for dc in (-1, 0, 1) if (r + dr, c + dc) in filled]
        seen |= cells
        rows, cols = [r for r, _ in cells], [c for _, c in cells]
        blocks.append((min(rows), min(cols), max(rows), max(cols)))
    return blocks

wb = load_workbook("branch_data.xlsx")
for ws in wb.worksheets:
    print(ws.title, find_blocks(ws))

A block is a group of filled cells that touch each other, and the function finds them with a flood fill: start at a filled cell, collect every filled neighbour, and repeat until the group stops growing. The rectangle around the group is the candidate table. Blocks separated by an empty row or column come out separately, which is how the quarterly figures and the rep targets on the first sheet become two blocks, and the note below them a third. A gap inside a block — a missing value in the middle of the data — does not split it, because the cells around the gap still touch it.

Step 2 — Repair the header row

def repair_header(ws, top, left, right):
    """Make every header cell non-empty, text and unique (case-insensitively); return the changes."""
    changes, used = [], set()
    for c in range(left, right + 1):
        cell = ws.cell(top, c)
        name = str(cell.value).strip() if cell.value not in (None, "") else f"Column{c - left + 1}"
        base, n = name, 2
        while name.lower() in used:
            name = f"{base}_{n}"
            n += 1
        used.add(name.lower())
        if cell.value != name:
            changes.append(f"{cell.coordinate}: {cell.value!r} -> {name!r}")
            cell.value = name
    return changes

Excel insists on three things for a table's header row: every cell has a value, every value is text, and no two values are the same, ignoring case. A file that breaks any of these opens with a repair message, and Excel quietly renames columns — so it is better to fix them deliberately and report what changed. Blank headings become Column1, Column2 and so on, in the same style Excel uses; numbers become their text form, so the heading 2026 is stored as the string "2026"; and duplicates get a suffix, so a second Qty becomes Qty_2. Comparing in lower case catches Qty and QTY, which Excel also treats as the same.

Step 3 — Valid, unique names and the conversion itself

import re
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter as col

def table_name(ws, top, left, taken):
    heading = str(ws.cell(top, left).value or "Data")
    name = re.sub(r"\W+", "_", f"{ws.title}_{heading}").strip("_")
    if not re.match(r"[A-Za-z_]", name):
        name = "T_" + name                              # must start with a letter or underscore
    if re.fullmatch(r"[A-Za-z]{1,3}\d+", name) or name.upper() in ("R", "C"):
        name = "T_" + name                              # must not look like a cell address
    base, n = name[:240], 2
    while name.lower() in taken:
        name = f"{base}_{n}"
        n += 1
    taken.add(name.lower())
    return name

taken = {t.lower() for ws in wb.worksheets for t in ws.tables}
report = []
for ws in wb.worksheets:
    existing = [ws.tables[t].ref for t in ws.tables]
    for top, left, bottom, right in find_blocks(ws):
        if bottom - top < 1 or right - left < 1:
            report.append(f"{ws.title}: skipped {col(left)}{top} (too small for a table)")
            continue
        ref = f"{col(left)}{top}:{col(right)}{bottom}"
        if any(ref == e for e in existing):
            continue
        fixes = repair_header(ws, top, left, right)
        name = table_name(ws, top, left, taken)
        t = Table(displayName=name, ref=ref)
        t.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True)
        ws.add_table(t)
        report.append(f"{ws.title}: {name} = {ref}" + (f"  (header fixes: {'; '.join(fixes)})" if fixes else ""))
wb.save("branch_data_tables.xlsx")
print("\n".join(report))

Table names follow the rules for defined names, and they must be unique across the whole workbook, not just the sheet. The generated name combines the sheet title and the block's first heading — Sales_2026_Region, Stock_SKU — with anything that is not a letter, digit or underscore replaced by an underscore. Two checks catch names Excel would reject: a name must not start with a digit, and it must not look like a cell address, such as ABC1 or R. Existing table names are collected first so a new name never collides with one already in the file.

Blocks smaller than two rows or two columns are skipped and reported: a title, a single note or a lone label is not a table, and converting it would only add clutter. The report lists every table created and every header cell that was changed, which is the record someone needs when a formula elsewhere refers to a column by its old heading.

Step 4 — Check the result

from itertools import combinations
from openpyxl.worksheet.cell_range import CellRange

wb2 = load_workbook("branch_data_tables.xlsx")
for ws in wb2.worksheets:
    ranges = [(n, CellRange(ws.tables[n].ref)) for n in ws.tables]
    clashes = [(a, b) for (a, ra), (b, rb) in combinations(ranges, 2) if not ra.isdisjoint(rb)]
    headers_ok = all(isinstance(ws.cell(r.min_row, c).value, str)
                     for _, r in ranges for c in range(r.min_col, r.max_col + 1))
    print(f"{ws.title:13} tables={[n for n, _ in ranges]} overlaps={clashes} text_headers={headers_ok}")

Reading the saved file back confirms the two conditions that most often break a batch conversion: no two tables on a sheet overlap, and every header cell is text. Running the check as part of the script means a workbook with an odd layout — two blocks that touch diagonally, say, and were merged into one — is caught before anyone opens it in Excel.

When a block is not really a table

The flood fill is deliberately simple, and some layouts need a person's judgement. A report-style sheet with a title row directly above its headings, with no blank row between, will be detected as one block whose header row is the title; leave a blank row, or pass the header row explicitly for that sheet. Two tables placed side by side with no empty column between them come out as one block. Cross-tab layouts with headings in both the first row and the first column convert fine, but the first column becomes an ordinary column of the table. Running the conversion on a copy and reviewing the report is the safe routine; anything odd can be fixed in the source and the conversion rerun.

Common pitfalls

• Numbers or blanks in headers. Excel repairs the file and renames columns; fix them first.
• Duplicate headings in a different case. Qty and QTY clash; compare in lower case.
• Names starting with a digit or shaped like cells. Prefix them, e.g. T_2026_....
• Names unique only per sheet. Table names must be unique in the whole workbook.
• Titles and notes as tables. Skip blocks smaller than two rows or columns.

Conclusion

To turn every block of data in a workbook into an Excel table, find the blocks on each sheet with a flood fill over filled cells, skip the ones too small to be tables, repair each header row so it is non-empty, text and unique, generate a valid name that is unique across the workbook, and add the table with openpyxl. Report every change, and check the saved file for overlaps and text headers before anyone opens it.

Frequently asked questions

How do I convert a range to an Excel table with openpyxl? Create Table(displayName="Name", ref="A1:D10"), optionally set a TableStyleInfo, and add it with ws.add_table(table).

Why does Excel say it repaired my file after adding a table? Usually the header row has a blank, a number or a duplicate heading, or two tables overlap; make every header unique text and keep tables apart.

What are the rules for Excel table names? They start with a letter or underscore, contain only letters, digits, underscores and full stops, must not look like a cell reference, and must be unique in the workbook.

Can one sheet hold several tables? Yes, as long as their ranges do not overlap.

Related

• Up one level: Creating Excel Tables and Autofilters with Python — tables and filters.
• Create an Excel Table with Python — one table at a time.
• Read Multiple Tables from One Excel Sheet — finding blocks when reading.
• Read an Excel Table by Name into pandas — using the new tables.