## Set formula

### Set formula by editing cell

By enter a value with an equal-sign prefix (“=”) to set the formula into cell.

### Set formula by programming

To set cell formula in .NET programs, use the one of following methods:

// method 1: access cell data directly worksheet["E3"] = "=(A3+B3)*C3"; // method 2: via cell instance var cell = worksheet.Cells["E3"]; cell.Formula = "(A3+B3)*C3"; // do not need equal-sign (=)

## Get cell formula

To get the formula from cell:

// method 1: via worksheet method var formula = worksheet.GetCellFormula("C1"); // method 2: via cell instance var cell = worksheet.Cells["C1"]; var formula = cell.Formula;

## Check whether a cell has formula

To check whether or not a cell has formula and its value is calculated from formula, use the following property of cell instance:

bool hasFormula = cell.HasFormula;

## Get formula result

After finish of cell editing, formula will be parsed and the cell value will be calculated immediately.

### Get formula result by programming

Use one of following methods to get the calculation result of formula.

// method 1: access cell data directly object value = worksheet["E3"]; // method 2: via cell instance var cell = worksheet.Cells["E3"]; object value = cell.Data;

## Types of formula value

ReoGrid always use .NET native type as the formula value type. If cell value is number, .NET double type will be used; if value is string, .NET string type will be used. For example:

worksheet["E3"] = "=(A3+B3)*C3"; // object value = worksheet["E3"]; // OK, type is double System.Diagnostics.Debug.Assert(worksheet["E3"] is double); // true

The following .NET types will be used in formula calculation:

- double
- string
- bool
- DateTime

Integer, long, short and float will be converted to double type; string and `StringBuilder`

will be converted to string. For example:

worksheet["A1"] = "=10"; // 10 will be treated as double type object val = worksheet["A1"]; MessageBox.Show(val.GetType().Name); // result is 'Double'

## Prevent cell text considered as formula

Cell text that starts with single quote (‘) will be ignored to parse as formula:

worksheet["A1"] = "'=10"; // cell data is '=10'

## Cell Reference

It is same as Excel, ReoGrid supports two types of address:

**A1**format: Relative address**$A$1**format: Absolute address

Cell or range address used to reference value from other cells:

Example formula that uses a relative reference to another cell:

Result:

## Range Reference

The format StartCell:EndCell used to describe an address to reference a range:

Result:

## Get formula reference list

ReoGrid creates and manages the reference relations between cells in memory, to get the reference list of a cell formula, use `GetCellFormulaReferenceRanges`

method:

worksheet["H8"] = "=A1+B1-SUM(A1:C3)+AVERAGE(D1:H5)"; // Get reference list from cell var rangeList = worksheet.GetCellFormulaReferenceRanges("H8"); // Assert information from reference list AssertTrue(rangeList != null); AssertEquals(rangeList[0].Range, new RangePosition("A1")); AssertEquals(rangeList[1].Range, new RangePosition("B1")); AssertEquals(rangeList[2].Range, new RangePosition("A1:C3")); AssertEquals(rangeList[3].Range, new RangePosition("D1:H5"));

## Remove formula from range

Method `ClearRangeContent`

of worksheet could be used to delete formula from specified range.

var sheet = grid.CurrentWorksheet; sheet.ClearRangeContent("A1:B5", CellElementFlags.Formula);

## Trace Precedents and Dependents

Trace formula precedents and dependents for cells available from 0.8.6.

To enable trace precedents, set `TraceFormulaPrecedent`

property of cell to true:

var sheet = workbook.CurrentWorksheet; sheet["C2"] = 10; sheet["C3"] = 5; ReoGridCell cell = sheet.Cells["C5"]; cell.Formula = "C2+C3"; cell.TraceFormulaPrecedents = true;

Or enable trace by call `TraceCellPrecedents`

method of worksheet:

sheet.TraceCellPrecedents("C5");

Once formula changed, the trace arrows will disappear. To always enable trace formula use the following code:

sheet.CellDataChanged += (s, e) => e.Cell.TraceFormulaPrecedents = true;

## Named Range Reference

About how to define and remove a named range, see Named Range.

To reference a named range in formula, use its name directly, for example:

sheet.DefineNamedRange("myrange", "A1:B5"); sheet.Cells["E10"] = "=SUM(myrange)";

## Built-in Excel-compatible functions

From version 0.8.8.5, ReoGrid supports more than 50 commonly used Excel functions, see List of Excel-compatible functions.

## Automatic reference cell updating

When a cell’s value is changed, all cells that reference to this cell will also be recalculated. ReoGrid performs this behavior by default. To disable this behavior, see the sector ‘Pause and resume automatic reference cell updating’ below.

Learn more about reference cell updating.

### Pause and resume automatic reference cell updating

When a cell that references to another cell, it will be updated when the depended cell is updated. By default, this behavior will be performed automatically. To disable this behavior, use the following method:

sheet.SuspendFormulaReferenceUpdates();

To resume it:

sheet.ResumeFormulaReferenceUpdates();

Or by changing the settings of worksheet to do the same:

// disable reference updating sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, false);

// enable reference updating sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, true);

## Force to recalculate entire worksheet

Sometimes it is necessary to recalculate the entire worksheet, especially when the automatic formula reference updating is disabled.

sheet.Recalculate();

## Custom Function

To add customize function, see the next topic Customize Function.

Hi Jing!

First of all, I would like to thank you for ReoGrid. It’s very impressive and I found it to be the best alternative to Interop, so bravo!

So I opened a xslx file with ReoGrid through a SSIS script task, and I would like to know how I can update the formula of certain cells when I add a column.

For example, cell H2 has for formula “=A2*C2”. I inserted a column between A and B so the previous value of C2 is now at D2. However, the formula in H2 stays the same after I saved the file.

I tried to set Formula_AutoUpdateReferenceCell at true, but it didn’t help. Do you have any idea? Thank you for your time!

Hi Anthony,

I have the same problem. Did you find a solution?

Regards…

Formula:

=SUM(Sheet2!A1:A10)

Why couldn’t calculated the result

When I’m in a cell and I hit = and then move the cursor, it doesn’t pick up the cell addresses automatically as I move. Is there a way to emulate that behavior?

ReoGrid doesn’t support this feature currently. It is on the development plan but we can’t provide you the specific time that it get be supported.