How to Ignore Formulas and Retrieve Actual Cell Values Using Openpyxl

When working with Excel spreadsheets that contain formulas, retrieving the underlying cell values can be challenging. Openpyxl, a popular Python library for reading and writing Excel files, allows you to access cell values without the computed formula results.

Issue: Formula Instead of Actual Value

One common issue when using Openpyxl is encountering the computed formula instead of the actual cell value. This can occur when the data_only parameter is set to True, which typically retrieves the final values after formula calculations.

Solution: data_only=True with Specific Load Option

To resolve this issue, the data_only flag can be used in conjunction with a specific loading option:

wb = openpyxl.load_workbook(filename, data_only=True)

Here, the load_workbook method loads the Excel file while setting data_only to True. This combination ensures that formulas are ignored and the actual cell values are retrieved directly.

Conclusion

By setting data_only=True in the load_workbook method, you can retrieve the raw cell values without any formula calculations, providing accurate representation of the data in your Excel spreadsheets using Openpyxl.