First Pages

Creating and Editing a Worksheet Lab 1

Objectives
After completing this lab, you will know how to:

●
1 Create new worksheets.

●
2 Enter and edit data.

●
3 Modify column widths.

●
4 Use proofing tools.

●
5 Copy and paste cell contents.

●
6 Create formulas.

●
7 Insert and delete rows and columns.

●
8 Format cells and cell content.

●
9 Hide and unhide rows and columns.

●
10 Create a basic chart.

●
11 Format values as a date.

●
12 Preview and print a worksheet.

●
13 Display and print formulas.

●
14 Change worksheet orientation and scale content.

EX1.1

ole31265_EXLab1_001-096.indd EX1.1 30/07/10 11:47 AM

 First Pages

CASE STUDY

Downtown Internet Café

You are excited about your new position as manager place to meet, study, work, or download music for their
and financial planner for a local coffeehouse. Evan, iPods and PDAs. You also believe the rental computers
the owner, has hired you as part of a larger effort to will be a draw for vacationers who want to check e-mail
increase business at the former Downtown Café. Evan during their travels.
began this effort by completely renovating his coffee- Evan wants to create a forecast estimating sales
house and installing a wireless network. He plans to and expenses for the first quarter. As part of a good
offer free Wi-Fi service for customers to use with their business plan, you and Evan need a realistic set of
own laptop computers. In addition, he has set up sev- financial estimates and goals.
eral computer kiosks In this lab, you will
for customers to use help with the first quarter
who do not have lap- forecast by using Micro-
tops and has provided soft Office Excel 2010,
a printer and copier for a spreadsheet appli-
all customers to use. cation that can store,
He also has decided manipulate, and display
to rent an MP3 down- numeric data. You will
load kiosk for custom- learn to enter numbers,
ers who may want to perform calculations,
update the music on copy data, and label
their iPods or PDAs. rows and columns as
Finally, to reflect the you create the basic
new emphasis of the structure of a worksheet
café, he has changed for the Downtown Inter-
its name to the Down- net Café. You will then
town Internet Café. learn how to enhance
You and Evan expect the worksheet using for-
to increase sales by matting features and by
attracting techno-savvy adding color as shown
café-goers, who you here.
hope will use the Down-
town Internet Café as a

EX1.2 WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.2 30/07/10 11:47 AM

 First Pages

Enter labels, numbers, and

formulas to create the basic
structure of a worksheet.

Basic
B i fformatting
i suchh
as alignment, indents,
and numeric styles
quickly improve the
appearance of the
worksheet.

Addi color
Adding l text andd bbackground
k d fill ffurther
h
enhances the appearance of the worksheet.
EX1.3

ole31265_EXLab1_001-096.indd EX1.3 30/07/10 11:47 AM

 First Pages

Concept Preview
The following concepts will be introduced in this lab:

1 Data The basic information or data you enter in a cell can be text or numbers.
2 AutoCorrect The AutoCorrect feature makes some basic assumptions about the text
you are typing and, based on these assumptions, automatically corrects the entry.
3 Column Width The column width is the size or width of a column and controls the
amount of information that can be displayed in a cell.
4 Spelling Checker The spelling checker locates misspelled words, duplicate words, and
capitalization irregularities in the active worksheet and proposes the correct spelling.
5 Thesaurus The thesaurus is a reference tool that provides synonyms, antonyms, and
related words for a selected word or phrase.
6 Range A selection consisting of two or more cells on a worksheet is a range.
7 Formula A formula is an equation that performs a calculation on data contained in a
worksheet.
8 Relative Reference A relative reference is a cell or range reference in a formula
whose location is interpreted in relation to the position of the cell that contains the
formula.
9 Function A function is a prewritten formula that performs certain types of calculations
automatically.
10 Recalculation When a number in a referenced cell in a formula changes, Excel
automatically recalculates all formulas that are dependent upon the changed value.
11 Alignment Alignment settings allow you to change the horizontal and vertical
placement and the orientation of an entry in a cell.
12 Number Formats Number formats change the appearance of numbers onscreen and when
printed, without changing the way the number is stored or used in calculations.

Creating a Workbook
As part of the renovation of the Downtown Internet Café, Evan upgraded the
office computer with the latest version of the Microsoft Office System suite
of applications, Office 2010. You are very excited to see how this new and
powerful application can help you create professional budgets and financial
forecasts for the Café.
You will use the spreadsheet application Excel 2010 included in the
Microsoft Office 2010 System suite to create the first-quarter forecast for the
Café.

EX1.4 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.4 30/07/10 11:47 AM

 First Pages

1 Title bar displays

default file name
• Start Microsoft Ribbon

Excel 2010.

• If necessary,
maximize the Excel
application window.
Formula bar
Name box
Having Trouble?
See “Common 2010 Office Features”
page IO.10, for information on how to
start the application and for a discussion
of features common to all Microsoft
Office 2010 applications.

Mouse pointer
Your screen should be similar to
Figure 1.1

Workbook window
Figure 1.1

After a few moments, the Excel application window is displayed. Because

Additional Information
Excel remembers many settings that were in use when the program was last
Because the Ribbon can adapt to the closed, your screen might look slightly different.
screen resolution and orientation, your
Ribbon may look slightly different. It The Excel application window title bar displays the default file name,
also may display additional tabs if other Book1, and program name. The Ribbon below the title bar consists of seven
application add-ins associated with tabs that provide access to the commands and features you will use to create
Office 2010 are on.
and modify a worksheet.
Below the Ribbon is the formula bar. The formula bar displays entries as
they are made and edited in the workbook window. The Name box, located
Additional Information
at the left end of the formula bar, provides information about the selected
You will learn all about using these
features throughout these labs.
item.
The large center area of the program window is the workbook window.
A workbook is an Excel file that stores the information you enter using
the program. You will learn more about the different parts of the workbook
Having Trouble?
window shortly.
If the workbook is floating in the
workbook window, the title bar displays
The mouse pointer can appear as many different shapes. The mouse pointer
changes shape depending upon the task you are performing or where the
the file name and the Minimize,
pointer is located on the window. Most commonly it appears as a or .
Maximize, and Close When it appears as a , it is used to move to different locations in the work-
buttons. Click to maximize the book window and when it appears as a , it is used to choose items, such as
workbook window. commands from the Ribbon.

Creating a Workbook EX1.5

ole31265_EXLab1_001-096.indd EX1.5 30/07/10 11:47 AM

 First Pages

2
• Move the mouse
pointer into the
center of the
workbook window to
see it appear as .
Mouse pointer with
• Move the mouse selection capabilities
pointer to the Ribbon
to see it appear
as .

Your screen should be similar to

Figure 1.2
Zoom feature

View Options

Figure 1.2
Status bar displays
current mode

The status bar at the bottom of the Excel window displays information about
various Excel settings. The left side of the status bar displays the current mode
or state of operation of the program, in this case, Ready. When Ready is dis-
played, you can move around the workbook, enter data, use the function keys,
or choose a command. As you use the program, the status bar displays the
current mode. The right side of the status bar contains buttons to change the
view and a zoom feature.

Exploring the Workbook Window

When you first start Excel 2010, the workbook window displays a new blank
workbook that has many predefined settings. These settings, called default
settings, are stored in the default workbook template file named Book.xltx. A
template is a file that contains settings that are used as the basis for a new file
you are creating.
The default workbook file includes three blank sheets. A sheet is used to
display different types of information, such as financial data or charts. When-
ever you open a new workbook, it displays a worksheet. A worksheet, also
commonly referred to as a spreadsheet, is a rectangular grid of rows and
columns used to enter data. It is always part of a workbook and is the primary
type of sheet you will use in Excel. The worksheet is much larger than the part
you are viewing in the window. The worksheet actually extends 16,384 col-
umns to the right and 1,048,576 rows down.

EX1.6 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.6 30/07/10 11:47 AM

 First Pages

A B C D E F G H I J K L M N O P … I V
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 Row numbers Column letters Column

Cell selector
identifies active cell

Worksheet

Cell Row

Sheet tabs

The row numbers along the left side and the column letters across the
Additional Information top of the workbook window identify each worksheet row and column. The
Columns are labeled A through Z, AA intersection of a row and column creates a cell. Notice the black border, called
through ZZ, and so forth through the last
column, XFD. There are 17,179,869,184
the cell selector, surrounding the cell located at the intersection of column A
cells in a worksheet. and row 1. This identifies the active cell, which is the cell your next entry or
procedure affects. Additionally, the Name box in the formula bar displays the
cell reference, consisting of the column letter and row number of the active
cell. The reference of the active cell is A1.
Each sheet in a workbook is named. Initially, the sheets are named Sheet1,
Sheet2, and so on, displayed on sheet tabs at the bottom of the workbook
window. The name of the active sheet, which is the sheet you can work in,
appears bold. The currently displayed worksheet in the workbook window,
Sheet1, is the active sheet.

Exploring the Workbook Window EX1.7

ole31265_EXLab1_001-096.indd EX1.7 30/07/10 11:47 AM

 First Pages

1
• Click the Sheet2 tab.

Another Method
You also can press c + Y
to move to the next sheet and
c + U to move to the
previous sheet.

Your screen should be similar to

Figure 1.3

Sheet tabs
Blank worksheet
in Sheet2

Figure 1.3
Active sheet
Tab scroll buttons

An identical blank worksheet is displayed in the window. The Sheet2 tab letters
Additional Information
are bold, the background is highlighted, and it appears in front of the other
Do not be concerned if your workbook sheet tabs to show it is the active sheet.
window displays more or fewer column
letters and row numbers than shown The sheet tab area also contains tab scroll buttons, which are used to
here. This is a function of your computer scroll tabs right or left when there are more sheet tabs than can be seen. You
monitor settings. will
w learn about these features throughout the labs.

MOVING AROUND THE WORKSHEET

The mouse or keyboard commands can be used to move the cell selector from
Additional Information
one cell to another in the worksheet. To move using a mouse, simply point
You can use the directional keys in the to the cell you want to move to and click the mouse button. Depending upon
numeric keypad (with n off)
or, if you have an extended keyboard, what you are doing, using the mouse to move may not be as convenient as
w
you can use the separate directional using the keyboard, in which case the directional keys can be used. You will
keypad area. make Sheet1 active again and use the mouse, then the keyboard to move in the
worksheet.
w

EX1.8 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.8 30/07/10 11:47 AM

 First Pages

1
• Click the Sheet1 tab
to make it the active
sheet again.

• Click cell B3. Name box displays

cell reference
• Press S (3 times). Cell selector
identifies active cell
• Press Z (4 times).

Highlighted row number

Your screen should be similar to
and column letter identify
Figure 1.4
location of active cell

Figure 1.4

Cell E7 is outlined in black, indicating this cell is the active cell. The Name box
displays the cell reference. In addition, the row number and column letter are
Having Trouble? gold to further identify the location of the active cell.
Refer to the Scrolling the Document As you have learned, the worksheet is much larger than the part you are
Window section of the Introduction to viewing in the window. To see an area of the worksheet that is not currently in
Office 2010 for more keyboard and
mouse procedures.
view, you need to scroll the window. The keyboard procedures shown in the
table that follows can be used to move around the worksheet.

Keyboard Action
a+Y Moves right one full window
a+U Moves left one full window
h Moves to beginning of row
c+h Moves to upper-left corner cell of worksheet
c+j Moves to last used cell of worksheet
j+S Moves to last-used cell in row
j+Z Moves to last-used cell in column

In addition, if you hold down the arrow keys, the a + U or a +

Y keys, or the U or Y keys, you can quickly scroll through
the worksheet. When you use the scroll bar, however, the active cell does not
change until you click on a cell that is visible in the window.
You will scroll the worksheet to see the rows below row 27 and the columns
to the right of column O.

Exploring the Workbook Window EX1.9

ole31265_EXLab1_001-096.indd EX1.9 30/07/10 11:47 AM

 First Pages

2
• Press Y
(3 times).

• Press a +
Y (3 times).
Columns AT
Having Trouble? through BH
Do not use the numeric keypad
U and Y keys,
as this may enter a character Active cell AX88
in the cell.

Rows 82 through 108

Drag vertical scroll
Your screen should be similar to box to move up
Figure 1.5 Drag horizontal and down
scroll box to move
side to side

Having Trouble?
Your screen may display more or fewer
rows and columns and the active cell
may be a different cell. This is a function Figure 1.5
of your screen and system settings.

The worksheet scrolled downward and left three full windows, and the window
Additional Information displays rows 82 through 106 and columns AT through BH of the worksheet.
If you have a mouse with a scroll wheel, The active cell is cell AX88. As you scroll the worksheet using the keyboard,
rotating the wheel forward or back the active cell also changes.
scrolls up or down a few rows at a time.
It is even more efficient to use the scroll bar to move long distances.

3
• Slowly drag the
vertical scroll box up
the scroll bar until
row 1 is displayed.

• Slowly drag the Cell selector location

horizontal scroll box has not changed
Columns A through O
left along the scroll
bar until column A is
displayed.

Additional Information
As you scroll, the scroll bar identifies
the current row position at the top of the Rows 1 through 27
window or column position at the left
side of the window in a ScreenTip.

Your screen should be similar to

Figure 1.6

Figure 1.6

EX1.10 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.10 30/07/10 11:47 AM

 First Pages

Notice that the Name box displays the active cell location as AX88. When you
Another Method
use the scroll bar to scroll the worksheet, the active cell does not change.
You also can type a cell address in the
Name box and press E to move
to that location.

4
• Practice moving around the worksheet using the keys presented in
the table on page EX1.x.

• Press c + h to move to cell A1.

You can use the mouse or the keyboard with most of the exercises in these
Additional Information
labs. As you use both the mouse and the keyboard, you will find that it is more
Some of the keys presented in the table
will not change the worksheet location
efficient to use one or the other in specific situations.
until the worksheet contains data.
DEVELOPING A WORKSHEET
Now that you are familiar with the parts of the workbook and with moving
around the worksheet, you are ready to create a worksheet showing the fore-
cast for the first three months of operation for the Downtown Internet Café.
Worksheet development consists of four steps: planning, entering and edit-
ing, testing, and formatting. The objective is to create well-designed work-
sheets that produce accurate results and are clearly understood, adaptable,
and efficient.

Step Description
1. Plan Specify the purpose of the worksheet and how it should be organized. This means clearly
identifying the data that will be input, the calculations that are needed to achieve the results,
and the output that is desired. As part of the planning step, it is helpful to sketch out a design
of the worksheet to organize the worksheet’s structure. The design should include the work-
sheet title and row and column headings that identify the input and output. Additionally,
sample data can be used to help determine the formulas needed to produce the output.
2. Enter and edit Create the structure of the worksheet using Excel by entering the worksheet labels, data, and
formulas. As you enter information, you are likely to make errors that need to be corrected or
edited, or you will need to revise the content of what you have entered to clarify it or to add or
delete information.
3. Test Test the worksheet for errors. Use several sets of real or sample data as the input, and verify
the resulting output. The input data should include a full range of possible values for each
data item to ensure the worksheet can function successfully under all possible conditions.
4. Format Enhance the appearance of the worksheet to make it more readable or attractive. This step is
usually performed when the worksheet is near completion. It includes many features such as
boldface text, italic, and color.

As the complexity of the worksheet increases, the importance of following the

design process increases. Even for simple worksheets like the one you will
create in this lab, the design process is important. You will find that you will
generally follow these steps in the order listed above for your first draft of a
worksheet. However, you will probably retrace steps such as editing and for-
matting as the final worksheet is developed.
During the planning phase, you have spoken with the Café manager, Evan,
regarding the purpose of the worksheet and the content in general. The pri-
mary purpose is to develop a forecast for sales and expenses for the next year.
First, Evan wants you to develop a worksheet for the first-quarter forecast
and then extend it by quarters for the year. After reviewing past budgets and
consulting with Evan, you have designed the basic layout for the first-quarter
forecast for the Café, as shown on the next page.

Exploring the Workbook Window EX1.11

ole31265_EXLab1_001-096.indd EX1.11 30/07/10 11:47 AM

 First Pages

