56 Excel
only at the night shift. A fi rm interested in salary data might want to con-
sider just the subset of those making between $55,000 and $85,000. Excel
allows you to specify the criteria for creating these subsets in the following
two ways:
Comparison criteria, which compares data values to specifi ed values or
constants
Calculated criteria, which compares data values to a calculated value
For the gas station data, an example of a comparison criterion would be one
that determines which service stations have gas sales exceeding $5,000. On
the other hand, a calculated criterion would be one that determines which
service stations have gas sales that exceed the average of gas sales of all sta-
tions in the data sample.
Once you have determined your criteria for creating a subset of the data
values, you select worksheet cells that fulfill these criteria by filtering
or querying the data. Excel provides two ways of fi ltering data. The fi rst
method, called the AutoFilter, is primarily used for simple queries employ-
ing comparison criteria. For more complicated queries and those involving
calculated values, Excel provides the Advanced Filter. You’ll have a chance
to use both methods in exploring the gas station data.Using the AutoFilter
Let’s say the service station company plans a massive advertising campaign
to boost sales for the service stations that are reporting gas sales of less than
$8,500. You can construct a simple query using comparison criteria to have
Excel display only service stations with gas sales <$8,500.To query the service station list:1 Click the Sort & Filter button from the Editing group on the Home
tab and then click Filter from the drop-down menu.
Excel adds drop-down arrows to each of the column titles in the
data list. By clicking these drop-down arrows you can fi lter the data
list on the basis of the values in the selected column.
2 Click the Gas drop-down arrow to display the shortcut menu. Click
Number Filters and then Less Than as shown in Figure 2-16.