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 openpyxlSample 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