Skip to main content

Set Formula

Sets a formula in a specific cell or range of cells in an Excel worksheet. The formula is evaluated automatically by Excel.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.

Input​

  • Client Id - The client ID from the Connect node (optional if using direct credentials).
  • Workbook Id - The workbook ID from Get Workbook or Create Workbook node.
  • Worksheet Name - Name of the worksheet. Default is "Sheet1".
  • Cell/Range Address - Cell or range address to set formula in (e.g., A1, C1:C10).
  • Formula - The Excel formula to set. Should start with = (e.g., =SUM(A1:A10)).

Options​

  • Credentials - OAuth2 credentials (optional, alternative to using Client ID).

Output​

  • Success - True if the formula was set successfully.
  • Cell Address - The cell or range address where the formula was set.

Examples​

Set Simple Formula:

// Input
Cell/Range Address: "D10"
Formula: "=SUM(D1:D9)"

// Sets a SUM formula in D10

Set Formula with References:

// Input
Cell/Range Address: "E5"
Formula: "=A5*B5+C5"

// Creates a calculation formula

Set Array Formula:

// Input
Cell/Range Address: "F1:F10"
Formula: "=A1:A10*2"

// Applies formula to range

Create Calculated Column:

Get Workbook
  → Set Formula (C1: "=A1+B1")
  → Set Formula (C2: "=A2+B2")
  → ... repeat for each row

Formula Syntax​

Use standard Excel formula syntax:

  • Start with = sign: =SUM(A1:A10)
  • Reference cells: =A1+B1
  • Use functions: =IF(A1>100,"High","Low")
  • Combine operations: =SUM(A1:A10)/COUNT(A1:A10)

Use Cases​

  • Create calculated columns
  • Add summary formulas (SUM, AVERAGE, etc.)
  • Implement business logic in Excel
  • Create dynamic calculations
  • Automate formula insertion

Tips​

  • Always start formulas with =
  • Excel automatically evaluates formulas after setting
  • Use absolute references ($A$1) when needed
  • For multiple formulas, consider using a loop with Set Formula
  • Test formulas in Excel first to ensure correct syntax

Common Errors​

ErrInvalidArg - "Cell/Range address cannot be empty"

  • Solution: Provide a valid cell or range address

ErrInvalidArg - "Formula cannot be empty"

  • Solution: Provide a valid Excel formula starting with =

ErrGraph - "Invalid formula"

  • Solution: Check formula syntax matches Excel standards

ErrInvalidArg - "Workbook Id cannot be empty"

  • Solution: Ensure you've called Get Workbook first