You will find the following pointers helpful to remember when you are working with databases and records.
Watch Out for Spaces. Excel is very tolerant regarding capitalization rules. However, Excel behaves differently with spaces at the end of an entry. If you don’t find any records, the filter might contain an unintended space. A space can cause problems in a record or criteria. If you are sure there are data matching the criteria but no data is returned, use a question mark or an asterisk as a wildcard to select data with trailing blanks, or use the TRIM() and CLEAN() functions (described in Chapter 8) to clean the data.
Include All Records with or without Content. To include all records with a field entry in a calculation, use < > as the criterion for the field. For example, if you use this string for the product, all records with an entry in the product field are included.
To include all records with an empty field, specify = as the criterion. In other words, just type an equal sign and then press the Enter key.
Calculated Criteria. To specify multiple conditions, add a further row to the criteria range specifying the additional conditions. You can also specify calculated criteria. You cannot use field names as labels for calculated criteria.
Overview of Operators and Wildcards. The operators and wildcards are explained in Table 13-1 and Table 13-2.
Table 13-1. Overview of the Operators
Operator | Example | Returns All Records in the Selected Column... |
|---|---|---|
> (greater than) | > 5000 | ... greater than 5,000. |
< (less than) | < 5000 | ... less than 5,000. |
>= (greater than or equal to) | >= 5000 | ... greater than or equal to 5,000. |
<= (less than or equal to) | <= 5000 | ... less than or equal to 5,000. |
< > (unequal) | < > 5000 | ... different from 5,000. |
= (equals) | = 5000 | ... exactly 5,000. You don’t have to enter the equal sign in search criteria. |
Table 13-2. Overview of the Wildcards
Wildcard | Example | Returns All Records in the Selected Column... |
|---|---|---|
* (any number of characters) | D* | ... starting with D. |
=* (any number of characters in combination with an exact string) | =”=*and” | ... starting with one or more characters and ending in and, such as land or wand. Candy is not displayed because it has another character after and. |
? (a single character) | ?and | ... starting with any character and followed by and, such as candy. Stand is not displayed because it has other characters before and. |
=? (single character in combination with an exact string) | =”=?and” | ... starting with any character and ending in and, such as land or wand. Stand and candy are not displayed. |
If you use wildcards in combination with an exact string, you have to enclose the search term in quotation marks because Excel expects a formula after the equal sign (=) unless the equal sign is followed by a string in quotation marks. You also need to enter the equal sign, otherwise Excel would look for the string “?and”.