How to Install the Excel 2007 Analysis ToolPak
Adding Up with Excel 2007's SUM and SUMIF Functions
An Overview of Excel 2007's Database Functions

An Overview of Excel 2007's Reference Functions

The reference functions in Excel 2007, 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 but 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(reference) returns the number of columns in a reference.

  • HYPERLINK(link_location,[friendly_name]) creates a link that opens another document stored on your computer, network, or the Internet (you can also do this with the Insert→Hyperlink command). 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(reference) returns the number of rows in a cell range or array.

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

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Using Boolean Expressions in Data Analysis with Excel 2007
Depreciating Assets with Excel 2007's SLN, SYD, DB, and DDB Functions
Deconstructing Time with Excel 2007's HOUR, MINUTE, and SECOND Functions
Fixing Capitalization with Excel 2007's UPPER, LOWER, and PROPER Functions
Rounding Numbers in Excel 2007 with ROUND, ROUNDUP, and ROUNDDOWN