MICROSOFT OFFICE EXCEL

Creating a New Workbook

In this lesson, you learn how to start and exit Excel and you become familiar with the Excel window.
You also learn how to create new workbooks and open existing workbook files.

Starting Excel

Excel is a spreadsheet program that can help you create worksheets and invoices and do simple and
sophisticated number crunching; it is designed to help you calculate the results of formulas and help
you organize and analyze numerical data.
To start Excel from the Windows desktop, follow these steps:
1. Click the Start button, and the Start menu appears.
2. Point at All Programs in Windows XP and the Programs menu appears.
3. Select the Microsoft Office program group and then Microsoft Office Excel 2003 to start
the program.
When you work in Excel, you use workbook files to hold your numerical data, formulas, and other
objects, such as Excel charts. Each Excel workbook can consist of several sheets; each sheet is
called a worksheet.

You enter your numbers and formulas on one of the workbook's worksheets. Each worksheet
consists of 256 columns. The columns begin with column A and proceed through the alphabet. The
27th column is AA, followed by AB, AC, and this convention for naming subsequent columns
continues through the entire alphabet until you end up with the last column (column 256), which is
designated IV.
Each worksheet also consists of 65,536 rows. The intersection of a column and a row on the
worksheet is called a cell. Each cell has an address that consists of the column and row that
intersect to make the cell. For example, the very first cell on a worksheet is in column A and row 1,
so the cell's address is A1.
The Excel window shown here includes many of the various elements available in other Office
applications, such as Word or PowerPoint. These elements include a menu bar (from which you select
commands), a status bar (which displays the status of the current activity), and toolbars (which
contain buttons and drop-down lists that provide quick access to various commands and features).

Element Description

Formula bar When you enter information into a cell, it appears in the Formula bar. You can
use the Formula bar to edit the data later. The cell's location also appears in
the Formula bar.

Column headings The letters across the top of the worksheet, which identify the columns in the
worksheet.

Row headings The numbers down the side of the worksheet, which identify the rows in the
worksheet.

Cell selector The dark outline that indicates the active cell. (It highlights the cell you are
currently working in.)
Worksheet tabs These tabs help you move from worksheet to worksheet within the workbook.

To create a new workbook, follow these steps:

1. Open the File menu and select New. The New Workbook task pane appears on the right side
of the Excel window (if you did not close it as outlined earlier, it should already be open).
2. The New Workbook task pane enables you to create new blank workbooks or create
workbooks based on an existing workbook or a template.
3. To create a blank workbook, click the Blank Workbook icon. A new blank workbook opens in
the Excel window.
The first time you save a workbook; you must name it and specify a location where it should be
saved.
Follow these steps to save your workbook:
1. Open the File menu and select Save, or click the Save button on the Standard toolbar. The
Save As dialog box appears.
2. Type the name you want to give the workbook in the File Name text box. You can use up to
218 characters, including any combination of letters, numbers, and spaces.
3. Normally, Excel saves your workbooks in the My Documents folder. To save the file to a
different folder or drive (such as a network drive), select a new location using the Save In
list.
4. Click Save to save your workbook and close the Save As dialog box.
To save changes that you make to a workbook that you have previously saved, just click the Save
button on the Standard toolbar. You can also press the shortcut key combination of Ctrl+S to save
changes to your workbook.
Opening an Existing Workbook
If you have a workbook you've previously saved that you would like to work on, you must open the
file first, before you can make any changes. Follow these steps to open an existing workbook:
1. Open the File menu and select Open, or click the Open button on the Standard toolbar. The
Open dialog box appears.
2. If the file is not located in the current folder, open the Look In drop-down list box and
select the correct drive and folder.
3. Select the file you want to open in the files and folders list.
4. To see a preview of the workbook before you open it, click the Views button and select
Preview. Excel displays the contents of the workbook in a window to the right of the dialog
box.
5. Click Open to open the currently selected workbook.
Closing Workbooks
When you have finished with a particular workbook and want to continue working in Excel, you can
easily close the current workbook. Click the Close (X) button in the upper-right corner of the
workbook. (There are two Close buttons; the one on top closes Excel, and the one below it closes
the current workbook window.)
You can also close the current workbook by selecting File, Close. If you have changed the workbook
since the last time you saved it, you will be prompted to save any changes.
Exiting Excel
When you have finished working with Excel, you need to exit the application. This closes all
workbooks that are currently open. To exit Excel, select the File menu and select Exit. Or you can
click the Close (X) button at the upper-right corner of the Excel window.
If you have changed any of the workbooks that you were working with, you are prompted to save
changes to these workbook files before exiting Excel.
Entering Text into the Worksheet
Text is any combination of letters, numbers, and spaces. By default, text is automatically
left-aligned in a cell, whereas numerical data is right-aligned.

To enter text into a cell, follow these steps:

