Microsoft Office Tutorials and References
In Depth Information
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
necessary to meet the requirements shown in Figure 2 – 2, you should follow these general
1. Plan the layout of the worksheet. Rows typically contain items analogous to items in a list.
A name could serve as an item in a list, and, therefore, each name could be placed in a row.
As a list grows, such as a list of employees, the number of rows in the worksheet will increase.
Information about each item in the list and associated calculations should appear in columns.
2. Determine the necessary formulas and functions needed. Calculations result from
known values. Formulas for such calculations should be known in advance of creating
a worksheet. Values such as the average, highest, and lowest values can be calculated
using Excel functions as opposed to relying on complex formulas.
3. Identify how to format various elements of the worksheet. The appearance of the
worksheet affects its ability to express information clearly. Numeric data should be
formatted in generally accepted formats, such as using commas as thousands separators
and parentheses for negative values.
4. Establish rules for conditional formatting. Conditional formatting allows you to format a
cell based on the contents of the cell. Decide under which circumstances you would like
a cell to stand out from related cells and determine in what way the cell will stand out.
5. Specify how the hard copy of a worksheet should appear. When it is possible that a
person will want to create a hard copy of a worksheet, care should be taken in the
development of the worksheet to ensure that the contents can be presented in a
readable manner. Excel prints worksheets in landscape or portrait orientation, and
margins can be adjusted to i t more or less data on each page. Headers and footers
add an additional level of customization to the printed page.
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 performed and
decisions made regarding these guidelines during the creation of the worksheet shown in
Figure 2 – 1 on page EX 67.
In addition, using a sketch of the worksheet can help you visualize its design. The
sketch for The Mobile Masses Store Biweekly Payroll Report worksheet includes a title,
a subtitle, column and row headings, and the location of data values (Figure 2 – 3 on the
following page). It also uses specii c characters to dei ne the desired formatting for the
worksheet, as follows:
1. The row of Xs below the leftmost column dei nes the cell entries as text, such as
2. The rows of Zs and 9s with slashes, dollar signs, decimal points, commas, and
percent signs in the remaining columns dei ne the cell entries as numbers. The
Zs indicate that the selected format should instruct Excel to suppress leading 0s.
The 9s indicate that the selected format should instruct Excel to display any
digits, including 0s.
3. The decimal point means that a decimal point should appear in the cell entry and
indicates the number of decimal places to use.
4. The slashes in the second column identify the cell entry as a date.
5. The dollar signs that are not adjacent to the Zs in the i rst row below the column
headings and in the total row signify a i xed dollar sign. The dollar signs that are
adjacent to the Zs below the total row signify a l oating dollar sign, or one that
appears next to the i rst signii cant digit.
The function, or purpose,
of a worksheet is to
provide a user with direct
ways to accomplish tasks.
In designing a worksheet,
should come before visual
aesthetics. Avoid the
temptation to use l ashy or
confusing visual elements
within the worksheet.
One exception to this
guideline occurs when
you may need to draw the
user’s attention to an area
of a worksheet that will
help the user more easily
complete a task.