How to Use Python to Read Excel Formula

Sharing is caring!

Last Updated on March 10, 2022 by Jay

Sometimes we want to use Python to read Excel formula instead of the values, and the openpyxl library offers a way to do that easily.

Why do we need to read formulas? Because we might want to copy the formula to another place while keeping the column and row unchanged, or we want to tweak the formula but we first need to know what it is.

Library

openpyxl is a third-party Python library to work with Excel files. We can download it using pip. Open up a command prompt, then type the following. If you already have pandas, it’s likely openpyxl is already installed.

pip install openpyxl

Sample Excel file

We are going to use a simple Excel file for this illustration so no download is required. See screenshot below:

Python Read Excel Formula From Spreadsheet

There are three formulas on the <formula> tab in cell B2, B4, and D2. Let’s use openpyxl to read the Excel sheet into Python:

import openpyxl
wb = openpyxl.load_workbook('Book1.xlsx')
ws = wb['formula']


ws['B2'].data_type
'f'

ws['B2'].value
'=PI()'

Checking cell B2’s data_type, we get ‘f’ which means this cell contains a formula. Note the .value attribute returns the actual Excel formula, which is ‘=PI()’. Also note the formula cell “value” is actually a String data type in Python.

This is nice, but what if our spreadsheet has many formulas and we want actual values instead?

Python Read Excel FIle As Value-Only

The openpyxl.load_work() method has an optional argument data_only that is set to False by default. We can turn this on by setting it to True, then we’ll get values only instead of formulas.

Note the below code, this time the data_type shows ‘n’ which means a number. And indeed we got the value of Pi, which is 3.14159…

import openpyxl
wb = openpyxl.load_workbook('Book1.xlsx', data_only = True)
ws = wb['formula']


ws['B2'].data_type
'n'

ws['B2'].value
3.141592653589793

Additional Resources

How to Access Excel File In Python

How to Work with Excel Named Range in Python

Leave a Reply

Your email address will not be published. Required fields are marked *