1. Use your mouse or the keyboard arrows to select the cell in which you want to enter text.
2. Type the text. As you type, your text appears in the cell and in the Formula bar.
3. Press Enter. Your text appears in the cell, left-aligned. The cell selector moves down one
cell. You can also press Tab or an arrow key to enter the text and move to the next cell to
the right (or in the direction of the arrow).
Adding Comments to Cells
These comments allow you to associate information with a cell—information that does not appear
(by default) with the worksheet when sent to the printer.
Comments are similar to placing a Post-it note on a cell, reminding you that an outstanding issue is
related to that cell. For example, if you need to check the value that you've placed in a particular
cell to make sure that it's accurate, you can place a comment in the cell . Cells containing comments
are marked with a red triangle in the upper-right corner of the cell. To view a comment, place the
mouse pointer on the comment triangle.
To insert a comment into a cell, follow these steps:
1. Click the cell in which you want to place the comment.
2. Select Insert, Comment. A comment box appears next to the cell.
3. Type your information into the comment box.
4. Click anywhere else in the worksheet to close the comment box.
You can also easily remove comments from cells. Select the cell, and then select Edit and point at
Clear. On the cascading menu, select Comments to remove the comment.
Entering Numbers
Data that serves as the values in your workbooks can include the numeric characters 0–9.
To enter a value, follow these steps:
1. Click in the cell where you want to enter the value.
2. Type the value. To enter a negative number, precede it with a minus sign or surround it with
parentheses.
3. Press Enter or the Tab key; the value appears in the cell right-aligned.
Copying Data to Other Cells
Another way to enter labels or values onto a sheet is to use the Fill feature. You can copy (fill) an
entry into surrounding cells. For example, suppose you have a list of salespeople on a worksheet, and
they will each get a $100 bonus. You can enter the 100 once and then use the Fill feature to insert
multiple copies of 100 into nearby cells. To use the Fill feature for copying, follow these steps:
1. Click the fill handle of the cell (the small block in the lower-right corner of the cell) that
holds the data that you want to copy .
2. Drag the fill handle down or to the right to copy the data to adjacent cells. A data tag
appears to let you know exactly what data is being copied into the cells.
3. Release the mouse button. The data is "filled" into the selected cells.
When you release the mouse, a shortcut box for Fill options appears at the end of the cells that you
filled. Copy Cells is the default option for the Fill feature, so you can ignore the shortcut box for
the moment. It does come into play when you enter a series in the next section.
Entering a Series of Numbers, Dates, and Other Data
Entering a value series (such as January, February, and March or 1, 2, 3, 4, and so on) is
accomplished using the Fill feature discussed in the preceding section. When you use the Fill
feature, Excel looks at the cell holding the data and tries to determine whether you want to just
copy that information into the adjacent cells or use it as the starting point for a particular series
of data. For example, with Monday entered in the first cell of the series, Excel automatically
inserts Tuesday, Wednesday, and so on into the adjacent cells when you use the Fill feature.
Sometimes Excel isn't quite sure whether you want to copy the data when you use Fill or create a
series. This is where the Fill options shortcut box comes in. It enables you to select how the Fill
feature should treat the data that you have "filled" into the adjacent cells.
When you create a series using Fill, the series progresses by one increment. For example, a series
starting with 1 would proceed to 2, 3, 4, and so on. If you want to create a series that uses some
increment other than 1, you must create a custom series.
Entering a Custom Series
If you want to create a series such as 10, 20, 30, where the series uses a custom increment
between the values, you need to create a custom series. Excel provides two ways to create a custom
series. To create a custom series using Fill, follow these steps:
1. Enter the first value in the series into a cell.
2. Enter the second value in the series into the next cell. For example, you might enter 10 into
the first cell and then 20 into the second cell. This lets Excel know that the increment for
the series is 10.
3. Select both cells by clicking the first cell and dragging over the second cell.
4. Drag the fill handle of the second cell to the other cells that will be part of the series.
Excel analyzes the two cells, sees the incremental pattern, and re-creates it in subsequent
cells.
You can also create a custom series using the Series dialog box. This enables you to specify the
increment or step value for the series and even specify a stop value for the series.
1. Enter the first value in the series into a cell.
2. Select the cells that you want included in the series.
3. Select the Edit menu, point at Fill, and then select Series. The Series dialog box opens.
4. Enter the Step Value for the series. You can also enter a Stop Value for the series if you
did not select the cells used for the series in step 2. For example, if you want to add a
series to a column of cells and have clicked in the first cell that will receive a value, using a
Stop Value (such as 100 for a series that will go from 1 to 100) will "stop" entering values in
the cells when it reaches 100—the Stop Value.
5. Click OK to create the series.
Understanding Excel Formulas
One way to add calculations to an Excel workbook is to create your own formulas. Formulas are
typically used to perform calculations such as addition, subtraction, multiplication, and division.
Notice that the formula begins with the equal sign (=). This lets Excel know that the information
that you are placing in the cell is meant to do a calculation.
Formula Operators
As previously mentioned, you can create formulas that add, subtract, and multiply cells in the
worksheet.
Operator Performs Sample Result
Formula

