use Python exist Excel Automation formulas and functions

In modern data processing work, Excel formulas and functions are core tools for performing calculations, data analysis, and business logic. However, manually entering and managing large numbers of formulas is time-consuming and error-prone. By automating the insertion and management of Excel formulas in Python, developers can batch process spreadsheets, dynamically generate calculation logic, and ensure the accuracy and consistency of data processing.

This article explains how to use Python to insert various types of formulas and functions into Excel worksheets, including basic arithmetic operations, built-in functions, array formulas, and the use of named ranges. These technologies are suitable for scenarios such as financial statement automation, data analysis pipeline construction, and batch data processing.

Environmental preparation

First, you need to install the Spire.XLS for Python library:

pip install Spire.XLS

The library provides a complete Excel file operation API, supporting functions such as formula insertion, calculation execution, and file format conversion.

Insert basic formula

Excel formulas begin with the equal sign (=) and can contain constants, cell references, operators, and function calls. When inserting a formula using Python, just assign the formula string to the cell's Formula property.

The following example demonstrates how to insert several basic formulas into a worksheet:

from spire.xls import *
from spire.xls.common import *

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

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

# Insert string formula
sheet.Range["A4"].Text = '="hello"'
sheet.Range["B4"].Formula = '="hello"'

# Insert arithmetic formulas
sheet.Range["A5"].Text = "=1+2+3+4+5-6-7+8-9"
sheet.Range["B5"].Formula = "=1+2+3+4+5-6-7+8-9"

# insert multiplication
sheet.Range["A6"].Text = "=33*3/4-2+10"
sheet.Range["B6"].Formula = "=33*3/4-2+10"

# Reference other cells
sheet.Range["A7"].Text = "=Sheet1!$B$2"
sheet.Range["B7"].Formula = "=Sheet1!$B$2"

# Reference a range of cells and calculate the average
sheet.Range["A8"].Text = "=AVERAGE(Sheet1!$B$2:$D$2)"
sheet.Range["B8"].Formula = "=AVERAGE(Sheet1!$B$2:$D$2)"

