Working with Excel Formulas in Python: A Practical Guide

Python Work with Excel Formulas

If you’ve ever automated Excel reports or processed spreadsheet data in Python, you know that formulas are essential. Whether you’re building financial models, generating monthly reports, or analyzing datasets, knowing how to work with formulas programmatically can save you hours of manual work.

I’ve been using Python to handle Excel files for years, and I’ve picked up some useful patterns along the way. Today, I’m sharing what I’ve learned about working with Excel formulas in Python — hopefully it helps someone out there facing similar challenges.

Environment Setup

To handle Excel formulas with Python, this article uses Spire.XLS for Python, a library specifically designed for Excel file manipulation in Python. If you’re already comfortable with openpyxl or pandas, those work too, but Spire.XLS offers some convenient features for formula handling.

Installation

Install via pip:

pip install spire.xls

1. Getting Started: Writing Basic Formulas into Excel

Let’s start with the fundamentals — writing simple formulas to cells.

from spire.xls import *

# Create a workbook
workbook = Workbook()
# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add some test data
sheet.Range["B2"].NumberValue = 7.3
sheet.Range["C2"].NumberValue = 5
sheet.Range["D2"].NumberValue = 8.2

# Write a SUM formula
sheet.Range["E2"].Formula = "=SUM(B2:D2)"
# Write an AVERAGE formula
sheet.Range["F2"].Formula = "=AVERAGE(B2:D2)"

# Save the file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
# Clean up resources
workbook.Dispose()
print("File created successfully!")

Quick tip: If you want to display the formula as text instead of calculating it, set the cell’s number format to text first:

# Display formula as text
sheet.Range["A2"].NumberFormat = "@"
sheet.Range["A2"].Text = "=SUM(B2:D2)"

2. Reading Formulas from Existing Excel Files

Sometimes you need to inspect formulas in existing Excel files — maybe for auditing purposes or to understand how a template works.

from spire.xls import *

# Load an existing file
workbook = Workbook()
workbook.LoadFromFile("existing.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
# Read the formula from a cell
formula = sheet.Range["C14"].Formula
print(f"Formula: {formula}")

# Get the calculated value
value = sheet.Range["C14"].FormulaNumberValue
print(f"Calculated value: {value}")

workbook.Dispose()

This is super handy when you’re debugging complex spreadsheets or validating formula logic.

3. Cross-Sheet References

Real-world reports often pull data from multiple sheets. Here’s how to reference cells across worksheets:

# Reference a specific cell from Sheet1
sheet.Range["A1"].Formula = "=Sheet1!$B$3"
# Reference a range for averaging
sheet.Range["A2"].Formula = "=AVERAGE(Sheet1!$D$3:G$3)"

Important: Pay attention to absolute references (with $) vs relative references. This matters when you copy formulas around.

4. Date and Time Functions

Working with dates and times? These functions will come in handy:

# Current date and time
sheet.Range["A1"].Formula = "=NOW()"
sheet.Range["A1"].Style.NumberFormat = "yyyy-MM-DD HH:mm:ss"

# Extract year, month, day
sheet.Range["B1"].Formula = "=YEAR(TODAY())"
sheet.Range["C1"].Formula = "=MONTH(TODAY())"
sheet.Range["D1"].Formula = "=DAY(TODAY())"

# Extract hour, minute, second
sheet.Range["E1"].Formula = "=HOUR(NOW())"
sheet.Range["F1"].Formula = "=MINUTE(NOW())"
sheet.Range["G1"].Formula = "=SECOND(NOW())"

# Day of the week
sheet.Range["H1"].Formula = "=WEEKDAY(TODAY())"

Watch out: NOW() recalculates every time you open the file. If you need a fixed timestamp, calculate it once and convert to a static value.

5. Mathematical and Statistical Functions

Beyond basic SUM and AVERAGE, here are some commonly used math functions:

# Maximum and minimum
sheet.Range["A1"].Formula = "=MAX(10,30,50)"
sheet.Range["A2"].Formula = "=MIN(5,7,3)"
# Rounding
sheet.Range["A3"].Formula = "=ROUND(3.14159, 2)" # Result: 3.14
# Integer truncation
sheet.Range["A4"].Formula = "=INT(9.8)" # Result: 9
# Absolute value
sheet.Range["A5"].Formula = "=ABS(-15.6)" # Result: 15.6
# Square root
sheet.Range["A6"].Formula = "=SQRT(144)" # Result: 12
# Random number (0 to 1)
sheet.Range["A7"].Formula = "=RAND()"

6. Logical Functions

Conditional logic is everywhere in reports:

# IF function
sheet.Range["A1"].Formula = '=IF(B1>60, "Pass", "Fail")'
# AND, OR
sheet.Range["A2"].Formula = "=AND(B1>60, C1>60)"
sheet.Range["A3"].Formula = "=OR(B1>90, C1>90)"
# NOT
sheet.Range["A4"].Formula = "=NOT(TRUE)" # Result: FALSE

Real example: Sometimes I use nested IF statements for grade classification:

sheet.Range["A1"].Formula = '=IF(A1>=90, "Excellent", IF(A1>=80, "Good", IF(A1>=60, "Pass", "Fail")))'

7. Text Functions

String manipulation also requires formulas:

# String length
sheet.Range["A1"].Formula = '=LEN("Hello World")' # Result: 11
# Extract substring
sheet.Range["A2"].Formula = '=MID("Hello World", 7, 5)' # Result: World
# Type conversion
sheet.Range["A3"].Formula = '=VALUE("123")' # Convert text to number

8. Array Formulas (Advanced)

Array formulas perform calculations on multiple values simultaneously — perfect for complex data analysis:

from spire.xls import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Prepare data
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["A3"].NumberValue = 3
sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5
sheet.Range["B3"].NumberValue = 6

# Set array formula (linear regression)
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:B3,TRUE,TRUE)"

