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
   [Collapse]Cell Basics
     Cell Data Types
     Dates and Times in Cells
     Error Types
     Cell Referencing
     Formatting Cells
   [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
   [Expand]Visual Elements
    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]ASP.NET Core 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]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

Cell Referencing

A cell reference is a set of coordinates that specify the position of a cell or cell range on a worksheet. Use cell references in formulas to obtain and process data contained in the corresponding cells. The results of formulas that use cell references are automatically updated each time the values of these cells are changed.

SpreadsheetControl supports the following cell reference types.

Expanded A1 Cell References

The A1 cell reference style is used by default. The A1 cell reference is a combination of column and row headings to which the cell belongs - a column letter is followed by a row number. By default, column and row headings are displayed at the top and at the left of a worksheet (see How to: Show and Hide Row and Column Headings).

The following table provides examples of A1 references to a single cell, and various cell ranges (including references to entire rows and columns).

Cell Reference

Description

E7 Refers to a single cell located at the intersection of column E and row 7.
A2:B5 Refers to a range that includes cells from the top left cell A2 to the bottom right cell B5.
B:B Refers to a range that includes the entire column B.
B:H Refers to a range that includes the entire columns from B to H.
5:5 Refers to a range that includes the entire row 5.
5:10 Refers to a range that includes the entire rows from 5 to 10.

When used in a formula, a cell reference can be relative, absolute or mixed, depending on whether or not it should be automatically adjusted when the formula is copied.

  • Relative Cell References

    A relative cell reference in a formula is based on the relative position of a referenced cell and a cell containing a formula. To keep this relative position unchanged, the cell reference is automatically changed each time you copy a formula to another cell. For example, if you copy a formula with a relative reference to cell B2 from cell C3 to D6, the reference will automatically change from B2 to C5.

  • Absolute Cell References

    An absolute cell reference in a formula always refers to a specific cell, and it does not change if the formula is copied or moved to another cell. In absolute cell references, the column letter and row number are preceded by the '$' sign. For example, if you copy a formula with an absolute reference to cell B2 from cell C3 to D6, the reference will remain unchanged ($B$2).

  • Mixed Cell References

    A mixed cell reference in a formula can either be combined from an absolute reference to a cell column and a relative reference to a cell row (for example, $B2), or from a relative reference to a cell column and an absolute reference to a cell row (for example, A$1). If the formula is copied, the absolute element of the mixed reference (the column letter or row number preceded by the '$' sign) will remain unchanged, and the relative element of the reference will automatically be adjusted.

    For example, if you copy a formula with the $C3 mixed reference from cell B2 to D6, the reference will change to $C7.

Expanded R1C1 Cell References

You can also use the R1C1 reference style, where both rows and columns are numbered in a worksheet. To switch on this style in a workbook, use the DocumentSettings.R1C1ReferenceStyle property. A cell reference of the R1C1 style is a combination of "R" followed by the row number and "C" followed by the column number.

Cell Reference

Description

R5C7 Refers to a single cell located at the intersection of row 5 and column 7.
R3C4:R7C10 Refers to a range that includes cells from the top left cell R3C4 to the bottom right cell R7C10.
C4 Refers to a range that includes the entire column 4.
C5:C7 Refers to a range that includes the entire columns from 5 to 7.
R10 Refers to a range that includes the entire row 10.
R12:R15 Refers to a range that includes the entire rows from 12 to 15.

Like the A1 cell reference style, the R1C1 cell reference style also supports the relative, absolute and mixed reference types.

  • Relative Cell References

    In relative R1C1 cell references, the row and column numbers are enclosed in square brackets. Positive numbers refer to cells below and/or to the right, relative to a cell containing a formula. Negative numbers refer to cells above and/or to the left, relative to the cell containing the formula.

    If an R1C1 cell reference does not include a row or column number, it refers to the cell in the same row or column that contains the cell with the formula.

    When you copy a formula with relative cell references in the R1C1 style, the notation of these cell references remains the same in copied formulas, while the A1 relative references are changed for each copied formula to keep the relative position of the referenced cell and the cell containing the formula.

  • Absolute Cell References

    In absolute R1C1 cell references, row and column numbers are used without brackets.

  • Mixed Cell References

    An R1C1 cell reference can either be combined from an absolute reference to a cell row and a relative reference to a cell column (for example, R10C[-5]), or from a relative reference to a cell row and an absolute reference to a cell column (for example, R[3]C5).

