Using Excel Formulas

Using Excel Formulas

The add-in has different features depending on formula categories:

  • Formulas selected as a text from views and stored procedures.
  • Column formulas added by users.
  • Cell formulas added by users.

In the first case, the add-in converts text values to Excel formulas, if possible.

For example, a view can contain a column with a formula like '=[@Price]*[@Qty]' to calculate the row sum in Microsoft Excel.

Specify English version formulas and the comma as a parameter separator to use formulas in any localized version of Microsoft Excel.

Developers can disable converting formulas using the DoNotConvertFormulas handler or force converting using the ConvertFormulas handler.

Loading text values into the column can be visible for users. To fix this, use an empty string part in the cell format like "0;-0;;".

In the second case, by default, column formulas exist in the user workbooks only.

Users can use the Table Format Wizard or the Save Table Format and Restore Table Format menu items to save and restore such formula columns from a database.

So, users can share formulas with colleagues themselves.

In the third case, the add-in has a solution starting version 8.15.

The add-in saves and restores cell formulas during the refresh phase automatically.

The option is enabled by default. Users can disable it in the Options dialog box.

Developers can disable it for any database object using the DoNotKeepFormulas handler with the NULL value in the COLUMN_NAME column.

Also, developers can specify the column to hold formulas using the KeepFormulas handler.

In this case, other users that reload data will have the same cell formulas.