How to create named ranges and tables in Excel with openpyxl

Working with Python code becomes significantly more manageable when objects are assigned clear, sensible names, and Excel is no different. This blog post covers how to create named ranges and tables in Excel using openpyxl.

To begin this demonstration, we will import several modules from openpyxl, along with a sample of rows from the penguins dataset available in the seaborn package.

import openpyxl
from openpyxl.worksheet.table import Table, TableStyleInfo
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook
wb = Workbook()

import seaborn as sns
penguins = sns.load_dataset('penguins')
penguins = penguins.sample(frac=.05, random_state=1234)
penguins.head()

	species	island	bill_length_mm	bill_depth_mm	flipper_length_mm	body_mass_g	sex
64	Adelie	Biscoe	36.4	17.1	184.0	2850.0	Female
237	Gentoo	Biscoe	49.2	15.2	221.0	6300.0	Male
115	Adelie	Biscoe	42.7	18.3	196.0	4075.0	Male
97	Adelie	Dream	40.3	18.5	196.0	4350.0	Male
37	Adelie	Dream	42.2	18.5	180.0	3550.0	Female

Creating named ranges with openpyxl

The first task is to compile a list of all unique values in the penguins dataset’s species column and export them to an Excel range. If your Python source data is stored as a list, this is straightforward.

The code, penguins['species'].unique().tolist(), retrieves the unique species values and converts them from a numpy array to a list:

species = penguins['species'].unique().tolist()
species

['Adelie', 'Gentoo', 'Chinstrap']

Next, we’ll transfer this data to Excel. Create a worksheet named ws1 and add the species list using ws1.append(species). Following that, define a range in openpyxl with specific code to name the new range in Excel and identify the cells it encompasses:

ws1 = wb.create_sheet('species')
ws1.append(species)

new_range = openpyxl.workbook.defined_name.DefinedName('species', attr_text='species!$A$1:$C$1')
wb.defined_names.append(new_range)

With the range defined in openpyxl, it’s time to add it to the workbook using wb.defined_names.append(new_range).

Creating named tables with openpyxl

Named ranges are incredibly useful for managing one-dimensional data, such as lists.

However, for datasets that include rows, columns, and headers, storing your data in an Excel table is more appropriate. Fortunately, creating tables is also possible with openpyxl.

First, create a new worksheet named penguins. To transfer the penguins DataFrame to this worksheet, use a loop with the dataframe_to_rows() function.

ws2 = wb.create_sheet('penguins')

for r in dataframe_to_rows(penguins, index=False, header=True):
    ws2.append(r)

I’ve chosen to omit the index column and include the header row by setting the index to False and the header to True.

After adding the DataFrame to the worksheet, like with the range, you must define a table object and designate the cell range it covers. Tables offer style options, so begin by setting the style with TableStyleInfo(name='TableStyleMedium9', showRowStripes=True). There are numerous ways to style a table, so feel free to explore and consult the documentation for additional information.

style = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True)

Next, define the table with the table function, naming it “penguins” in Excel and specifying its cell range. This can be achieved with precise openpyxl references to include as many active rows and columns as present in the worksheet.

table = Table(displayName="penguins", 
              ref="A1:" + get_column_letter(ws2.max_column) + str(ws2.max_row))

Once the table is defined, apply the chosen style to it.

table.tableStyleInfo = style

Finally, add the table to the penguins worksheet with ws2.add_table(table) and save the workbook as ranges-tables.xlsx:

ws2.add_table(table)
wb.save('ranges-tables.xlsx')

Open the resulting workbook in Excel, navigate to Formulas on the home ribbon, and open the Name Manager. You’ll find the table and range ready for use..

If you haven’t been utilizing ranges and tables in Excel, the ability to quickly create them via openpyxl should provide ample motivation. This approach lays the groundwork for combining pandas with openpyxl to produce comprehensive analyses and reports.

What questions do you have about working with named ranges, tables, or other Excel entities using openpyxl? Feel free to share your inquiries in the comments. Additionally, you might find my LinkedIn Learning course helpful for an introduction to automating Excel tasks with Python.