# 2003 Excel Formulas

### Pages to are hidden for

"2003 Excel Formulas"

```					Excel 2003                                                                          Page 1 of 21

Microsoft Office 2003
Concepts and Techniques
EXCEL 2003
Instructor’s Manual
PROJECT TWO
FORMULAS, FUNCTIONS, FORMATTING, AND WEB QUERIES
OBJECTIVES
Students will have mastered the material in this project when they can:
 Enter formulas using the keyboard and               Check the spelling of a worksheet
Point mode                                         Preview how a printed copy of the
 Recognize smart tags and option buttons                worksheet will look
 Apply the AVERAGE, MAX, and MIN                     Print a partial or complete worksheet
functions                                          Display and print the formulas version of
 Verify a formula using Range Finder                    a worksheet
 Format a worksheet using buttons and                Use a Web query to get real-time data
commands                                              from a Web site
 Add conditional formatting to a range of            Rename sheets in a workbook
cells                                              E-mail the active worksheet from within
 Change the width of a column and                       Excel
height of a row

PROJECT OVERVIEW
In creating the workbook for this project, students learn how to enter formulas, calculate an
average, find the highest and lowest numbers in a range, verify formulas using Range Finder,
change fonts, draw borders, align text, format numbers, change column widths and row heights,
and add conditional formatting to a range of numbers. Students learn how to spell check a
worksheet, preview a worksheet, print a worksheet, print a section of a worksheet, and display
and print the formulas version of the worksheet using the Fit to option. They also learn how to
complete a Web query to generate a worksheet using external data obtained from the Web and
how to rename sheet tabs. Finally, students learn how to send an e-mail directly from within
Excel with the opened workbook as an attachment.

INSTRUCTOR NOTES
Case Perspective, EX 65
LECTURE NOTES
The Case Perspective presents a real-life situation in which Microsoft Excel could be used and
offers background for the worksheet created in this project (Figure 2-1). Review the Case
Page 2 of 21                           Project 2: Formulas, Functions, Formatting, and Web Queries

Perspective. Note how Excel will be used to create the worksheet and access real-time data from
the Web.

Introduction, EX 66
LECTURE NOTES
Remind students of what they learned in Project 1 and introduce them to the topics covered in
this project. Discuss Figures 2-1a and 2-1b. Much of the work in this project is done using
formulas. A formula can be defined as an equation with more than one variable that is used to
solve practical problems. Students probably have worked with formulas in mathematics, science,
and business classes. Perhaps surprisingly, however, formulas also are used in psychology,
anthropology, sports, art, music, the language arts, and other areas. Formulas can ascertain
abstract possibilities, such as an object’s length as its speed approaches the speed of light (l =
(c 2  r 2 )
s               , where s is the stationary length, c is the speed of light, and r is the object’s speed)
c2
and concrete realities, such as a woman’s shoe size (s = 3l-22, where l is the foot’s length in
inches). Worksheets make formulas even more powerful. This project shows how formulas and
functions can be entered into one cell and then copied to other cells, instantly displaying results
based on different variables.

CLASSROOM ACTIVITIES
As students work through this project, encourage them to recognize the symbiotic relationship
between formulas and worksheets.

Project Two – Blue Chip Stock Club Investment Analysis, EX 67
LECTURE NOTES
Using Figure 2-2, summarize the need, source of data, summary of calculations, Web
requirements, and other facts about the development for this worksheet. Help students to
understand all of the calculations that must be made for each stock. Using the sketch in Figure 2-
3, review the desired formatting for the worksheet. Use Figure 2-1a to describe the worksheet
created and Figure 2-1b to identify the Web queries. Discuss More About Aesthetics versus
Function on page EX 68.

CLASSROOM ACTIVITIES
Divide the class into small groups. Ask each group to refer to pages EX 67-69 and discuss the
Blue Chip Stock Club Investment Analysis project, making certain they understand the needs,
source of data, required calculations, and Web requirements of the project.

Starting and customizing Excel, EX 69
LECTURE NOTES
Recall that Windows must be running to start Excel. If necessary, tell students to change their
computer’s resolution to 800 x 600 so their screens will match the figures in the textbook. To
change the resolution, click Control Panel on the Start menu, click the Display icon, select the
Settings tab, and adjust the slide in the Screen resolution area. Point out that Appendix B
provides information on how to change the resolution. Review Steps 1 through 5 on page EX 70
to start and customize Excel. Discuss More About Starting Excel on page EX 70.
Excel 2003                                                                             Page 3 of 21

CLASSROOM ACTIVITIES
Ask students to recall other ways to start Excel (e.g., double-click the Excel icon on the desktop;
click Microsoft Office Excel 2003 on the Start menu; click the Start button, point to All
Programs, click New Office Document, click the General tab, and then double-click the Blank
Workbook icon).

Entering the titles and numbers into the worksheet, EX 70
LECTURE NOTES
Use Figure 2-1a to describe the worksheet title and subtitle. Review Steps 1 and 2 to enter the
worksheet title and subtitle. Using Figure 2-1a, point out that some column titles include multiple
lines of text. Explain how to start a new line in a cell by pressing ALT+ENTER. In Microsoft
Word, pressing ALT+ENTER is called inserting a manual line break – this starts a new line,
without starting a new paragraph. Use Table 2-1 to summarize the stock club’s investments.
Review Steps 1 through 10 on page EX 71 to enter the column titles. Discuss More About
Wrapping Text on page EX 71. Point out that Excel considers the dates in Table 2-1 to be
numbers, and thus will display them right-aligned. The dates will be formatted later in this
project. Review Steps 1 through 4 on page EX 72. to enter the stock data shown in Table 2-1.
Review Steps 1 and 2 on page EX 72 to enter the row titles. Use Figure 2-4 to illustrate the
worksheet to this point, including the worksheet title, column titles, and stock data. Review Steps
1 through 3 on page EX 72 to save the workbook. Discuss More About Two-Digit Years, More
About Formatting a Worksheet, and More About Entering Numbers into a Range on page EX 72.

DISCUSSION TOPICS
Text that is too long for a cell can be handled in several different ways. What is the difference
between:
(1) Not giving Excel any instructions about what to do with the text overflow
(2) Letting Excel wrap text in a cell
(3) Using ALT+ENTER to cause Excel to go to a new line while entering text.
In what situations might each of these three techniques be used?

TROUBLESHOOTING TIPS
Remind students that when they are entering data in a cell and press the ENTER key, Excel moves
to a new cell. Emphasize that they should use ALT+ENTER, rather than ENTER, to create a new line
in a cell.
Students always should enter a year with four digits. Any two-digit year under 30 is considered
by Excel to be a part of the 2000s, but if a two-digit year is greater than or equal to 30, Excel
thinks it is part of the 1900s. For example, if students wish to enter the year 2040 in an Excel
worksheet and they just type “40,” Excel will think they mean “1940.”

Entering formulas, EX 72
LECTURE NOTES
Explain how the initial cost for each stock is determined. Define formula. Consider the
advantages of assigning a formula to a cell. Discuss More About Automatic Recalculation on
page EX 74. Use Figures 2-5 and 2-6 to describe entering a formula using the keyboard. Excel
calculates the result of a formula as soon as the formula is entered and displays the result in the
worksheet. Emphasize the importance of the equal sign preceding a formula. Without the equal
Page 4 of 21                         Project 2: Formulas, Functions, Formatting, and Web Queries

sign, Excel considers the “formula” to be text. Discuss the Q&A on page EX 74. Use Table 2-2
to list the arithmetic operators for negation, percentage, exponentiation multiplication, division,
addition, and subtraction. Emphasize the operators with which students may be less familiar
(e.g., ^, *, and /). Note the various ways formulas can be typed. Using uppercase and spaces can
make formulas easier to read. If Excel recognizes an error in a formula, an error message
displays. Common error messages, and their meanings, include:
 #####          The formula produces a result that is too wide for the column
 #DIV/0!        The formula attempts to divide by 0 (a referenced cell acting as a divisor may be
empty, making its value 0)
 #REF!          The formula contains an invalid cell reference (a referenced cell may have been
deleted)
 #VALUE The formula has the wrong type of data (a referenced cell in an arithmetic formula
may contain text)
 #NUM           The formula uses an invalid number (a referenced cell may contain a value that
produces too large, or too small, a result to represent)
Another error is to create a formula with a circular reference. This occurs when a cell reference
in a formula refers to the formula’s result. For example, the formula = B1 + B2 + B3 in cell B3 is
a circular reference because the result of the formula in cell B3 depends on the value in cell B3.
Therefore, Excel is unable to determine the formula’s result. When a formula with a circular
reference is entered, Excel immediately points out the problem and offers Help.

CLASSROOM ACTIVITIES
Divide the class into small groups. Ask each group to play “Hot Potato” using the information in
Table 2-2 on page EX 74. To play “Hot Potato,” each group needs a ball or some similar item (a
crumpled piece of notebook paper will serve). The student who is “It” holds the ball, calls out the
meaning of any of the arithmetic operators, and then tosses the ball to another member of the
group, who must describe the operator. This person then becomes “It,” calling out a term and
tossing the ball. Students may refer to Table 2-2 in their textbooks to help them identify the
operators. Continue until all terms have been visited at least once.

DISCUSSION TOPICS
Cell references are an integral part of a formula. The cells referenced in a formula (i.e., the cells
on which the result of the formula depends) are called precedent cells (or simply precedents).
When the values in the precedent cells change, the formula’s result automatically changes.
Therefore, specific numbers should be used in formulas only if they are constants (i.e., if they
will not change). What are some examples in which entering specific numbers in formulas would
be appropriate?

TROUBLESHOOTING TIPS
If students are working in an Excel worksheet, they should expect the cells with formulas to
change their values when they change one of the values in the formula. If they enter such a value,
and the value in the formula cell does not change, it probably means that Excel currently is in
Manual Recalculation mode. Students can press F9 to recalculate, or change to Automatic
Recalculation using the Calculation sheet from the Tools menu.
Excel 2003                                                                               Page 5 of 21

QUICK QUIZZES
What beginning symbol alerts Excel that what follows is a formula and not just text? (Answer:
The equal sign)
What does the asterisk (*) mean in a formula? (Answer: Multiply)
How does Excel show you what cells you have included in a formula? (Answer: When the
formula is selected, the included cells are identified by colored borders)

Order of operations, EX 74
LECTURE NOTES
The result of many numerical expressions depends on the sequence in which calculations are
completed. Even a simple expression, such as 2 + 3 * 4, can have different results depending on
what operation is completed first. Point out that Excel, like algebra, uses the order of operations
agreed to by mathematicians. Review the order of operations. Some operations, such as
multiplication and division or addition and subtraction, have the same rank in terms of
precedence. In this case, Excel performs the operations from left to right. In other words, the
result of the expression 12 / 3 * 4 is 16 (not 1), and the result of 8 – 2 + 4 is 10 (not 2). Explain
how parentheses are used to override the order of operations. An opening parenthesis must have
a closing parenthesis. If not, Excel will offer to supply the missing parenthesis or ask you to
supply it. Use Table 2-3 to illustrate examples of valid Excel formulas.

CLASSROOM ACTIVITIES
Explore the effect of parentheses by presenting an expression, such as 3 * 4 ^ 2 - 12 / 2 + 1,
evaluating it using the order of operations (43), then adding parentheses in one or more places,
such as (3 * 4) ^ 2 - 12 / (2 + 1), and reevaluating the expression (140), noting how the result has
changed.
Write the following expression on the board: 6 * 2 ^ 4 - 12 / 3 + 1
Have students determine all of the different values that can be derived from this expression,
depending on where pairs of parentheses are placed.

Entering formulas using Point mode, EX 75
LECTURE NOTES
Review how the current value, gain/loss, and percent gain/loss are derived for each stock. Define
Point mode. Discuss More About Using Point Mode. Use Figures 2-7 through 2-10 to describe
entering formulas using Point mode. Explain that Point mode can be a faster and more accurate
(because it eliminates cell reference typing errors) way of entering formulas. Point mode also can
more clearly show the relationships between cells in a formula. Note that Point mode and the
keyboard can be combined to enter formulas. Mention that Excel rounds decimal values that are
too long to display in a cell but maintains the original values for computational purposes, and
that this can result in some seeming errors in displayed values. Discuss More About Troubling
Formulas on page EX 77.

PROJECTS TO ASSIGN
Ask students to practice entering formulas using both the keyboard and Point mode. Have them
prepare a brief report explaining which method generally is easier, faster, and/or more accurate,
as well as a list of situations in which one method or the other would be preferable.
Page 6 of 21                       Project 2: Formulas, Functions, Formatting, and Web Queries

Copying the formulas using the fill handle, EX 77
LECTURE NOTES
Point out that although similar formulas could be entered for the remaining stocks, an easier (and
possibly more accurate) technique is to copy the formulas using the fill handle. Use Figures 2-11
through 2-13 to illustrate copying formulas using the fill handle. Consider Other Ways to copy
formulas. Remind students that cell references are adjusted when formulas are copied. Explain
how the cell references are changed when formulas are copied downward and across. To see how
cell references change, encourage students to compare the formula in the source area, cell F4
(D4*E4), to the formula in a cell in the paste area, such as F12 (D12*E12).

QUICK QUIZZES
If you copy across, which references does Excel adjust? (Answer: The column references)

Smart tags, EX 78
LECTURE NOTES
Define smart tags. List some types of data labeled with smart tags. Tell how to use smart tags.
Define smart tag indicator and explain how to display, and use, the Smart Tag Actions button.
Point out the Auto Fill Options button that appears on the screen in Figures 2-12. Note that Excel
also can display other Options buttons. Use Table 2-4 to summarize the smart tags and Options
buttons available in Excel.

CLASSROOM ACTIVITIES
Divide the class into small groups. Ask each group to review Table 2-4 on page EX 79, making
sure that everyone in the group understands each smart tag and Options button and the function
of the corresponding menu. Encourage the groups to bring up any questions they have when the
class reconvenes.

DISCUSSION TOPICS
Microsoft’s smart tag technology was introduced with Office XP and users continue to discover
tags that come with Excel, users also can create their own smart tags that connect to custom-
defined user information, or acquire smart tags from third-party sources. For example, you could
develop a smart tag that recognizes specific names of items in a store’s inventory. When those
item names are entered into a worksheet, the smart tag would recognize the name and list
appropriate options, such as accepting automatic corrections, changing the format, or even
obtaining real-time inventory statistics from the company’s e-commerce Web site. What are
other situations in which creating a custom smart tag would be beneficial? How could a custom
smart tag make some specific Excel computing tasks easier? Why would a third-party, such as a
software company or large e-commerce enterprise, want to create smart tags? To whom would
such a company distribute the third-party smart tags?

Determining totals using the AutoSum button, EX 79
LECTURE NOTES
Using Figure 2-13, point out that the cells in row 13 will contain totals. Remind students how
totals can be obtained by entering the SUM function or clicking the AutoSum button. Recall
when the AutoSum button must be clicked twice. Review Steps 1 and 2 to determine totals using
Excel 2003                                                                              Page 7 of 21

the AutoSum button. Discuss Figure 2-14. Explain how the CTRL key could have been used to
select the nonadjacent range before clicking the AutoSum button. Discuss More About Selecting
a Range on page EX 80.

CLASSROOM ACTIVITIES
Ask students why totals are not obtained in cells D13, E13, G13, and J13.

TROUBLESHOOTING TIPS
Students should be careful to differentiate between double-clicking the AutoSum button and
clicking it twice. When they point to a cell where they want a sum, they should click the
AutoSum button twice, not double-click it.

Determining the total percent gain/loss, EX 80
LECTURE NOTES
Review Steps 1 and 2 to determine the total percent gain/loss. Discuss Figure 2-15. Explain why
the formula was not copied originally to the cell. Define blank cell. As implied in the text, if the
formula had been copied earlier, the formula in cell J13 (I13/F13) would have resulted in an
error message (#DIV/0!) because F13, the divisor, would have a numerical value of zero (0).
Mathematically, division by zero (0) always is meaningless or impossible.

TROUBLESHOOTING TIPS
If students enter a formula that references a blank cell, the value in that blank cell is considered
to be zero. If the blank cell happens to be the divisor in an arithmetic operation, they will get an
error message, since division by zero is not allowed.

Using the AVERAGE, MAX, and MIN functions, EX 80
LECTURE NOTES
Using Figure 2-15, point out that the average, highest, and lowest value must be determined for
each number of shares. Define function. Students might be interested in comparing the
definition of a function in Excel with the common definition of a function in mathematics (a
correspondence between two sets of numbers so that for every number in the first set there is
exactly one number in the second set). Discuss More About Formulas and Functions. Define
arguments. Arguments can be numbers, text, logical values (such as TRUE or FALSE),
constants, arrays (specially arranged groups of constants or areas of cells), error values (such as
#N/A), cell references, formulas, or other functions. The argument designated must produce a
valid value for that function. Describe the general format of a function. List the six methods that
can be used to enter functions. Mention the factors on which the choice of method depends.

DISCUSSION TOPICS
There are six methods that can be used to enter functions. Many other tasks in Excel can be
carried out in a variety of ways. Why does Microsoft give so many options, rather than just
offering one way to do each task? Do multiple methods make things more confusing or less
confusing? What has your experience been like, carrying out tasks in Excel (and other
applications) in more than way? Is it best to experiment with many methods, or stick with the
first one you learn?
Page 8 of 21                           Project 2: Formulas, Functions, Formatting, and Web Queries

Determining the average of a range of numbers, EX 81
LECTURE NOTES
Define the AVERAGE function. Discuss More About Statistical Functions. Use Figures 2-16
and 2-17 to illustrate determining the average of a range of numbers using the keyboard and
mouse. Discuss Other Ways to determine the average. Point out that in a function the argument
must be included with parentheses. Note that the right parenthesis is appended automatically
when the Enter box is clicked or the ENTER key pressed. Explain why the arrow keys cannot be
used to complete an entry in Point mode.

CLASSROOM ACTIVITIES
Ask students how the average number of shares would be found manually (add the number of
shares of each stock and then divide by the number of stocks).

Determining the highest number in a range of numbers, EX 82
LECTURE NOTES
Describe the MAX function. Use Figures 2-18 through 2-20 to illustrate determining the highest
number in a range of numbers using the Insert Function dialog box. Discuss Other Ways to
determine the highest number in a range. Using Figure 2-19, point out that the Function
Arguments dialog box displays the value the function will return and the first few numbers in the
selected range. Note the advantages of using the MAX function instead of simply scanning the
range and then entering the highest value. Discuss More About Entering Functions.

CLASSROOM ACTIVITIES
Divide the class into small groups. Ask each group to display the Insert Function dialog box
shown in Figure 2-18. Have them click the drop down arrow and select All in the Or select a
category box.. Then, have them scroll through the list of functions , and view descriptions of
selected functions. Ask each group to record a list of functions they have used in the past,
functions that look interesting, and functions they cannot imagine anyone using. Have the groups
share their recordings with the class.

DISCUSSION TOPICS
Sometimes, it is just as easy simply to scan a list and pick out the highest value as it is to use the
MAX function. Consider various numerical lists, such as a list of the ages of family members, a
list of class grades, a list of stock prices, a list of the areas of states, a list of city populations, and
so on. When would it make more sense to use the MAX function than simply to scan the list to
determine the highest value? Why? Does the answer depend on the amount of data, the type of
data, or both? Why?

Determining the lowest number in a range of numbers, EX 84
LECTURE NOTES
Describe the MIN function. Use Figures 2-21 through 2-24 to illustrate determining the lowest
number in a range of numbers using the AutoSum menu. Consider Other Ways to determine the
lowest number. Note the advantage of using the AutoSum menu to enter a function. Explain how
to access a function not available on the AutoSum menu. Note the many functions available in
Excel. Using Figure 2-18, tell how to view categories and obtain descriptions of functions.
Excel 2003                                                                             Page 9 of 21

PROJECTS TO ASSIGN
Encourage students to explore the functions that Excel offers. After displaying the dialog box,
students should use the drop-down arrow and select a category in the Or select a category box.
Make a list of four functions that are displayed. Select each of the four function in turn and use
function. What does the function do? How is it used? When might it be used? Why is it in this
category? Then, have students experiment with the Search for a function text box by entering a
brief description of what they might want to do (e.g., find interest due) and clicking the Go
button. List the functions returned and, by selecting each turn, describe the functions. Based on
these results, how effective is the Search for a function box? Why? Have students write a brief
report on their findings.

QUICK QUIZZES
What do you do if the range Excel selects when you use the AutoSum button to choose a
function is not the correct range? (Answer: Select the correct range with the mouse)

Copying the AVERAGE, MAX, and MIN functions, EX 87
LECTURE NOTES
Use Figures 2-25 through 2-27 to describe copying a range of cells across columns to an adjacent
range using the fill handle. Discuss Other Ways to copy a range of cells across columns to an
adjacent range. Tell why the average in cell J14 was deleted. The negative Percent Gain/Loss
values that appear in column J indicate a percent loss. In performing statistical computations
such as average, however, a negative percent (i.e., a percent less than 0) makes no sense. For
example, in basketball, how can a shooting percentage be less than 0%? Remind students that
ranges are adjusted when functions are copied.

Saving a workbook using the same file name, EX 88
LECTURE NOTES
Review Step 1 to save the workbook. Note that when you save a workbook a second time using
the same file name, Excel will not display the Save As dialog box as it does the first time you
save the workbook. Mention other ways to save the workbook using the same file name – click
Save on the File menu, press SHIFT+F12 or press CTRL+S. Explain how to save the workbook
with a new name or on a different drive. Discuss More About File Types on page EX 81.

CLASSROOM ACTIVITIES
Ask students to suggest circumstances when they might save a workbook with a new name.
Why?

DISCUSSION TOPICS
The importance of saving a workbook is clear – unless it is saved, a malfunction or power failure
could cause the workbook to be lost. But, how often should an Excel workbook should be saved?
Once a minute? Once every 15 minutes? Once an hour? Once a day? Why? Some computer users
feel it is not time that is important, but that a workbook should be saved whenever you would not
want to have to redo the work completed since the last time the workbook was saved. Is this a
good rule of thumb? Why or why not?
Page 10 of 21                       Project 2: Formulas, Functions, Formatting, and Web Queries

Verifying formulas using Range Finder, EX 89
LECTURE NOTES
Mention the common mistake made when entering formulas. Explain how formula references
can be verified. Define Range Finder. Explain how Range Finder is used to verify cell
references. Use Figure 2-28 to describe verifying a formula using the Range Finder. Explain how
the Range Finder can be used to change cells referenced in a formula. When using Range Finder,
incorrect cell references also can be changed by double-clicking the incorrect reference in the
formula bar, clicking the correct cell in the worksheet, and then pressing the ENTER key. To see
the result of a formula when using Range Finder, press function key F9. To enter that result,
instead of the formula, in the cell, press the ENTER key; to keep the formula in the cell, press the
ESC key. Discuss More About Checking Formulas on page EX 90.

PROJECTS TO ASSIGN
With the Blue Chip Stock Club worksheet open, have students select a cell that contains a
formula and then click Formula Auditing on the Tools menu. Have students use Help to learn
more about two of the auditing commands. Then, ask them to use the two commands and note
what takes place. Finally, have students write a brief report describing the commands and
explaining how the commands can help to find errors in a worksheet.

Formatting the worksheet, EX 90
LECTURE NOTES
Recall reasons for formatting a worksheet. Explain how the formatting in this project is different
from the formatting in the previous project. Using Figure 2-29, compare the unformatted
worksheet in Figure 2-29a to the formatted worksheet in Figure 2-29b. Outline the type of
formatting required for Project 2. Note the formatting that can be applied using the Formatting
toolbar and mouse. Discuss More About Colors and More About Toolbars on page EX 91.

CLASSROOM ACTIVITIES
Using Figure 2-29b and the outline of formatting changes on page EX 91, ask students to
identify formatting tasks they already know how to perform (e.g., bolding text) and those that
they must learn how to do (e.g., formatting the date) to produce the formatted worksheet in
Figure 2-29b.

Changing the font and centering the worksheet title and subtitle, EX 91
LECTURE NOTES
Mention that different fonts often are used in presentation-quality worksheets. Because too many
font types and styles can be distracting, generally no more than two different fonts are used in a
worksheet. Using Figure 2-29b, describe the format of the worksheet title. Use Figures 2-30
through 2-33 to illustrate changing the font and centering the worksheet title. Discuss Other
Ways to format the worksheet title. Note that fonts can be changed at any time the worksheet is
active. Explain why the Formatting toolbar is used to center the worksheet title instead of the
Merge and Center button. Review Steps 1 through 5 on page EX 93 to change the font and center
the worksheet subtitle. Discuss Figure 2-34. Consider formatting tasks that could, and could not,
be doe to both titles at the same time.
Excel 2003                                                                             Page 11 of 21

CLASSROOM ACTIVITIES
Ask each student to choose which of the following statements best represents his or her opinion:
(1) It is better to change fonts before any data is entered.
(2) It is better to change fonts as you enter data.
(3) It is better to change fonts after all the data is entered.
Divide the class into three groups based on their choice. Ask each group to prepare a defense of
their choice. Conduct an informal debate among the three groups.

Changing the background and font colors and applying a box border to the worksheet title and
subtitle, EX 94
LECTURE NOTES
Use Figure 2-29b on page EX 90 to identify the final formats assigned to the worksheet title.
Discuss the Q&A on page EX 93. Use Figures 2-35 through 2-38 to illustrate changing the
background and font colors and applying a box border to the worksheet title and subtitle. Discuss
Other Ways to change the background, font colors, and border. Explain how borders and
background colors can be removed, and font colors returned to black. Discuss More About
Adding Colors and Borders on page EX 94. To return a cell’s font, font size, and font color
quickly back to the default setting (10 point Arial black), select the cell, click Cells on the
Format menu, click the Font tab in the Format Cells dialog box, click the Normal font check box,
and then click OK.

DISCUSSION TOPICS
One of the reasons for formatting a worksheet is to improve readability. When a background
color is assigned to a cell, it is important to choose a contrasting font color. To make this point,
compare the readability of the title in Figure 2-34 to the worksheet title in Figure 2-38. Using the
Fill Color palette (Figure 2-35) and Font Color palette (Figure 2-36), which background
color/font color combinations would be suitable? Why? Are there circumstances under which
some combinations would be appropriate, while others would not be inappropriate?

Applying formats to the column titles, EX 96
LECTURE NOTES
Use Figure 2-29b to characterize the format of the column titles. Using Figure 2-39, describe
bolding, centering, and applying a bottom border to the column titles. Discuss Other Ways to
format column titles. Note the different ways in which cell contents can be aligned. As an
alternative to using buttons on the Formatting toolbar, text can be aligned as it is entered into a
cell by beginning the cell entry with a special character. An apostrophe (’) left-aligns text, a caret
(^) centers text, and a quotation mark (“) right-aligns text. Using the Format Cells dialog box
shown in Figure 2-32, point out that cell contents can be aligned vertically and rotated to various
angles. The height of a row will adjust to fit the cell contents.

Centering the stock symbols and formatting the dates and numbers in the worksheet, EX 96
LECTURE NOTES
Explain why the stock symbols in column B should be centered. Use Figures 2-40 and 2-41 to
describe centering data in cells and formatting dates. Consider Other Ways to center data in cells
and format dates. Mention how the Center button and column B heading also could have been
used to center column B.
Page 12 of 21                         Project 2: Formulas, Functions, Formatting, and Web Queries

DISCUSSION TOPICS
The Type list on Number sheet in Format Cells dialog box offers many different styles from
which you can select to format a date. Is there a “best” style to select? Why might one style be
preferable to another? What should be taken into consideration when selecting a style for the
date?

Formatting numbers using the Formatting toolbar, EX 98
LECTURE NOTES
Use Figure 2-29b to identify the number formats in the worksheet. Discuss More About Rotating
and Shrinking Entries in Cells. Define Currency style format. Use Figure 2-42 to identify the
Currency Style button. Differentiate between a fixed dollar sign and a floating dollar sign.
When a dollar sign is entered as part of a number, the number displays in the cell with a floating
dollar sign. Emphasize that the Currency Style button applies a fixed dollar sign. In many
financial documents, such as this worksheet, dollar signs (\$) are displayed only in the first row
where values appear and in the summary rows (e.g., total, average, highest, and lowest). Define
Comma style format. Use Figures 2-42 through 2-44 to describe applying a Currency style
format and Comma style format using the Formatting toolbar. Explain how the Increase Decimal
button and the Decrease Decimal button are used to add, or eliminate, decimal places. Emphasize
that a numeric format does not affect the actual contents of a cell. Discuss More About
Formatting Numbers as You Enter Them on page EX 100.

TROUBLESHOOTING TIPS
Excel rounds numbers to fit the Currency style format. Rounding numbers can result in some
seeming errors. When numbers are rounded, the actual number, not the rounded number, still is
used in calculations. For example, if the Currency style format is applied to 3.425 and 5.269, the
numbers display as \$3.43 and \$5.27. Yet, when the AutoSum button is used to add the numbers,
the displayed result will be \$8.69 (3.425 + 5.269 = 8.694 ≈ \$8.69) instead of the \$8.70 that might
be anticipated from the displayed values. Emphasize that Excel stores the value of the numbers
to several decimal places, but displays them by rounding to fit the selected format.

QUICK QUIZZES
What is the difference between Currency style format and Comma style format? (Answer:
Currency style displays a dollar sign; Comma style does not)

Applying a thick bottom border to the row above the total row and bolding the total row titles,
EX 100
LECTURE NOTES
Using Figure 2-29b, point out the bottom double border below row 12 and the bold row titles in
cells A13:A16. Review Steps 1 and 2 to apply a thick bottom border to the row above the title
row and bold the total row titles. Discuss Figure 2-45. Discuss the Q&A.

Formatting numbers using the Format Cells command on the shortcut menu, EX 100
LECTURE NOTES
Using Figure 2-29b, compare the position of the dollar sign in cell E4 with the position of the
dollar sign in cell E14. Recall that cell E4 displays a fixed dollar sign to the far left in the cell,
Excel 2003                                                                             Page 13 of 21

while cell E14 displays a floating dollar sign immediately to the left of the first digit. Remind
students that the Cells command on the Format menu or the Format Cells command on the
shortcut menu must be used to assign a floating dollar sign. Use Figures 2-46 through 2-48 to
illustrate applying a Currency style format with a floating dollar sign using the Format Cells
command. Discuss Other Ways to apply a floating dollar sign. Point out the difference between
the fixed dollar signs and floating dollar signs in Figure 2-48. Use Figure 2-46 to recall the
categories of formats from which students can choose. Note the importance of selecting the
appropriate negative numbers format. Explain why the third selection was chosen in the Negative
numbers list box.
In addition to the many formats available in the Category list in the Format Cells dialog box
(Figure 2-47), custom formats also can be created. To create a custom format, click Custom in
the Category list and then edit the format codes in the Type field. The newly created format
displays in the Custom format list for that workbook and can be applied just as any other format.

PROJECTS TO ASSIGN
Have students open an Excel worksheet, select a cell or range that contains a number, right-click
the cell or range, and select Format Cells from the shortcut menu. Select the Number tab in the
Format Cells dialog box. Choose at least four categories from the Category list (one at a time),
characterize and provide a sample of each format, note the options available, and observe the
effect the formats have on the selected cell(s). Finally, have students write a brief report on their
findings.

Formatting numbers using the Percent Style button and Increase Decimal button, EX 102
LECTURE NOTES
Tell how the numbers in column J must be formatted. Point out the Percent Style button on the
Formatting toolbar. Use Figure 2-49 to describe applying a Percent style format. Discuss Other
Ways to apply a percent style format. Explain how the displayed value is determined when the
Percent Style button is clicked.

Conditional formatting, EX 103
LECTURE NOTES
Describe conditional formatting. Mention how conditional formatting is used. Define
condition. Explain how Excel uses a condition to determine whether to apply the formatting.
Tell why conditional formatting is a powerful feature of Excel. The format used for conditional
formatting takes the place of a cell’s normal formatting as long as the condition is true. When the
condition is not true, the cell’s normal format is displayed again. Use Figures 2-50 through 2-54
to illustrate applying conditional formatting. Consider Other Ways to apply conditional
formatting. Note the preview box in the Conditional Formatting dialog box in Figure 2-53.
Explain the purpose of the Add button, Delete button, and middle text box in the Conditional
Formatting dialog box. Use Table 2-5 to summarize the conditional formatting relational
operators. Discuss More About Conditional Formatting on page EX 106.

CLASSROOM ACTIVITIES
Divide the class into small groups. Ask each group to play “Hot Potato” using the information in
Table 2-5 on page EX 106. To play “Hot Potato,” each group needs a ball or some similar item
(a crumpled piece of notebook paper will serve). The student who is “It” holds the ball, calls out
Page 14 of 21                       Project 2: Formulas, Functions, Formatting, and Web Queries

any one of the relational operators, and then tosses the ball to another member of the group, who
must describe the operator. This person then becomes “It”, calling out a term and tossing the
ball. Students may refer to Table 2-5 at any time during the game and they may help one another.
Continue until all operators have been visited at least once.

DISCUSSION TOPICS
Conditional formatting often is applied when creating a worksheet that others will use. What are
some specific circumstances under which conditional formatting might be used? Why?

Changing the widths of columns and heights of rows, EX 107
LECTURE NOTES
Note Excel’s default column width. Define character and pixel (pronounced pick´ sell or pick
sell´). A pixel can be thought of as similar to the lights that are used to create characters and
graphics on electronic scoreboards. Note the resolution of a typical screen display. Mention
Excel’s default row height. Explain why column widths and row heights sometimes are changed.

Changing the widths of columns, EX 107
LECTURE NOTES
Explain how column width can be changed. Define best fit. Consider when column widths are
set manually instead of using best fit. Explain how to set a column width to best fit. Use Figures
2-55 through 2-57 to illustrate changing the widths of columns. Discuss Other Ways to change
the column width. Mention how to use the Column Width command on the shortcut menu to
change a column’s width. The exact width of a column can be ascertained by right-clicking the
column heading and then clicking Column Width on the shortcut menu. Note that column width
can vary between zero (0) and 255 characters. Describe hiding cells. Explain how to display a
hidden column. When the mouse pointer is on the hidden column boundary, it becomes a split
double arrow separated by two parallel lines. Dragging this pointer to the right displays the
hidden column. Columns also can be hidden by right-clicking the column heading and then
clicking Hide on the shortcut menu. Once hidden, columns also can be revealed by positioning
the mouse pointer just to the right of the hidden column, right-clicking, and then clicking Unhide
on the shortcut menu. Discuss More About Hidden Columns on page EX 110.

QUIZ QUIZZES
What is the maximum number of characters for a column’s width? (Answer: 255)
How do you display a hidden column? (Answer: Position the mouse pointer to the right of the
column heading boundary where the hidden column is located and then drag to the right)

Changing the heights of rows, EX 110
LECTURE NOTES
Note that row height automatically is adjusted when font size is changed. Explain why row
heights are changed manually. Use Figures 2-60 through 2-62 to illustrate changing the height of
a row by dragging. Consider Other Ways to change row height. Note that row height can vary
from zero (0) to 409 points. Explain hiding and displaying rows, which is similar to hiding and
displaying columns. Tell how to set a row height to best fit. Although hidden rows and columns
cannot be seen, cells in them still can be used in formulas. To see the contents of a hidden cell
Excel 2003                                                                            Page 15 of 21

without unhiding the hidden row or column, type the cell reference in the Name box and press
the ENTER key. The hidden cell’s contents will appear in the formula bar. Discuss More About
Hidden Rows on page EX 111.

DISCUSSION TOPICS
Excel makes it possible to hide a row or column in a worksheet by changing the row height or
column width to zero. Why might someone want to hide cells when creating a worksheet?

QUICK QUIZZES
How do you display a hidden row? (Answer: Position the mouse pointer just below the row
heading boundary where the row is hidden and then drag down)
How to you set a row height to best fit? (Answer: Double-click the bottom boundary of the row

Checking spelling, EX 111
LECTURE NOTES
A misspelled word can damage the credibility of even the best worksheet. Imagine reviewing a
stockbroker’s worksheet entitled “Curent Best Buys.” No matter how thorough the broker’s
research, the misspelled “Current” is sure to shake the confidence of the worksheet’s readers.
Define Excel’s spell checker. Note that spell checker checks words in the worksheet against
words in its standard dictionary, which is shared by all Microsoft Office 2003 applications. Point
out that the Spelling dialog box in Figure 2-63, and indicate that it can be used to add specialized
words to a custom dictionary (by clicking the Add to Dictionary button). Use Figures 2-63 and 2-
64 to illustrate checking spelling on the worksheet. Discuss Other Ways to check spelling. Using
the Spelling dialog box in Figure 2-63, tell how to correct a word with one of the suggestions,
correct a word with a spelling that is not in the suggestions list, change the word throughout a
worksheet, skip correcting a word, and ignore a word throughout the entire worksheet. Consider
additional guidelines when using the spell checker. Discuss the Q&A on page EX 113.

CLASSROOM ACTIVITIES
Using Figure 2-63, ask students to distinguish between the top three buttons (Ignore Once,
Ignore All, and Add to Dictionary) in the Spelling Dialog box and bottom three buttons (Change,
Change All, and AutoCorrect) in the dialog box. If necessary, explain that the first group
represents options used when a word is spelled correctly; the second group of buttons represents
options used when a word is misspelled. Have students suggest situations in which each button
might be used.

DISCUSSION TOPICS
Excel’s spell checker is a valuable tool, but is it enough merely to spell check a worksheet, or is
proofreading necessary? Consider how a worksheet detailing company expenses with a column
title “pay roll” instead of “payroll” would be received. What are some other examples of
misspellings that might not be detected by spell checker?
Page 16 of 21                        Project 2: Formulas, Functions, Formatting, and Web Queries

Previewing and printing the worksheet, EX 113
LECTURE NOTES
Describe previewing the worksheet. Differentiate between portrait orientation and landscape
orientation. Point out how the Print Preview command on the File menu or the Print Preview
button on the Standard toolbar can reduce frustration. Print preview saves time and paper by
helping to eliminate unattractive printouts. Use Figures 2-65 through 2-67 to illustrate
previewing and printing a worksheet. Discuss Other Ways to preview a worksheet. Note that a
saved workbook will maintain the same orientation until it is changed. Use Table 2-6 to describe
the buttons in the Preview window. Point out alternative ways to move from page to page and
magnify or reduce the print preview. Use Figure 2-65 to describe the options available in the
Page Setup dialog box. Each tab in the Page Setup dialog box is used for a different purpose:
 Page               Set orientation, scaling, paper size, print quality, first page number
 Margins            Specify margins and whether to center the printout on the page
 Sheet              Stipulate a print area, print titles, what is printed, and page order
Mention when the Print dialog box does, and does not, display. Discuss More About Error
Checking.

TROUBLESHOOTING TIPS
It is important to distinguish the difference between clicking the Print button on the Standard
toolbar and clicking the Print command on the File menu or a Print button in the dialog box or
Preview window. To choose a printer, select the number of copies, or determine what pages to
print students must use the Print command on the File menu or the Print button in a dialog box or
the Preview window. Clicking the Print button on the Standard toolbar will not display the Print
dialog box.

Printing a section of the worksheet, EX 116
LECTURE NOTES
Consider circumstances in which it might be desirable to print only a section of a worksheet. Use
Figures 2-71 and 2-72 to describe printing a section of the worksheet. Discuss Other Ways to
print a section. Using Figure 2-71, describe three option buttons in the Print what area: the
Selection option button, the Active sheet(s) option button, and the Entire workbook option
button. The Print dialog box also has two check boxes: Print to file in the Printer area and Collate
in the Copies area. When the Print to file check box is checked, a printer file is created that saves
the workbook along with the necessary information to print the workbook. Any printer that uses
the same printer language as the printer specified in the Name text box can print the file and
match the output. If the printer file is attached to an e-mail message, it can be used to print the
workbook even if the computer receiving the message does not have Excel installed. When the
Collate check box is checked and more than one copy of a multi-page workbook will be printed,
an entire copy of the workbook is printed before the next copy of the workbook is printed.
Discuss More About Printing a Section of a Worksheet.

QUICK QUIZZES
Which option button in the Print what area of the Print dialog box instructs Excel to print the
worksheet currently on the screen? (Answer: The Active sheet(s) option button)
Excel 2003                                                                              Page 17 of 21

Which option button in the Print what area of the Print dialog box instructions Excel to print all
of the worksheets in the workbook? (Answer: The Entire workbook option button)

Displaying and printing the formulas version of the worksheet, EX 118
LECTURE NOTES
Explain how the values version of a worksheet is different from the formulas version of a
worksheet. Discuss More About Values versus Formulas. Point out that CTRL+ACCENT MARK (`)
is used to toggle between the two versions. Define debugging. The term “debugging” is said to
have originated with famed computer programmer Grace Hopper (inventor of COBOL), who
traced a computer malfunction to a bug (a moth) in the machine. Note the adjustments that must
be made to fit the formulas version printed on one page. Describe the Fit to option. Use Figures
2-73 through 2-75 to illustrate displaying the formulas in the worksheet and fitting the printout
on one page. Consider Other Ways to display formulas. Discuss More About the Fit To Option
on page EX 121.