# save file
workbook.SaveToFile("BasicFormulas.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

In this example we show:

  • String constant: Use double quotes to wrap text
  • Arithmetic operations:Support addition, subtraction, multiplication, division and bracket priority
  • cell reference:use $ Symbol creates absolute reference
  • zone reference: Use colons to specify a continuous range of cells

Use built-in functions

Excel provides hundreds of built-in functions covering areas such as mathematics, statistics, date and time, logical judgment, and text processing. Python provides the flexibility to plug in these functions.

Mathematical and statistical functions

from spire.xls import *
from spire.xls.common import *

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

currentRow = 1

# COUNT Function - count the number of parameters
sheet.Range[f"A{currentRow}"].Text = "=Count(3,5,8,10,2,34)"
sheet.Range[f"B{currentRow}"].Formula = "=Count(3,5,8,10,2,34)"
currentRow += 1

# SUM Function - Sum
sheet.Range[f"A{currentRow}"].Text = "=SUM(18,29)"
sheet.Range[f"B{currentRow}"].Formula = "=SUM(18,29)"
currentRow += 1

# AVERAGE Function - average
sheet.Range[f"A{currentRow}"].Text = "=AVERAGE(12,45)"
sheet.Range[f"B{currentRow}"].Formula = "=AVERAGE(12,45)"
currentRow += 1

# MAX and MIN function
sheet.Range[f"A{currentRow}"].Text = "=MAX(10,30)"
sheet.Range[f"B{currentRow}"].Formula = "=MAX(10,30)"
currentRow += 1

sheet.Range[f"A{currentRow}"].Text = "=MIN(5,7)"
sheet.Range[f"B{currentRow}"].Formula = "=MIN(5,7)"
currentRow += 1

# ROUND Function - Rounding
sheet.Range[f"A{currentRow}"].Text = "=ROUND(7,3)"
sheet.Range[f"B{currentRow}"].Formula = "=ROUND(7,3)"
currentRow += 1

# SQRT Function - square root
sheet.Range[f"A{currentRow}"].Text = "=SQRT(40)"
sheet.Range[f"B{currentRow}"].Formula = "=SQRT(40)"
currentRow += 1

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

Date and time functions

Processing date and time data is a common need for Excel automation. The following code shows how to use datetime functions:

from spire.xls import *
from spire.xls.common import *

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

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

# DATE Function - construct date
sheet.Range["A2"].Text = "=DATE(2024,1,15)"
sheet.Range["B2"].Formula = "=DATE(2024,1,15)"
sheet.Range["B2"].Style.NumberFormat = "yyyy-MM-DD"

# TIME Function - Construction time
sheet.Range["A3"].Text = "=TIME(14,30,0)"
sheet.Range["B3"].Formula = "=TIME(14,30,0)"
sheet.Range["B3"].Style.NumberFormat = "HH:mm:ss"

# YEAR, MONTH, DAY Function - Extract date part
sheet.Range["A4"].Text = "=YEAR(NOW())"
sheet.Range["B4"].Formula = "=YEAR(NOW())"

sheet.Range["A5"].Text = "=MONTH(NOW())"
sheet.Range["B5"].Formula = "=MONTH(NOW())"

sheet.Range["A6"].Text = "=DAY(NOW())"
sheet.Range["B6"].Formula = "=DAY(NOW())"

# HOUR, MINUTE, SECOND Function - Extract time part
sheet.Range["A7"].Text = "=HOUR(NOW())"
sheet.Range["B7"].Formula = "=HOUR(NOW())"

sheet.Range["A8"].Text = "=MINUTE(NOW())"
sheet.Range["B8"].Formula = "=MINUTE(NOW())"

sheet.Range["A9"].Text = "=SECOND(NOW())"
sheet.Range["B9"].Formula = "=SECOND(NOW())"

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

Logical and conditional functions

Logical functions are used to implement conditional judgments and business rules:

from spire.xls import *
from spire.xls.common import *

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

# IF Function - conditional judgment
sheet.Range["A1"].Text = "=IF(10>5, \"greater than\", \"less than\")"
sheet.Range["B1"].Formula = "=IF(10>5, \"greater than\", \"less than\")"

# AND Function - Logical AND
sheet.Range["A2"].Text = "=AND(TRUE, TRUE)"
sheet.Range["B2"].Formula = "=AND(TRUE, TRUE)"

# OR function - logical OR
sheet.Range["A3"].Text = "=OR(FALSE, TRUE)"
sheet.Range["B3"].Formula = "=OR(FALSE, TRUE)"

# NOT Function - Logical NOT
sheet.Range["A4"].Text = "=NOT(FALSE)"
sheet.Range["B4"].Formula = "=NOT(FALSE)"

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

Text processing functions

Text functions are used for string manipulation and data cleaning:

from spire.xls import *
from spire.xls.common import *

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

# LEN Function - string length
sheet.Range["A1"].Text = '=LEN("Hello World")'
sheet.Range["B1"].Formula = '=LEN("Hello World")'

# MID Function - extract substring
sheet.Range["A2"].Text = '=MID("Hello World",7,5)'
sheet.Range["B2"].Formula = '=MID("Hello World",7,5)'

# VALUE Function - Convert text to value
sheet.Range["A3"].Text = '=VALUE("123")'
sheet.Range["B3"].Formula = '=VALUE("123")'

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

Use array formulas

Array formulas can perform calculations on multiple values ​​and return a single result or multiple results. This is useful when performing matrix operations and batch data processing.

from spire.xls import *
from spire.xls.common 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

sheet.Range["C1"].NumberValue = 7
sheet.Range["C2"].NumberValue = 8
sheet.Range["C3"].NumberValue = 9

# Insert array formula - LINEST Function performs linear regression analysis
sheet.Range["A5:C6"].FormulaArray = "=LINEST(A1:A3,B1:C3,TRUE,TRUE)"

# Calculate the value of all formulas
workbook.CalculateAllValue()

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

Key points explained:

  • use FormulaArray attribute rather than Formula to insert array formula
  • Array formula needs to span multiple cell ranges
  • call CalculateAllValue() method to ensure that the formula is calculated correctly

Using the SUBTOTAL function

The SUBTOTAL function performs aggregate calculations on a list of data and can ignore hidden rows, which is useful when creating interactive reports.

from spire.xls import *
from spire.xls.common import *

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

# Prepare data
for row in range(1, 4):
    sheet.Range[f"A{row}"].NumberValue = row
    sheet.Range[f"B{row}"].NumberValue = row + 3
    sheet.Range[f"C{row}"].NumberValue = row + 6

# insert different SUBTOTAL function
# 1 express AVERAGE
sheet.Range["A5"].Formula = "=SUBTOTAL(1,A1:C3)"

# 2 express COUNT
sheet.Range["B5"].Formula = "=SUBTOTAL(2,A1:C3)"

# 5 express MIN
sheet.Range["C5"].Formula = "=SUBTOTAL(5,A1:C3)"

# Calculation formula
workbook.CalculateAllValue()

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

The first parameter of the SUBTOTAL function specifies the calculation type:

  • 1: AVERAGE
  • 2: COUNT
  • 3: COUNTA (non-null count)
  • 4: MAX (maximum value)
  • 5: MIN (minimum value)
  • 9: SUM (summation)

Use named ranges and formulas

Named ranges can improve the readability and maintainability of formulas. Make formulas easier to understand by replacing complex cell references with meaningful names.

from spire.xls import *
from spire.xls.common import *

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

# Set data
sheet.Range["A1"].Value = "10"
sheet.Range["A2"].Value = "20"

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

# Use named ranges in formulas
sheet.Range["C1"].Formula = "=SUM(SumRange)"

# You can also directly define the naming formula
namedFormula = workbook.NameRanges.Add("TotalCalc")
namedFormula.NameLocal = "=SUM(A1+A2)"
sheet.Range["C2"].Formula = "TotalCalc"

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

The advantages of this approach are:

  • Improve readability: =SUM(SumRange) Compare =SUM(A1:A2) clearer
  • Easy to maintain: Modifying a reference to a named range automatically updates all formulas using that name
  • Reduce errors: Avoid manually entering complex cell references

Practical Tips

Format formula cells

To distinguish formula cells from data cells, special formatting can be applied:

from spire.xls import *
from spire.xls.common import *

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

# Insert formula
sheet.Range["A1"].Formula = "=SUM(B1:B10)"

# Set formula cell background color
sheet.Range["A1"].Style.Color = Color.get_LightYellow()

# Add border
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin
sheet.Range["A1"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin

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

Force calculation of formula value

In some cases, it may be necessary to obtain the result of a formula immediately rather than retaining the formula itself:

from spire.xls import *
from spire.xls.common import *

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

sheet.Range["A1"].NumberValue = 10
sheet.Range["A2"].NumberValue = 20
sheet.Range["A3"].Formula = "=A1+A2"

# Calculate all formulas
workbook.CalculateAllValue()

# at this time A3 The value of is already 30
result = sheet.Range["A3"].Value
print(f"Calculation result: {result}")

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

Handle cross-sheet references

When a formula needs to reference data from other worksheets, use the syntax of the worksheet name followed by an exclamation point:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()

# Create two worksheets
sheet1 = workbook.Worksheets[0]
sheet1.Name = "Data"
sheet2 = workbook.Worksheets.Add("Calculation")

# Set data in first worksheet
sheet1.Range["A1"].NumberValue = 100
sheet1.Range["A2"].NumberValue = 200

# Reference data from the first worksheet in the second worksheet
sheet2.Range["A1"].Formula = "=Data!A1+Data!A2"

workbook.CalculateAllValue()
workbook.SaveToFile("CrossSheetFormula.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Summarize

This article covers a variety of techniques for automating formulas and functions in Excel using Python, including:

  • Insert basic arithmetic formulas and cell references
  • Use mathematical, statistical, datetime, logical, and text processing functions
  • Perform array formulas for complex calculations
  • Create flexible aggregate calculations with the SUBTOTAL function
  • Improve formula readability and maintainability with named ranges

These technologies enable developers to:

  • Batch generate spreadsheets containing complex calculation logic
  • Automated financial modeling and data analysis reporting
  • Dynamically build condition-based calculation formulas
  • Improve consistency and accuracy in data processing

By combining the programming capabilities of Python and the calculation capabilities of Excel, you can build powerful data processing automation solutions that significantly increase work efficiency and reduce human errors.