What is a cell reference

A cell reference in Excel refers to the value of a different cell or cell range on the current worksheet or a different worksheet within the spreadsheet. A cell reference can be used as a variable in a formula.

A simple cell reference

The simplest type of reference appears as a simple mention of the referred cell after an equal sign. For example (=C5) refers to the value within cell C5. It means that the value of the current cell is equal to the value of C5.

Reference to a cell range

The notation (=A1:C6) refers to cell range A1 through C6. Independently it doesn’t mean anything and Excel will return standard error #VALUE! You can find more information about standard Excel errors in our Excel Guideline for Professionals: Don’t ignore Excel errors!

Cell reference in a function

But when the reference to a range is used in a function, the magic happens. For example =SUM(A1:C6) will return the total value of the cell range A1 through C6 and =AVERAGE(A1:C6) returns the average of this cell range.

Example 1

Example 2

Cell references and range issues

Larger Excel files are usually stuffed with formulas based on cell ranges. When spreadsheets are edited or expanded over time, different types of range issues may occur. These issues are usually hard to detect, but they can be very risky! We’ve seen big numbers and large sums of money ‘disappear’ because of range issues.

Certain risks concerning cell references in spreadsheets occur frequently. Like references to empty cells, references to merged cells (it is not obvious how to correctly refer to the merged cells) or when a referred cell range is expected to be longer or shorter, because the actual table in the spreadsheet is. PerfectXL detects these risks and provides the user with suggestions to fix the problem.