Learn more with dummies

Enter your email to join our mailing list for FREE content right to your inbox. Easy!

An Overview of Excel 2010’s Reference Functions

By Greg Harvey

The reference functions in Excel 2010, which are a part of the Lookup & Reference category, enable you to return specific information about particular cells or parts of the worksheet; create hyperlinks to different documents on your computer, network, or the Internet; and transpose ranges of vertical cells so that they run horizontally and vice versa.

This group of functions includes:

  • ADDRESS(row_num,column_num,[abs_num]) returns a cell reference as a text entry in a cell of the worksheet. The optional abs_num argument designates which type of reference you want the function to return:

    • 1 (or omitted) returns an absolute reference: $F$1.

    • 2 returns a reference with an absolute row and relative column: F$1.

    • 3 returns a reference with a relative row and absolute column: $F1.

    • 4 returns a relative reference: F1.

  • AREAS(reference) returns the number of areas in a list of values (areas are defined as a range of contiguous cells or a single cell in the cell reference).

  • COLUMN(reference) returns the number representing the column position of a cell reference.

  • COLUMNS(array) returns the number of columns in a reference.

  • HYPERLINK(link_location,[friendly_name]) creates a link that opens another document stored on your computer, a network, or the Internet (you can also do this with the Hyperlink button on the Insert tab). The optional friendly_name argument is the text that should be shown in the cell. If this argument is omitted, you will see the link_location.

  • INDIRECT(ref_text) returns a cell reference specified by a text string and brings the contents in the cell to which it refers to that cell.

  • ROW(reference) returns the row number of a cell reference.

  • ROWS(array) returns the number of rows in a cell range or array.

  • TRANSPOSE(array) returns a vertical array as a horizontal array and vice versa.