Excel: Using Cell References and Defined Names in Criteria

Last reviewed: November 2, 1994
Article ID: Q61090

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 and/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.

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.