CLASSROOM ACTIVITIES
Figure 2-73 shows the formulas version of the worksheet. How might displaying the worksheet
in this version make it easier to find and correct errors?

QUICK QUIZZES
What is displayed in the formulas version of the worksheet? (Answer: Formulas instead of
values)
How do you toggle between the values version and the formulas version of a worksheet?
How does Excel make a worksheet fit on one page when you choose the Fit to option? (Answer:
Excel automatically reduces or enlarges the worksheet by the necessary percentage.)

Changing the Print Scaling option back to 100%, EX 120
LECTURE NOTES
Point out when the Print Scaling option must be reset. Review Steps 1 through 4 to change the
Print Scaling option back to 100%. Describe the Adjust to box.

CLASROOM ACTIVITIES
Ask students to suggest circumstances when the Fit to option might be used. If necessary, point
out that the Fit to option is especially convenient when a worksheet is just a little bit larger than a
single page.

Importing external data from a Web source using a Web query, EX 120
LECTURE NOTES
Mention Excel’s capability of obtaining external data from World Wide Web sites. When data is
subject to change (such as the current value of stocks in the Blue Chip Stock Club worksheet),
this capability is invaluable. Define Web query. Discuss More About Web Queries. Use Table
2-7 to list the types of Web queries available when Excel is installed. Explain why the data
returned by the stock-related Web queries is considered real-time. Use Figures 2-76 through 2-82
to illustrate getting external data from a Web source using a Web query. Consider Other Ways to
use a Web query. Using Figure 2-80, describe Excel’s display of the data returned from the Web
Page 18 of 21                       Project 2: Formulas, Functions, Formatting, and Web Queries

