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