Entering and Editing Data

Now that you understand the purpose of the worksheet and have a general
idea of the content, you are ready to begin entering the data. When you first
start Microsoft Excel 2010, a new blank Excel workbook file is opened con-
taining three blank worksheets. Each worksheet is like a blank piece of paper
that already has many predefined settings. These settings, called default set-
tings, are generally the most commonly used settings.
You will use the blank worksheet with the default settings to create the
worksheet for the Café.
As you can see, the budget you designed above contains both descriptive
text entries and numeric data. These are two types of data you can enter in a
worksheet.

Concept 1 Data

The basic information or data you enter in a cell can be text, numbers, dates, or times. Text entries can contain
any combination of letters, numbers, spaces, and any other special characters. Number entries can include only
the digits 0 to 9 and any of the special characters + − ( ) , . / $ % ? =. Number entries can be used in calculations.
Text and number entries generally appear in the cell exactly as they are entered. However, some entries such
as formulas direct Excel to perform a calculation on values in the worksheet. In these cases, the result of the
formula appears in the cell, not the formula itself. You will learn about formulas later in the lab.

Adding Text Entries

You enter data into a worksheet by moving to the cell where you want the
data displayed and typing the entry using the keyboard. First, you will enter
the worksheet headings. Row and column headings are entries that are used

EX1.12 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.12 30/07/10 11:47 AM

 First Pages

to create the structure of the worksheet and describe other worksheet entries.
Generally, headings are text entries. The column headings in this worksheet
consist of the three months (January through March) and a total (sum of
entries over three months) located in columns B through E. To enter data in a
worksheet, you must first select the cell where you want the entry displayed.
The column heading for January will be entered in cell B2.

1
• Click on cell B2 to Completes entry
move to it.

• Type January

Formula bar
Having Trouble? displays entry
Do not be concerned if you make a
typing error. You will learn how to Cancels entry
correct it shortly.

Your screen should be similar to

Active cell displays
Figure 1.7
entry and insertion point

Mode indicator

Figure 1.7

Several changes have occurred in the window. As you type, the entry is dis-
played both in the active cell and in the formula bar. An insertion point appears
in the active cell and marks your location in the entry. Two new buttons,
and , appear in the formula bar. They can be used with a mouse to cancel
your entry or complete it.
Notice also that the mode displayed in the status bar has changed from
Ready to Enter. This notifies you that the current mode of operation in the
worksheet is entering data.
Although the entry is displayed in both the active cell and the formula bar,
you need to press the E or t key, click , or click on any other cell

to complete your entry. If you press q or click , the entry is cleared and
nothing appears in the cell. Since your hands are already on the keyboard, it is
quicker to press E or t than it is to use the mouse.

Adding Text Entries EX1.13

ole31265_EXLab1_001-096.indd EX1.13 30/07/10 11:47 AM

 First Pages

2
• Press E.

Your screen should be similar to

Figure 1.8
Text entry is left-
aligned in cell space

Cell selector moves

down to next cell after
pressing E

Figure 1.8

The entry January is displayed in cell B2, and the mode has returned to Ready.
Additional Information In addition, the active cell is cell B3. Whenever you use the E key to
Pressing S + E to complete an entry, the cell selector moves down one cell.
complete an entry moves up a cell, and Notice that the entry is positioned to the left side of the cell space. This is
c + E completes the entry
without moving to another cell. one of the worksheet default settings.

CLEARING AN ENTRY
After looking at the entry, you decide you want the column headings to be in
row 3 rather than in row 2. This will leave more space above the column head-
ings for a worksheet title. The D key can be used to clear the contents from
a cell. You will remove the entry from cell B2 and enter it in cell B3.

1
• Move to B2.

• Press D.

• Move to B3.
Cell contents of

• Type January active cell displayed in

formula bar

• Click Enter.
Using to complete
entry leaves cell selector
Another Method in current cell location
You also can use Clear/Clear
Contents in the Editing group of the
Home tab.

Figure 1.9
Your screen should be similar to
Figure 1.9

The active cell does not change when you use to complete an entry.
Because the active cell contains an entry, the cell content is displayed in the
formula bar.

EX1.14 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.14 30/07/10 11:47 AM

 First Pages

EDITING AN ENTRY
Next, you decide to change the heading from January to JAN. An entry in a
cell can be entirely changed in the Ready mode or partially changed or edited
in the Edit mode. To use the Ready mode, you move to the cell you want to
change and retype the entry the way you want it to appear. As soon as a new
character is entered, the existing entry is cleared.
Generally, however, if you need to change only part of an entry, using the
Edit mode is quicker. To change to Edit mode, double-click on the cell whose
contents you want to edit.

1
• Double-click B3.

Having Trouble?
The mouse pointer must be when
you double-click on the cell. Mouse pointer

Another Method Insertion point

Pressing the 2 key also will change

to Edit mode. The insertion point is
positioned at the end of the entry.

Your screen should be similar to

Figure 1.10

Mode indicator

Figure 1.10

Having Trouble? The status bar shows that the new mode of operation is Edit. The insertion
Refer to the Entering and Editing Text point appears at the location you clicked in the entry, and the mouse pointer
and Selecting Text sections of the
Introduction to Office 2010 to review
changes to an I-beam when positioned on the cell. Now you can click again or
these features. use the directional keys to move the insertion point within the cell entry to the
location of the text you want to change.
After the insertion point is appropriately positioned, you can edit the entry
Additional Information by removing the incorrect characters and typing the correct characters. To do
You also can use c + D to this, you can use the B and D keys to delete text character by char-
delete everything to the right of the acter and enter the new text, or you can select the text to be changed and then
insertion point.
type the correction. You will change this entry to JAN.

Adding Text Entries EX1.15

ole31265_EXLab1_001-096.indd EX1.15 30/07/10 11:47 AM

 First Pages

2
• Select and delete
uary.

• Press h.
Edited entry
• Press S.
replaces
existing entry
• Press l.

• Press I.

• Type AN

• Press E.

Your screen should be similar to

Figure 1.11

Figure 1.11

The four characters at the end of the entry were deleted. Turning on the Caps
Additional Information
Lock feature produced the uppercase letters AN without having to hold down
The Caps Lock indicator light on your S. Finally, by pressing Insert, the program switched from inserting text
keyboard is lit when this feature is on.
to overwriting text as you typed. The insertion point changed to a highlight to
show that the character will be replaced.
The new heading JAN is entered into cell B3, replacing January. As you can
Additional Information
see, editing will be particularly useful with long or complicated entries.
Overwrite is automatically turned off Next, you will enter the remaining three headings in row 3. Because you
when you leave Edit mode or you press
I again. want to move to the right one cell to enter the next month label, you will
w
complete the entries using S or T.

EX1.16 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.16 30/07/10 11:48 AM

 First Pages

EDITING AN ENTRY
Next, you decide to change the heading from January to JAN. An entry in a
cell can be entirely changed in the Ready mode or partially changed or edited
in the Edit mode. To use the Ready mode, you move to the cell you want to
change and retype the entry the way you want it to appear. As soon as a new
character is entered, the existing entry is cleared.
Generally, however, if you need to change only part of an entry, using the
Edit mode is quicker. To change to Edit mode, double-click on the cell whose
contents you want to edit.

1
• Double-click B3.

Having Trouble?
The mouse pointer must be when
you double-click on the cell. Mouse pointer

Another Method Insertion point

Pressing the 2 key also will change

to Edit mode. The insertion point is
positioned at the end of the entry.

Your screen should be similar to

Figure 1.10

Mode indicator

Figure 1.10

Having Trouble? The status bar shows that the new mode of operation is Edit. The insertion
Refer to the Entering and Editing Text point appears at the location you clicked in the entry, and the mouse pointer
and Selecting Text sections of the
Introduction to Office 2010 to review
changes to an I-beam when positioned on the cell. Now you can click again or
these features. use the directional keys to move the insertion point within the cell entry to the
location of the text you want to change.
After the insertion point is appropriately positioned, you can edit the entry
Additional Information by removing the incorrect characters and typing the correct characters. To do
You also can use c + D to this, you can use the B and D keys to delete text character by char-
delete everything to the right of the acter and enter the new text, or you can select the text to be changed and then
insertion point.
type the correction. You will change this entry to JAN.

Adding Text Entries EX1.15

ole31265_EXLab1_001-096.indd EX1.15 30/07/10 11:47 AM

 First Pages

2
• Press E.

Your screen should be similar to

Figure 1.8
Text entry is left-
aligned in cell space

Cell selector moves

down to next cell after
pressing E

Figure 1.8

The entry January is displayed in cell B2, and the mode has returned to Ready.
Additional Information In addition, the active cell is cell B3. Whenever you use the E key to
Pressing S + E to complete an entry, the cell selector moves down one cell.
complete an entry moves up a cell, and Notice that the entry is positioned to the left side of the cell space. This is
c + E completes the entry
without moving to another cell. one of the worksheet default settings.

CLEARING AN ENTRY
After looking at the entry, you decide you want the column headings to be in
row 3 rather than in row 2. This will leave more space above the column head-
ings for a worksheet title. The D key can be used to clear the contents from
a cell. You will remove the entry from cell B2 and enter it in cell B3.

1
• Move to B2.

• Press D.

• Move to B3.
Cell contents of

• Type January active cell displayed in

formula bar

• Click Enter.
Using to complete
entry leaves cell selector
Another Method in current cell location
You also can use Clear/Clear
Contents in the Editing group of the
Home tab.

Figure 1.9
Your screen should be similar to
Figure 1.9

The active cell does not change when you use to complete an entry.
Because the active cell contains an entry, the cell content is displayed in the
formula bar.

EX1.14 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.14 30/07/10 11:47 AM

 First Pages

The two typing errors were automatically corrected as soon as you completed
a word by pressing space. If the entry was a single word, it would be checked
as soon as you completed the entry.
Next, the row headings need to be entered into column A of the worksheet.
The row headings and what they represent are shown in the following table.

Heading Description
Sales
Espresso Income from sales of espresso-based drinks
Coffee Income from drip coffee sales
Food/Beverage Income from sales of baked goods, sandwiches, and salads and
other beverages
Merchandise Income from sales of mugs, books, magazines, candy, etc.
Computer Income from computer rental usage, printing, copier use, and
MP3 downloads
Total Sales Sum of all sales
Expenses
Cost of Goods Cost of espresso, coffee, and food items sold
Cost of Merchandise Cost of merchandise other than food and beverage
Wages Manager and labor costs
Internet Wi-Fi access, MP3 kiosk rental, etc.
Building Lease, insurance, electricity, water, etc.
Capital Assets Equipment leases, interest, depreciation
Miscellaneous Maintenance, phone, office supplies, outside services, taxes, etc.
Income
Net Income Total sales minus total expenses
Profit Margin Net income divided by total sales

2
• Complete the row
headings for the
Sales portion of
the worksheet
by entering the
following headings in
the indicated cells. Row headings
Cell Heading
A3 Sales
A4 Espresso
A5 Coffee
A6 Food/
Beverage
A7 Merchandise
A8 Computer Figure 1.15
A9 Total Sales

Your screen should be similar to

Figure 1.15

Adding Text Entries EX1.19

ole31265_EXLab1_001-096.indd EX1.19 30/07/10 11:48 AM

 First Pages

ADDING NUMBER ENTRIES

Next, you will enter the expected Espresso sales numbers for January through
March into cells B4 through D4. As you learned earlier, number entries can
include the digits 0 to 9 and any of these special characters: +  − ( ) , . / $
% ? =. When entering numbers, it is not necessary to type the comma to sepa-
rate thousands or the currency ($) symbol. You will learn about adding these
symbols shortly.
You will enter the expected espresso sales for January first. Unlike text
entries, Excel displays number entries right-aligned in the cell space by default.

1
• Move to B4.

• Type 13300 and

press E.

• In the same manner,

enter the January Long text entries are cut
sales numbers for off because cell to the right
the remaining items contains an entry
using the values
shown below.
Cell Number
B5 5800
B6 3600
B7 1000
B8 600

Additional Information
You can use the number keys above the
alphabetic keys or the numeric keypad Figure 1.16
area to enter numbers. If you use the
numeric keypad, the n key
must be on.
After entering the numbers for January in column B, any long headings in col-
umn A are cut off or interrupted. Notice that the entries in cells A6 and A7 are
no longer completely displayed. They contain long text entries and because
Your screen should be similar to the cells to the right now contain an entry, the overlapping part of the entry is
Figure 1.16
shortened. However, the entire entry is fully displayed in the formula bar. Only
the display of the entry in the cell has been shortened.

Modifying Column Widths

To allow the long text entries in column A to be fully displayed, you can
increase the column’s width.

EX1.20 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.20 30/07/10 11:48 AM

 First Pages

Concept 3 Column Width

The column width is the size or width of a column and controls the amount of information that can be displayed
in a cell. A text entry that is larger than the column width will be fully displayed only if the cells to the right are
blank. If the cells to the right contain data, the text is interrupted. On the other hand, when numbers are entered
in a cell, the column width is automatically increased to fully display the entry.
The default column width setting is 8.43. The number represents the average number of digits that can be
displayed in a cell using the standard type style. The column width can be any number from 0 to 255. If it is set
to 0, the column is hidden.
When the worksheet is printed, it appears as it does currently on the screen. Therefore, you want to increase
the column width to display the largest entry. Likewise, you can decrease the column width when the entries in
a column are short.
There are several ways to change the column width. Using the mouse, you can change the width by dragging
the boundary of the column heading. You also can set the column width to an exact value or to automatically fit
the contents of the column.

DRAGGING THE COLUMN BOUNDARY

The column width can be quickly adjusted by dragging the boundary line
located to the right of the column letter. Dragging it to the left decreases the
column width, while dragging it to the right increases the width. As you drag,
a temporary column reference line shows where the new column will appear
and a ScreenTip displays the width of the column.

1
• Point to the boundary
line to the right of
the column letter A
and when the mouse
pointer changes to
, click and drag
the mouse pointer to
the right. Mouse pointer indicates
you can change the size of
• When the ScreenTip the column
displays 24.00,
release the mouse Column width increased
button. to 24 and fully displays row
headings

Your screen should be similar to

Figure 1.17

Figure 1.17

Now column A is more than wide enough to fully display all the row headings.

Modifying Column Widths EX1.21

ole31265_EXLab1_001-096.indd EX1.21 30/07/10 11:48 AM

 First Pages

USING A SPECIFIED VALUE

Next, you will reduce the width of column A to 15.

1
• Move to any cell in
column A.

• Click
in the Cells group
and choose Column
Width.

• Type 20 in the
Column width
text box and click
.
Column width
decreased to 20

Your screen should be similar to

Figure 1.18
Figure 1.18

Although this is close, you would like to refine it a little more.

Additional Information
You can quickly return the column
width to the default width setting USING AUTOFIT
using /Default Width. Another way to change the column width is to use the AutoFit feature to auto-
matically adjust the width to fit the column contents. When using AutoFit,
double-click the boundary to the right of the column heading of the column
you want to fit to contents.

1
• Double-click the
right boundary line
of column A.

Having Trouble?
Make sure the mouse pointer changes
to before you double-click on the
column boundary line.

Your screen should be similar to

Column width automatically
Figure 1.19
adjusted using AutoFit

Another Method
Figure 1.19
You also can use
/AutoFit Column Width. The column width is sized to just slightly larger than the longest cell contents.
You also can adjust the height of a row using the same procedures you used to
adjust the column width.

EX1.22 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.22 30/07/10 11:48 AM

 First Pages

Saving, Closing, and Opening a Workbook File

You have a meeting you need to attend shortly, so you want to save the work
Having Trouble? you have completed so far on the workbook to a file and then close the file.
Refer to the section Saving a File in the You will name the file Cafe Forecast and use the default file type settings of
Introduction to Office 2010 to review Excel Workbook (*.xlsx). The file extension .xlsx identifies the file as an Excel
this feature.
2007 or 2010 workbook. The default file type saves the workbook file in XML
(Extensible Markup Language) format.
Excel 2003 and earlier versions used the .xls file extension. If you plan to
share a file with someone using Excel 2003 or earlier, you can save the file
using the .xls file type; however, some features may be lost. Otherwise, if you
save it as an .xlsx file type, the recipient may not be able to view all the features.

