[Expand]General Information
[Expand]WinForms Controls
[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
[Collapse]Office File API
  Demo Application
  Redistribution and Deployment
 [Collapse]Spreadsheet Document API
   Product Structure
   Getting Started
  [Expand]Spreadsheet Document
   Supported Formats
  [Expand]Cell Basics
  [Expand]Formulas
   Defined Names
   Data Binding
  [Expand]Pivot Tables
  [Expand]Charts and Graphics
  [Expand]Mail Merge
  [Collapse]Examples
   [Expand]Files
   [Expand]Workbooks
   [Expand]Worksheets
   [Expand]Rows and Columns
   [Expand]Cells
   [Collapse]Formulas
     How to: Use Constants and Calculation Operators in Formulas
     How to: Use Cell and Worksheet References in Formulas
     How to: Use Names in Formulas
     How to: Create Named Formulas
     How to: Use Functions and Nested Functions in Formulas
     How to: Create Shared Formulas
     How to: Create Array Formulas
   [Expand]Import and Export Data
   [Expand]Data Binding
    Sorting
   [Expand]Mail Merge
    Search
   [Expand]Shapes
   [Expand]Charts
   [Expand]Sparklines
   [Expand]Formatting Cells
   [Expand]Conditional Formatting
   [Expand]Group Data
   [Expand]Filter Data
   [Expand]Tables
   [Expand]Pivot Tables
   [Expand]Printing
   [Expand]Pictures
   [Expand]Protection
 [Expand]Word Processing Document API
 [Expand]PDF Document API
 [Expand]Excel Export Library
 [Expand]Snap Report API
 [Expand]Zip Compression and Archive API
 [Expand]Barcode Generation API
 [Expand]Unit Conversion API
 [Expand]API Reference
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

How to: Use Cell and Worksheet References in Formulas

In addition to constants, a formula can contain references to other cells or cell ranges in the same worksheet or other worksheets. The following reference styles are supported.

 

  • A1 References

    A cell reference of the A1 style is a combination of column and row headings to which the cell belongs - a column letter is followed by a row number. For example, C10 refers to the cell that is located at the intersection of column C and row 10. 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).

  • Cross-Worksheet References

    In formulas, you can also 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 (!).

  • Relative 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 or move a formula to another cell. For example, if you copy a formula with a relative reference to cell A1 from cell B2 to C3, the reference will automatically change from A1 to B2.

  • Absolute 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 A1 from cell C3 to D4, the reference will remain unchanged ($A$1).

  • Mixed 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 or moved, 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 $B2 mixed reference from cell E5 to F6, the reference will change to $B3.

  • 3D 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 (!).

  • R1C1 References

    This example demonstrates how to create formulas using the R1C1 reference style. To do this, switch on the DocumentSettings.R1C1ReferenceStyle option and assign a formula string containing R1C1 cell references to the Range.Formula property. To obtain a cell reference in the R1C1 reference style, you can use the Range.GetReferenceR1C1 method.

Is this topic helpful?​​​​​​​