Microsoft Office Tutorials and References
In Depth Information
Overview
two functions require that a criteria area (range O1:Q3) be set up to tell Excel what items
to average and count. Icon sets will be used to provide a visual means of identifying grades
based on a conditional formatting rule. Finally, the SUMIF and COUNTIF functions will
be used to sum selectively the sales of sales reps that received a grade of A and count the
number of male sales reps in the table (range O8:R9 in Figure 5–1b).
Table 5–1 on the next page describes the ﬁ eld names, columns, types of data, and
column widths to use when creating the table.
As you read this chapter, you will learn how to create the worksheet shown in
Figure 5–1 by performing these general tasks:
• Create and format the sales rep table
• Sort the sales rep table
• Display subtotals by grouping the sales reps
• Obtain answers to questions about the sales reps using a variety of methods to query
the sales rep table
• Extract records from the table based on given criteria
• Save the worksheet in different ﬁ le formats
Plan
General Project Decisions
While creating an Excel worksheet, you need to make several decisions that will determine
the appearance and characteristics of the ﬁ nished worksheet. As you create the worksheet
required to meet the requirements shown in Figure 5–2, you should follow these general
guidelines:
1. Create and format the sales rep table. The sales rep table should include the data provided
in Table 5–1. The table should be formatted so that the records are easily distinguised. The
data in the worksheet should start several rows from the top in order to leave room for
the criteria area. Using banded rows to format the table provides greater readability. The
last two columns require calculations for the % of Quota and Grade. The Grade can be
obtained using Excel’s VLOOKUP function. Totals also should be added to the table for the
sales reps’ average age, the sum of the sales reps’ quotas, and the sum of the sales reps’
year-to-date sales.
2. Sort the sales rep table. The user of the worksheet should be able to sort the table in a
variety of manners and sort using multiple ﬁ elds at the same time. Excel includes simple
and advanced methods for sorting tables.
3. Display subtotals by grouping the sales reps. The user of the worksheet should be able to
create subtotals of groups of sales reps after sorting the table. Excel’s grouping features
provide for subtotaling.
4. Obtain answers to questions (queries) about the sales reps using a variety of methods to
query the sales rep table. A query can include ﬁ lters, the use of which results in the table
displaying only those records that meet certain criteria. Or, a query can include a calculation
based on data in the table that then is displayed in the worksheet outside of the table.
5. Extract records from the table based on given criteria. A criteria area and extract area
can be created on the worksheet. The criteria area can be used to enter rules regarding
which records to extract, such as all female representatives with a grade of A. The extract
area can be used to store the records that meet the criteria. The column headings from
the table should be used as column headings in both the criteria and extract areas of the
worksheet.
(continued)

Search JabSto ::

Custom Search