1 New file name

• Click Save in the

Quick Access Toolbar.

• Select the location

where you want to
save your file.

• Click in the File Name

text box to highlight
the proposed file
name, or if necessary
triple-click on the file
name to select it.

• Type Cafe
Forecast

• Click
or press E.

Your screen should be similar to

Figure 1.20
Figure 1.20

Additional Information
The new file name is displayed in the application window title bar. The work-
sheet data that was on your screen and in the computer’s memory is now saved
The file name in the title bar may
display the workbook file extension, at the location you specified in a new file called Cafe Forecast.
.xlsx, depending on your Windows You are now ready to close the workbook file.
Folder settings.

2
• Open the File tab and click .

Saving, Closing, and Opening a Workbook File EX1.23

ole31265_EXLab1_001-096.indd EX1.23 30/07/10 11:48 AM

 First Pages

Because you did not make any changes to the workbook after saving it, the
Additional Information
workbook file is closed immediately and the Excel window displays an empty
Opening the File tab and choosing
New allows you to open a new blank
workbook window. If you had made changes to the file before closing it, you
w
workbook file or use a template. would have been prompted to save the file to prevent the accidental loss of
w
Choosing Recent allows you to open a data.
recently used file by selecting it from
the list of file names.
After attending your meeting, you continued working on the Café forecast.
To see what has been done so far, you will open the workbook file named
ex01_CafeForecast1.
Having Trouble?
Refer to the Opening a File section in
the Introduction to Office 2010 to review
this feature.

3
• Open the File
tab and click Name of open

. workbook

Another Method
The keyboard shortcut is c + O.

• Select the location

containing your data
files.

• Select ex01_Cafe
Forecast1.

• Click .

Another Method
You also could double-click the file
name to both select it and choose
Figure 1.21
.

The workbook is opened and displayed in the workbook window. The work-
• If necessary, book contains the additional sales values for February and March, the expense
maximize the row headings, and several of the expense values for the month of January.
workbook window.

Your screen should be similar to

Figure 1.21

Using Proofing Tools

When entering information into a worksheet, you are likely to make spelling
and typing errors. To help locate and correct these errors, the spelling checker
feature can be used. Additionally, you may find that the descriptive headings
you have entered may not be exactly the word you want. The thesaurus can
suggest better words to clarify the meaning of the worksheet.
EX1.24 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.24 30/07/10 11:48 AM

 First Pages

CHECKING SPELLING
In your rush to get the row headings entered you realize you misspelled a few
words. For example, the Expenses label is spelled “Espenses.” Just to make
sure there are no other spelling errors, you will check the spelling of all text
entries in this worksheet.

Concept 4 Spelling Checker

The spelling checker locates misspelled words, duplicate words, and capitalization irregularities in the active
worksheet and proposes the correct spelling. This feature works by comparing each word to a dictionary of
words, called the main dictionary, that is supplied with the program. You also can create a custom dictionary
to hold words you commonly use but that are not included in the main dictionary. If the word does not appear in
the main dictionary or in a custom dictionary, it is identified as misspelled.
When you check spelling, the contents of all cell entries in the entire active sheet are checked. If you are in
Edit mode when you check spelling, only the contents of the text in the cell are checked. The Spell Checker does
not check spelling in formulas or in text that result from formulas.

Excel begins checking all worksheet entries from the active cell forward.

1
• If necessary, move to Starts spelling checker
A1.

• Open the Review tab.

• Click in the

Proofing group.

Another Method
The keyboard shortcut is 7. Changes
misspelled word to
selected choice from
Suggested replacements
Located misspelled Suggestions list
Your screen should be similar to
Figure 1.22 word

Figure 1.22

The spelling checker immediately begins checking the worksheet for words
Additional Information that it cannot locate in its main dictionary. The first cell containing a mis-
Spell checking operates the same spelled word, in this case Espenses, is now the active cell and the Spelling dia-
way in all Office 2010 programs. The
dictionaries are shared between Office
log box is displayed. The word it cannot locate in the dictionary is displayed in
applications. the Not in Dictionary text box. The Suggestions text box displays a list of pos-
sible replacements. If the selected replacement is not correct, you can select
Using Proofing Tools EX1.25

ole31265_EXLab1_001-096.indd EX1.25 30/07/10 11:48 AM

 First Pages

another choice from the suggestions list or type the correct word in the Not in
Dictionary text box.
The option buttons shown in the table below have the following effects:

Option Effect
Ignore Once Leaves selected word unchanged
Ignore All Leaves this word and all identical words in worksheet unchanged
Add to Dictionary Adds selected word to a custom dictionary so Excel will not question this word during
subsequent spell checks
Change Changes selected word to word highlighted in Suggestions box
Change All Changes this word and all identical words in worksheet to word highlighted
in Suggestions box
AutoCorrect Adds a word to the AutoCorrect list so the word will be corrected as you type

You want to accept the suggested replacement, Expenses.

2
• Click .

Your screen should be similar to

Figure 1.23 Spelling error
corrected

Next spelling
error located

Figure 1.23

The correction is made in the worksheet, and the program continues checking
the worksheet and locates another error, Miscelaneous. You will make this cor-
rection. When no other errors are located, a dialog box is displayed, informing
you that the entire worksheet has been checked.

3
• Change this word to Miscellaneous.

• Click to end spell checking.

The worksheet is now free of spelling errors.

EX1.26 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.26 30/07/10 11:48 AM

 First Pages

USING THE THESAURUS

The next text change you want to make is to find a better word for “Wage” in
cell A14. To help find a similar word, you will use the thesaurus tool.

Concept 5 Thesaurus

The thesaurus is a reference tool that provides synonyms, antonyms, and related words for a selected word
or phrase. Synonyms are words with a similar meaning, such as “cheerful” and “happy.” Antonyms are words
with an opposite meaning, such as “cheerful” and “sad.” Related words are words that are variations of the
same word, such as “cheerful” and “cheer.” The thesaurus can help to liven up your documents by adding
interest and variety to your text.

To use the thesaurus, first move to the cell containing the word you want to
change. If a cell contains multiple words, you need to select the individual
word in the cell.

1
• Move to A13.

• Click in the

Proofing group.

Another Method
You also can hold down a while
clicking on the cell containing the
word you want looked up to access the Word to be
Thesaurus in the Research task pane. replaced
Suggested synonyms
for the word “wage”

Your screen should be similar to

Figure 1.24
Click the heading word
to hide list

Figure 1.24

T Research task pane opens and the word in the active cell, Wage, is entered
The
Additional Information in the Search for text box and the list box displays words in the Thesaurus that
Clicking on the word is the same as have similar meanings for this word. The list contains synonyms for “wage”
using the Lookup menu option.
used as a noun or as a verb. The first word at the top of each group is the
u
group heading and is closest in meaning. It is preceded with a
g symbol and
the word is bold. The indicates the list of synonyms is displayed. Clicking
the heading word will hide the list of synonyms.

Using Proofing Tools EX1.27

ole31265_EXLab1_001-096.indd EX1.27 30/07/10 11:48 AM

 First Pages

When you point to a word in the list, a drop-down list of three menu options,
Insert, Copy, and Lookup, becomes available. The Insert option inserts the
word into the active cell. The Copy option is used to copy and then paste the
word into any worksheet cell. The Lookup option displays additional related
words for the current word. You decide to use the word “Pay” and will insert
the word into cell A13 in place of “Wage.”

2
• Point to “Py” and
Click to display
the menu.

• Choose Insert.

• Click in the title

bar of the Research
task pane to close it.
“Wage” replaced with selected

Your screen should be similar to word from thesaurus

Figure 1.25

Figure 1.25

The word “Wage” is replaced with the selected word from the thesaurus. Notice
the replacement word is capitalized correctly. This is because the replacement
text follows the same capitalization as the word it replaces.

Copying and Pasting Cell Contents

Next, you want to enter the estimated expenses for salary, computers, lease,
Having Trouble? and miscellaneous for February and March. They are the same as the January
Refer to the section Copying and expense numbers. Because these values are the same, instead of entering the
Moving Selections in the Introduction
to Microsoft Office 2010 to review this same number repeatedly into each cell you can quickly copy the contents of
feature. one cell to another. You also want to move information from one location in
the worksheet to another.

COPYING AND PASTING DATA

To use the Copy command, you first must select the cell or cells in the source
containing the data to be copied. You will copy the Pay value in cell B13 into
cells C13 and D13.

EX1.28 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.28 30/07/10 11:48 AM

 First Pages

1
• Move to B13.

• Open the Home tab.

• Click Copy in the Copies selection

Clipboard group.

Another Method
The shortcut key is c + C. Copy is
also available on the context menu. Moving border indicates the
source has been copied

Your screen should be similar to

Figure 1.26
Status bar displays instructions
on how to complete command

Figure 1.26

A moving border identifies the source and indicates that the contents have
been copied to the system Clipboard. The instructions displayed in the status
bar tell you to select the destination where you want the contents copied. You
will copy it to cell C13.

2
• Move to C13. Pastes contents of
system Clipboard

• Click the top part of

the button.

Additional Information

The button is a split button.

Clicking the top part of the button

pastes using the default settings. Paste Options button
Clicking the lower part displays a
menu of options.

Contents from B13

Another Method copied into C13
The shortcut key is c + V. Paste is
also available on the context menu and

on the button’s drop-down menu.

Figure 1.27

Your screen should be similar to

Figure 1.27
Copying and Pasting Cell Contents EX1.29

ole31265_EXLab1_001-096.indd EX1.29 30/07/10 11:48 AM

 First Pages

The contents of the system Clipboard are inserted at the specified destination
Additional Information
You will learn about the different Excel location. Each time the Paste command is used, the Paste Options
paste options in later labs.
button is available. Clicking on the button opens the Paste Options menu that
allows you to control how the information you are pasting is inserted. Be care-
ful when pasting to the new location because any existing entries are replaced.
The moving border is still displayed, indicating the system Clipboard still
contains the copied entry. Now you can complete the data for the Pay row by
pasting the value again from the system Clipboard into cell D13. While the
moving border is still displayed, you also can simply press E to paste.
However, as this method clears the contents of the system Clipboard immedi-
ately, it can only be used once.

3
• Move to D13.

• Press E.

Your screen should be similar to

Figure 1.28

Moving cell border cleared

Contents of
system Clipboard
pasted into D13

Figure 1.28

The contents of the system Clipboard are inserted at the specified destination
Additional Information location and the moving border is cleared, indicating the system Clipboard is
You also can cancel a moving border empty.
and clear the system Clipboard contents
by pressing q.
SELECTING A RANGE
Now you need to copy the Internet value in cell B14 to February and March.
You could copy and paste the contents individually into each cell as you did
with the Pay values. A quicker method, however, is to select a range and paste
the contents to all cells in the range at once.

EX1.30 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.30 30/07/10 11:48 AM

 First Pages

Concept 6 Range

A selection consisting of two or more cells on a worksheet is a range. The cells in a range can be adjacent
or nonadjacent. An adjacent range is a rectangular block of adjoining cells. A nonadjacent range consists
of two or more selected cells or ranges that are not adjoining. In the example shown below, the shaded areas
show valid adjacent and nonadjacent ranges. A range reference identifies the cells in a range. A colon is used
to separate the first and last cells of an adjacent range reference. For example, A2:C4 indicates the range
consists of cells A2 through C4. Commas separate the cell references of a nonadjacent range. For example,
A10,B12,C14 indicates the range consists of cells A10, B12, and C14 of a nonadjacent range.

E1:E15
adjacent range

A2:C4
adjacent range

A7:H7
adjacent range

A10,
B12, C14
nonadjacent
range

You can select a range using the mouse procedures shown in the following
Additional Information table. You also can select using the keyboard by moving to the first cell of the
Selecting a range identifies the cells to range, holding down S or pressing 8 and using the navigational keys to
be included in the selection, rather than
the specific text within the cells.
expand the highlight. Using the 8 key turns on and off Extend mode. When
this mode is on, Extend Selection appears in the status bar.

To Select Mouse
A range Click first cell of range and drag to the last cell.
A large range Click first cell of range, hold down S, and click last cell of range.

All cells on worksheet Click the All button located at the intersection of the row and
column headings.
Nonadjacent cells or ranges Select first cell or range, hold down c while selecting the other cell
or range.
Entire row or column Click the row number or column letter heading.
Adjacent rows or columns Drag across the row number or column letter headings.
Nonadjacent rows or columns Select first row or column, hold down c, and select the other rows
or columns

Copying and Pasting Cell Contents EX1.31

ole31265_EXLab1_001-096.indd EX1.31 30/07/10 11:48 AM

 First Pages

To complete the data for the Internet row, you want to copy the value in cell
B14 to the system Clipboard and then copy the system Clipboard contents to
the adjacent range of cells C14 through D14.

1
• Move to B14.

• Click Copy.

• Drag to select the

range of cells C14
through D14.

• Click .

Your screen should be similar to

Figure 1.29

Entry in copy area is

pasted into
destination range

Figure 1.29

Information about contents of

selected range

The destination range is highlighted and identified by a dark border surround-

Additional Information ing the selected cells. The entry copied from cell B14 was pasted into the
The paste area does not have to be selected destination range. Also notice the status bar now displays the average,
adjacent to the copy area. count, and sum of values in the selected range.

USING THE FILL HANDLE

Next, you will copy the January Building expenses to cells C15 through D15,
the Capital Assets expenses to cells C16 through D16, and the Miscellaneous
expenses to cells C17 through D17. You can copy all values at the same time
across the row by first specifying a range as the source. Another way to copy is
to drag the fill handle, the black box in the lower-right corner of a selection.

EX1.32 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.32 30/07/10 11:48 AM

 First Pages

1
• Press q to clear
the moving border.

• Drag to select cells

B15 through B17.

• Point to the fill handle

and when the mouse
pointer is a , drag Fill handle used to copy source
the mouse to extend
the selection to cells
D15 through D17.

AutoFill options
button

• Release the mouse

button.

Another Method
Figure 1.30
You also can select the range B15:D17,
Click Fill in the Editing group
and choose Right. The shortcut key is
c + R.

Your screen should be similar to

Figure 1.30
The range of cells to the right of the source is filled with the same values as
in the source range. The Fill Series command does not copy the source to the
system Clipboard and therefore you cannot paste the source multiple times.
Additional Information When you copy by dragging the fill handle, the AutoFill Options button
You will learn more about the AutoFill appears. Its menu commands are used to modify how the fill operation was
feature in later labs.
performed. It will disappear as soon as you make an entry in the worksheet.

INSERTING COPIED CELL CONTENT

You also decide to include another row of month headings above the expenses
to make the worksheet data easier to read. To do this quickly, you can insert
copied data between existing data. To indicate where to place the copied con-
tent, you move the cell selector to the upper-left cell of the area where you
want the selection inserted.
The column headings you want to copy are in cells B3 through E3. You will
also copy cell A3, and clear the text in column A of the new row when you
paste the contents.

Copying and Pasting Cell Contents EX1.33

ole31265_EXLab1_001-096.indd EX1.33 30/07/10 11:48 AM

 First Pages

1 Inserts copied

• Copy the contents of selection

cells A3 through E3.

• Move to A10.

• Click in
the Cells group.

• Select cell A10 and

delete the word
“Sales.” Copied data

Your screen should be similar to

Figure 1.31

Inserted row with

copied data and “Sales”
deleted from cell A10

Figure 1.31

Additional Information
The copied data is inserted into the existing row (10) and all entries below are
moved down one row.
You also can insert cut selections
between existing cells by
choosing Insert Cut Cells from the CUTTING AND PASTING DATA
drop-down menu. Next, you decide the Income, Net Income, and Profit Margin rows of data
would stand out more if a blank row separated them from the expenses. Also,
the Profit Margin row of data would be better separated from the Net Income
row by a blank row. You will first remove the cell contents of the three cells
using Cut and then paste the contents from the system Clipboard into the
new location. The pasted content will copy over any existing content. You will
use the keyboard shortcuts for these commands to complete this process.

