An Overview of Excel 2010's Reference Functions

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.

blog comments powered by Disqus
Advertisement

Inside Dummies.com