query. Use Figure 2-81 to characterize the External Data toolbar. Explain the purpose of the
Refresh All button and the Query Parameters button.

PROJECTS TO ASSIGN
In addition to obtaining real-time stock quotes, major indices, and currency rates, Web queries
can be used for many other purposes. For example, Web queries can be used to acquire the latest
sales information from an e-commerce Web site. Web queries are suitable for almost any content
that involves dynamic lists of data or tables of information. As an extra-credit project, encourage
students to use a Web query to obtain up-to-date information on a topic of interest. Using a
search engine and appropriate keywords, they should locate a Web site that has the required
information readily available. Ideally, the site should have validity, reliability, and longevity.
(Government or professional organization sites often offer a good source of useful information.)
After locating a site, they should record the URL. Then, they should open a new Excel workbook
and use the New Web Query command on the Import External Data submenu (Figure 2-76) to
import the data. Have students print the and turn in the worksheet that results, including the URL
they used to obtain their information.

QUICK QUIZZES
What types of external data can be returned by the built-in Web queries in Excel? (Answer:
Currency rates, major indices, and stock quotes)
How do you make the External Data toolbar appear? (Answer: Right-click any toolbar and then
click External Data)

Changing the worksheet names, EX 124
LECTURE NOTES
Use Figure 2-80 to point out the tabs at the bottom of the Excel window. Explain how the sheet
tabs are used to display sheets in a workbook. Use Figures 2-82 and 2-83 to describe changing
the worksheet names. Note limitations on sheet names. Using Figure 2-83, explain how the tab
split box can be used to display more sheet tabs and how the tab scrolling buttons can be used to
move between sheets. Keyboard shortcuts (CTRL+PAGE UP or CTRL+PAGE DOWN) also can be
used to move between sheets. A tab scrolling button can be right-clicked to display a list of
worksheets in the workbook. To access a worksheet, simply click its name in the list. This is
particularly convenient if a workbook contains so many worksheets that moving between them
using the tab scrolling buttons would be inconvenient. Discuss More About Sheet Tabs.

