Excel formulas are recalculated when the spreadsheet is opened. The cmdlet includes a formula engine that can natively calculate most of the commonly used Excel formulas. The cmdlet stores the state of a previous calculation in the spreadsheet and uses it to make efficient choices of when recalculation is necessary.

Recalculating Formulas

The Recalculate property allows you to control if formulas should be calculated. You should set Recalculate to True when you intend to read data from a spreadsheet that has formulas and you know that formula results might not be up to date. Once a value has been calculated for a cell, it is also updated into the spreadsheet. This can help avoid recalculations.

Calculating formula cells can add to the processing time. If a spreadsheet has not been modified since the last recalculation, you may choose to omit the calculation and simply read the previously calculated value in the formula cell. To skip recalculation, set Recalculate to False. You may also want to omit formula calculation when you are opening a spreadsheet just to insert or update cells and not reading anything from it.

Inserting Formulas

The cmdlet allows you to insert formulas into cells. If the AllowFormula connection string property is set to true, then anything you insert beginning with an equals sign ('=') will be treated as a formula. For example, the following will insert a formula into the B column that will sum cells B1:B5:

INSERT INTO Excel_Sheet (A, B) VALUES ('Bill', '=SUM(B1:B5)')