You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article was previously published under Q61090
SUMMARY
In Microsoft Excel, criteria can be set by typing the exact value thatis desired in the criteria cells, or by using cell references ordefined names.

To use the value of cell D1 as the criteria, type the followingformula in the criteria cell:
   =$D$1				
To use the value of a defined name, such as "CritVar", type thefollowing formula in the criteria cell:
   =CritVar				
To use the operators, such as less than (<) and greater than (>), theoperator must be concatenated with the formula. For example, tospecify a match of greater than the value in cell D1, type thefollowing 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 theinformation as "greater than "$D$1"" where "$D$1" is a text string.The same applies for a defined name.
noupd Excel XL2002 XL2003 XL2000 XL97 XL2007
Properties

Article ID: 61090 - Last Review: 11/23/2006 00:59:36 - Revision: 3.0

Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition, Microsoft Excel 97 Standard Edition

  • KB61090
Feedback