^ Exponentiation =A1^3 Enters the result of raising the value in cell A1 to the
third power

+ Addition =A1+A2 Enters the total of the values in cells A1 and A2

– Subtraction =A1–A2 Subtracts the value in cell A2 from the value in cell A1
* Multiplication =A2*A3 Multiplies the value in cell A2 by cell A3

/ Division =A1/B1 Divides the value in cell A1 by the value in cell B1

Order of Operations
The order of operations, or operator precedence, simply means that some operations take
precedence over other operations in a formula. For example, in the formula =C2+D2*E2, the
multiplication of D2 times E2 takes precedence, so D2 is multiplied by E2 and then the value in cell
C2 is added to the result.
You can force the precedence of an operation by using parentheses. For example, if you want C2 and
D2 added before they are multiplied by E2, the formula would have to be written =(C2+D2)*E2.
The natural order of math operators follows:
1. Exponent (^) and calculations within parentheses
2. Multiplication (*) and division (/)
3. Addition (+) and subtraction (–)
In the case of operations such as multiplication and division, which operate at the same level in the
natural order, a formula containing the multiplication operator followed by the division operator will
execute these operators in the order they appear in the formula from left to right. If you don't
take this order into consideration, you could run into problems when entering your formulas. For
example, if you want to determine the average of the values in cells A1, B1, and C1, and you enter
=A1+B1+C1/3, you'll get the wrong answer. The value in C1 will be divided by 3, and that result will be
added to A1+B1. To determine the total of A1 through C1 first, you must enclose that group of
values in parentheses: =(A1+B1+C1)/3.
Entering Formulas
You can enter formulas in one of two ways: by typing the entire formula, including the cell
addresses, or by typing the formula operators and selecting the cell references. Take a look at both
ways.
To type a formula, perform the following steps:
1. Select the cell where you will place the formula.
2. Type an equal sign (=) into the cell to begin the formula.
3. Enter the appropriate cell references and operators for the formula. The formula also
appears in the Formula bar as you type it. The cells that you specify in the formula are
highlighted with a colored border.
4. Press Enter when you have finished the formula, and Excel calculates the result.
To enter a formula by selecting cell addresses, follow these steps:
1. Click in the cell where you will place the formula.
2. Type the equal sign (=) to begin the formula.
3. Click the cell whose address you want to appear first in the formula. You can also click a cell
in a different worksheet or workbook. The cell address appears in the cell and in the
Formula bar.
4. Type a mathematical operator after the value to indicate the next operation you want to
perform. The operator appears in the cell and in the Formula bar.
5. Continue clicking cells and typing operators until the formula is complete.
6. Press Enter to accept the formula and have Excel place its results into the cell.
Displaying Formulas
Normally, Excel does not display the formula in a cell. Instead, it displays the result of the
calculation. You can view the formula by selecting the cell and looking in the Formula bar. However,
if you're trying to review all the formulas in a large worksheet, it would be easier if you could see
them all at once (and even print them).
If you want to view formulas in a worksheet, follow these steps:
1. Open the Tools menu and choose Options.
 2. Click the View tab.
3. In the Window options area of the View tab (near the bottom of the tab), click to select
the Formulas check box.
4. Click OK.

Editing Formulas
Editing a formula is the same as editing any entry in Excel. The following steps show how you do it:
1. Select the cell that contains the formula you want to edit.
2. Click in the Formula bar to place the insertion point in the formula, or press F2 to enter
Edit mode (the insertion point is placed at the end of the entry in that cell).
3. Press the left-arrow key or the right-arrow key to move the insertion point within the
formula. Then, use the Backspace key to delete characters to the left, or use the Delete
key to delete characters to the right. Type any additional characters.
4. When you finish editing the data, click the Enter button on the Formula bar or press Enter
to accept your changes.
Performing Calculations with Functions
In this lesson, you learn how to perform calculations with functions and how to use the Insert
Function feature to quickly insert functions into your worksheets.
What Are Functions?
Functions are ready-made formulas that perform a series of operations on a specified range of
values. For example, to determine the sum of a series of numbers in cells A1 through H1, you can
enter the function =SUM(A1:H1). Excel functions can do all kinds of calculations for all kinds of
purposes, including financial and statistical calculations.
Every function consists of the following three elements:
The = sign, which indicates that what follows is a function (formula).
The function name, such as SUM, that indicates which operation will be performed.
A list of cell addresses, such as (A1:H1), which are to be acted upon by the function. Some
functions can include more than one set of cell addresses, which are separated by commas
(such as A1,B1,H1).
You can enter functions into the worksheet by typing the function and cell references (as you did
with your own formulas), or you can use the Insert Function feature, which walks you through the
process of creating a function in a worksheet (you will work with the Insert Function feature in a
moment).
Function Example Description

AVERAGE =AVERAGE(B4:B9) Calculates the mean or average of a group of cell values.

