How to Edit Formulas in a Worksheet

by Chaknith Bin

An Excel formula is an expression starting with an equal sign (=) used for mathematical calculations, data manipulation, and result derivation based on cell values. It may contain arithmetic, functions, cell references, constants, and logical operations. Formulas enable dynamic updates as cell values change, making Excel a versatile tool for automating tasks and data analysis.

IronXL supports editing existing formulas in an Excel file, retrieving the result from the formula, and forcing the workbook to be reevaluated. This ensures that each formula will be recalculated for accurate results. IronXL supports over 165 formulas.

Install with NuGet

Install-Package IronXL.Excel

nuget.org/packages/IronXL.Excel/

or

Download DLL

Download DLL

Manually install into your project

Edit Formulas Example

To edit or set the formula, access the Formula property. First, select a Range or Cell, and then access and set the Formula property. The Formula property is both a get and set property that returns the formula string, if any. Invoke the EvaluateAll method to reevaluate the entire workbook, ensuring accurate calculations.

:path=/static-assets/excel/content-code-examples/how-to/edit-formulas-edit-formulas.cs

using IronXL;

// Load workbook
WorkBook workBook = WorkBook.Load("Book1.xlsx");

// Select worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Edit or Set formula
workSheet["A4"].Formula = "=SUM(A1,A3)";

// Reevaluate the entire workbook
workBook.EvaluateAll();

Imports IronXL

' Load workbook
Private workBook As WorkBook = WorkBook.Load("Book1.xlsx")

' Select worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Edit or Set formula
Private workSheet("A4").Formula = "=SUM(A1,A3)"

' Reevaluate the entire workbook
workBook.EvaluateAll()

Retrieve Result from Formula

Although it is sometimes applicable to retrieve the result from the Value property of the selected Range and Cell, it is recommended to retrieve the result from the FormattedCellValue property of the Cell for a more accurate result from the formula. In the selected Range, you can access the Cell by invoking the First method. This method will select the first element of the list, which, in our case, is the Cell "A4." From there, you can access the FormattedCellValue property.

:path=/static-assets/excel/content-code-examples/how-to/edit-formulas-retrieve-formula-value.cs

using IronXL;
using System;
using System.Linq;

// Load workbook
WorkBook workBook = WorkBook.Load("Book1.xlsx");

// Select worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Retrieve the result value
string value = workSheet["A4"].First().FormattedCellValue;

// Print the result to console
Console.WriteLine(value);

Imports IronXL
Imports System
Imports System.Linq

' Load workbook
Private workBook As WorkBook = WorkBook.Load("Book1.xlsx")

' Select worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Retrieve the result value
Private value As String = workSheet("A4").First().FormattedCellValue

' Print the result to console
Console.WriteLine(value)

Supported Formula

Excel contains 450+ formulas that can be used to calculate for a variety of purposes. IronXL supports around 165 of the most commonly used formulas. Please check the following for the supported formulas: