Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Collapse]Visual Elements
     Workbook
     Worksheet
    [Expand]Cells and Cell Ranges
     Rows and Columns
     Formulas
     Name Box
     Pictures
    [Expand]Dialogs
    Services
   [Expand]Shortcuts
   [Expand]Examples
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Formulas

The SpreadsheetControl's built-in UI, the Formula Bar control, and the Formulas Ribbon tab commands that you can optionally add to your spreadsheet application allow end-users to easily manage formulas in their documents. For an example on how to create a formula bar and provide the Ribbon UI for the SpreadsheetControl, see the Getting Started topic.

Expanded Formula Bar

The Formula Bar is a visual control (SpreadsheetFormulaBarControl) that accompanies the SpreadsheetControl. The formula bar is usually located above the worksheet area. It allows end-users to view, enter and edit data values and formulas contained in worksheet cells as well as a cell's in-place editor. The formula bar is bound to the SpreadsheetControl via the SpreadsheetFormulaBarControl.SpreadsheetControl property. To add a formula bar to your spreadsheet application, you can drag-and-drop the corresponding item from the DX.17.1: Spreadsheet toolbox tab or select Create FormulaBar in the SpreadsheetControl's smart tag menu (see the Getting Started example).

By default, the formula bar contains three buttons, each of which has a default action, as described in the table below. When an end-user clicks a particular button, a corresponding event is fired. Handle these events to override the default behavior or perform additional actions when buttons are clicked, if needed. To perform the action associated with a particular button in code, use the SpreadsheetFormulaBarControl.ExecuteResourceNavigatorAction method.

Button

Description

Event

Cancel Cancels the user input. SpreadsheetFormulaBarControl.CancelButtonClick
Enter Completes the cell entry and calculates the formula, if one has been entered. SpreadsheetFormulaBarControl.OkButtonClick
Insert Function Invokes the Insert Function or Function Arguments dialog to insert a function from the Function Library into a formula, or specify the function arguments. SpreadsheetFormulaBarControl.InsertFunctionButtonClick

Expanded Enter Formulas

A formula is a string expression that starts with an equal sign ("="). It can contain constants, operators, cell references, calls to functions, and names. An end-user can specify a cell formula in the cell's in-place editor or the formula bar, and use the following tools to enter different formula elements.

  • Cell References

    The SpreadsheetControl allows end-users to insert cell references in formulas by selecting the desired cell ranges directly in the worksheet when editing a formula in a cell's in-place editor or formula bar. The cell ranges referenced in a formula are highlighted in different colors when an end-user edits the formula.

  • Defined Names

    To insert a defined name into a formula, an end-user can type the name or select it from the Use in Formula list on the Formulas Ribbon tab in the Defined Names group.

  • Functions

    The SpreadsheetControl supports a variety of functions to be used in formulas. An end-user can insert a function in a formula in one of the following ways.

    • Type the function name and enter its arguments in parentheses.
    • Select the desired function from the Function Library group on the Formulas Ribbon tab. Frequently used functions such as Sum, Average, Count Numbers, Max and Min are also listed under the AutoSum button on the Home tab in the Editing group.

    • Insert a function using the Insert Function dialog and specify the function parameters using the Function Arguments dialog.

Expanded Display Formulas in Cells

An end-user can specify whether cells should show formulas or calculated values. The Show Formulas button on the Formulas tab switches this behavior.

Expanded Options

The Calculation group on the Formulas tab contains buttons allowing an end-user to specify how formulas should be recalculated.

There are two available modes of formula calculation (the Calculation Options sub-menu).

  • Automatic - All dependent formulas are recalculated each time a cell value, formula, or name is changed. This is the default mode.

  • Manual - Formulas are recalculated only when an end-user explicitly requests it.

When the Manual recalculation mode is activated, an end-user can manually recalculate formulas throughout the entire workbook (Calculate Now) or in the active worksheet only (Calculate Sheet).

Expanded See Also

How would you rate this topic?​​​​​​​