QUICK QUIZZES
What is the maximum number of characters for a worksheet name? (Answer: 31 characters,
including spaces)
Where are the tab scrolling buttons located? (Answer: To the left of the sheet tabs)

E-mailing a workbook from within Excel, E 2.63
LECTURE NOTES
Describe e-mail. Discuss More About Obtaining an E-Mail Account. Explain the capability of e-
mailing a workbook or worksheet directly from within Excel. Students will be able to complete
this activity only if they have an e-mail address with Outlook, Outlook Express, Microsoft
Exchange Client, or another 32-bit e-mail program compatible with Messaging Application
Excel 2003                                                                            Page 19 of 21

Interface. Use Figures 2-84 and 2-85 to illustrate e-mailing a workbook from within Excel.
Discuss Other Ways to e-mail a worksheet. Contrast sending a workbook as an attachment with
sending a worksheet from within Excel using the E-mail button or by clicking the Mail Recipient
command on the File menu. Consider some of the options available when sending an e-mail from
within Excel.

DISCUSSION TOPICS
If you want to e-mail a workbook, you can either send it as an attachment or send it from within
Excel. What circumstances might require e-mailing a workbook from within Excel? What
circumstances might require e-mailing the workbook as an attachment? Does personal preference
play a role?

PROJECTS TO ASSIGN
Explain to students that if they currently do not have an e-mail address, they can obtain a free

