How to Work with Excel Named Range in Python

Sharing is caring!

Last Updated on July 14, 2022 by Jay

The named range is a common Excel feature and we’ll learn how to work with it using Python in this tutorial. Named range is kind of like assigning a variable name to a cell and it makes your Excel formulas more descriptive.

Library

We’ll use the openpyxl library for this demonstration. If you haven’t already, go ahead and use pip to install it:

pip install openpyxl

Accessing Excel Named Range Using Python

Our sample Excel file currently has three named ranges. See the screenshot below:

1. ‘apple_range’
2. ‘fruits’
3. and ‘private_range’ – Note this is a “private” range which means this named range is only accessible within the scope of the sheet “calc”

Let’s load the Excel file into Python, then check the workbook’s defined_names attribute. Note the first two names “apple_range” and “fruits” both have localSheetId=None that implies they are a global range.

from openpyxl import load_workbook

wb = openpyxl.load_workbook('Book1.xlsx')

wb.defined_names

<openpyxl.workbook.defined_name.DefinedNameList object>
Parameters:
definedName=[<openpyxl.workbook.defined_name.DefinedName object>

Parameters:
name='apple_range', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$C$3', <openpyxl.workbook.defined_name.DefinedName object>
Parameters:

name='fruits', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=None, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='Sheet1!$B$2:$D$8', <openpyxl.workbook.defined_name.DefinedName object>

Parameters:
name='private_range', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=1, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='calc!$C$6']

Excel Global vs Private Named Ranges

A global named range can be used anywhere (any sheet) inside the same workbook. Whereas a private named range can be used only on a specific sheet as defined by the scope. This concept of “scope” is similar to Python’s variable scope.

• A global named range:
  • Inside the Excel Name Manager, Scope = Workbook
  • As a defined_name object in Python, it has a parameter localSheetId=None
• A private named range:
  • Inside the Excel Name Manager, Scope = <name of a sheet>
  • As a defined_name object in Python, it has a parameter localSheetId=N, with N being the sheet id (1,2,3…)

Get Values from A Global Named Range

Let’s take a look at the “fruits” named range in Python. Both value and attr_text attributes return a reference to the range location, which is Sheet1!B2:D8.

wb.defined_names['fruits'].value
'Sheet1!$B$2:$D$8'

wb.defined_names['fruits'].attr_text
'Sheet1!$B$2:$D$8'

A defined_name also has a destinations attribute which is a generator of (worksheet name, cell range) tuples:

dest = wb.defined_names['fruits'].destinations
for title, coord in dest:
    print(title)
    print(coord)

Sheet1     #sheet name
$B$2:$D$8  #cell range

Now we can create a range (with sheet name and coordinates) from a defined name, then loop through the range as usual, i.e. rows and cells:

#create a range
for title,coord in dest:
    fruits = wb[title][coord]

for row in fruits:
    for cell in row:
        print(cell.value)
Date
Item
Sales
2022-03-01 00:00:00
Apple
100
2022-03-02 00:00:00
Banana
200
2022-03-03 00:00:00
Orange
300
2022-03-04 00:00:00
Watermelon
400
2022-03-05 00:00:00
Cherry
500
2022-03-06 00:00:00
Peach
600

Get Values From A Private Named Range

Note we can’t access a private named range as we did with the global named range. It shows that the private range name isn’t inside the defined_names list.

'private_range' not in wb.defined_names
True

This design is intentional because we want to prevent accidentally accessing a private named range.

To access a private named range, we need to use the get() method with an argument scope:

wb.sheetnames.index('calc')
1

wb.defined_names.get('private_range',scope = wb.sheetnames.index('calc'))
<openpyxl.workbook.defined_name.DefinedName object>
Parameters:
name='private_range', comment=None, customMenu=None, description=None, help=None, statusBar=None, localSheetId=1, hidden=None, function=None, vbProcedure=None, xlm=None, functionGroupId=None, shortcutKey=None, publishToServer=None, workbookParameter=None, attr_text='calc!$C$6'

Now we have access to the private named range, let’s access its value:

private_dest = wb.defined_names.get('private_range',scope = wb.sheetnames.index('calc')).destinations

for title,coord in private_dest:
    private_range = wb[title][coord]

private_range
<Cell 'calc'.C6>

private_range.value
"I'm private!"

Create An Excel Named Range Using Python

We can also create both global and private named ranges using Python. In Excel, we call this “Define Name”.

See below code, the only difference between creating a global and a private named range is adding the localSheetId=sheetid for constructing a private named range.

NOTE: we have to append() the newly created named ranges to the wb.defiend_names object in order to keep them!

from openpyxl.workbook.defined_name import DefinedName

## enter some value into cell "calc!A1"
## then create a global named range 
sheet = 'calc'
cell = 'A1'
wb[sheet][cell].value = 'hi from python'
python_global_range = DefinedName('python_global_range', attr_text=f'{sheet}!{cell}')
wb.defined_names.append(python_global_range)


## enter some value into cell "calc!A2"
## then create a private named range 
sheet = 'calc'
cell2 = 'A2'
sheetid = wb.sheetnames.index(sheet)
wb[sheet][cell2].value = 'python private'
python_private_range = DefinedName('python_private_range', attr_text=f'{sheet}!{cell2}', localSheetId=sheetid)
wb.defined_names.append(python_private_range)

wb.save('Book1.xlsx')

Additional Resources

How to Access Excel File In Python

How to Use Python to Read Excel Formula