Microsoft Office Tutorials and References
In Depth Information
2 Formulas, Functions, Formatting,
and Web Queries
In Chapter 1, you learned how to enter data, sum values, format the worksheet to make it
easier to read, and draw a chart. You also learned about using Help and saving, printing,
and opening a workbook. This chapter continues to highlight these topics and presents
some new ones.
The new topics covered in this chapter include using formulas and functions to
create a worksheet. A function is a prewritten formula that is built into Excel. Other new
topics include smart tags and option buttons, verifying formulas, applying a theme to a
worksheet, adding borders, formatting numbers and text, using conditional formatting,
changing the widths of columns and heights of rows, spell checking, e-mailing from within
an application, renaming worksheets, using alternative types of worksheet displays and
printouts, and adding page headers and footers to a worksheet. One alternative worksheet
display and printout shows the formulas in the worksheet, instead of the values. When
you display the formulas in the worksheet, you see exactly what text, data, formulas, and
functions you have entered into it. Finally, this chapter covers Web queries to obtain
real-time data from a Web site.
and Web Queries
The project in the chapter follows proper design guidelines and uses Excel to create the
two worksheets shown in Figure 2–1. The Silver Dollars Stock Club was started and is
owned by a national academic fraternity, which pools contributions from a number of
local chapters. Each local chapter contributes $150 per month; the money is then invested
in the stock market for the beneﬁ t of the organization and as a tool to help members learn
about investing. At the end of each month, the club’s treasurer summarizes the club’s
ﬁ nancial status in a portfolio summary. This summary includes information such as the
stocks owned by the club, the cost of the stocks to the club, and the gain or loss that the
club has seen over time on the stock. As the complexity of the task of creating the sum-
mary increases, the treasurer wants to use Excel to create the monthly portfolio summary.
The treasurer also sees an opportunity to use Excel’s built-in capability to access real-time
stock quotes over the Internet.
Recall that the ﬁ rst step in creating an effective worksheet is to make sure you
understand what is required. The people who will use the worksheet usually provide
requirements. The requirements document for the Silver Dollars Stock Club Portfolio
Summary worksheet includes the following: needs, source of data, summary of calculations,
Web requirements, and other facts about its development (Figure 2–2 on page EX 84).
The real-time stock quotes (shown in Figure 2–1b) will be accessed via a Web query. The
stock quotes will be returned to the active workbook on a separate worksheet. Microsoft
determines the content and format of the Real-Time Stock Quotes worksheet.