EX1.34 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.34 30/07/10 11:48 AM

 First Pages

1
• Select cells A20
through A22.

• Press c + x.

• Move to cell A21.

• Press c + V.

Another Method

You also can Click Cut followed

by in the Clipboard group. These

commands are also available on the Selected cells cut and then
shortcut menu. pasted to a new location

Your screen should be similar to

Figure 1.32
Figure 1.32

The contents of the three selected cells are copied to the system Clipboard.
Then, when you paste, the cell contents are removed and inserted at the new
location, copying over any existing content.
Another way you can cut and paste is to use drag and drop to move the cell
contents. This method is quickest and most useful when the distance between
cells is short and they are visible within the window, whereas cut and paste
is best for long-distance moves. You will use this method to move the Profit
Margin entry down one cell.

Copying and Pasting Cell Contents EX1.35

ole31265_EXLab1_001-096.indd EX1.35 30/07/10 11:48 AM

 First Pages

2 New file name

• Move to cell A23.

• Point to the border

of the selection and
when the mouse
pointer shape
is , drag the
selection down one
row to cell A24 and
release the mouse
button.

Additional Information
As you drag, an outline of the cell
selection appears and the mouse
pointer displays the cell reference to
show its new location in the worksheet. Cell entry moved
using drag and drop

• Open the File

tab and click Drag and drop
. mouse pointer

• Save the changes

you have made to the Figure 1.33
workbook as Café
Forecast1 to your
solution file location.

Your screen should be similar to

Figure 1.33

The cell contents were moved into cell A24 and cleared from the original cell.
Additional Information When you use the Copy and Cut commands, the contents are copied to the
You also can hold down c and drag system Clipboard and can be copied to any location in the worksheet, another
a selection to copy it to a new location.
workbook, or a document in another application multiple times. When you
w
The mouse pointer appears as as
you drag when copying.
use Fill or drag the fill handle, the destination must be in the same row
or column as the source, and the source is not copied to the system Clipboard.
Dragging the cell border to move or copy also does not copy the source to the
system Clipboard.
NOTE If you are running short on lab time, this is an appropriate place to end your session.

Working with Formulas

The remaining entries that need to be made in the worksheet are formula
entries.

EX1.36 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.36 30/07/10 11:48 AM

 First Pages

Concept 7 Formula

A formula is an equation that performs a calculation on data contained in a worksheet. A formula always begins
with an equal sign (=) and uses arithmetic operators. An operator is a symbol that specifies the type of numeric
operation to perform. Excel includes the following operators: + (addition), − (subtraction), / (division),
* (multiplication), % (percent), and ^ (exponentiation). The calculated result from formulas is a variable value
because it can change if the data it depends on changes. In contrast, a number entry is a constant value. It does
not begin with an equal sign and does not change unless you change it directly by typing in another entry.
In a formula that contains more than one operator, Excel calculates the formula from left to right and
performs the calculation in the following order: percent, exponentiation, multiplication and division, and addition
and subtraction (see Example A). This is called the order of precedence. If a formula contains operators with
the same precedence (for example, addition and subtraction), they are again evaluated from left to right. The
order of precedence can be overridden by enclosing the operation you want performed first in parentheses (see
Example B). When there are multiple sets of parentheses, Excel evaluates them working from the innermost set
of parentheses out.
Example A: =5*4−3 Result is 17 (5 times 4 to get 20, and then subtract 3 for a total of 17)
Example B: =5*(4−3) Result is 5 (4 minus 3 to get 1, and then 1 times 5 for a total of 5)
The values on which a numeric formula performs a calculation are called operands. Numbers or cell references
can be operands in a formula. Usually cell references are used, and when the numeric entries in the referenced
cell(s) change, the result of the formula is automatically recalculated.

ENTERING FORMULAS
The first formula you will enter will calculate the total Espresso sales for Janu-
ary through March (cell E4) by summing the numbers in cells B4 through D4.
You will use cell references in the formula as the operands and the + arithmetic
operator to specify addition. A formula is entered in the cell where you want
the calculated value to be displayed. As you enter the formula, Excel helps
you keep track of the cell references by identifying the referenced cell with a
colored border and using the same color for the cell reference in the formula.

1
• Move to E4.

• Type =b

Your screen should be similar to

Figure 1.34

Drop-down list of function

names that begin with the
letter “b”

Figure 1.34

A drop-down list of function names that begin with the letter “b” are displayed.
Functions are a type of formula entry that you will learn about shortly.

Working with Formulas EX1.37

ole31265_EXLab1_001-096.indd EX1.37 30/07/10 11:48 AM

 First Pages

2
• Type 4+c4+d4

Additional Information
Cell references can be typed in either
uppercase or lowercase letters. Spaces
between parts of the formula are
optional. Formula cell
references are color
coded

Your screen should be similar to

Figure 1.35 Each referenced
cell is surrounded by a
colored box

Figure 1.35

As you enter the formula, each cell that is referenced in the formula is
surrounded by a colored box that matches the color of the cell reference in the
formula.

3
• Press c + E
or Click Enter in
the Formula bar. Formula displayed in
formula bar

Your screen should be similar to

Figure 1.36

Result of formula
displayed in cell

Figure 1.36

The number 41100 is displayed in cell E4, and the formula that calculates this
value is displayed in the formula bar.

COPYING FORMULAS WITH RELATIVE REFERENCES

The formulas to calculate the total sales for rows 5 through 8 can be entered
next. Just as you can with text and numeric entries, you can copy formulas
from one cell to another.

EX1.38 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.38 30/07/10 11:48 AM

 First Pages

1
• Copy the formula in
cell E4 to cells E5
through E8 using
any of the copying
methods.
Cell references in the copied formula
• Move to E5.
were adjusted relative to new location
of formula in worksheet
• If necessary, press
q to clear the
moving border.

Your screen should be similar to

Figure 1.37

Figure 1.37

The calculated result, 18000, is displayed in the cell. The formula displayed in
the formula bar is =B5+C5+D5. The formula to calculate the Coffee total sales
is not an exact duplicate of the formula used to calculate the Espresso total
sales (=B4+C4+D4). Instead, the cells referenced in the formula have been
changed to reflect the new location of the formula in row 5. This is because
the references in the formula are relative references.

Concept 8 Relative Reference

A relative reference is a cell or range reference in a formula whose location is interpreted by Excel in
relation to the position of the cell that contains the formula. When a formula is copied, the referenced cells in
the formula automatically adjust to reflect the new worksheet location. The relative relationship between the
referenced cell and the new location is maintained. Because
relative references automatically adjust for the new location, the
relative references in a copied formula refer to different cells
than the references in the original formula. The relationship
between cells in both the copied and the pasted formulas is the
same although the cell references are different.
For example, in the figure here, cell A1 references the value in
cell A4 (in this case, 10). If the formula in A1 is copied to B2, the
reference for B2 is adjusted to the value in cell B5 (in this case, 20).

Working with Formulas EX1.39

ole31265_EXLab1_001-096.indd EX1.39 30/07/10 11:48 AM

 First Pages

2
• Move to cell E6, E7,
and then to cell E8.

Your screen should be similar to

Figure 1.38

Cell references in formula

adjusted when formula copied

Figure 1.38

The formulas in these cells also have changed to reflect the new row location
and to appropriately calculate the total based on the sales.

SUMMARIZING DATA
Next, you will calculate the monthly total sales. The formula to calculate the
total sales for January needs to be entered in cell B9 and copied across the
row. You could use a formula similar to the formula used to calculate the cat-
egory sales in column E. The formula would be =B4+B5+B6+B7+B8. However,
it is faster and more accurate to use a function.

Concept 9 Function

A function is a prewritten formula that performs certain types of calculations automatically. The syntax or rules
of structure for entering all functions is
=Function name (argument1, argument2, . . .)
The function name identifies the type of calculation to be performed. Most functions require that you enter one
or more arguments following the function name. An argument is the data the function uses to perform the
calculation. The type of data the function requires depends upon the type of calculation being performed. Most
commonly, the argument consists of numbers or references to cells that contain numbers. The argument is
enclosed in parentheses, and commas separate multiple arguments. The beginning and ending cells of a range
are separated with a colon.
Some functions, such as several of the date and time functions, do not require an argument. However, you
still need to enter the opening and closing parentheses; for example, =NOW( ). If a function starts the formula,
enter an equal sign before the function name; for example, =SUM(D5:F5)/25.

EX1.40 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.40 30/07/10 11:48 AM

 First Pages

Excel includes several hundred functions divided into 11 categories. Some

Additional Information
common functions from each category and the results they calculate are
Use Help for detailed explanations of
every function.
shown in the following table.

Category Function Calculates

Financial PMT Calculates the payment for a loan based on constant
payments and a constant interest rate
PV Returns the present value of an investment—the total
amount that a series of future payments is worth now
FV Returns the future value of an investment—the total
amount that a series of payments will be worth
Date & Time TODAY Returns the serial number that represents today’s date
DATE Returns the serial number of a particular date
NOW Returns the serial number of the current date and time
Math & Trig SUM Adds all the numbers in a range of cells
ABS Returns the absolute value of a number (a number
without its sign)
Statistical AVERAGE Returns the average (arithmetic mean) of its arguments
MAX Returns the largest value in a set of values; ignores
logical values and text
MIN Returns the smallest value in a set of values; ignores
logical values and text
COUNT Counts the number of cells in a range that contain
numbers
COUNTA Counts the number of cells in a range that are not empty
COLUMNS Returns the number of columns in an array or reference
Lookup & Reference HLOOKUP Looks for a value in the top row of a table and returns
the value in the same column from a row you specify
VLOOKUP Looks for a value in the leftmost column of a table and
returns the value in the same row from a column you
specify
Database DSUM Adds the numbers in the field (column) or records in the
database that match the conditions you specify
DAVERAGE Averages the values in a column in a list or database that
match conditions you specify
Text PROPER Converts text to proper case in which the first letter of
each word is capitalized
UPPER Converts text to uppercase
LOWER Converts text to lowercase
SUBSTITUTE Replaces existing text with new text in a text string
Logical IF Returns one value if a condition you specify evaluates to
TRUE and another value if it evaluates to FALSE
AND Returns TRUE if all its arguments are TRUE; returns
FALSE if any arguments are FALSE
OR Returns TRUE if any arguments are TRUE; returns
FALSE if all arguments are FALSE
NOT Changes FALSE to TRUE or TRUE to FALSE
IFERROR Returns value-if-error if expression is an error and the
value of the expression itself otherwise
Information ISLOGICAL Returns TRUE if value is a logical value, either TRUE
or FALSE
ISREF Returns TRUE if value is a reference
Engineering BIN2DEC Converts a binary number to decimal
CONVERT Converts a number from one measurement system
to another
Cube CUBESETCOUNT Returns the number of items in a set

You will use the SUM function to calculate the total sales for January. Because
the SUM function is the most commonly used function, it has its own
command button.
Working with Formulas EX1.41

ole31265_EXLab1_001-096.indd EX1.41 30/07/10 11:48 AM

 First Pages

1
Function name Range argument
• Move to B9.

• Click Sum in
the Editing group.

Another Method Moving border

Pressing a + = is the keyboard identifies proposed
shortcut for Sum. This function is also
range to sum
available on the Formulas tab.

Your screen should be similar to

Figure 1.39

Additional Information
Figure 1.39
The Sum button also can
calculate a grand total if the worksheet
contains subtotals. Select a cell below
or to the right of a cell that contains a
Excel automatically proposes a range based upon the data above or to the left
of the active cell. The formula bar displays the name of the function followed
subtotal and then Click Sum.
by the range argument enclosed in parentheses. You will accept the proposed
range and enter the function.

2
• Click Enter.

Your screen should be similar to

Figure 1.40

Calculated result of function

Figure 1.40

The result, 24300, calculated by the SUM function is displayed in cell B9. Next
you need to calculate the total sales for February and March and the Total
column.

EX1.42 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.42 30/07/10 11:48 AM

 First Pages

3
• Copy the function
from cell B9 to cells
C9 through E9.

• Move to C9.

Range references adjusted

Your screen should be similar to relative to new location of
Figure 1.41
function in worksheet

Figure 1.41

The result calculated by the function, 25100, is displayed in cell C9 and the cop-
ied function is displayed in the formula bar. The range reference in the func-
tion is adjusted relative to its new cell location because it is a relative reference.
You also decide to calculate the minimum, maximum, and average sales
for each sales category. You will add appropriate column headings and enter
the functions in columns F, G, and H. The Sum button also includes a
drop-down menu from which you can select several other common functions.
As you enter these functions, the proposed range will include the Total cell.
Simply select another range to replace the proposed range.

4
• Enter MIN in cell F3,
MAX in cell G3, and
AVG in cell H3.

• Move to F4.
Calculated result of function

• Open the Sum

drop-down menu and
choose Min.

Having Trouble?
Click to the right of the button to
open the drop-down menu.

• Select the range B4

Figure 1.42
through D4 to specify
the January through The MIN function correctly displays 13300, the smallest value in the range.
March sales values Next you will enter the MAX and AVG values for the Espresso sales. Then
and Click Enter. you will copy the functions down the column through row 8.

Your screen should be similar to

Figure 1.42
Working with Formulas EX1.43

ole31265_EXLab1_001-096.indd EX1.43 30/07/10 11:48 AM

 First Pages

5
• Enter the MAX
function in cell G4
and the AVG function
in cell H4 to calculate
the Espresso sales
values for January
through March. Cell references adjusted
when functions copied
• Copy the functions in
cells F4 through H4 to
F5 through H8.

• Move to H8.

Your screen should be similar to

Figure 1.43

Figure 1.43

The minimum, maximum, and average values for the five sales categories have
been calculated. The Average column displays as many decimal places as cell
space allows.

USING POINTING TO ENTER A FORMULA

Next, you will enter the formula to calculate the cost of goods for espresso,
coffee, and food and beverages sold. These numbers are estimated by using a
formula to calculate the number as a percent of sales. Evan suggested using
estimated percents for this worksheet so he could get an idea of what to
expect from the first three months after the remodel. He wants you to cal-
culate espresso expenses at 25 percent of espresso sales, coffee expenses at
30 percent of coffee sales, and food and beverage expenses at 60 percent of
food sales.
Rather than typing in the cell references for the formula, you will enter
them by selecting the worksheet cells. In addition, to simplify the process of
entering and copying entries, you can enter data into the first cell of a range
and have it copied to all other cells in the range at the same time by using
c + E to complete the entry. You will use this feature to enter the for-
mulas to calculate the beverage expenses for January through March. This
formula needs to calculate the beverage cost of goods at 25 percent first and
add it to the food cost of goods calculated at 50 percent.

EX1.44 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.44 30/07/10 11:48 AM

 First Pages

1
• Select B12 through
D12.

• Type =
Cell reference of selected cell
is entered in formula
• Click cell B4.

Additional Information Moving border surrounds

selected cell
Even when a range is selected, you can
still point to specify cells in the formula.
You also can use the direction keys to
move to the cell.
Preselected range

Your screen should be similar to

Figure 1.44

Mode indicator

Figure 1.44

Additional Information
Notice that the status bar displays the current mode as Point. This tells you
While entering the formula in Point
mode, if you make an error, edit the that the program is allowing you to select cells by highlighting them. The cell
entry like any other error and then reference, B4, is entered following the = sign. You will complete the formula by
continue entering the remainder of the entering the percentage value to multiply by and adding the Food percentage
formula.
to the formula.

2
• Type *25%+

• Click on B5.

• Type *30%+

• Click on B6.

• Type *60%
Formula entered into
• Press c + E. all cells of range

Your screen should be similar to

Figure 1.45

Figure 1.45

Working with Formulas EX1.45

ole31265_EXLab1_001-096.indd EX1.45 30/07/10 11:48 AM

 First Pages

