Microsoft Office Tutorials and References
In Depth Information
As you read this chapter, you will learn how to create the worksheet shown in
Figure 3 – 1 by performing these general tasks:
Create a series of month names
Just a short time ago,
a what-if question of
any complexity could
be answered only by
using a large, expensive
by highly paid computer
generating a result could
take days. Excel gives the
the ability to get complex
Use absolute cell references in a formula
Use the IF function to perform a logical test
Create Sparkline charts in a range of cells
Use the Format Painter button to format cells
Create a 3-D pie chart on a separate chart sheet
Answer what-if questions
Manipulate large worksheets
General Project Decisions
While creating an Excel worksheet, you need to make several decisions that will determine the
appearance and characteristics of the i nished worksheet. As you create the worksheet required
to meet the requirements shown in Figure 3–2, you should follow these general guidelines:
1. Plan the layout of the worksheet. Worksheets that include i nancial data associated
with time frames typically include dates, such as months, quarters, or years, as column
headers. What-if assumptions should not clutter the worksheet, but placing them in an
easily located portion of the worksheet allows for quicker creation of new projections.
2. Determine the necessary formulas and functions needed. Often, i nancial calculations rely
on strict dei nitions and commonly accepted formulas for performing the calculations.
Look for such situations and always use the accepted formulas. When using a what-if
section on a worksheet, make certain to create formulas that use the what-if criteria.
When a requirement necessitates a calculation only under a certain condition, a function
can check for the condition and make the calculation when necessary.
3. Specify how to best utilize Sparkline charts. Sparkline charts allow worksheet users
quickly to visualize information in a small chart within a cell. The use of multiple
Sparkline charts in the worksheet will provide the user with a visual comparison of the
various data items for each month. The user, therefore, can see trends for each line item
over time and also compare relationships among various line items.
4. Identify how to format various elements of the worksheet. Format separate parts of
a worksheet, such as what-if assumptions, in a manner that indicates that they are
separate from the main area of the worksheet. Other i nancial items, such as sales
revenue and expenses, are distinct categories of i nancial data and should be separated
visually. Totals and subtotals should stand out to draw the reader’s attention.
5. Specify how charts should convey necessary information. As you have learned, different
chart types convey different messages and are appropriate in different situations. For
example, a 3-D pie chart is a good way to compare visually a small set of numbers. Often
one or two slices of a pie chart displays as exploded, meaning that the slice appears
pulled away from the cart, in order to emphasize the slice to the user. Format chart data
points so that the worksheet user’s eye is drawn to important information.
6. Perform what-if analysis and goal seeking using the best techniques. What-if analysis
allows you quickly to answer questions regarding various predictions. A what-if area of a
worksheet allows users of the worksheet efi ciently to ask questions. Goal seeking allows
you automatically to modify values in a what-if area of a worksheet based on a goal that
you have for another cell in the worksheet.
When necessary, more specii c details concerning the above guidelines are presented
at appropriate points in the chapter. The chapter also will identify the actions you perform
and decisions made regarding these guidelines during the creation of the worksheet shown
in Figure 3 – 1 on page EX 139.