How-to

Formatting And Charting Excel Reports With PythonDeep dive

Convert an Excel Table Back to a Normal Range

Q: Why do my formulas show errors after removing the table?

They use structured references such as Table[Column], which point at the table by name. Once the table is gone the name no longer exists. Rewrite them as ordinary cell references before deleting the table.

Do from Python what Excel's "Convert to Range" does: remove a table object with openpyxl, rewrite the structured references that would otherwise break into ordinary A1 references, keep the banded look by writing it into the cells, keep an AutoFilter if one is wanted, and check with a recalculation that every formula still gives the same answer.

Tables, filters & outlines openpyxl

Excel tables — the objects created with Ctrl+T — are excellent for data entry and analysis, but some destinations do not want them: an older import tool that chokes on the table XML, a template that must be a plain grid, a sheet about to be restructured in ways a table will not allow, like merging cells in the header. Excel's Convert to Range command removes the table and quietly rewrites every formula that used structured references such as Sales[Amount] into ordinary cell references. openpyxl can remove the table in one line, but it does not rewrite anything, so those formulas break. This page, part of Creating Excel Tables and Autofilters with Python, does the full conversion.

Prerequisites

Bash

pip install openpyxl

orders_table.xlsx has a table called Sales in A1:D5 with an Amount column calculated from structured references — Sales[[#This Row],[Qty]]*Sales[[#This Row],[Price]] — and three summary formulas beside it that refer to the table by name: a total, a maximum and a row count. LibreOffice is used to recalculate the file and prove the conversion keeps every result.

Step 1 — See what breaks if you only delete the table

A table lives in ws.tables, a dictionary keyed by name. Deleting the entry removes the table object and leaves the cells:

Python

from openpyxl import load_workbook

wb = load_workbook("orders_table.xlsx")
ws = wb["Orders"]
print(list(ws.tables.keys()), ws.tables["Sales"].ref)
del ws.tables["Sales"]
wb.save("orders_naive.xlsx")
print(ws["D2"].value)
print(ws["G1"].value)

The formulas still say Sales[...], but there is no longer anything called Sales. Recalculate both versions with LibreOffice to see the effect:

Bash

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

Python

def results(path):
    ws = load_workbook(path, data_only=True)["Orders"]
    return [ws.cell(r, 4).value for r in range(2, 6)], [ws[f"G{r}"].value for r in (1, 2, 3)]

print("with table:   ", results("calc/orders_table.xlsx"))
print("table deleted:", results("calc/orders_naive.xlsx"))

With the table, the amounts are 12, 12.5, 8 and 35 and the total 67.5. With the table simply deleted, every formula that used it returns an error — seven broken cells from one line of code. Excel cannot resolve a reference to a table that no longer exists either, so the file is just as broken there. The table has to be converted, not just removed.

Step 2 — Rewrite structured references as A1 references

Structured references come in a few common shapes. Translate each into the equivalent cell or range, using the table's position and column names:

Python

import re
from openpyxl.utils import get_column_letter, range_boundaries

def a1_rewriter(table, ws):
    min_col, min_row, max_col, max_row = range_boundaries(table.ref)
    headers = [ws.cell(min_row, c).value for c in range(min_col, max_col + 1)]
    col = {h.lower(): get_column_letter(min_col + i) for i, h in enumerate(headers)}
    first, last = min_row + 1, max_row
    name = re.escape(table.displayName)

    def rewrite(formula, row):
        f = re.sub(rf"{name}\[\[#This Row\],\[([^\]]+)\]\]", lambda m: f"{col[m.group(1).lower()]}{row}", formula, flags=re.I)
        f = re.sub(r"\[@\[?([^\]]+?)\]?\]", lambda m: f"{col[m.group(1).lower()]}{row}", f)
        f = re.sub(rf"{name}\[#Data\]", f"${get_column_letter(min_col)}${first}:${get_column_letter(max_col)}${last}", f, flags=re.I)
        f = re.sub(rf"{name}\[#All\]", f"${get_column_letter(min_col)}${min_row}:${get_column_letter(max_col)}${last}", f, flags=re.I)
        f = re.sub(rf"{name}\[([^\[\]#]+)\]", lambda m: f"${col[m.group(1).lower()]}${first}:${col[m.group(1).lower()]}${last}", f, flags=re.I)
        return f
    return rewrite

wb = load_workbook("orders_table.xlsx")
ws = wb["Orders"]
rewrite = a1_rewriter(ws.tables["Sales"], ws)
for text, row in [("=Sales[[#This Row],[Qty]]*Sales[[#This Row],[Price]]", 2), ("=SUM(Sales[Amount])", 1),
                  ("=ROWS(Sales[#Data])", 3), ("=[@Qty]*[@Price]", 4)]:
    print(f"{text:55} -> {rewrite(text, row)}")

Row-relative references — [#This Row] and the [@Col] shorthand — become a relative cell in the same row, such as B2; whole-column references become absolute ranges, $D$2:$D$5, because a column of a table is a fixed block once the table is gone. #Data means the data rows without the header, #All includes the header. The patterns cover the forms Excel and Python scripts commonly write; references to the totals row (#Totals) or to other tables need their own rule — add it the same way, and fail loudly when a formula still contains a [ after rewriting.

Step 3 — Convert: rewrite every formula, then remove the table

Apply the rewriter to every formula on every sheet — references to a table can live anywhere in the workbook — then delete the table:

Python

def convert_table_to_range(wb, sheet, table_name):
    ws = wb[sheet]
    table = ws.tables[table_name]
    rewrite = a1_rewriter(table, ws)
    changed = 0
    for other in wb.worksheets:
        for row in other.iter_rows():
            for cell in row:
                if cell.data_type != "f":
                    continue
                uses_table = table_name.lower() in cell.value.lower()
                uses_shorthand = other is ws and "[@" in cell.value
                if uses_table or uses_shorthand:
                    new = rewrite(cell.value, cell.row)
                    if "[" in new:
                        raise ValueError(f"{other.title}!{cell.coordinate}: could not rewrite {cell.value}")
                    cell.value = new
                    changed += 1
    del ws.tables[table_name]
    return changed

wb = load_workbook("orders_table.xlsx")
print(convert_table_to_range(wb, "Orders", "Sales"), "formulas rewritten")
ws = wb["Orders"]
print(ws["D2"].value, ws["G1"].value, ws["G3"].value, "| tables left:", list(ws.tables))

The check for a remaining [ means a formula the rewriter did not understand stops the conversion with its address, instead of being left half-translated. Nothing has been saved or deleted at that point — the earlier rewrites exist only in memory — so the workbook can be inspected, the rule added, and the conversion run again from the same file.

Step 4 — Keep the look and the filter buttons

The banded rows and coloured header came from the table style, not from the cells, so they vanish with the table. If the range should still look the same, write the styling into the cells; if the filter buttons are wanted, add a plain AutoFilter:

Python

from openpyxl.styles import Font, PatternFill

header_fill = PatternFill("solid", fgColor="4F81BD")
band_fill = PatternFill("solid", fgColor="DCE6F1")
for c in ws[1][:4]:
    c.font, c.fill = Font(bold=True, color="FFFFFF"), header_fill
for r in range(2, 6):
    if r % 2 == 0:
        for c in ws[r][:4]:
            c.fill = band_fill
ws.auto_filter.ref = "A1:D5"
wb.save("orders_range.xlsx")
print("filter:", ws.auto_filter.ref)

Baked-in fills do not re-band when rows are sorted or inserted, which is one of the reasons tables exist; decide whether the plain range really needs to look banded. Add Alternating Row Colors with openpyxl shows a conditional-formatting alternative that does re-band.

Step 5 — Prove nothing changed

Recalculate the converted file and compare every result with the original:

Bash

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

Python

before, after = results("calc/orders_table.xlsx"), results("calc/orders_range.xlsx")
print("before:", before)
print("after: ", after)
print("identical:", before == after)

Amounts 12, 12.5, 8 and 35, a total of 67.5, a maximum of 35 and four lines — the same as with the table. A check like this is cheap and should be part of any script that rewrites formulas; Recalculate Excel Formulas Without Excel explains the LibreOffice step.

Performance notes

The conversion touches every formula cell in the workbook once, and each rewrite is a handful of regular-expression substitutions, so even workbooks with tens of thousands of formulas convert in a second or two. Loading and saving the workbook with openpyxl costs more than the rewriting. The one thing to avoid is running the conversion table by table with a save in between: load once, convert every table that needs it, and save once. Remember too that openpyxl drops charts and some other objects when it saves an existing workbook, so check What openpyxl Loses When Saving an Existing Workbook before converting a file that contains them.

Common pitfalls

Deleting the table without rewriting formulas. Every structured reference breaks.
Formulas on other sheets. Summary sheets refer to tables by name; scan the whole workbook.
[@Col] shorthand. It appears in formulas inside the table; translate it to the same-row cell.
Totals rows and special items. #Totals, #Headers and references to other tables need their own rules; stop on anything unrecognised.
Expecting the banding to survive. It belongs to the table style; write fills or conditional formats if needed.

Conclusion

Converting a table to a range is two jobs: rewriting structured references into A1 references everywhere in the workbook, and removing the table object with del ws.tables[name]. Translate [#This Row] and [@Col] to same-row cells and whole columns to absolute ranges, stop on anything unrecognised, write the header and band fills into the cells if the look matters, add a plain AutoFilter if the buttons are wanted, and recalculate before and after to prove every result is unchanged.

Frequently asked questions

How do I remove an Excel table with openpyxl? Delete it from the worksheet's tables dictionary: del ws.tables"TableName". The cells and their values stay; the table object, its style and its filter buttons go.

Why do my formulas show errors after removing the table? They use structured references such as TableColumn, which point at the table by name. Once the table is gone the name no longer exists. Rewrite them as ordinary cell references before deleting the table.

Does openpyxl have a Convert to Range function? No. openpyxl removes table objects but does not rewrite formulas. Translate structured references yourself, as shown on this page, then delete the table.

How do I keep the table's colours after converting? The colours come from the table style, not the cells. Write equivalent fills and fonts into the header and data cells, or use conditional formatting for banding so it adapts when rows change.

Up one level: Creating Excel Tables and Autofilters with Python — tables, filters and outlines.
Create an Excel Table with Python — the other direction.
Use Structured Table References in Formulas from Python — how the references this page removes are written.
Add an AutoFilter to an Excel Sheet with openpyxl — filter buttons without a table.

More on tables, filters & outlines