The formula to calculate the January cost of goods expense was entered in cell
Having Trouble?
B12 and copied to all cells of the selected range.
If you made an error in the formula, edit
the entry in the formula bar and then
Now you will enter the cost of merchandise by multiplying the value in B8
press c + E again to copy it by 70%. Then you will calculate the total expenses in row 19 and column E.
to the selected range.
To do this quickly, you will preselect the range and use the Sum button.
Then you will enter the formula to calculate the net income. Net income is
calculated by subtracting total expenses from total sales.

3
• Select cells B13
through D13.

• Type =

• Click on B7.

• Type *70%

• Press c + E.

• Select B12 through

E19.

• Click Sum. Total expenses calculated

• Select B22 through

Formula entered into
E22.
all cells of range

• Enter the formula

=B9-B19
Figure 1.46
• Press c + E.

The formulas were quickly entered into all cells of the specified ranges.
Your screen should be similar to Finally, you will enter the formula to calculate the profit margin. Profit
Figure 1.46 margin is calculated by dividing net income by total sales.

EX1.46 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.46 30/07/10 11:49 AM

 First Pages

4
• Select B24 through
E24.

• Enter the formula

=B22/B9

• Press c + E.

Your screen should be similar to

Figure 1.47

Formula entered into

all cells of range

Figure 1.47

The net income and profit margins are calculated and displayed in the
worksheet.

RECALCULATING THE WORKSHEET

Now that you have created the worksheet structure and entered some sample
data for the forecasted sales for the first quarter, you want to test the formulas
to verify that they are operating correctly. A simple way to do this is to use a
calculator to verify that the correct result is displayed. You can then further
test the worksheet by changing values and verifying that all cells containing
formulas that reference the value are appropriately recalculated.

Concept 10 Recalculation

When a number in a referenced cell in a formula changes, Excel automatically recalculates all formulas that
are dependent upon the changed value. Because only those formulas directly affected by a change in the data
are recalculated, the time it takes to recalculate the workbook is reduced. Without this feature, in large work-
sheets it could take several minutes to recalculate all formulas each time a number is changed in the work-
sheet. Recalculation is one of the most powerful features of electronic worksheets.

After considering the sales estimates for the three months, you decide that the
estimated sales generated from Computer usage for January are too high and
you want to decrease this number from 600 to 400.

Working with Formulas EX1.47

ole31265_EXLab1_001-096.indd EX1.47 30/07/10 11:49 AM

 First Pages

1
• Change the entry in
cell B8 to 400

Your screen should be similar to

Figure 1.48

All dependent formulas have

been automatically recalculated

Figure 1.48

The Computer total in cell E8 has been automatically recalculated. The num-
ber displayed is now 1600. The MIN and AVG values in cells F8 and H8 have
been recalculated to 400 and 533.3333 respectively. Likewise, the January total
in cell B9 of 24100 and the grand total in cell E9 of 75100 each decreased by
200 from the previous totals to reflect the change in cell B8. Finally, the Net
Income and Profit Margin values also have adjusted appropriately.
The formulas in the worksheet are correctly calculating the desired result.
The Sales portion of the worksheet is now complete.

Inserting and Deleting Rows and Columns

As you are developing a worksheet, you may realize you forgot to include
information or decide that other information is not needed. To quickly add
and remove entire rows and columns of information, you can insert and delete
rows and columns. A new blank row is inserted above the active cell location
and all rows below it shift down a row. Similarly, you can insert blank cells
and columns in a worksheet. Blank cells are inserted above or to the left of the
active cell and blank columns are inserted to the left of the active cell. Like-
wise, you can quickly delete selected cells, rows, and columns and all informa-
tion in surrounding cells, rows, or columns automatically shifts appropriately
to fill in the space.
Additionally, whenever you insert or delete cells, rows, or columns, all for-
mula references to any affected cells adjust accordingly.

EX1.48 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.48 30/07/10 11:49 AM

 First Pages

INSERTING ROWS
You realize that you forgot to include a row for the Advertising expenses. To
add this data, you will insert a blank row above the Capital Assets row.

1
• Move to A17.

• Open the
drop-down menu in
Formula range reference
the Cells group and
includes data from inserted row
choose Insert Sheet
Rows.

Another Method
You also can choose Insert from the
active cell’s context menu.

New row inserted

• Enter the heading and values entered

Advertising
in cell A17 and the Formulas
value 600 in cells recalculated
B17 through D17.

• Copy the function

from cell E16 to E17
to calculate the total Figure 1.49
advertising expense.

• Move to cell B20.

• Click Save to
save the workbook
using the same file
name.

Your screen should be similar to

Figure 1.49

A blank row was inserted in the worksheet and the cell references in all formu-
Additional Information
las and functions below the inserted row adjusted appropriately. The range in
Click to insert blank the formula to calculate monthly total expenses in row 20 has been adjusted
cells, shifting existing cells down, and to include the data in the inserted row and the total expense for the first quar-
ter is 69110. Additionally, the net income in row 23 and the profit margin in
/Sheet Columns to
row 25 have been recalculated to reflect the change in data.
insert blank columns, shifting existing
columns right.

Inserting and Deleting Rows and Columns EX1.49

ole31265_EXLab1_001-096.indd EX1.49 30/07/10 11:49 AM

 First Pages

DELETING COLUMNS
As you look at the worksheet data, you decide the minimum and maximum
values are not very useful since this data is so easy to see in this small work-
sheet. You will delete these two columns from the worksheet to remove this
information. To specify which column to delete, select any cell in the column.

1
• Select cells F20 and
G20.
MIN and MAX columns deleted

• Open the

drop-down menu in
the Cells group and
choose Delete Sheet
Columns.

Your screen should be similar to

Figure 1.50

Additional Information
Figure 1.50
Select a cell or row and choose
/Delete Cells or Delete
Sheet Rows to delete it and shift the
other cells or rows up or to the left.
The two columns have been removed and the columns to the right of the
deleted columns automatically shifted to the left.

Formatting Cells and Cell Content

Now that the worksheet data is complete, you want to improve the appear-
ance of the worksheet. Applying different formatting to text and numbers can
greatly enhance the appearance of the document. In Excel, formats control
how entries are displayed in a cell and includes such features as the position of
data in a cell, character font and color, and number formats such as commas
and dollar signs.
You want to change the appearance of the row and column headings and
apply formatting to the numbers. Applying different formats greatly improves
both the appearance and the readability of the data in a worksheet.

EX1.50 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.50 30/07/10 11:49 AM

 First Pages

CHANGING CELL ALIGNMENT

You decide the column headings would look better if they were right-aligned
in their cell spaces, so that they would appear over the numbers in the column.
Alignment is a basic format setting that is used in most worksheets.

Concept 11 Alignment

Alignment settings allow you to change the horizontal and vertical placement and the orientation of an
entry in a cell.

Horizontal placement allows you to left-, right-, or center-align text and number entries in the cell space.
Entries also can be indented within the cell space, centered across
Text Text Text a selection, or justified. You also can fill a cell horizontally with a repeated
entry.

Text Vertical placement allows you to specify whether the cell contents are dis-
Text played at the top, the bottom, or the center of the vertical cell space or justified
Text
vertically.

You also can change the angle of text in a cell by varying the
Text

Text
t

Te
x

degrees of rotation.
Te

x t

The default workbook horizontal alignment settings left-align text entries

and right-align number entries. The vertical alignment is set to Bottom for
both types of entries, and the orientation is set to zero degrees rotation from
the horizontal position. You want to change the horizontal alignment of the
month headings in rows 3 and 10 to right-aligned.
The Alignment group contains commands to control the horizontal and ver-
tical placement of entries in a cell. You can quickly apply formatting to a range
Additional Information of cells by selecting the range first. A quick way to select a range of filled cells
If you do not hold down S while is to hold down S and double-click on the edge of the active cell in the
double-clicking on a cell border, the direction in which you want the range expanded. For example, to select the
active cell moves to the last-used cell in
the direction indicated. range to the right of the active cell, you would double-click the right border.
You will use this method to select and right-align these entries.

Formatting Cells and Cell Content EX1.51

ole31265_EXLab1_001-096.indd EX1.51 30/07/10 11:49 AM

 First Pages

1
• Move to B3.
Align text right
• Hold down S
and double-click the
right cell border of
cell B3.

Having Trouble?
The mouse pointer must be when
you click the cell border.

Cell entries right-

• Click Align aligned in cell space
Text Right from the
Alignment group.

• Select B10 through

E10.

• Click Align Text

Figure 1.51
Right.

Your screen should be similar to The entries in the selected ranges are right-aligned in their cell spaces. You
Figure 1.51 notice the month labels do not stand out well and decide to try rotating them.

2
• Select cells B3
through F3.

• Click
Orientation and
choose Angle
Counterclockwise. Cell entries angled
counterclockwise

Your screen should be similar to

Figure 1.52

Figure 1.52

EX1.52 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.52 30/07/10 11:49 AM

 First Pages

Notice how the row height increased automatically to accommodate the

change in size. You don’t like the way it looks rotated and decide to undo the
change and to add height to the row manually to help identify the month label
row better and to center align the labels. You also decide to move the month
labels in row 10 down a row to match the first row of month labels.

• Click Undo.
Align text center
• Move the entries in
cells B10 through
E10 into the same
columns in row 11.

• Change the row

height to 22.5 for
rows 3 and 11.

• Select the text in

cells B3 through F3 Row height 22.5 and
center-aligned text
and Click Center.

• Do the same for cells

B11 through E11.

Your screen should be similar to

Figure 1.53
Figure 1.53

INDENTING CELL CONTENT

Next, you would like to indent the row headings in cells A4 through A8 and
A12 through A19 to increase the space between the cell border and the entry.
Additional Information You want to indent the headings in both ranges at the same time. To select
You also can select entire nonadjacent nonadjacent cells or cell ranges, after selecting the first cell or range, hold
rows or columns by holding down c
while selecting the rows or columns.
down c while selecting each additional cell or range. You will select the cells
and indent their contents.

Formatting Cells and Cell Content EX1.53

ole31265_EXLab1_001-096.indd EX1.53 30/07/10 11:49 AM

 First Pages

1
• Select A4 through A8.

• Hold down c.

• Select A12 through Increase indent

A19.

• Release c.

• Click Increase
Entries in
Indent in the
nonadjacent selection
Alignment group.
are indented

• AutoFit the width of

column A.

Your screen should be similar to

Figure 1.54

Additional Information
Figure 1.54
Clicking Increase Indent multiple
times indents the selection in two-space
increments. Clicking Decrease Each entry in the selected range is indented two spaces from the left edge of
Indent reduces the margin between the the cell. Finally, you want to right-align the Total Sales, Total Expenses, and
border and the text in the cell. Net Income headings.

2
• Select A9, A20, and
A23.
Right-aligns entry
in cell space
• Click Align Text
Right.

Your screen should be similar to

Figure 1.55

Text entries
right-aligned

Figure 1.55

EX1.54 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.54 30/07/10 11:49 AM

 First Pages

MERGING CELLS
Next, you want to center the worksheet titles across columns A through E so
they are centered over the worksheet data. To do this, you will merge or com-
bine the cells in the range over the worksheet data (A1 through F1) into a sin-
gle large merged cell and then center the contents of the range in the merged
cell. This process is easily completed in one simple step using the Merge &
Center command.

1 Cell reference of
Merges cells and

• Select A1 through F1. merged cell

centers content

• Click Merge
Cell entry centered
& Center in the
in merged cell
Alignment group.

Your screen should be similar to

Figure 1.56

Figure 1.56

The six cells in the selection have been combined into a single large cell and
the entry that was in cell B1 is centered within the merged cell space. Only the
contents of the first cell containing an entry in the upper-leftmost section of
the selected range are centered in the merged cell. If other cells to the right of
that cell contain data, it would be deleted. The cell reference for a merged cell
is the upper-left cell in the original selected range, in this case A1.

2
• Merge and center
the second title line
across columns A
through F.

Cell entry centered

Your screen should be similar to in merged cell

Figure 1.57

Figure 1.57

Formatting Cells and Cell Content EX1.55

ole31265_EXLab1_001-096.indd EX1.55 30/07/10 11:49 AM

 First Pages

You also can use the commands in the Merge & Center drop-down menu
shown in the following table to control a merge. You can merge cells horizon-
tally and vertically.

Merge Menu Action

Merge & Center Merges cells and centers entry

Merge Across Merges cells horizontally

Merge Cells Merges cells horizontally and vertically

UnMerge Cells Splits cells that have been merged back into individual cells

CHANGING FONTS AND FONT SIZES

Finally, you want to improve the worksheet appearance by enhancing the
appearance of the title. One way to do this is to change the font and font size
used in the title. There are two basic types of fonts: serif and sans serif. Serif
fonts have a flare at the base of each letter that visually leads the reader to the
next letter. Two common serif fonts are Roman and Times New Roman. Serif
fonts generally are used in paragraphs. Sans serif fonts do not have a flare at
the base of each letter. Arial and Helvetica are two common sans serif fonts.
Because sans serif fonts have a clean look, they are often used for headings in
documents. It is good practice to use only two types of fonts in a worksheet,
one for text and one for headings. Too many styles can make your document
look cluttered and unprofessional.
Here are several examples of the same text in various fonts and sizes.

Typeface Font Size (12 pt/18 pt)

Calibri (Sans Serif) This is 12 pt. This is 18 pt.

Times New Roman (Serif) This is 12 pt. This is 18 pt.
Book Antiqua (Serif) This is 12 pt. This is 18 pt.

Using fonts as a design element can add interest to your document and give
readers visual cues to help them find information quickly. First you will try a
different font for the title and a larger font size.

EX1.56 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.56 30/07/10 11:49 AM

 First Pages

1 Selected font

• Select A1 and A2.

• Open the

Font drop-down list

box in the Font group.

Your screen should be similar to

Font names appear
Figure 1.58
in font format

Indicates
TrueType font

Figure 1.58

The Font drop-down list displays examples of the available fonts on your sys-
tem in alphabetical order. The default worksheet font, Calibri, is highlighted.
Notice the preceding the font name. This indicates the font is a TrueType
font. TrueType fonts appear onscreen as they will appear when printed. They
are installed when Windows is installed. Fonts that are preceded with a blank
space are printer fonts. These fonts are supported by your printer and are
displayed as closely as possible to how they will appear onscreen but may not
match exactly when printed. You will change the font and increase the font
size to 14. As you point to the font options, the Live Preview will show how it
will appear if chosen.

Formatting Cells and Cell Content EX1.57

ole31265_EXLab1_001-096.indd EX1.57 30/07/10 11:49 AM

 First Pages

2 Selected font Selected font size

• Scroll the list and

choose Lucida Sans.

Having Trouble?
You will not be able to see the Fonts Live
Preview because the drop-down menu Titles in selected
covers the selection to be formatted. font and font size

• Open the
Font Size drop-down
list box.
Row height increased to
accommodate larger font size
• Point to several
different font sizes in
the list to see the Live
Preview.

• Choose 14.

Your screen should be similar to

Figure 1.59
Figure 1.59

The title appears in the selected typeface and size and the Font and Size but-
Another Method
tons display the name of the font and the size used in the active cell. Notice
The Font and Font Size commands are
also available on the Mini toolbar.
that the height of the row has increased to accommodate the larger font size
of the heading.

APPLYING TEXT EFFECTS

In addition to changing font and font size, you can apply different text effects
to enhance the appearance of text. The table below describes some of the text
effects and their uses.

Format Example Use

Bold Bold Adds emphasis
Italic Italic Adds emphasis
Underline Underline Adds emphasis
Strikethrough Strikethrough Indicates words to be deleted
1
Superscript “To be or not to be.” Used in footnotes and formulas
Subscript H2O Used in formulas
Color Color Color Color Adds interest

First you want to enhance the appearance of the column headings by increas-
ing the font size and adding bold, italic, and underlines.

EX1.58 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.58 30/07/10 11:49 AM

 First Pages

1
• Select B3 through F3.
Adds underline effect

• Increase the font size

to 12. Adds bold effect

• Click Bold.
Entries bold and

• Click underlined