COUNT =COUNT(A3:A7) Counts the number of cells that hold values in the selected
range or group of cells. This can also be used to tell you how
many cells are in a particular column, which tells you how
many rows are in your spreadsheet.

IF =IF(A3>=1000, Allows you to place a conditional function in a cell. In this

"BONUS","NO example, if A3 is greater than or equal to 1000, the true
BONUS") value, BONUS, is used. If A3 is less than 1000, the false
value, NO BONUS, is placed in the cell.

MAX =MAX(B4:B10) Returns the maximum value in a range of cells.

MIN =MIN(B4:B10) Returns the minimum value in a range of cells.

SUM =SUM(A1:A10) Calculates the total in a range of cells.

Specify Text with Quotation Marks When you are entering text into a function, the text must be
enclosed within quotation marks. For example, in the function =IF(A5>2000,"BONUS","NO
BONUS"), if the condition is met (the cell value is greater than 2000), the word BONUS will be
returned by the function. If the condition is not met, the phrase NO BONUS will be returned in the
cell by the function.
Using AutoSum
Adding a group of cells is probably one of the most often-used calculations in an Excel worksheet.
Because of this fact, Excel makes it very easy for you to place the SUM function into a cell. Excel
provides the AutoSum button on the Standard toolbar. AutoSum looks at a column or row of cell
values and tries to select the cells that should be included in the SUM function.
To use AutoSum, follow these steps:
1. Select the cell where you want to place the SUM function. Typically, you will choose a cell
that is at the bottom of a column of values or at the end of a row of data. This makes it
easy for AutoSum to figure out the range of cells that it should include in the SUM
function.
2. Click the AutoSum button on the Standard toolbar. AutoSum inserts =SUM and the cell
addresses that it thinks should be included in the function.
3. If the range of cell addresses that AutoSum selected is incorrect, use the mouse to drag
and select the appropriate group of cells.
4. Press the Enter key. AutoSum calculates the total for the selected range of cells.
Freezing Column and Row Labels
When you work with very large worksheets, it can be very annoying as you scroll to the right or
down through the worksheet when you can no longer see your row headings or column headings,
respectively. For example, you might be entering customer data where the customer's name is in
the first column of the worksheet, and when you scroll to the extreme right to enter data, you can
no longer see the customer names.
You can freeze your column and row labels so that you can view them no matter how far you scroll
down or to the right in your worksheet.
To freeze row or column headings (or both), follow these steps:
1. Click the cell to the right of the row labels and/or below any column labels you want to
freeze. This highlights the cell.
2. Select the Window menu, and then select Freeze Panes.
You might want to experiment on a large worksheet. Freeze the column and row headings, and then
use the keyboard or the mouse to move around in the worksheet. As you do, the row and/or column
headings remain locked in their positions. This enables you to view data in other parts of the
worksheet without losing track of what that data represents.
When you have finished working with the frozen column and row headings, you can easily unfreeze
them. Select the Window menu again and select Unfreeze Panes.
Locking Cells in a Worksheet
In some situations, you might create a worksheet or worksheets and someone else will enter the
data. In these situations, you might want to lock cells that contain formulas and functions so that
the person doing the data entry does not accidentally overwrite or delete the worksheet formulas
or functions. Locking cells in a worksheet is a two-step process. You must first select and lock the
cells. Then, you must turn on protection on the entire worksheet for the "lock" to go into effect.
Follow these steps to lock cells on a worksheet:
1. Select the cells in the worksheet that you want to lock. These are typically the cells that
contain formulas or functions.
2. Select Format and then Cells. The Format Cells dialog box appears. Click the Protection tab
on the dialog box .
3. Be sure the Locked check box is selected on the Protection tab. Then click OK.
 4. Now you must protect the entire worksheet to have the lock feature protect the cells that
you selected.
Select the Tools menu, point at Protections, and then select Protect Sheet. The Protect
Sheet dialog box appears.
5. Enter a password if you want to require a password for "unprotecting" the worksheet. Then
click OK.
The cells that you locked in steps 1, 2, and 3 will no longer accept data entry. Every time someone
tries to enter data into one of those cells, Excel displays a message stating that data will not be
accepted. The cells are now protected, and you can pass the workbook on to the person who handles
the data entry.
Editing Worksheets
In this lesson, you learn how to change data and how to undo those changes if necessary. You also
learn how to search for data and replace it with other data, how to spell check your work, and how
to copy, move, and delete data.
Correcting Data
You've taken a look at entering text, values, formulas, and functions. There will definitely be
occasions when you need to edit information in a cell. One way to change an entry in a cell is to
replace it by selecting the cell and then entering new data. Just press Enter after entering the
information. If you just want to modify the existing cell content, you can also edit data within a cell.
To edit information in a cell, follow these steps:
1. Select the cell in which you want to edit data.
2. To begin editing, click in the Formula bar to place the insertion point into the cell entry. To
edit within the cell itself, press F2 or double-click the cell. This puts you in Edit mode; the
word Edit appears in the status bar.
3. Press the right- or left-arrow key to move the insertion point within the entry. Press the
Backspace key to delete characters to the left of the insertion point; press the Delete key
to delete characters to the right. Then, type any characters you want to add.
4. Press the Enter key when you have finished making your changes.
5. If you change your mind and you no longer want to edit your entry, click the Cancel button
on the Formula bar or press Esc.
Undoing an Action
Although editing a worksheet is supposed to improve it, you might find that you've done something
to a cell or range of cells that you had not intended. This is where the Undo feature comes in.
You can undo just about any action while working in Excel, including any changes you make to a cell's
data. To undo a change, click the Undo button on the Standard toolbar (or select Edit, Undo).
Moving Data
Moving data is similar to copying except that the data is removed from its original place and placed
into the new location.
To move data, follow these steps:
1. Select the cells you want to move.
2. Click the Cut button.
3. Select the first cell in the area where you want to place the data. To move the data to
another worksheet, change to that worksheet.
4. Click Paste.
Deleting Data
To delete the data in a cell or range of cells, select them and press Delete. Excel also offers some
additional options for deleting cells and their contents:
With the Edit, Clear command, you can delete only the formatting of a cell (or an attached
comment) without deleting its contents. The formatting of a cell includes the cell's color,
 border style, numeric format, font size, and so on. You'll learn more about this option in a
moment.
With the Edit, Delete command, you can remove cells and then shift surrounding cells over
to take their place ("Inserting and Removing Cells, Rows, and Columns").

To use the Clear command to remove the formatting of a cell or a note, follow these steps:
1. Select the cells you want to clear.
2. Open the Edit menu and point at Clear. The Clear submenu appears.
3. Select the desired Clear option: All (which clears the cells of all contents, formatting, and
notes), Formats, Contents, or Comments.
Changing How Numbers and Text Look
In this lesson, you learn how to customize the appearance of numbers in your worksheet and how to
customize your text formatting to achieve the look you want.
Formatting Text and Numbers
When you work in Excel, you work with two types of formatting: value formatting and font
formatting. In value formatting, you assign a particular number style to a cell (or cells) that holds
numeric data. You can assign a currency style, a percent style, or one of several other numeric
styles to values.
Another formatting option available to you in Excel relates to different font attributes. For
example, you can add bold or italic to the contents of a cell or cells. You can also change the font
used for a range of cells or increase the font size.
Next, you take a look at numeric formatting, and then you look at how different font attributes are
controlled in Excel.
Numeric Formatting Options
The numeric values that you place in your Excel cells are more than just numbers; they often
represent dollar amounts, a date, or a percentage. If the various numeric style buttons on the
Formatting toolbar (discussed in the previous section) do not offer the exact format you want for
your numbers, don't worry. Excel's Format Cells dialog box offers a wide range of number formats
and even allows you to create custom formats.
To use the Format Cells dialog box to assign numeric formatting to cells in a worksheet, follow
these steps:
1. Select the cell or range that contains the values you want to format.
2. Select the Format menu and select Cells. The Format Cells dialog box appears.
3. Click the Number tab. The different categories of numeric formats are displayed in a
Category list.
4. In the Category list, select the numeric format category you want to use. The sample box
displays the default format for that category.
5. Click OK to assign the numeric format to the selected cells.
As you can see from the Number tab on the Format Cells dialog box, Excel offers several numeric
formatting styles.
You can also open the Format Cell dialog box using a shortcut menu. Select the cell or cells that you
want to assign a numeric format to, and then right-click those cells. On the shortcut menu that
appears, select Format Cells. Then, select the Number tab to select your numeric format.
How You Can Make Text Look Different
When you type text into a cell, Excel automatically formats it in the Arial font with a text size of
10 points. The 12-point font size is considered typical for business documents (the higher the point
size, the bigger the text is; there are approximately 72 points in an inch). You can select from
several fonts (such as Baskerville, Modern, or Rockwell) and change the size of any font characters
in a cell. You can also apply special font attributes, such as bold, italic, and underline.
Accessing Different Font Attributes
If you would like to access a greater number of font format options for a cell or range of cells, you
can use the Font tab of the Format Cells dialog box. It provides access to different fonts, font
styles, font sizes, font colors, and other text attributes, such as strikethrough and
superscript/subscript.

To format cells using the Font tab of the Format Cells dialog box, follow these steps:
1. Select the cell or range that contains the text you want to format.
2. Select the Format menu and select Cells, or press Ctrl+1. (You can also right-click the
selected cells and choose Format Cells from the shortcut menu.)
3. Click the Font tab. The Font tab provides drop-down lists and check boxes for selecting the
various font attributes.
4. Select the options you want.
5. Click OK to close the dialog box and return to your worksheet.

Aligning Text in Cells

When you enter data into a cell, that data is aligned automatically. Text is aligned on the left, and
numbers are aligned on the right (values resulting from a formula or function are also right-aligned).
Both text and numbers are initially set at the bottom of the cells. However, you can change both
the vertical and the horizontal alignment of data in your cells.
Follow these steps to change the alignment:
1. Select the cell or range you want to align.
2. Select the Format menu and then select Cells. The Format Cells dialog box appears.
3. Click the Alignment tab
4. Choose from the following options to set the alignment:
o Horizontal— Lets you specify a left/right alignment in the cells. (The Center Across
selection centers a title or other text within a range of cells, which is discussed in a
moment.)
o Vertical— Lets you specify how you want the text aligned in relation to the top and
bottom of the cells.
o Orientation— Lets you flip the text sideways or print it from top to bottom instead
of left to right.
o Wrap Text— Tells Excel to wrap long lines of text within a cell without changing the
width of the cell. (Normally, Excel displays all text in a cell on one line.)
o Shrink to Fit— Shrinks the text to fit within the cell's current width. If the cell's
width is adjusted, the text increases or decreases in size accordingly.
o Merge Cells— Combines several cells into a single cell. All data is overlaid, except
for the cell in the upper-left corner of the selected cells.
5. Click OK when you have finished making your selections.
Adding Cell Borders and Shading
In this lesson, you learn how to add borders and shading to your worksheets.
Adding Borders to Cells
As you work with your worksheet onscreen, you'll notice that each cell is identified by gridlines
that surround the cell. By default, these gridlines do not print; even if you choose to print them,
they don't look very good on the printed page. To create well-defined lines on the printout (and
onscreen, for that matter), you can add borders to selected cells or entire cell ranges. A border
can appear on all four sides of a cell or only on selected sides; it's up to you.
To add borders to a cell or range, perform the following steps:
1. Select the cell(s) around which you want a border to appear.
2. Open the Format menu and choose Cells. The Format Cells dialog box appears.
3. Click the Border tab to see the Border options.
 4. Select the desired position, style (thickness), and color for the border. The position of the
border is selected using the buttons along the left of the Border box. You can also click
inside the Border box itself to place the border.
5. Click OK or press Enter.
When you're adding borders to a worksheet, hiding the gridlines onscreen gives you a preview of
how the borders will look when printed. To hide gridlines, select the Tools menu, select Options
(this opens the Options dialog box), and then select the View tab. Remove the check mark from the
Gridlines check box, and then click OK to return to the worksheet. Selecting this option has no
effect on whether the gridlines actually print, only on whether they are displayed onscreen.
Adding Shading to Cells
Another way to offset certain cells in a worksheet is to add shading to those cells. You can always
use the Print Preview command ("Printing Your Workbook") to view your results in black and white
before you print.
1. Select the cell(s) you want to shade.
2. Open the Format menu and choose Cells.
3. Click the Patterns tab. Excel displays the shading options
4. Click the Pattern drop-down arrow to see a grid that contains colors and patterns.
5. Select the shading color and pattern you want to use. The Color options let you choose a
color for the overall shading. The Pattern options let you select a black or colored pattern
that is placed on top of the cell-shading color you selected. A preview of the results appears
in the Sample box.
6. When you have finished making your selections, click OK.
Using AutoFormat
If you don't want to take the time to test different border types and shading styles, you can let
Excel help you with the task of adding some emphasis and interest to the cells of your worksheet.
You can take advantage of AutoFormat, which provides various predesigned table formats that you
can apply to a worksheet.
To use predesigned formats, perform the following steps:
1. Select the cell(s) that contain the data you want to format. This could be the entire
worksheet.
2. Select the Format menu, and then select AutoFormat. The AutoFormat dialog box appears.
3. Scroll through the list to view the various AutoFormat styles provided. When you find a
format that you want to use, click it to select it.
4. To prevent AutoFormat from overwriting certain existing formatting (such as numbers,
alignment, or fonts), click the Options button and deselect the appropriate check boxes.
5. Click OK and your worksheet is formatted.
Applying Conditional Formatting
Another useful formatting feature that Excel provides is conditional formatting. Conditional
formatting formats only cells that meet a certain condition.
To apply conditional formatting, follow these steps:
1. Select the cells to which you want to apply the conditional formatting.
2. Select the Format menu and select Conditional Formatting. The Conditional Formatting
dialog box appears.
3. Be sure that Cell Value Is selected in the Condition 1 drop-down box on the left of the
dialog box.
4. In the next drop-down box to the right, you select the condition. The default is Between.
Other conditions include Equal To, Greater Than, Less Than, and other possibilities. Use the
drop-down box to select the appropriate condition.
5. After selecting the condition, you must specify a cell or cells in the worksheet that Excel
can use as a reference for the conditional formatting. For example, if you select Less Than
as the condition, you must specify a cell in the worksheet that contains a value that can be
 used for comparison with the cells that you are applying the conditional formatting to. Click
the Shrink button on the Conditional Formatting dialog box. You are returned to the
worksheet. Select the reference cell for the condition.
6. Click the Expand button on the Conditional Formatting dialog box to expand the dialog box.
7. Now you can set the formatting that will be applied to cells that meet your condition. Click
the Format button in the Conditional Formatting dialog box and select the formatting
options for your condition in the Format Cells dialog box. Then click OK.
8. After setting the conditions to be met for conditional formatting (you can click Add to set
more than one condition), click OK.
You are returned to the worksheet. Cells that meet the condition you set up for conditional
formatting will be formatted with the options you specified.
Inserting Rows and Columns
As you edit and enhance your worksheets, you might need to add rows or columns within the
worksheet. Inserting entire rows and columns into your worksheet is very straightforward. Follow
these steps:
1. To insert a single row or column, select a cell to the right of where you want to insert a
column or below where you want to insert a row.
To insert multiple columns or rows, select the number of columns or rows you want to insert.
To insert columns, drag over the column letters at the top of the worksheet. To insert rows,
drag over the row numbers. For example, select three column letters or row numbers to
insert three rows or columns.
2. Select the Insert menu, and then select Rows or Columns. Excel inserts rows above your
selection and columns to the left of your selection. The inserted rows or columns contain
the same formatting as the cells (or rows and columns) you selected in step 1.
As you can see, when you insert rows or columns, the Insert Options shortcut icon appears to the
right of the inserted columns or below inserted rows. Use the Insert Options menu to specify the
column or row from which the new column or row should copy its formatting. For example, in the
case of inserted columns, you can choose to copy the formatting from the column to the right or
left of the inserted column or columns, or you can choose to clear the formatting in the inserted
columns.
Removing Rows and Columns
When you delete a row in your worksheet, the rows below the deleted row move up to fill the space.
When you delete a column, the columns to the right shift left.
Follow these steps to delete a row or column:
1. Click the row number or column letter of the row or column you want to delete. You can
select more than one row or column by dragging over the row numbers or column letters.
2. Select the Edit menu and then select Delete. Excel deletes the rows or columns and
renumbers the remaining rows and columns sequentially. All cell references in formulas and
functions are updated appropriately.
Adjusting Column Width and Row Height with a Mouse
It doesn't take very long when you are working in Excel to realize that the default column width of
8.43 characters doesn't accommodate long text entries or values that have been formatted as
currency or other numeric formats. You can adjust the width of a column quickly using the mouse.
You can also adjust row heights, if you want, using the mouse. However, your row heights will adjust
to any font size changes that you make to data held in a particular row. Row heights also adjust if
you wrap text entries within them, "Changing How Numbers and Text Look," for more about
wrapping text). You will probably find that you need to adjust column widths in your worksheets far
more often than row heights.
What Is ########? When you format a value in a cell with numeric formatting and Excel
cannot display the result in the cell because of the column width, Excel displays ######## in
the cell. This lets you know that you need to adjust the column width so that it can accommodate
the entry and its formatting.