Saving the workbook and quitting Excel, EX 126
LECTURE NOTES
Review Steps 1 and 2 to save the workbook and quit Excel. Discuss More About Microsoft
Certification.

Project summary, EX 127
LECTURE NOTES
Briefly review the material presented in this project. If students have a SAM user profile,
encourage them to log in to their SAM account and go to the assignments page for additional
assignments.

What you should know, EX 127
LECTURE NOTES
Encourage students to use this section in preparing for tests and quizzes. Discuss More About
The Quick Reference on page EX 128.

Learn it online, EX 129
LECTURE NOTES
These exercises ask students to use the Web for additional activities, information, and resources
related to topics presented in this project. Have students use their browsers and the given URL to
complete selected exercises.

LECTURE NOTES
This exercise gives students a chance to use what they have learned in this project with a
document on the Data Disk. Exercise 1 can be reviewed and assigned at this time.
Page 20 of 21                       Project 2: Formulas, Functions, Formatting, and Web Queries

In the lab, EX 132
LECTURE NOTES
These exercises provide students with practice in using the skills developed in this project.
Exercises 1 through 3 can be reviewed and assigned at this time.

Cases and places, EX 140
LECTURE NOTES
ended activities with varying degrees of difficulty. Students can be assigned one or more
exercises or be allowed to choose the exercises in which they are most interested.
Excel 2003                                                  Page 21 of 21

KEY TERMS
arguments (EX 81)               hiding cells (EX 109)
AVERAGE function (EX 81)        landscape orientation (EX 113)
best fit (EX 107)               MAX function (EX 82)
blank cell (EX 80)              MIN function (EX 84)
character (EX 107)              order of operations (EX 74)
comma style format (EX 98)      pixel (EX 107)
condition (EX 104)              Point mode (EX 75)
currency style format (EX 98)   portrait orientation (EX 113)
debugging (EX 118)              previewing the worksheet (EX 113)
e-mail (EX 125)                 Range Finder (EX 89)
equal sign (EX 74)              smart tag indicator (EX 78)
fixed dollar sign (EX 98)       smart tags (EX 78)
floating dollar sign (EX 98)    spell checker (EX 111)
formula (EX 73)                 values version (EX 118)
formulas version (EX 118)       Web query (EX 120)
function (EX 81)

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 508 posted: 12/6/2010 language: English pages: 21
Description: 2003 Excel Formulas document sample