Underline.

Your screen should be similar to

Figure 1.60

Figure 1.60

Many of the formatting commands are also available on the Mini toolbar that
appears when you display the shortcut menu. To use the Mini toolbar, choose
command buttons just as you would from the Ribbon.

2
• Select A4 through A8.

• Right-click on the
selection to display Adds bold effect

the Mini toolbar. Adds italic effect

• Click Bold.

• Click Italic.

Another Method
Entries bold and italic
The keyboard shortcut for bold is
c + B; for italic, it is c + I;
and for underline, it is c + U.

Your screen should be similar to

Figure 1.61

Figure 1.61

Formatting Cells and Cell Content EX1.59

ole31265_EXLab1_001-096.indd EX1.59 30/07/10 11:49 AM

 First Pages

CLEARING FORMATS
Sometimes formatting changes you make do not have the expected result. In
this case, you feel that the sales category names would look better without the
formatting. One way to remove the format from cells is to use Clear in the
Editing group and choose Clear Formats. Because this will remove all format-
ting in the selected cells, you will need to redo the indenting in those cells.

1
• With cells A4 through
A8 still selected,
open the Clear
drop-down list in the
Editing group.

• Choose Clear
Formats.
Bold and italic

• Click Increase effects removed,

indent applied
Indent.

Another Method

You could also use Undo to remove

the formats by reversing your last
actions.

Your screen should be similar to

Figure 1.62 Figure 1.62

Additional Information
You can remove both formatting and
content using Clear/Clear All.

USING FORMAT PAINTER

You do think, however, that the Total Sales, Total Expenses, and Net Income
headings would look good in bold. You will bold the entry in cell A9 and then
copy the format from A9 to the other cells using Format Painter. You also will
format the headings in row 11.

EX1.60 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.60 30/07/10 11:49 AM

 First Pages

1
Format Painter
• Apply bold to cell A9. copies format of
selected cell
• With cell A9 selected,
double-Click
Format Painter in the
Clipboard group.

Additional Information
When Format Painter is on, the mouse
Bold, underline, and center-
pointer appears as and the cell
alignment formats copied from
whose format will be copied appears
B3 using Format Painter
with a moving border.

• Click A20.
Bold effect copied from

• Click A23. A9 using Format Painter

• Click Format
Painter to turn it off.
Figure 1.63

Another Method
You also can press q to turn off
Format Painter.
The formatting was quickly added to each cell or range as it was selected.

• Use Format Painter to

copy the format from
cell B3 to cells B11
through E11.

Your screen should be similar to

Figure 1.63

FORMATTING NUMBERS
You also want to improve the appearance of the numbers in the worksheet by
changing their format.

Concept 12 Number Formats

Number formats change the appearance of numbers onscreen and when printed, without changing the way the
number is stored or used in calculations. When a number is formatted, the formatting appears in the cell while
the value without the formatting is displayed in the formula bar.
The default number format setting in a worksheet is General. General format, in most cases, displays
numbers just as you enter them, unformatted. Unformatted numbers are displayed without a thousands
separator such as a comma, with negative values preceded by a – (minus sign), and with as many decimal place
settings as cell space allows. If a number is too long to be fully displayed in the cell, the General format will
round numbers with decimals and use scientific notation for large numbers.

Formatting Cells and Cell Content EX1.61

ole31265_EXLab1_001-096.indd EX1.61 30/07/10 11:49 AM

 First Pages

First, you will change the number format of cells B5 through F10 to display as
currency with dollar signs, commas, and decimal places.

1
• Select cells B4
Currency format
through F9.
applied to numbers

• Open the in selected range

Number Format
drop-down list in the
Number group. Column widths increased
automatically to fully display
• Choose Currency. formatted values

Your screen should be similar to

Figure 1.64

Figure 1.64

The number entries in the selected range appear with a currency symbol,
Another Method
comma, and two decimal places. The column widths increased automatically
Excel will also automatically apply a to fully display the formatted values.
format to a cell based on the symbols
you use when entering the number. A second format category that displays numbers as currency is Accounting.
For example, entering 10,000 in a cell You will try this format next on the same range. Additionally, you will specify
formats the cell to Comma format, and zero as the number of decimal places because most of the values are whole
entering $102.20 formats the cell to
Currency with two decimal places. values. To specify settings that are different than the default setting for a for-
mat, you can use the Format Cells dialog box.

EX1.62 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.62 30/07/10 11:49 AM

 First Pages

2
• Make sure you still
have cells B4 through
F9 selected.

• Click in the
Number group to Accounting format with
open the Format no decimal places applied
Cells: Number dialog to range
box.

Another Method
The keyboard shortcut to open the
Format Cells dialog box is c + 1.

• From the Category

list box, choose
Accounting.

• Reduce the decimal

places to 0.
Figure 1.65
• Click .
The numbers now appear in Accounting format. The primary difference
between the Accounting and the Currency formats is that the Accounting
Your screen should be similar to
format aligns numbers at the decimal place and places the dollar sign in a
Figure 1.65
column at the left edge of the cell space. In addition, it does not allow you
to select different ways of displaying negative numbers but displays them in
black in parentheses.
You decide the Accounting format will make it easier to read the numbers
in a column and you will use this format for the rest of the worksheet. An
easier way to apply the Accounting format with 0 decimals is to use the com-
mands in the Number group.

3
• Select the range B12
Custom number
through E20.
format created

• Click
Accounting Number Applies Accounting Decreases number
Format in the Number number format of decimal places
group.

• Click Decrease
Decimal twice.

Your screen should be similar to

Figure 1.66 Accounting format with
no decimal places

Figure 1.66
Formatting Cells and Cell Content EX1.63

ole31265_EXLab1_001-096.indd EX1.63 30/07/10 11:49 AM

 First Pages

Notice the Number Format box displays Custom because you modified a copy
of the existing Accounting number format code. The custom number format
is added to the list of number format codes. Between 200 and 250 custom for-
mats can be added depending on the language version of Excel you are using.
You can then reapply the custom format by selecting it from the Custom cat-
egory of the Format Cells: Number dialog box. This is useful for complicated
formats, but not for formats that are easy to recreate.
Finally, you will format the Net Income as Accounting with zero decimal
places and the Profit Margin values to percentages with two decimal places.
You will do this using the Mini toolbar. This feature is particularly helpful
when working at the bottom of the worksheet window.

4
• Select B23 through
E23.

• Click
Accounting Number
Format on the Mini
toolbar.

Having Trouble
Right-click on the selection to display
the Mini toolbar.

• Click Decrease
Decimal twice on the
Mini toolbar.
Formatted as percent
with two decimal places
• Select B25 through
E25.

• Click Percent Figure 1.67

Style on the Mini
toolbar.

• Click Increase
Decimal twice on the
Mini toolbar.

Your screen should be similar to

Figure 1.67
ADDING FONT COLOR
The last formatting change you would like to make to the worksheet is to add
color to the text of selected cells. Font color can be applied to all the text in a
selected cell or range or to selected words or characters in a cell.

EX1.64 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.64 30/07/10 11:49 AM

 First Pages

1
• Select A1 through A2.

• Open the Font

Color drop-down
Font color palette
menu in the Font
group.

Another Method
Font Color is also available on
the Mini toolbar.

Your screen should be similar to

Figure 1.68 Figure 1.68

A palette of colors is displayed. Automatic is the default text color setting. This
Additional Information setting automatically determines when to use black or white text. Black text is
You will learn about using Document used on a light background and white text on a dark background. The center
Themes in Lab 2.
area of the palette displays the Theme colors. Theme colors are a set of colors
that are associated with a document theme, a predefined set of fonts, col-
ors, and effects that can be applied to an entire worksheet. If you change the
theme, the theme colors change. The Standard Colors bar displays 10 colors
that are always the same.
As you point to a color, the entry in the selected cell changes color so you
can preview how the selection would look. A ScreenTip displays the name of
the standard color or the description of the theme color as you point to it.

2
• Choose Yellow from
the Standard Colors
bar.
Font color applied to

• Click Bold. selection

Your screen should be similar to

Figure 1.69

Figure 1.69

The font color of all the text in cells A1 and A2 has changed to the selected
color and bold. The selected color appears in the button and can be applied
again simply by clicking the button.

Formatting Cells and Cell Content EX1.65

ole31265_EXLab1_001-096.indd EX1.65 30/07/10 11:49 AM

 First Pages

ADDING FILL COLOR

Next, you will change the cell background color, also called the fill color,
behind the titles and in several other areas of the worksheet. Generally, when
adding color to a worksheet, use a dark font color with a light fill color or a
light font color with a dark fill color.

1 Applies displayed

• Select cells A1 fill color

through F3.

• Open the Fill

Color drop-down Opens fill color
color palette. drop-down menu

• Point to several
colors to see a Live
Preview.

• Select the Light

Blue color from the
Standard Colors bar.
Fill color applied
to selections
• Select cells A11
through F11 and
Click Fill Color
to apply the last
selected fill color.

• Apply the same fill

color to A22 through
F22 and A25 through
F25. Figure 1.70

Your screen should be similar to The color highlight helps distinguish the different areas of the worksheet.
Figure 1.70

ADDING AND REMOVING CELL BORDERS

Finally, you decide to add a border around the entire worksheet area. Excel
includes many predefined border styles that can be added to a single cell or to
a range of cells. Then you will make several additional formatting changes to
improve the appearance and readability of the worksheet.

EX1.66 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.66 30/07/10 11:49 AM

 First Pages

1 Opens Borders

• Select the range A1 drop-down menu

through F25.

• Open the
Borders drop-down Thick box border style
menu in the Font applied to selection
group and choose
the Thick Box Border
style.

• Click outside the

range to see the
border.

Your screen should be similar to

Figure 1.71

Figure 1.71

The range is considered a single block of cells and the box border surrounds
the entire worksheet selection.
When adding borders, the border also is applied to adjacent cells that share
a bordered cell boundary. In this case, cells G1 through G26 acquired a left
border and cells A27 through F27 acquired a top border. When pasting a cell
that includes a cell border, the border is included unless you specify that the
paste does not include the border. To see how this works, you will first copy a
cell and its border, and then you will copy it again without the border.

2
• Copy cell A1 and Top and side
paste it in cell G2. borders copied

• Move to G4, open

the drop-down

menu and choose No

Borders.

• Move to G6 to see the

No borders copied
changes.

Your screen should be similar to

Figure 1.72

Figure 1.72

If you want to add additional borders or replace an existing border with

another, select the range and then add the border. However, if you want to
remove a border style from one area of a selection and add a border to another
Formatting Cells and Cell Content EX1.67

ole31265_EXLab1_001-096.indd EX1.67 30/07/10 11:49 AM

 First Pages

area, you need to remove all borders first and then apply the new border styles.
You will try these features next on the entry in cell G2.

3
• Move to G2 and Existing borders were
choose No Border removed

from the
Borders drop-down
menu.

• Apply a Bottom
Double Border to the
Double bottom border
selection.
applied

• Move to G6 to see the

changes.

Your screen should be similar to

Figure 1.73

Figure 1.73

All existing borders were removed, including those that share a cell boundary,
and the new double bottom border is applied to the selection. You will restore
the worksheet to how it was prior to copying the title using Undo and then
make some final adjustments to the worksheet.

4
• Undo your last four
actions.

• Move to any cell

in row 10 and
choose Delete Sheet
Rows from the

drop-down menu in
the Cells group.
Rows deleted
• In the same manner,
delete the blank rows
20 and 23.

• Add bold and yellow

font color to cells A3,
A10, A20, and A22.
Color and bold added

• Click Save to
save the worksheet
Figure 1.74
changes.

Your screen should be similar to

Figure 1.74
EX1.68 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.68 30/07/10 11:49 AM

 First Pages

Hide and Unhide Rows and Columns

Now that the worksheet is nicely formatted, you want to focus on the data.
One way to do this is to hide areas of data that you do not want to see in order
to emphasize others. You will use this method to emphasize the total data.

1
• Select columns B
through D.

Additional Information
Any range of cells within the area you
want to hide can be selected.

Columns B through
• Open the
D hidden

drop-down menu in
the Cells group and
select Hide & Unhide.

• Choose Hide
Columns.

Your screen should be similar to

Figure 1.75
Figure 1.75

Now the worksheet focus is on the monthly total values, not the month values.
Another Method
The columns were hidden by reducing their column width to zero. Instead,
You could also right-click on the you want to hide the rows instead.
y
selected columns and choose Hide
from the context menu.

Hide and Unhide Rows and Columns EX1.69

ole31265_EXLab1_001-096.indd EX1.69 30/07/10 11:49 AM

 First Pages

2
• Click on column A
and drag to select
columns A and E.

• In the Cells group,

Click
and select Hide &
Rows hidden to
Unhide, and then
emphasize totals
choose Unhide
Columns.

• Select any range of

cells within rows 4
through 8.

• Open the

drop-down menu,
select Hide & Unhide
and then choose Hide
Rows. Figure 1.76

• Repeat to hide rows

Notice how hiding the rows emphasizes the monthly totals by category.
11 through 18.

Your screen should be similar to

Figure 1.76

• Click Undo twice to unhide the rows.

Creating a Simple Chart

Another way to better understand the data in a worksheet is to create a chart.
A chart is a visual representation of data that is used to convey information in
an easy-to-understand and attractive manner. You decide to create a chart of
the sales data for the three months.

SPECIFYING THE DATA TO CHART

To tell Excel what data to chart, you need to select the range containing the
data you want to appear in the chart plus any row or column headings you
want used in the chart.

EX1.70 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.70 30/07/10 11:49 AM

 First Pages

1
• Select cells A3
Chart Tools tab
through D8.

• Hold down the a

key and press 1.
Data outlined in color to
show selection for chart

Your screen should be similar to

Figure 1.77

Column chart using

data from Sales section
of worksheet

Figure 1.77

Additional information
The information in the selected range was translated into a chart based on the
shape and contents of the selection. A column chart showing the sales for the
You will learn all about creating charts
in Lab 2. five items over three months was quickly created.

2
• Point to the edge of the chart object and drag to move it below
the worksheet to cover rows 24 to 38.

• Click outside the chart object to deselect it.

Formatting Values as a Date

Now that the worksheet is complete, you want to include your name and the
date in the worksheet as documentation. There are many ways to enter the
date. For example, you could type the date using the format mm/dd/yy or as
month dd, yyyy. When a date is entered as text, Excel converts the entry to
a numeric entry that allows dates to be used in calculations. Excel stores all
dates as serial values with each day numbered from the beginning of the 20th
century. The date serial values are consecutively assigned beginning with 1,
which corresponds to the date January 1, 1900, and ending with 2958465,
which is December 31, 9999.

Formatting Values as a Date EX1.71

ole31265_EXLab1_001-096.indd EX1.71 30/07/10 11:49 AM

 First Pages

1
• Enter your first and
last name in cell A40.

• Type the current date

as mm/dd/yy in cell
A41.

Another Method
You also could use the Today function,
=Today(), to display the current date in
the default date format or the keyboard
shortcut c + ;.

Your screen should be similar to Chart moved

Figure 1.78

Date displayed in
default format

Figure 1.78

The date is displayed using the default date format, which is based on the
settings in Windows. It is right-aligned in the cell because it is a numeric
entry. You can change the date format in the worksheet without changing the
Windows settings using the Format Cells: Number dialog box.

2
• If necessary, move to
cell A41.

• Click in the
Number group to
open the Format Cells
dialog box.

• Choose the month

xx, xxxx (March 14,
2001) date format
from the Type list.

• Click .

Your screen should be similar to

Figure 1.79 Date in
chosen format

Figure 1.79

The date appears in the specified format.

EX1.72 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.72 30/07/10 11:49 AM

 First Pages

Documenting a Workbook
You are finished working on the worksheet for now and want to save the
Having Trouble?
changes you have made to the file. In addition, you want to update the file
Refer to the section Specifying properties to include your name as the author, a title, and keywords.
Document Properties in the Introduction
to Microsoft Office 2010 to review this
feature.

1
• Open the File tab.