Note

If the R1C1 cell reference style is switched on in a workbook, A1 references cannot be used in formulas, and vice-versa. The style of cell references used in formulas is automatically adjusted when you change the active cell reference style in the workbook.

Expanded Cross-Worksheet Cell References

In formulas, you can use references to cells located in other worksheets. To do this, specify the worksheet name before the cell reference, and separate them by an exclamation point (!).

When using a worksheet name in a cell reference, enclose it in single quotation marks (') in the following cases.

  • The worksheet name contains a character other than a letter or number (spaces, parentheses, braces, etc.).
  • The worksheet name starts with neither a letter nor an underscore symbol ("_").
  • The worksheet name is the same as an A1 cell reference ("A1", "$M$15", etc.).
  • The worksheet name starts with an R1C1 cell reference notation.

If you rename a worksheet, this worksheet name is automatically updated in all cell references where it is used.

Expanded 3D Cell References

3D references allow you to process data contained in the same cells on multiple worksheets within a workbook. To create a 3D reference, specify the range of worksheet names before the cell (or cell range) reference, and separate them by an exclamation point (!).

For example, the formula in the following image sums all values in cells B2 through C3 located on worksheets Sheet2, Sheet3, Sheet4 and Sheet5. If you insert other worksheets between Sheet2 and Sheet5 (for example, by creating new worksheets, or by duplicating or moving existing worksheets), the B2:C3 cell ranges of the added worksheets will be included into the calculation. If you remove worksheets from the Sheet2:Sheet5 worksheet range, the values of these worksheets are excluded from the calculation.

Expanded External Cell References

A reference that refers to a cell, cell range or defined name in another workbook is called an external reference. In order to use external references, all referenced workbooks should be included in the IWorkbook.ExternalWorkbooks collection of the workbook that contains the external references.

  • An external reference includes the entire path to the workbook file, the workbook file name in square brackets ([ ]), the worksheet name, an exclamation point (!), and the cell reference.

    This example demonstrates how to programmatically create an external reference to the B3 cell in the Book1.xlsx workbook loaded into a non-visual spreadsheet component instance (the Workbook object). To use a non-visual spreadsheet component, add a reference to the DevExpress.Docs.v17.2.dll library.

  • If you set the current file name of the source workbook (the WorkbookSaveOptions.CurrentFileName property), you can create external references to its cells without specifying the entire path to this workbook file. In this case, an external reference includes the specified file name of the source workbook in square brackets ([ ]), the worksheet name, an exclamation point (!) and the cell reference.

    This example demonstrates how to create an external reference in a workbook loaded into one SpreadsheetControl instance to cells located in another workbook in a second spreadsheet control.

  • An external reference to a defined name in another workbook includes the source workbook name, an exclamation point (!) and the defined name. For example, =book.xlsx!range_name.

    If the scope of the defined name is a worksheet in another workbook, specify this worksheet name in an external reference. For example, =[book.xlsx]Sheet3!range_name.

Expanded Structured Cell References

Structured references allow you to refer to tables and different ranges within tables. A structured reference has the following syntax.

=TableName[[#Data],[ColumnName]]

  • TableName is a table name (Table.Name).

  • [#Data] is a special item specifier that refers to a data range of a table or table column (as in the current example). You can also use the following special item specifiers in structured references, to refer to specific parts of tables or table columns: [#All], [#Headers], [#Totals] and [#This Row].

  • [ColumnName] is a table column specifier (TableColumn.Name). If it is not preceded by any special item specifier, the table column specifier refers to the column data range (excluding the column header and total cells).

    To combine column specifiers in structured references, you can use the following operators: colon - to refer to a range of two or more adjacent columns, comma - to refer to a combination of two or more columns, and space - to refer to an intersection of two or more columns.

The images below illustrate examples of structured references using different special item specifiers and reference operators.

Expanded See Also

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