How can we help you?
   Reference operators
  • Interface overview
  •    Screen elements
  •    Toolbar
  •    Quick actions box
  • General information
  • Files
  •    New file
  •    Save as template
  •    New from template
  •    Open a file
  •    Recent files
  •    Open with
  •    Send a file
  •    Print document without entering the application
  •    Preview and print
  •    Save a file
  •    Autorecovery of documents
  •    Close a file
  • Sheets
  •    Insert a sheet
  •    Rename a sheet
  •    Hide or unhide sheets
  •    Duplicate a sheet
  •    Change sheets order
  •    Zoom a sheet
  •    Hide or display zero values
  •    Delete a sheet
  • Cells, columns, and rows
  •    Expand the workspace
  •    Select cell ranges
  •    Resize cells
  •    Merge or unmerge cells
  •    Insert columns or rows
  •    Select colums or rows
  •    Hide or unhide a column or row
  •    Resize a column or row
  •    Freeze columns, rows, or area
  •    Group columns or rows
  •    Delete columns or rows
  • Data
  •    Enter data
  •    Sort and filter
  •    Data validation
  •    Remove duplicates
  •    Links
  •    Notes
  •    Special characters
  •    Current date or time
  •    Check spelling
  •    Find and replace
  •    Delete data
  • Number formats
  •    Identify cell format
  •    Open number format settings window
  •    General
  •    Number
  •    Currency
  •    Accounting
  •    Date, Time, and Date and Time
  •    Percentage
  •    Fraction
  •    Scientific
  •    Text
  • Formulas and functions
  •    Formulas and functions basics
  •    Order of operations in a formula
  •    Reference cells and cell ranges
  •    Recalculation of formulas
  •    Automatic calculation
  •    Enter functions
  •    Replace a formula with its result
  •    Copy and insert formulas
  •    Names in formulas and functions
  •    Reference to data in another document
  •    Structured references
  • Calculation operators
  •    Arithmetic operators
  •    Comparison operators
  •    Text concatenation operator 
  •    Reference operators
  • Tables
  •    Create a table
  •    Open the table settings pane
  •    Table customization
  •    Automatic table expansion
  •    Add a calculated column
  •    Select the type of calculation in the total row
  •    Convert a table to a regular range
  •    Delete a table
  • Pivot tables
  •    Create a pivot table
  •    Open the pivot table settings pane
  •    Customize a pivot table
  •    Customize the pivot table parameters
  •    Collapse or expand the elements of pivot table
  •    View cell details
  •    Refresh a pivot table
  •    Delete a pivot table
  • Charts
  •    Create a chart
  •    Chart settings
  •    Move a chart
  •    Copy, cut or insert a chart
  •    Change chart type
  •    Resize a chart
  •    Change data in chart
  •    Change chart data source
  •    Delete a chart
  • Images
  •    Insert an image
  •    Resize an image
  •    Move an image
  •    Save an image
  •    Delete an image
  • Shapes
  •    Insert a shape
  •    Fill a shape
  •    Outline
  •    Line style
  •    Resize a shape
  •    Fit shape size to text
  •    Change line length and angle
  •    Select arrow type
  •    Move a shape
  •    Delete a shape
  • Format
  •    Font
  •    Font size
  •    Font formatting
  •    Font сolor
  •    Highlight color
  •    Alignment
  •    Rotate text
  •    Wrap text
  •    Character spacing
  •    Cell background color
  •    Cell borders
  •    Copy and apply formatting
  • Edit content
  •    Undo and redo
  •    Clipboard
  • Automation tools
  •    Macros
  • Document protection
  •    Protect content from changes
  • Appendices
  •    Appendix A. List of functions
  •    Appendix B. List of supported currencies

Reference operators are used to describe references to cell ranges. It is possible to refer to cells in the current sheet as well as cells in other sheets.

Reference operator

Meaning

Example

:

A range operator.

Creates a reference to a range of cells. The operator is placed between the first and the last cell of the range.

The operator can be used to combine cell ranges. In this case it returns the range between the upper left and lower right cells, including these cells.

 

=SUM(В2:C4)

 

=SUM(A1:B1:C4:D4) – in this example, the sum of all cells between and including A1 and D4 is calculated.

Space

Intersection operator. Creates a reference to the cells located at the intersection of the specified ranges.

=SUM(A2:C4 B2:D4)

'sheetname'!

Creates a reference to a cell or range of cells in another sheet of the same spreadsheet.

=SUM(B2+'Sheet2'!В2)

=SUM(B2:C4+'Sheet2'!В2:C4)

'sheetname n:sheetname m'!

Creates a reference to the same cell on several sheets of the current spreadsheet (a 3D reference).

=SUM('January_2019:December_2019'!A1) – in this example, the sum of all A1 cells on all sheets within the specified range is calculated.

When entering formulas, you can select the cell range to avoid entering it manually.

sum_1        

Range operator

sum_2

Intersection operator

sum_3

Reference to a range of cells in another sheet

Was this helpful?
Yes
No
Previous
Text concatenation operator