To adjust a column width with the mouse, place the mouse pointer on the right border of the
column. A sizing tool appears. Drag the column border to the desired width. You can also adjust the
column width to automatically accommodate the widest entry within a column; just double-click the
sizing tool. This is called AutoFit, and the column adjusts according to the widest entry.
If you want to adjust several columns at once, select the columns. Place the mouse on any of the
column borders and drag to increase or decrease the width. Each selected column is adjusted to the
width you select.
Changing row heights is similar to adjusting column widths. Place the mouse on the lower border of a
row and drag the sizing tool to increase or decrease the row height. To change the height of
multiple rows, select the rows and then drag the border of any of the selected rows to the desired
height.
Inserting Worksheets
When you create a new workbook, it contains three worksheets. You can easily add additional
worksheets to a workbook.
Follow these steps to add a worksheet to a workbook:
1. Select the worksheet that you want to be to the right of the inserted worksheet. For
example, if you select the August sheet, the new sheet will be inserted to the left of
August.
2. Select the Insert menu.
3. Select Worksheet. Excel inserts the new worksheet to the right of the previously selected
worksheet.
Changing Worksheet Tab Names
By default, all worksheets are named SheetX, where X is a number starting with the number 1. So
that you'll have a better idea of the information each sheet contains, you should change the names
that appear on the tabs. Here's how to do it:
1. Double-click the tab of the worksheet you want to rename. The current name is highlighted.
2. Type a new name for the worksheet and press Enter. Excel replaces the default name with
the name you type.
Headers and Footers
Excel enables you to add headers and footers to your worksheets that will appear at the top and
bottom of every page of the printout (respectively). The information can include any text, as well as
page numbers, the current date and time, the workbook filename, and the worksheet tab name.
You can choose the headers and footers that Excel suggests, or you can include any text plus
special commands to control the appearance of the header or footer. For example, you can apply
bold, italic, or underline to the header or footer text. You can also left-align, center, or right-align
your text in a header or footer.
To add headers and footers, follow these steps:
1. Select the File menu and then select Page Setup. The Page Setup dialog box appears. Click
the Header/Footer tab on the dialog box
2. To select a header, click the Header drop-down arrow. Excel displays a list of suggested
header information. Scroll through the list and click a header you want. The sample header
appears at the top of the Header/Footer tab.
3. To select a footer, click the Footer drop-down arrow. Excel displays a list of suggested
footer information. Scroll through the list and click a footer you want. The sample footer
appears at the bottom of the Header/Footer tab.
4. Click OK to close the Page Setup dialog box and return to your worksheet, or click the Print
button to display the Print dialog box and click OK to print your worksheet.
Creating Charts, Understanding Charting Terminology
Charts enable you to create a graphical representation of data in a worksheet. You can use charts
to make data more understandable to people who view your printed worksheets. Before you start
creating charts, you should familiarize yourself with the following terminology:
Data Series— The bars, pie wedges, lines, or other elements that represent plotted values in
a chart. For example, a chart might show a set of similar bars that reflects a series of
values for the same item. The bars in the same data series would all have the same pattern.
If you have more than one pattern of bars, each pattern would represent a separate data
series. For example, charting the sales for Territory 1 versus Territory 2 would require two
data series—one for each territory. Often, data series correspond to rows of data in your
worksheet (although they can correspond to columns of data if that is how you have
arranged the information in your worksheet).
Categories— Categories reflect the number of elements in a series. You might have two data
series that compare the sales of two territories and four categories that compare these
sales over four quarters. Some charts have only one category, and others have several.
Categories normally correspond to the columns in your worksheet, with the category labels
coming from the column headings.
Axis— One side of a chart. A two-dimensional chart has an x-axis (horizontal) and a y-axis
(vertical). The x-axis contains the data series and categories in the chart. If you have more
than one category, the x-axis often contains labels that define what each category
represents. The y-axis reflects the values of the bars, lines, or plot points. In a
three-dimensional chart, the z-axis represents the vertical plane, and the x-axis (distance)
and y-axis (width) represent the two sides on the floor of the chart.
Legend— Defines the separate series of a chart. For example, the legend for a pie chart
shows what each piece of the pie represents.
Gridlines— Typically, gridlines appear along the y-axis of the chart. The y-axis is where your
values are displayed, although they can emanate from the x-axis as well (the x-axis is where
label information normally appears on the chart). Gridlines help you determine a point's
exact value.

