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
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Collapse]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
      How to: Create a Simple Custom Function
      How to: Use Excel Add-Ins in the Spreadsheet Control
      How to: Create a Custom Function that Returns an Array
    [Expand]Import and Export Data
    [Expand]Data Binding
    [Expand]Mail Merge
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [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

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).

  • 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 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, 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.

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

  • 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

    When the R1C1 reference style is switched on in the workbook (DocumentSettings.R1C1ReferenceStyle), both rows and columns are numbered in a worksheet.

    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

    A relative reference to the cell one row down and two columns to the left of the cell that contains the formula.
    A relative reference to the cell in the same row and two columns to the right of the cell that contains the formula.
    A relative reference to the cell three rows up and in the same column of the cell that contains the formula.
    An absolute reference to the cell in the fifth row and fourth column.

    Show Me

    A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=E4655.

    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.

Expanded See Also

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