• In the Backstage
view Info window
enter the following
information in the
appropriate boxes.
Title Downtown
Internet
Café
Document
Tags Sales properties
Projections
Author Your Name

Additional Information
The Author text box may be blank or
may show your school or some other
name. Clear the existing contents first
if necessary.

Figure 1.80
Your screen should be similar to
Figure 1.80

Previewing and Printing a Worksheet

Although you still plan to make more changes to the worksheet, you want to
print a copy of the estimated first-quarter forecast for the owner to get feed-
back regarding the content and layout.

Previewing and Printing a Worksheet EX1.73

ole31265_EXLab1_001-096.indd EX1.73 30/07/10 11:49 AM

 First Pages

1 Print preview

• Choose Print and

view the preview in
the right pane.

Your screen should be similar to

Figure 1.81

Gridlines
Additional Information do not print
If you have a monochrome printer, the
preview appears in shades of gray, as it
will appear when printed.

Figure 1.81

The preview displays the worksheet as it will appear on the printed page.
Another Method
Notice that the row and column gridlines are not displayed and will not print.
You also can use the keyboard shortcut
c + P from the worksheet window to
This is one of the default worksheet print settings.
open the Print tab of Backstage view. The preview of your worksheet may appear slightly different from that
shown in Figure  1.81. This is because the way pages appear in the preview
depends on the available fonts, the resolution of the printer, and the available
Additional Information colors. If your printer is configured to print in black and white, the preview
You can change printer-specific settings,
will not display in color.
such as color, by clicking Printer The Excel print settings let you specify how much of the worksheet you
Properties and specifying the settings you want printed. The options are described in the following table.
want in the Printer Properties dialog box.

Option Action
Print Active Sheets Prints the active worksheet (default)
Print Entire Workbook Prints all worksheets in the workbook
Print Selection Prints selected range only
Pages Prints pages you specify by typing page numbers in the text box

The worksheet looks good and does not appear to need any further modifica-
tions immediately. Now you are ready to print the worksheet using the default
print settings.
NOTE Please consult your instructor for printing procedures that may differ from the
Having Trouble?
following directions.
Refer to the section “Printing a
Document” in the Introduction to
Microsoft Office 2010 to review this
feature.

EX1.74 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.74 30/07/10 11:49 AM

 First Pages

2
• If necessary, make sure your printer is on and ready to print.

• If you need to change the selected printer to another printer, open the
Printer drop-down list box and select the appropriate printer.

• Click .

The printed copy should be similar to the document shown in the preview
area.
When printing is complete, Backstage view is automatically closed. A dot-
ted line may appear between columns G and H. This is the automatic page
break line that shows where one printed page ends and the next begins.

DISPLAYING AND PRINTING FORMULAS

Often, when verifying the accuracy of the data in a worksheet, it is helpful to
display all the formulas in a worksheet rather than the resulting values. This
way you can quickly verify that the formulas are referencing the correct cells
and ranges.

1 Displays formulas

• Open the Formulas

tab.

• Click
Unformatted values

in the Formula
Auditing group.

Another Method
You also can use c + ` (accent
grave is located to the left of the
number 1 key) to toggle between values
and formulas.

• Move to B9.

Your screen should be similar to

Figure 1.82 Formulas and functions

Figure 1.82

The display of the worksheet has changed to display unformatted values and
the formulas and functions. It has automatically increased the column widths
so the formulas and text do not overflow into the adjacent cells.

Previewing and Printing a Worksheet EX1.75

ole31265_EXLab1_001-096.indd EX1.75 30/07/10 11:49 AM

 First Pages

CHANGING WORKSHEET ORIENTATION AND SCALING CONTENT

Next, you will print the worksheet with formulas. Because the worksheet is
so much wider, you will need to change the orientation to landscape, which
prints across the length of the paper. Then you will reduce the scale of the
worksheet so it fits on one page. The scaling feature will reduce or enlarge the
worksheet contents by a percentage or to fit it to a specific number of pages by
height and width. You want to scale the worksheet to fit on one page.

1
Page orientation
• Open the File tab and changed to landscape
choose Print.

• Change the
orientation setting
to Landscape
Orientation.

• Open the

drop-down menu and

choose Fit Sheet on
One Page.

Your screen should be similar to

Figure 1.83

Worksheet sized to fit

on one page

Figure 1.83

The entire worksheet will easily print across the length of the page when
Another Method printed using landscape orientation and scaled to fit a single page.
You also can scale the worksheet using
in the Scale to Fit group
of the Page Layout tab and setting the
scale percentage.

2
• Print the worksheet.

• Press c + ` to return the display to values.

EX1.76 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.76 30/07/10 11:50 AM

 First Pages

Exiting Excel 2010

You are now ready to exit the Excel application. If you attempt to close the
Having Trouble?
application without first saving the workbook, Excel displays a warning ask-
Refer to the section Closing a File and ing whether you want to save your work. If you do not save your work and you
Exiting an Application to review these
features. exit the application, all changes you made from the last time you saved are
lost.

1
• Move to cell A1.

• Click Close (in the application window title bar).

• Click to resave the worksheet.

Additional Information Because you added the date since last saving the worksheet, you were prompted
to save it again before closing it.
Excel saves the file with the cell selector
in the same cell location it is in at the
time it is saved.

FOCUS ON CAREERS
EXPLORE YOUR CAREER OPTIONS track of the income and expenses for coordinated half-time
activities at professional sporting events. These worksheets
Fan Coordinator would provide valuable information for promoting sponsors’
Did you know that 40 percent of the advertised positions in products and services at games. A fan coordinator might start
sports are for marketing and promotion? A marketing graduate out as an unpaid intern, but after graduation could expect to
hired as a basketball fan coordinator would use Excel to keep earn from $25,000 to $45,000.

Exiting Excel 2010 EX1.77

ole31265_EXLab1_001-096.indd EX1.77 30/07/10 11:50 AM

 First Pages

Lab 1 CONCEPT SUMMARY

Column Width
Creating and Editing a Worksheet

Data Entries (EX1.xx)

The basic information or data
you enter in a cell can be text or
numbers.
AutoCorrect
AutoCorrect (EX1.xx)
The AutoCorrect feature makes
some basic assumptions about the
text you are typing and, based on
these assumptions, automatically
corrects the entry. Data Entries

Column Width (EX1.xx)

The column width is the size or
width of a column and controls the
amount of information that can be
displayed in a cell.

Spelling Checker (EX1.xx)

The spelling checker locates
misspelled words, duplicate words,
and capitalization irregularities in
the active worksheet and proposes
the correct spelling.
Thesaurus

Thesaurus (EX1.xx)
The thesaurus is a reference tool
that provides synonyms, antonyms, Spelling Checker
and related words for a selected
word or phrase.

Range (EX1.xx)
A selection consisting of two or
more cells on a worksheet is a
range.

Range

EX1.78 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.78 30/07/10 11:50 AM

 First Pages

Formula (EX1.xx)
A formula is an equation that
performs a calculation on data Formula
contained in a worksheet.

Relative Reference (EX1.xx) Relative Reference

A relative reference is a cell or

range reference in a formula whose
location is interpreted in relation to
the position of the cell that contains
the formula.

Function (EX1.xx)
A function is a prewritten formula
that performs certain types of
calculations automatically. Function

Recalculation (EX1.xx)
When a number in a referenced
cell in a formula changes, Excel
automatically recalculates all
formulas that are dependent upon
the changed value.

Recalculation

Alignment (EX1.xx)
Alignment settings allow you to
change the horizontal and vertical
placement and the orientation of an
entry in a cell.

Alignment
Number Format (EX1.xx)
Number formats change the
appearance of numbers onscreen
and when printed, without changing
the way the number is stored or
used in calculations. Number Format

Concept Summary EX1.79

ole31265_EXLab1_001-096.indd EX1.79 30/07/10 11:50 AM

 First Pages

LAB REVIEW Lab 1 Creating and Editing a Worksheet

KEY TERMS
active cell EX1.xx number format EX1.xx
active sheet EX1.xx operand EX1.xx
adjacent range EX1.xx operator EX1.xx
alignment EX1.xx order of precedence EX1.xx
antonym EX1.xx range EX1.xx
argument EX1.xx range reference EX1.xx
AutoCorrect EX1.xx recalculation EX1.xx
AutoFit EX1.xx relative reference EX1.xx
cell EX1.xx row EX1.xx
cell reference EX1.xx row number EX1.xx
cell selector EX1.xx sans serif EX1.xx
chart EX1.xx scaling EX1.xx
column EX1.xx serial value EX1.xx
column letter EX1.xx serif EX1.xx
column width EX1.xx sheet EX1.xx
constant EX1.xx sheet tab EX1.xx
custom dictionary EX1.xx spelling checker EX1.xx
data EX1.xx spreadsheet EX1.xx
default EX1.xx synonym EX1.xx
fill handle EX1.xx syntax EX1.xx
formula EX1.xx tab scroll buttons EX1.xx
formula bar EX1.xx template EX1.xx
function EX1.xx text EX1.xx
heading EX1.xx theme EX1.xx
main dictionary EX1.xx thesaurus EX1.xx
merged cell EX1.xx variable EX1.xx
Name box EX1.xx workbook EX1.xx
nonadjacent range EX1.xx workbook window EX1.xx
number EX1.xx worksheet EX1.xx

EX1.80 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.80 30/07/10 11:50 AM

 First Pages

COMMAND SUMMARY
Command Shortcut Action
File

Open c+O Opens an existing workbook file

Save c+S Saves file using same file name

Save As = Saves file using a new file name

Close c+4 Closes open workbook file

New c+N Opens a new blank workbook

Print/ c+P Prints a worksheet

Print/ /Fit sheet on one page Scales worksheet to fit on a single page

or a + 4 Exits Excel program

Quick Access Toolbar

Save c+S Saves document using same file name

Undo c+Z Reverses last editing or formatting change

Redo c+Y Restores changes after using Undo

Home tab
Clipboard group

c+V Pastes selections stored in system Clipboard

Cut c+X Cuts selected data from the worksheet

Copy c+C Copies selected data to system Clipboard

Copies formatting from one place and applies

Format Painter
it to another
Font group
Font Changes text font

Font Size Changes text size

Bold c+B Bolds selected text

Italic c+I Italicizes selected text

Lab Review EX1.81

ole31265_EXLab1_001-096.indd EX1.81 30/07/10 11:50 AM

 First Pages

Lab 1 CONCEPT SUMMARY

Column Width
Creating and Editing a Worksheet

Data Entries (EX1.xx)

The basic information or data
you enter in a cell can be text or
numbers.
AutoCorrect
AutoCorrect (EX1.xx)
The AutoCorrect feature makes
some basic assumptions about the
text you are typing and, based on
these assumptions, automatically
corrects the entry. Data Entries

Column Width (EX1.xx)

The column width is the size or
width of a column and controls the
amount of information that can be
displayed in a cell.

Spelling Checker (EX1.xx)

The spelling checker locates
misspelled words, duplicate words,
and capitalization irregularities in
the active worksheet and proposes
the correct spelling.
Thesaurus

Thesaurus (EX1.xx)
The thesaurus is a reference tool
that provides synonyms, antonyms, Spelling Checker
and related words for a selected
word or phrase.

Range (EX1.xx)
A selection consisting of two or
more cells on a worksheet is a
range.

Range

EX1.78 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.78 30/07/10 11:50 AM

 First Pages

COMMAND SUMMARY (CONTINUED)

Command Shortcut Action
Deletes selected columns, shifting existing
/Delete Sheet Columns
columns left

/Row Height Changes height of selected row

/AutoFit Row Height Changes row height to match the tallest cell entry

/Column Width Changes width of selected column

/AutoFit Column Width Changes column width to match widest cell entry

/Default Width Returns column width to default width

Editing group
Calculates the sum of the values in the selected
Sum
cells
Calculates the average of the values in the
Sum/Average
selected range
Returns the smallest of the values in the selected
Sum/Min
range
Returns the largest of the values in the selected
Sum/Max
range

Fill/Right c+R Continues a pattern to adjacent cells to the right

Removes both formats and contents from

Clear
selected cells

Clear/Clear Formats Clears formats only from selected cells

Clear/Clear Contents D Clears contents only from selected cells

