Formatting & presenting reports
Tables, filters & outlines
Excel tables, autofilters, sorting and grouped outline rows.
Start with the full guides
The long-form treatment of tables, filters & outlines, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toAdd a Total Row to an Excel Table with PythonGive an Excel table a real totals row from Python — the one readers can switch between Sum, Average and Count in Excel — with openpyxl's totalsRowCount and table column settings, SUBTOTAL formulas that respect filters, custom totals, the matching XlsxWriter options and how to keep the totals row out of pandas.
- How-toAdd an Autofilter to an Excel Sheet with openpyxlSet ws.auto_filter.ref so every data row is covered, freeze the header, apply it after pandas writes the sheet, and understand why recorded filter criteria hide nothing.
- How-toAdd Calculated Columns to an Excel Table with PythonAdd a calculated column to an existing Excel table with openpyxl: find the table, widen its range and autofilter, register the new column with a calculated-column formula so Excel fills it into rows added later, write the formula into every row with portable structured references, add a total, and verify the results by recalculating.
- How-toAppend Rows to an Excel Table and Resize It with openpyxlAdd new records to an existing Excel table so it grows the way it does when someone types below it: extend the table's range and filter, fill calculated columns with the correct structured-reference formula, carry number formats down, move the totals row, and extend dropdowns — so pivots, charts and formulas that use the table pick the rows up.
- How-toApply Autofilter Criteria and Hide the Filtered Rows with openpyxlopenpyxl can record autofilter criteria, but it never hides a row — the file opens showing everything under a filter that claims otherwise. Write the criteria and hide the non-matching rows yourself so the workbook opens already filtered, with SUBTOTAL totals that match what readers see.
- How-toChoose and Customise Excel Table Styles with PythonPick the right look for an Excel table from Python: the 60 built-in styles and how their names work, the four banding and emphasis switches, styles in openpyxl and XlsxWriter, a custom company table style written with openpyxl, why typos in style names pass silently, and why LibreOffice and PDF conversions show tables unstyled unless you add direct formatting.
- How-toConvert an Excel Table Back to a Normal RangeDo 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.
- How-toConvert Several Ranges into Excel Tables at OnceTurn every block of data in a workbook into a proper Excel table in one pass with openpyxl: find the separate rectangular blocks on each sheet, check and repair their header rows so Excel will accept them, give each table a unique valid name from its sheet and heading, skip blocks that are too small or already tables, and verify that nothing overlaps.
- How-toCreate an Excel Table with PythonBuild a real Excel table with openpyxl — Table and TableStyleInfo, header rules that prevent the repair dialog, structured references, extending the range, and reading existing tables.
- How-toGroup and Outline Rows in Excel with openpyxlAdd collapsible groups to a generated report: row and column outlines, nested levels, collapsed-by-default state, summary rows below or above, and subtotal formulas.
- How-toRead an Excel Table by Name into pandaspandas has no option to read an Excel table by its name, but openpyxl knows every table's exact range. Find a table anywhere in the workbook, skip its totals row, get formula results, and load it into a DataFrame.
- How-toRename and Restyle Existing Excel TablesGive the Table1, Table2 and Table3 in an existing workbook meaningful names with openpyxl without breaking anything: rename the table object and its key, rewrite every structured reference to it in cell formulas, defined names, data validation and conditional formatting, refuse names that clash, then apply one table style across the workbook — checked by recalculating the formulas in LibreOffice before and after.
- How-toSort Excel Rows with Python Before Writingopenpyxl cannot sort a worksheet — order the data in pandas first. Multi-key sorts, custom category orders, natural sorting of codes, stable ties and where blanks end up.
- How-toApply Conditional Formatting to an Excel Table That GrowsKeep highlighting working when a report table gets new rows every week: why conditional formatting ranges do not follow rows appended by Python, re-pointing every rule at the resized table with openpyxl, generous ranges as a simpler alternative and their cost, why structured references are not allowed in rule formulas, and a check that every rule covers every data row after an append.
- How-toSort and Filter an Excel Online Table with Microsoft GraphSort and filter an Excel table in OneDrive or SharePoint from Python with Microsoft Graph: apply multi-column sorts, filter a column by values, custom criteria or top items, read back only the visible rows, clear filters again, and decide when it is better to read the rows and filter in pandas instead of changing the shared workbook.
- How-toWrite an Excel Table with Formula Columns Using XlsxWriterWrite a pandas DataFrame as a real Excel table with XlsxWriter, including calculated columns that use structured references, a totals row, column formats and a table style: get the range right, choose between the [@Column] shorthand and fully qualified references that also work outside Excel, add custom totals, and verify the formulas by recalculating.
- How-toMake an Excel Chart Grow with Its DataA chart points at fixed cell ranges, so rows appended by a script fall outside it. See the problem in a saved file, extend every series of an existing chart to the new last row with openpyxl, rebuild the chart from the data's current size instead, let Excel tables carry the chart when rows are added inside Excel, and know why dynamic named ranges are brittle in generated files.
- How-toUse Structured Table References in Excel Formulas from PythonWrite formulas that refer to Excel table columns by name — =SUM(Sales[Total]), calculated columns, SUMIFS over table columns — from openpyxl and xlsxwriter, including the [@Column] shorthand that must be written out in full.
Follow it through a track
Prefer an ordered route? These tracks cover tables, filters & outlines along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Tables, filters & outlines is part of Formatting & presenting reports.