Working with Different Chart Types

With Excel, you can create many types of charts. Some common chart types. The chart type you
choose depends on the kind of data you're trying to chart and on how you want to present that
data. The following are the major chart types and their purposes:
Pie— Use this chart type to show the relationship among parts of a whole.
Bar— Use this chart type to compare values at a given point in time.
Column— Similar to the bar chart; use this chart type to emphasize the difference between
items.
Line— Use this chart type to emphasize trends and the change of values over time.
Scatter— Similar to a line chart; use this chart type to emphasize the difference between
two sets of values.
Area— Similar to the line chart; use this chart type to emphasize the amount of change in
values over time.
Most of these basic chart types also come in three-dimensional varieties. In addition to looking
more professional than the standard flat charts, 3D charts can often help your audience distinguish
between different sets of data.

Creating a Chart
You can place your new chart on the same worksheet that contains the chart data (an embedded
chart) or on a separate worksheet (a chart sheet).
To use the Chart Wizard, follow these steps:
1. Select the data you want to chart. If you typed column or row labels (such as Qtr 1, Qtr 2,
and so on) that you want included in the chart, be sure you select those, too.
 2. Click the Chart Wizard button on the Standard toolbar.
3. The Chart Wizard - Step 1 of 4 dialog box appears. Select a Chart Type and a Chart
Sub-Type (a variation on the selected chart type). Click Next.
4. Next, Excel asks whether the selected range is correct. You can correct the range by
typing a new range or by clicking the Shrink button (located at the right end of the Data
Range text box) and selecting the range you want to use.
5. By default, Excel assumes that your different data series are stored in rows. You can
change this to columns if necessary by clicking the Series in Columns option. When you're
ready for the next step, click Next.
6. Click the various tabs to change options for your chart. For example, you can delete the
legend by clicking the Legend tab and deselecting Show Legend. You can add a chart title on
the Titles tab. Add data labels (labels that display the actual value being represented by
each bar, line, and so on) by clicking the Data Labels tab. When you finish making changes,
click Next.
7. Finally, Excel asks whether you want to embed the chart (as an object) in the current
worksheet (or any other existing worksheet in the workbook) or if you want to create a new
worksheet for it. Make your selection and click the Finish button. Your completed chart
appears.

The charts you create are part of the current workbook. To save a chart, simply save the workbook
that contains the chart.

Moving and Resizing a Chart

To move an embedded chart, click anywhere in the chart area and drag it to the new location. To
change the size of a chart, select the chart and then drag one of its handles (the black squares
that border the chart). Drag a corner handle to change the height and width, or drag a side handle
to change only one dimension. (Note that you can't really resize a chart that is on a sheet by itself.)