Formulas tab
Formula Auditing group
c+` Displays and hides worksheet formulas

Review tab
Proofing group

7 Spell-checks worksheet

Opens the Thesaurus for the selected word in the

Research task pane

Lab Review EX1.83

ole31265_EXLab1_001-096.indd EX1.83 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
SCREEN IDENTIFICATION
1. In the following Excel 2010 screen, letters identify important elements. Enter the correct term for
each screen element in the space provided.

A B C D E

K J I H G

Possible answers for the screen identification are

Column Workbook window A. ___________________ I. ___________________
Status bar Cell B. ___________________ J. ___________________
Font color Formula bar C. ___________________ K. ___________________
Numeric entry Active sheet D. ___________________ L. ___________________
Fill color Text label E. ___________________ M. ___________________
Font Cell reference F. ___________________ N. ___________________
Range Format painter G. ___________________ O. ___________________
Formula View buttons H. ___________________ P. ___________________
Row Border
Column labels Sheet tabs

EX1.84 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.84 30/07/10 11:50 AM

 First Pages

MATCHING
Match the lettered item on the right with the numbered item on the left.

1. _____ a. an arithmetic operator

2. .xlsx _____ b. changes the width of a column
3. _____ c. a graphic representation of data
4. chart _____ d. Excel workbook file name extension
5. / _____ e. two or more worksheet cells
6. _____ f. enters a SUM function
7. =C19*A21 _____ g. adds a cell border
8. D11 _____ h. merges cells and centers entry
9. range _____ i. a formula multiplying the values in two cells
10. _____ j. a cell reference
11. _____K. format painter

TRUE/FALSE
Circle the correct answer to the following questions.
1. Number formats affect the way that numbers are used in calculations. True False
2. Charts are visual representations of the data in a worksheet. True False
3. A colon is used to separate cell references in nonadjacent ranges. True False
4. A function is a prewritten formula that performs a calculation. True False
5. The default column width setting is 10.12. True False
6. When a formula containing relative references is copied, the cell references
in the copied formula refer to the same cells that are referenced in the
original formula. True False
7. An adjacent range is two or more selected cells or ranges that are adjoining. True False
8. The spell checker can only find misspelled words if they are entered
in the main dictionary. True False
9. Recalculation is one of the most powerful features of electronic worksheets. True False
10. Cell alignment allows you to change the horizontal and vertical placement
and the orientation of an entry in a cell. True False

Lab Exercises EX1.85

ole31265_EXLab1_001-096.indd EX1.85 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
FILL-IN
Complete the following statements by filling in the blanks with the correct key terms.
1. Cells or ranges that are included in the same selection but are not located next to each other are
part of a(n) _____________.
2. _____________ are integers assigned to the days from January 1, 1900, through December 31,
2099, that allow dates to be used in calculations.
3. The _____________ displays the cell selector and will be affected by the next entry or procedure.
4. A(n) _____________ window is used to display an open workbook file.
5. By default, text entries are _____________-aligned and number entries are _____________-aligned.
6. A(n) _____________ entry is used to perform a calculation.
7. The _____________ function automatically adds all the numbers in a range of cells.
8. A(n) _____________ is a rectangular grid of rows and columns.
9. The _____________ dictionary holds words the user enters that are not included in the main
dictionary.
10. A(n) _____________ cell is a cell made up of several selected cells combined into one.

MULTIPLE CHOICE
Circle the correct response to the questions below.

1. _____________ entries can contain any combination of letters, numbers, spaces, and any other
special characters.
a. Number
b. Variable
c. Constant
d. Text
2. The _____________ is a small black square, located in the lower-right corner of the selection, used
to create a series or copy to adjacent cells.
a. sheet tab
b. fill handle
c. scroll box
d. sizing handle
3. Rotating entries, using color, and using character effects are three ways to _____________.
a. emphasize information
b. create reports
c. perform calculations
d. update spreadsheets

EX1.86 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.86 30/07/10 11:50 AM

 First Pages

4. The amount of information that is displayed in a cell is determined by the _____________.

a. column size
b. row size
c. column width
d. row height
5. Which of the following is a valid Excel formula?
a. =(5 + 8)(2 + 1)
b. 5 + 8*2 + 1
c. =5 + 8(2 + 1)
d. =(5 + 8)*(2 + 1)
6. Whenever a formula containing _____________ references is copied, the referenced cells are
automatically adjusted.
a. relative
b. automatic
c. fixed
d. variable
7. The _____________ feature in Excel automatically inserts proper capitalization at the beginning
of sentences and in the names of days of the week.
a. AutoName
b. AutoCorrect
c. CorrectWords
d. Word Wrap
8. The Currency number format can display _____________.
a. dollar signs
b. commas
c. decimal places
d. all of the above
9. When a number in a referenced cell is changed, all the formulas that use the cell reference are
_____________.
a. recalculated
b. reformatted
c. redefined
d. left unchanged
10. The _____________ is a reference tool that provides synonyms and related words for a selected
word.
a. synonym locator
b. thesaurus
c. spelling checker
d. research book

Lab Exercises EX1.87

ole31265_EXLab1_001-096.indd EX1.87 30/07/10 11:50 AM

 First Pages

LAB EXERCISES Hands-On Exercises

RATING SYSTEM

È Easy

STEP-BY-STEP ÈÈ Moderate

ÈÈÈ Difficult
TEENAGE CELL PHONE USAGE DATA ANALYSIS È
1. Mary Collins works for a cell phone company. She’s been asked to analyze data gathered in
a survey of teenage cell phone users to find possible service packages for the company to offer.
After following the directions below to complete the worksheet, your solution will be similar
to that shown here.

a. Open an Excel 2010 workbook.

EX1.88 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.88 30/07/10 11:50 AM

 First Pages

b. Enter the data here into the spreadsheet.

Row Col A Col B Col C Col D

1 Never Sometimes A Lot
2 Email 5340 10832 1693
3 Instant Message 508 3164 14209
4 Listen to Music 2455 7271 8163
5 Shop Online 9857 6439 1153
6 Online Banking 2894 10847 4016
7 Get News 4816 7391 5548
8 Web Surf 4231 5939 7602
9 Watch Videos 3865 6242 7571
10 Get Directions 8426 5887 3489
11 Check Sports Scores 12940 3142 1640

c. AutoFit the width of column A. Insert a new row above row 1.

d. In cell A1, enter the worksheet title Teenage Cell Phone Usage.
e. Merge and center the worksheet title over columns A through D.
f. Change the font in cell A1 to 16 points.
g. Bold the column labels in row 2 and the row labels in column A.
h. Add an outside border around A1 through D2, and another around A3 through D12.
i. Center the column headings and data in cells B2 through D12. AutoFit column C.
j. Apply the comma number format with zero decimal places to the data in B3 through D12.
k. Add fill color of your choice, using one color for rows 1 and 2 and the row labels in column A
and a different color for cells B3 through D12. Change text color for readability if needed. Add
font color of your choice and bold to the worksheet title.
l. Create a chart using the data in the worksheet, and move the chart to row 14.
m. Type your name in cell A30 and the date in cell A31.
n. Save the workbook as Teenage Cell Phone Usage to your solution file location. Print the
worksheet.

Lab Exercises EX1.89

ole31265_EXLab1_001-096.indd EX1.89 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
ANIMAL RESCUE FOUNDATION ADOPTION ANALYSIS È
2. Edward Corwin works for the Animal Rescue Foundation. One of his responsibilities is to collect
and analyze data on the animals that enter the shelters. He has compiled a list of the cost of hous-
ing animals by the local shelters for the past four years. After following the directions below to
complete the worksheet, your solution will be similar to that shown here.
a. Open the workbook ex01_Animal
Housing. Spell check the work-
sheet and correct any misspelled
words.
b. Modify the title in cell B2 so the
first letter of each word is capi-
talized. Increase the font size to
14 point and change the row
height to 22.5. Merge and center
both title lines across columns
A through E.
c. Bold and center the headings in
row 5. Format cells B5 and C5
to text. Insert a blank row above
row 6.
d. In row 17, enter a function to
total the data under the 2009 col-
umn and a function to total the
data under the 2010 column.
e. Format the numbers in rows
7 and 17 using the Accounting
style with zero decimal places.
Format the numbers in rows 8
through 16 using the Comma
style with zero decimal places.
f. Adjust the column widths so all
the data is fully displayed. Insert
a blank row above row 17.

EX1.90 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.90 30/07/10 11:50 AM

 First Pages

g. Edward has just received the information for the last two years. Enter the following data in the
cells indicated.

Row Col D Col E

5 2011 2012
7 142600 152800
8 213500 220300
9 12500 14700
10 2200 4200
11 1400 1600
12 148500 142400
13 11700 10500
14 18900 25300
15 14200 13500
16 26700 29900

h. Format the column heads to match the style of the corresponding information in columns
B and C.
i. Copy the Total function in cell B18 to calculate the total for each of the new years. Increase the
indent in cell A18
j. Add font and fill colors to the worksheet as you like.
k. Add a thick box border around A1through E18.
l. Move the row of ASPCA data to above the row of FOCAS data.
m. Delete the word “Shelter” in cell A5.
n. Next you want to create a chart showing the annual data for the three largest shelter
groups. Select the range A5 through E9 and create a chart displaying the data. Move the
chart to cell A20.
o. Enter your name in cell A35 and the current date in cell A36. Format cell A36 to display the
month, day, and year (March 14, 2001) date format.
p. Move to cell A1. Save the workbook as Animal Housing Analysis to your solution file location.
Preview and print the worksheet.
q. Print the worksheet with formulas using landscape orientation so that it fits on one page.

Lab Exercises EX1.91

ole31265_EXLab1_001-096.indd EX1.91 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
HURRICANE ANALYSIS WORKSHEET È
3. Mary Ellen is a manager for an insurance agency. One of her responsibilities is to collect and
analyze data on weather conditions in geographical areas. She has compiled a list of hurricanes
from the National Weather Service. After following the directions below to complete the worksheet,
your solution will be similar to that shown here.
a. Open the workbook ex01_US
Hurricanes. Spell check the
worksheet and correct any
misspelled words.
b. Modify the title in cell A1 so the
first letter of each word is capi-
talized, except “by.” Merge and
center the two worksheet titles
across columns A through I.
Increase the font size to
12 point. Bold the titles.
c. Enter the heading % Major to All
in cell I3 and increase the widths
of columns H and I to fully
display their headings.
d. Merge and center cells A3 and
A4. Merge and center cells I3
and I4.
e. Adjust the width of column A so all the data is fully displayed.
f. Insert new rows above row 1 and below row 3.
g. Bold the titles in rows 5 and 6. Merge and center cells B5:H5. Underline and center the titles in
cells B6 through H6.
h. Enter the formula =H7/G7 in cell I7. Copy the formula down column I for the rest of the states.
Format the numbers in column I as a percent with one decimal place.
i. Center the data in cells B7 through I33.
j. Add font and fill colors to the worksheet as you like.
k. In the All column, locate the four states with the highest percent of major hurricanes and fill the
cells containing the percentages with a different fill color.
l. In cells K7 through K10, enter the names of the four states with the highest percent of major
hurricanes. In cells L7 through L10, enter the corresponding numbers. Enter “Total Hurricanes”
in cell L6. Add color to the cells to match the data.
m. Create a chart using the data in the table you just created and move it cell J12.
n. Enter your name and the current date on separate rows just below the chart. Format the date to
display the month, day, and year (March 14, 2001) date format.
o. Move to cell A1. Save the workbook as US Hurricanes Analysis to your solution file location. Print
the worksheet in landscape orientation on one page.

EX1.92 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.92 30/07/10 11:50 AM

 First Pages

COMPARATIVE MEDIAN INCOME FOR FOUR-PERSON FAMILIES ÈÈÈ

4. Terrence Lewis works for an employment agency and needs to provide information about salaries
in different states for his clients. He has started a worksheet with data from the years 2006–2008.
After following the directions below to complete the worksheet, the first page of your solution will
be similar to that shown here.
a. Open the workbook ex01_Family Income.
Spell check the worksheet and correct any
misspelled words.
b. Edit the title in cell A1 by capitalizing the
first letter of each word except the word
“by” and by deleting the comma following
“income.” Merge and center the title across
columns A through F. Increase the font size
to 12, and bold and apply a font color of your
choice to the title.
c. Center-align and underline the column head-
ings in row 2. Adjust the width of column A
to fully display the labels. Insert blank rows
above and below the title.
d. Right-align cells B5 through D56 and format
the cells as Accounting with zero decimal
places.
e. Enter the heading Average in cell E4. Center
and underline the heading. Calculate the
average income in cell E5 using the function
=Average(B5:D5). Copy the formula to cells
E6 through E56.
f. Next, you would like to calculate the percent
of change from 2006 to 2008. Enter the
heading % Change in cell F4. Center
and underline the title. Enter the formula
=(D5–B5)/D5 in cell F5. Format the cell as a
percentage with two decimal places. Copy the
formula to cells F6 through F56.
g. AutoFit columns B through F.
h. Add font and fill colors to the worksheet as
you like. Locate the state with the highest
positive % change and the state with the
highest negative % change. Surround their
entire rows with a box border and a different
fill color.
i. Enter your name and the current date on
separate rows just below the last lines. For-
mat the date to day, month, year (14-Mar-01)
date format.

Lab Exercises EX1.93

ole31265_EXLab1_001-096.indd EX1.93 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
j. Move to cell A1. Save the workbook as Family Income to your solution file location. Change page
size to legal to fit on one page, and print the worksheet.
k. Print the worksheet again with formulas on one page using landscape orientation.

PECAN GROVES HOMEOWNERS ASSOCIATION ÈÈÈ

5. The Pecan Groves Homeowners Association is planning a large building project and wants to proj-
ect how much there is likely to be in the cash budget after expenses. Using last year’s final budget
numbers, you will create a projected budget for 2012. After following the directions below to com-
plete the worksheet, your solution will be similar to that shown here.
a. Open the workbook file ex01_
Pecan Groves Budget. Spell
check the worksheet and cor-
rect any misspelled words.
b. Change the font type, size,
and color and fill color of the
three worksheet title lines to a
format of your choice. Merge
and center the titles across
columns A through E.
c. Set the width of column A to
25. Insert a column between
columns B and C. Merge and
center cell B5 across columns
B and C. Merge and center
cell D5 across columns D and
E. Set the fill color of cells B5
and D5 to match the fill color
in the titles.
d. Center the text in cell A6 and
change the font color and fill
color to a color of your choice.
Apply the same formats to cell
A13.
e. Right-justify the text in cells
A12 and A25. Indent the text in
cells A19:A23. Indent the text
in cell A23 again. Move the data in cells B14:B17 to C14:C17. Move the data in cell B24 to C24.
f. In cell C12, sum the income data. In cell C23, sum the maintenance expenditure data. In cell
C25, sum all the expenditures items. In cell C27, enter a formula to calculate the ending cash
balance. (Hint: =C12-C25)
g. Each of the 2012 Income items is projected to increase by 6 percent over the previous year.
Enter a formula in cell D7 to calculate the increase in cash on hand. (Hint: =B7*1.06) Copy this
formula down column D to the other Income items. Enter the appropriate function into cell E12
to calculate the 2012 total income value.

EX1.94 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.94 30/07/10 11:50 AM

 First Pages

h. Each of the 2012 expenditure items except for the maintenance expenditures is projected to
increase by 12 percent over the previous year. Enter the appropriate formulas in column E to
reflect this change. Each maintenance expense is projected to increase 15 percent. Enter the
appropriate formulas in column D. Enter the appropriate function in cell E23 to calculate the
total maintenance expenses. Use formulas to calculate the value for 2012’s total expenditures and
ending cash balance.
i. Format cells B7, B19, C12, C14, C25, C27, D7, D19, E12, E14, E25, and E27 as Accounting with
zero decimal places. Format all other cells containing numbers except for B5 and D5 to comma
with zero decimal places. Set the column widths of columns B through E to 12. Fill the cells
A27:E27 with the same fill color used for the titles. Delete column F. Surround the entire work-
sheet with a thick box border.
j. Enter your name and the current date on separate rows just below the worksheet.
k. Save the workbook file as Pecan Groves Budget to your solution file location. Print the
worksheet.
l. Print the worksheet again with formulas using landscape orientation.

ON YOUR OWN
TRACKING YOUR CALORIES È
1. A worksheet can be used to track your calories for the day. Design and create a worksheet to record
the food you consume and the exercise you do on a daily basis. The worksheet should include your
food consumption for all meals and snacks and the activities you performed for a week. Use the
Web as a resource to find out the calorie values for the items you consumed, or refer to the calorie
information on the product packaging, and to find out the caloric expenditure for the exercises you
do. Include an appropriate title, row and column headings, and formulas to calculate your total
calorie intake and expenditure on a daily basis. Include a formula to calculate the percent deviation
from your recommended daily calorie intake. Format the worksheet appropriately using features
presented in this lab. Enter real or sample data. Include your name and date above the worksheet.
Spell check the worksheet. Save the workbook as Calorie Tracking and print the worksheet.

Lab Exercises EX1.95

ole31265_EXLab1_001-096.indd EX1.95 30/07/10 11:50 AM

 First Pages

LAB EXERCISES
CREATING A PERSONAL BUDGET È
2. In a blank Excel 2010 workbook, create a personal three-month budget. Enter an appropriate title
and use descriptive labels for your monthly expenses (food, rent, car payments, insurance, credit
card payments, etc.). Spell check your worksheet. Enter your monthly expenses (or, if you prefer,
any reasonable sample data). Use formulas to calculate total expenses for each month and the aver-
age monthly expenditures for each expense item. Add a column for projection for the next year
showing a 2.5 percent increase in the cost of living. Enhance the worksheet using features you
learned in this lab. Enter your name and the current date on separate rows just below the work-
sheet. Save the workbook as Personal Budget. Preview and print the worksheet.

TRACKING PROJECT HOURS ÈÈ

3. Samantha Johnson is the project manager for a small publishing company. She has four part-time
employees (Melanie, Bob, Vanessa, and Rudy). Using the steps in the planning process, plan and
create a worksheet for Samantha that can be used to record and analyze the hours each employee
works per day during the month on two projects: magazine and brochure. Hours-worked data for
each employee will be entered into the worksheet. Using that data, the worksheet will calculate the
total number of hours for each person per project. Additionally, it will calculate the total weekly
hours for each project. Write a short paragraph describing how you used each of the planning
steps. Enter sample data in a worksheet. Include your name and the current date on separate rows
just below the worksheet. Spell check the worksheet. Save the workbook as Project Hours. Preview
and print the worksheet.

MUSIC ANALYSIS ÈÈÈ

4. Use the library and/or the Web to locate information on trends in CD sales versus music down-
loads on the Internet. Create a worksheet to display information relating to the increasing usage by
country, age group, or any other trend you locate. Calculate totals or averages based on your data.
Enhance the worksheet using features you learned in this lab. Enter your name and the current
date on separate rows just below the worksheet. Spell check the worksheet. Save the workbook as
Music Analysis. Preview and print the worksheet.

HOME ELECTRONICS ANALYSIS ÈÈÈ

5. A national electronics retailer wants to analyze the trend in home electronics sales and usage for
the past three years. Design and create a worksheet to record the number of households (one-
person, two-person, and four-person) who have computers, Internet access, televisions, and cable
TV access. Include an appropriate title, row and column headings, and formulas to calculate aver-
age by category and by year. Include a formula to calculate the percent growth over the three years.
Format the worksheet appropriately using features presented in this tutorial. Enter sample data for
the three years. Include your name and date above the worksheet. Spell check the worksheet. Save
the workbook as Home Electronics Analysis and print the worksheet.

EX1.96 Lab 1: Creating and Editing a Worksheet WWW.MHHE.COM/OLEARY

Excel 2010

ole31265_EXLab1_001-096.indd EX1.96 30/07/10 11:50 AM