How to use cell references and defined names in criteria in Excel

Article translations Article translations
Article ID: 61090 - View products that this article applies to.
This article was previously published under Q61090
Expand all | Collapse all

SUMMARY

In Microsoft Excel, criteria can be set by typing the exact value that is desired in the criteria cells, or by using cell references or defined names.

To use the value of cell D1 as the criteria, type the following formula in the criteria cell:
   =$D$1
				
To use the value of a defined name, such as "CritVar", type the following formula in the criteria cell:
   =CritVar
				
To use the operators, such as less than (<) and greater than (>), the operator must be concatenated with the formula. For example, to specify a match of greater than the value in cell D1, type the following formula in the criteria cell:
   =">"&$D$1
				
To specify a match of greater than or equal to a cell defined as "GVAR", type the following formula in the criteria cell:
   =">="&GVAR
				

MORE INFORMATION

Quotation marks must be used. Otherwise, Excel interprets the information as "greater than "$D$1"" where "$D$1" is a text string. The same applies for a defined name.

Properties

Article ID: 61090 - Last Review: November 23, 2006 - Revision: 3.0
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
KB61090

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com