# Calculate all formulas
workbook.CalculateAllValue()

workbook.SaveToFile("array_formulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

When to use: Financial modeling, statistical analysis, or any scenario requiring matrix operations.

9. SUBTOTAL Function (Ignore Hidden Rows)

When filtering data, regular SUM includes hidden rows. Use SUBTOTAL to count only visible cells:

# SUBTOTAL with different function codes
sheet.Range["A5"].Formula = "=SUBTOTAL(1, A1:C3)" # AVERAGE
sheet.Range["B5"].Formula = "=SUBTOTAL(2, A1:C3)" # COUNT
sheet.Range["C5"].Formula = "=SUBTOTAL(5, A1:C3)" # MIN
# Calculate
workbook.CalculateAllValue()

Use case: Interactive dashboards where users filter data dynamically.

10. Named Ranges with Formulas

Named ranges make formulas more readable and easier to maintain:

from spire.xls import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set values
sheet.Range["A1"].Value = "1"
sheet.Range["A2"].Value = "1"

# Create a named range
named_range = workbook.NameRanges.Add("NewNamedRange")
named_range.RefersToRange = sheet.Range["A1:A2"]

# Use the named range in a formula
sheet.Range["C1"].Formula = "=SUM(NewNamedRange)"

workbook.SaveToFile("named_range.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Benefit: When your data range changes, update the named range definition once instead of modifying every formula.

11. Removing Formulas While Keeping Values

Here’s a common requirement: send a report to clients without exposing your calculation logic. Convert formulas to static values:

from spire.xls import *

# Load workbook with formulas
workbook = Workbook()
workbook.LoadFromFile("with_formulas.xlsx")

# Loop through all worksheets
for sheet in workbook.Worksheets:
  # Loop through all cells
  for cell in sheet.Range:
    # Check if cell has a formula
    if cell.HasFormula:
      # Get the calculated value
      value = cell.FormulaValue
      # Clear the formula
      cell.Clear(ExcelClearOptions.ClearContent)
      # Set as static value
      cell.Value2 = value

# Save without formulas
workbook.SaveToFile("without_formulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Scenarios: Financial reports, published statistics, or protecting proprietary calculations.

12. R1C1 Reference Style

Besides the standard A1 notation, Excel supports R1C1 referencing (row + column numbers):

from spire.xls import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Set up data
sheet.Range["A1"].NumberValue = 1
sheet.Range["A2"].NumberValue = 2
sheet.Range["B1"].NumberValue = 4
sheet.Range["B2"].NumberValue = 5

# R1C1 formula: reference relative positions
sheet.Range["C2"].R1C1Formula = "=R[-1]C[-1]+R[0]C[-1]"

workbook.SaveToFile("r1c1_formula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Why use it: R1C1 makes it easier to generate formulas programmatically using coordinate calculations, especially in loops.

13. Comprehensive Example: Monthly Sales Report

Let’s put everything together with a real-world example — generating a monthly sales report:

from spire.xls import *
import random

# Create workbook
workbook = Workbook()
sheet = workbook.Worksheets[0]

# 1. Add headers
headers = ["Month", "Revenue", "Cost", "Profit", "Profit Margin"]
for col, header in enumerate(headers, 1):
    sheet.Range[1, col].Value = header

# Bold the header row
sheet.Range[1, 1][1, len(headers)].Style.Font.IsBold = True

# 2. Generate data with formulas
for month in range(1, 13):
    row = month + 1
    
    # Month name
    sheet.Range[row, 1].Value = f"{month} Month"
    
    # Random revenue and cost
    revenue = random.uniform(50000, 150000)
    cost = random.uniform(30000, 90000)
    
    sheet.Range[row, 2].NumberValue = revenue
    sheet.Range[row, 3].NumberValue = cost
    
    # Profit formula: Revenue - Cost
    sheet.Range[row, 4].Formula = f"=B{row}-C{row}"
    
    # Profit margin formula: Profit / Revenue
    sheet.Range[row, 5].Formula = f"=D{row}/B{row}"
    sheet.Range[row, 5].Style.NumberFormat = "0.00%"

# 3. Add summary row
summary_row = 14
sheet.Range[summary_row, 1].Value = "Total"

# Sum formulas
sheet.Range[summary_row, 2].Formula = "=SUM(B2:B13)"
sheet.Range[summary_row, 3].Formula = "=SUM(C2:C13)"
sheet.Range[summary_row, 4].Formula = "=SUM(D2:D13)"

# Average profit margin
sheet.Range[summary_row, 5].Formula = "=AVERAGE(E2:E13)"
sheet.Range[summary_row, 5].Style.NumberFormat = "0.00%"

# Bold the summary row
sheet.Range[summary_row, 1][summary_row, 5].Style.Font.IsBold = True

# 4. Auto-fit columns
sheet.AllocatedRange.AutoFitColumns()

# Save
workbook.SaveToFile("monthly_sales_report.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

print("Monthly sales report generated!")

And there you have it — a complete, dynamic report where all calculations happen through formulas. Change the source data, and everything updates automatically.

14. Common Pitfalls and Best Practices

After working with Excel formulas in Python for a while, here are some lessons learned:

Get Alice Yang’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

✅ Do:

• Always call Dispose() to release resources
• Use CalculateAllValue() after setting array formulas
• Test formulas manually in Excel before implementing in code
• Use named ranges for better maintainability
• Add error handling for file operations

❌ Don’t:

• Don’t forget number formatting for dates and percentages
• Don’t mix relative and absolute references carelessly
• Don’t hardcode cell references in loops — use variables
• Don’t assume formulas calculate immediately — some need explicit calculation

💡 Pro Tips:

• Keep a reference Excel file to compare results
• Document complex formula logic in comments
• Consider using templates for recurring report formats
• Validate formula results against expected values

Conclusion

In this article, we’ve covered how to handle formulas in Excel using Python, including:

1. Basic formula writing with Formula property
2. Reading formulas from existing files
3. Cross-sheet references using standard Excel syntax
4. Date/time, math, logical, and text functions
5. Array formulas for advanced calculations
6. SUBTOTAL for filtered data scenarios
7. Named ranges for maintainable formulas
8. Converting formulas to values for distribution
9. R1C1 notation for programmatic formula generation
10. Complete real-world example with a sales report

Key takeaway: Understand your business requirements first, then choose the right formula approach. Not every scenario needs complex formulas — sometimes simple SUM and IF statements do the job perfectly.

Hope this guide helps you work more efficiently with Excel formulas in Python! Feel free to share your own tips and tricks in the comments.