Working with cells
Use Undo and Redo
To undo a previous action, use the
Undo button on the Standard toolbar, click Edit => Undo,
or press CTRL+Z. Click the small down arrow next to the Undo
button to undo multiple actions. Take note that undoing the seventh previous
action, for example, undoes all actions including and subsequent to the
To redo an "undone" action, use the Redo
toolbar button located next to Undo, click Edit =>
Redo, or press CTRL+Y.
Enter text, dates, and numbers
By default, Excel 2000 left-aligns text and
right-aligns dates and numbers. All dates are formatted as numbers to allow you
to perform calculations using dates.
To change the default date and time
formats, open the Regional Settings (called Regional Operations in
Windows 2000) Control Panel program. You can adjust the appropriate formats on
the Date and Time tabs.
Edit cell content
To overwrite cell contents, select the cell
and simply start typing. To edit content in a cell without overwriting existing
contents, double-click inside the cell to place the cursor within the cell.
Alternatively, select the cell and press F2.
Go to a specific cell
To go to a specific cell in a worksheet,
place the cursor in the name box and type the cell address. Alternatively, press
F5 and enter the cell reference in the Go To dialog box. (You can
also invoke the Go To dialog box by clicking Edit => Go
Insert and delete selected cells
To insert or delete selected cells, select
the number of cells that you wish to insert or delete, and then click
Insert => Cells. In the Insert or Delete dialog
boxes, choose whether to shift the selected cells to the right or in a downward
direction. To delete an entire row or column, select either Entire row or
Entire column and click OK.
Cut, copy, paste, paste special and move selected cells; use the Office
Copying or moving text using Excel 2000's
Cut, Copy, Paste, and Paste Special tools can be
accomplished in four steps:
1. Select the cell or cells to be moved or
2. Click the Cut or Copy
buttons on the formatting toolbar. Alternatively, right-click inside the
selected text and choose Cut or Copy from the shortcut
3. Place the cursor in the destination
4. Click the Paste button on the
Formatting toolbar; you can also right-click in the destination location and
choose Paste from the shortcut menu.
Cut, copy and paste can also be
accomplished by using these shortcut keystrokes: CTRL+C is copy;
CTRL+X is cut; CTRL+V is paste.
The Paste Special command is useful
for pasting only certain elements from the cells that you cut or copied. For
example, you could copy cells that contain formulas and paste only their values
in another location. To use Paste Special, in step 4 of the above
procedure, click Edit => Paste Special.... either from the
shortcut menu or from the Edit menu. In the Paste Special dialog
box, choose the element to paste from the Paste area.
Another really "hot shot" way to use cut,
copy, paste and paste special is to drag a selected range of cells using the
right mouse button. Make sure that you point to an edge of the
selected range before you begin to drag. When you let go of the right mouse
button, the following shortcut menu will appear:
As you can see, you are provided with
several different options for placing the selected cells.
Use Find and Replace
To locate cell contents in a worksheet,
click Edit => Find... or press CTRL+F. In the
Find dialog box, enter the search string under Find what:.
Do use replace, either click Replace
in the Find dialog box or press CTRL+H. Enter the replacement text
under Replace with: and click Find Next to replace individual
cells or Replace All to do a global replace.
Clear cell formats
It is important to remember that cells have
two types of contents: cell contents and cell formats. An
example would be a cell that contains the value 1000, formatted in red italic.
By selecting the cell and pressing DELETE, you clear only the cell
contents. Anything else you type in the cell will appear in red italic. To clear
cell formats, select the cell and click Edit => Clear =>
Work with series (AutoFill)
Excel 2000 can automatically fill in many
different types of series. For example, type "Monday" in a cell. Click in
another cell, and then select the "Monday" cell. Point to the cell's lower
left-hand corner. You should get a small black cross. This is called the
AutoFill handle. Now drag either down or to the right. The yellow ScreenTip
should tell you that Excel is filling in the series for you. For a comprehensive
listing of the types of series that Excel can fill for you, open Excel Help,
click the Answer Wizard tab, and search for "About filling in data based
on adjacent cells."
Another cool thing that you can do with
AutoFill is to select the cell to be converted into a series, point to the lower
right-hand corner, and drag the AutoFill handle with the right mouse
button. Again, when you release the right mouse button a shortcut menu will
appear, giving more AutoFill options than simply using the left mouse
For the ultimate in control when
constructing an AutoFill series, drag the AutoFill handle using the right mouse
button and select Series... from the shortcut menu. In the Series
dialog box, you can select which direction you would like your series completed,
what type of series Excel should use, and so on.
In Excel 2000, you can create a hyperlink
not only to a Web page, but also to a selected range, named range, an e-mail
address, a new document, or to another existing document. The easiest way to
create a hyperlink is to create and select the cell that the reader will click
to use the hyperlink, and then click the Insert Hyperlink button on the
Standard toolbar. Alternatively, you can click Insert =>
Hyperlink, or press CTRL+K.
Next, select the hyperlink reference in the
Link to: portion of the Insert Hyperlink dialog box. You will then
have slightly different navigation options depending upon what kind of hyperlink
you are creating. For example, an Internet hyperlink will require an Internet
URL; a hyperlink to a separate file will require a file path, and so on. If you
click Place in this document, you will have the opportunity to specify
either a cell reference or a defined, named range of cells to which the
hyperlink will point.
Working with files
To save an Excel 2000 workbook (remember
that a workbook is the entire Excel file that is to be saved; a workbook
is by default comprised of three worksheets), click the Save
button on the Standard toolbar, click File => Save, or press
Use either the Places Bar or the Save
in: drop-down list box to navigate to the desired storage location. Enter
the workbook name in File name:, and adjust the format in which the
workbook will be saved in Save as type: (use only if you are saving in a
format other than Excel 2000).
Use Save As (different name, location, format)
Use Save As if you need to save a
copy of the open workbook to a new location and/or under a new name. Click
File => Save As... to open the Save As dialog box. Use
the steps given above for relocating and/or renaming the workbook.
Locate and open an existing workbook
To locate and open an existing workbook,
click the Open button on the Standard toolbar, click File =>
Open, or press CTRL+O.
In the Open dialog box, navigate to
where the desired file is stored by using either the Places Bar or the Look
in: drop-down list box. If the file is not an Excel file, choose the
appropriate file type from Files of type:.
Excel 2000 gives you plenty of options for
opening a file once you have found it. In the Open dialog box, click the
down arrow next to the Open button. Depending on the file type, you have
the option of opening the file read-only, opening a copy of the file, or opening
the file in your default Web browser.
Create a folder
While you are saving an Excel 2000
workbook, you can create a folder in which to store the file. From the Save
As dialog box, click the Create New Folder button on the toolbar.
Enter a name for the new folder and press ENTER to navigate into the new
folder. Save the file as usual.
Use templates to create a new workbook
Opening a new workbook by using the New
button on the Standard toolbar opens a blank workbook based on the default
To open a new workbook based on another
template, click File => New..., or press CTRL+N. In the
New dialog box, select the alternate template that you wish to use.
Depending upon how Office 2000 was installed on your machine, you may have few
or several templates from which to choose. You can visit Microsoft's
to download several useful template designs for Excel 2000.
Save a worksheet/workbook as a Web page
To save a workbook or a worksheet as a
Web-formatted HTML document, click File => Save as Web Page....
In the Save As dialog box, choose a storage location and name the file.
Next to Save:, choose whether you want to save the entire workbook or
just the selected worksheet as a Web page. Click Change Title... to
change the page title that will appear in the Web browser's title bar.
Send a workbook via e-mail
If you are already configured to use
Outlook as your e-mail client program, you can address and send the open
workbook directly by clicking the E-mail button on the Standard toolbar.
Another way to e-mail an open workbook is
to click File => Send To => Mail Recipient to send
the workbook as the body of the message, or Mail Recipient (as
Attachment)... to send the workbook as a message attachment.
Use the Office Assistant
By default, the Office Assistant is
programmed to appear at opportune (and inopportune) times. To invoke the Office
Assistant manually, click Help => Show the Office Assistant. To
use the Office Assistant, simply type your question into the box and press
Search. To configure Office Assistant options (including turning it off
permanently), you can either click Options on the Office Assistant
callout balloon, or right-click the Office Assistant and choose
Options... from the shortcut menu. The Gallery tab allows you to
choose another Office Assistant character; the Options tab allows you to
customize the behavior of the Office Assistant.
Apply font styles (typeface, size, color and styles)
To quickly adjust the font style of
selected cells, use the appropriate buttons on the Formatting
For more control, right-click within the
selected cells and choose Format Cells... from the shortcut menu.
Alternatively, click Format => Cells.... Navigate to the
Font tab of the Format Cells dialog box. Here you can adjust font
face, style, size, underline, color and effects. You also get a preview, which
is a handy way to sample several fonts and formats before deciding on
Apply number formats (currency, percent, dates, comma)
Similar to font formatting, you can go
either with the "quick and dirty" Formatting toolbar buttons, or the Format
Cells dialog box.
Alternatively, navigate to the
Number tab of the Format Cells dialog box. Within each number
category, you can select such options as decimal places, appropriate symbols
such as the dollar sign, and how negative values are represented. Again, you are
given a sample to preview the formats before deciding to use one.
Modify size of rows and columns
When adjusting row or column size, remember
that you always after the row or column to be resized. For example, to
increase the height of row six, place the cursor between row 6 and 7, and
then drag. To decrease the width of column H, place the cursor between columns H
and I, and then drag. To modify more than one row or column at a time, select
the rows or columns, place the cursor after the last selected row or
column, and then drag.
By the way, to select an entire row, click
on the row heading. To select an entire column, click on the column heading. To
select every cell in the worksheet, click the upper-left corner marker, called
the Select All button.
A more precise way to resize rows and
columns is to select the rows or columns to be resized and then click
Format => Row => Height... or Format =>
Column => Width.... Row height is measured in points (72
points=1 inch); column width is measured in characters.
Modify alignment of cell content
To modify the horizontal alignment of cell
content, select the appropriate cells and use the alignment buttons on the
For increased precision, including the
ability to indent cell content, navigate to the Alignment tab of the
Format Cells dialog box. Choose the appropriate text alignment options
under Horizontal: and Vertical:. The Indent: box allows you
to set cell content to a left indent based upon number of characters. The
Orientation area is particularly wild. Here you can rotate cell content
along a 180-degree plane. Either enter a degree value in Degrees, or
click and drag the orientation marker in the Orientation preview box.
Adjust the decimal place
To adjust the decimal place for numbers,
select the cell(s) to be formatted and use the Increase Decimal or
Decrease Decimal buttons on the Formatting toolbar (described above). For
more precision, navigate to the Number tab of the Format Cells
dialog box, choose the desired number category, and select the decimal place
from the Decimal Places: spin box.
Use the Format Painter
The Format Painter allows you to copy and
paste cell formatting. To use Format Painter, select a cell that contains the
formatting to be copied. Next, click the Format Painter button on the
Standard toolbar. Normally the Format Painter turns itself off after one use; to
use Format Painter multiple times consecutively, double-click the Format
Painter button. To apply copied formatting using Format Painter, simply
click the target cell(s). The copied formatting will be automatically applied.
If you make a mistake and accidentally
apply copied formatting, do not fret. Use Format Painter to copy the original
formatting from another cell in the worksheet and apply the original formatting
to the affected cell. Easy!
An AutoFormat is a preset arrangement of
text and number formats which you can conveniently add to a selected range of
cells. To apply an AutoFormat, select the range to be formatted and click
Format => AutoFormat.... In the AutoFormat dialog box,
scroll to view the AutoFormat types. Click Options... to selectively
apply only certain types of formatting. Click OK to apply the AutoFormat
to your selected range.
Apply cell borders and shading
To apply cell borders and shading, select
the cells to be formatted and open the Format Cells dialog box. Navigate
to the Border tab to select a cell border and the Patterns tab to
apply cell shading. On the Border tab, you can either start with a preset
border or draw your own by selecting a line style and color and clicking in the
Border preview box. In the Cell Shading area of the Patterns
tab, you can select a cell shading color and gradient pattern for your
You use the Merge and Center button
on the Formatting toolbar to create headings that span multiple columns. To
merge and center text in cells, first select the heading along with the desired
number of columns. For instance, in the example below you should highlight cells
A1 through F1. Next, click the Merge and Center button on the Formatting
toolbar. This merges the six cells into a single cell at location A1. Gotcha:
make sure that merge and center is one of the last things you do to a data
table, as a merged cell makes it very difficult to modify individual columns
that are located beneath the merged cells.
To remove merge and center from a cell
range, select the merged cell range, and then navigate to the Alignment
tab of the Format Cells dialog box. Under Text Control, clear
Merge cells to reestablish the original cell alignment.
Rotate text and change indents
These tasks are discussed above, beneath
the heading Modify alignment of cell content.
Define, apply, and remove a style
A style in Excel 2000 is a set of cell
formats that you can define and reuse repeatedly in a workbook. The easiest way
to create a style is to first format a cell to match the new style, and then
click Format => Style.... In the Style dialog box, enter
a name for the new style in Style name:. You can remove specific aspects
of the style at your discretion by clearing elements beneath Style Includes
(By Example). Click Modify... to access the Format Cells
dialog box. Click Add to add your newly-defined style to the
To apply a style, select the target cell(s)
and open the Style dialog box. Select the desired style from Style
name: and click OK.
To copy styles between workbooks, open the
workbook to which you would like to copy the style(s) as well as the workbook
that contains the styles to be copied. Next, open the Style dialog box
and click Merge.... Select the workbook that contains the styles to be
copied from the list beneath Merge styles from: and click OK. The
styles will be copied as a group; there is no way to copy individual styles in
Page setup and printing
Preview and print worksheets & workbooks
To use Print Preview, either click the
Print Preview button on the Standard toolbar or click File =>
Print Preview. Click anywhere within the previewed document to zoom in;
click again to zoom out. The Print Preview toolbar offers convenient access to
the Print and Page Setup dialog boxes. You can also view margins
or enter into Page Break Preview mode. Click Close to return to
To print one copy of the active worksheet
using your default printer, click the Print button on the Standard
toolbar. To configure print options, click File => Print... or
press CTRL+P. Click Properties... to configure printer-specific
properties, or select another printer from the Name: drop-down list. You
can also specify the print range, number of copies, and the scope of the print
job (entire workbook, selection, and so on). Click Preview to enter Print
Use Web Page Preview
To preview your Excel 2000 worksheet in
HTML, click File => Web Page Preview. The document will open in
your default Web browser. As you can see in the following graphic, not all
formatting elements translate into HTML. For instance, HTML is incapable of
displaying rotated text.
Print a selection
To print a selection, first select the area
of the worksheet that you want to print. Next, click File =>
Print.... In the Print What area of the Print dialog box,
Change page orientation and scaling
To change a page orientation between
portrait and landscape, open the Page Setup dialog box by clicking
File => Page Setup.... On the Page tab, click either
Portrait or Landscape under Orientation. To scale the
active worksheet to either a larger or a smaller percentage of its original
size, specify the percentage that you would like to scale the document in the
Adjust to: spin box under Scaling. You can also force the
worksheet to print at exactly one page by specifying 1 page wide and 1 page tall
for Fit to:.
Set page margins and centering
You can set page margins and
horizontal/vertical alignment by navigating to the Margins tab of the
Page Setup dialog box. Specify new margin settings by adjusting the
values for the appropriate margin; center information in the active worksheet by
selecting Horizontally or Vertically beneath Center on
page. The preview icon will update to reflect any changes that you make to
the margins or horizontal/vertical centering.
Insert and remove a page break
To insert a horizontal page break in an
Excel 2000 worksheet, click the row heading below the row where you want
the page break to appear and click Insert => Page Break. To
insert a vertical page break in an Excel 2000 worksheet, click the column
heading after the column where you want the page break to appear and
click Insert => Page Break. The page break will appear as a
dashed line. You can preview the page break by entering Print Preview
To remove a page break, click a single cell
below the horizontal page break, or click a single cell after the
vertical page break, and click Insert => Remove Page
Set, print, and clear a print area
A print area is a defined area of a
worksheet that can be used to print just a portion of a worksheet's contents. To
set a print area, select the cells that are to comprise the print area. Next,
click File => Print Area => Set Print Area. Similar
to a manual page break, the print area will appear within dashed lines. Preview
the print area by switching to Print Preview view. When you print using either
the Print toolbar button or the File => Print...
command, you will find that only the print area is printed by
default, even when Active Sheet(s) is selected in the Print what
portion of the Print dialog box.
To remove a print area, click File
=> Print Area => Clear Print Area.
Set up headers and footers
To set up headers and footers, you must
first click View => Header and Footer... to open the Page
Setup dialog box. On the Header/Footer tab, you have a choice of
either selecting one of the preset header and footer formats in the
Header: and Footer: drop-down list boxes, or you can click
Custom Header: or Custom Footer... to design your own.
If you opt to design a custom header or a
custom footer, you will find in the Header or Footer dialog boxes
that you can enter information in three predefined sections: left, center, and
right. Use the toolbar buttons to add date, time, page number, and file path
fields to your header or footer. Don't be surprised when you see a field code
rather than a field result in this dialog box. For example, rather than seeing
today's date, you will more likely see [Date].
Set print titles and options (gridlines, print quality, row & column
Open the Page Setup dialog box and
navigate to the Sheet tab. You can do many powerful things in this part
of Page Setup. If your data spills over one page, you can have Excel 2000 repeat
a title row or title column by marking that row or column. In the Print
titles area, click the Collapse Dialog button next to Rows to
repeat at top: or Columns to repeat at left:. This will "roll up" the
Page Setup dialog box and allow you to complete the next step. To mark a
repeating row or column, simply select one cell in the appropriate row or
column. That's it! Click the Collapse Dialog button again to expand the
Page Setup dialog box.
In the Print area of the Page
Setup dialog box, you can place a check in the appropriate box if you want
gridlines visible when you print your document (they do not print by default),
if you want row and column headings (A,B,C,1,2,3, etc...) to print (they do not
by default), whether you want to print in pure black-and white rather than in
grayscale, and so on. Select Draft Quality if you want to conserve
printer ink or print toner and print your worksheet at a lower
Working with worksheets and workbooks
Insert and delete rows and columns
To insert a row, select a cell in the row
that you want to appear after the new row. Then, click Insert
=> Rows. To insert a column, select a cell in the column that you want
to appear after the new column, and click Insert =>
To delete a row, select a cell in the row
or column that you wish to delete, and click Edit => Delete....
In the Delete dialog box, select Entire Row or Entire
column, as appropriate.
Hide and unhide rows and columns
To hide a row, select any cell in the row
that you want to hide, and then click Format => Row =>
Hide. To hide a column, select any cell in the column that you want to
hide and click Format => Column => Hide.
To unhide a row, select the rows that are
above and below the hidden row. Then, click Format => Row =>
Unhide. To unhide a column, select the columns that are before and after
the hidden column, and then click Format => Column =>
Freeze and unfreeze rows and columns
Freezing rows and columns is useful if you
have a long list and you want your header row(s) visible as you scroll through
the document. To freeze a row, select the row after the row(s) that you
want to freeze. Remember, to select an entire row you must click on the gray row
marker, not a selected range of cells. Next, click Window => Freeze
Panes. To freeze a column, select the column after the column(s) that
you want to freeze. Next, click Window => Freeze Panes.
To unfreeze rows or columns, click
Window => Unfreeze Panes. You do not have to select any rows or
Another, perhaps easier, method for viewing
two portions of an Excel 2000 worksheet at the same time involves what is termed
the split box. Click and drag either the horizontal or vertical split
boxes to create, well, a horizontal or vertical split in the document.
To remove split(s) from a document, click
Window => Remove Split.
Change the zoom setting
As usual, there is a quick way and a more
precise way to change the zoom setting. First, the quick way: adjust the zoom
level by making a selection from the Zoom drop-down list box on the
Standard toolbar. If you do not see the zoom level you want, you can type in any
integer value between 10 and 500.
An alternative way to zoom is to click
View => Zoom.... In the Zoom dialog box, you can either choose
a preset zoom level or specify your own next to Custom. The Fit
selection option is cool- select the range of cells you want to see, select
Fit selection in the Zoom dialog box, and Excel 2000 will adjust
the zoom such that all that you see is your selected cells.
Move between worksheets in a workbook
Excel 2000 gives you three worksheets per
workbook by default. To switch between the worksheets, simply click the
appropriate worksheet tab at the bottom of the screen.
To check spelling in a document, press
F7 or click Tools => Spelling.... As Excel 2000 scans
your document for misspelled words, you will have the choice to ignore or accept
Excel's suggestions, as well as to add a word that you know is correctly spelled
(a company name, for example) to your custom dictionary.
Rename a worksheet
The fastest way to rename a worksheet is to
double-click the appropriate sheet tab. Excel will select the tab; simply type
to rename the sheet. Alternatively, right-click the sheet tab and select
Rename from the shortcut menu.
Insert and delete worksheets
To insert a worksheet, right-click the
sheet tab that you want to follow the newly inserted worksheet and select
Insert... from the shortcut menu. In the Insert dialog box, make
sure Worksheet is selected and click OK. Follow the steps given
previously to rename the sheet as desired.
To delete a worksheet, right-click the
worksheet tab that you want to delete and select Delete. It is important
to note that a worksheet deletion is one of the few actions that Excel will not
be able to undo for you. So, be careful.
Move and copy worksheets
The easiest way to move a worksheet within
a workbook is to click and drag it to its new location. As you drag, you will
see a small black marker arrow. That arrow will tell you where the sheet will
appear once you let go of the left mouse button.
A slower, but more precise, way to move a
worksheet is to right-click the appropriate sheet and select Move or
Copy... from the shortcut menu.
If you want the sheet to be moved to
another open workbook, select it from the To book: drop-down list.
Otherwise, select a location for the sheet from the Before sheet: list.
To copy a worksheet, follow the same
procedure, except make sure to select Create a copy in the Move or
Copy dialog box.
Link worksheets & consolidate data using 3D references
You can use a formula that refers to the
same cell or cell range on two or more worksheets in the same workbook. This is
called a 3D reference. For a list of the Excel functions that can be used
with 3D references, see the article "Guidelines for using 3-D references" in
To use a 3D reference (in this example we
will use the SUM function), start the function as you normally would by typing
=sum( in the destination cell. Next, click the sheet tab for the first
worksheet that will be reference. Now comes the tricky part. Holding down the
SHIFT key, click the sheet tab for the last worksheet that will be
referenced by the formula. For instance, I might click the Sheet1 tab, hold down
SHIFT, and then click the Sheet3 tab. Finally, select the cell that
contains the data on the first sheet in the 3D reference and press ENTER.
Working with formulas & functions
Enter a range within a formula by dragging
You can specify the range of cells to be
included in a formula by selecting them. For example, type =sum(. At this
point Excel 2000 is waiting to be given a range of cells that it can sum. Select
the appropriate cells (they will be surrounded by what is called the
marquee) and press ENTER.
Enter formulas in a cell and using the formula bar
Another way to input formulas is to use the
formula bar. Click in the cell that will hold the formula result and then
click in the formula bar to place your cursor within it. Pressing the
Cancel button clears any information in the formula bar. Pressing the
Enter button executes the formula but keeps the result cell the active
cell instead of the cell below it (which happens if you press ENTER).
Clicking the Edit Formula button
produces a drop-down list that provides more information about the formula being
constructed, and gives you input boxes for any required argument(s). Click the
Collapse Dialog button(s) to temporarily hide the Edit Formula drop-down.
Formulas update themselves automatically.
As you change data in cells that are referenced by a formula, the formula result
will change accordingly. To revise the formula structure, click the cell that
contains the formula and press F2 to place your cursor within the active
cell. Alternatively, click the formula cell and click the formula bar. Edit the
formula as needed.
Use references (absolute and relative)
By default, cell references in formulas are
relative, which means that their address is relative to the location of the
formula. This is helpful in most cases when you are copying formulas:
In the above graphic, the original formula
read =sum(D3:E3). Using AutoFill, copying the formula from cell F3
through F8 will use relative cell references. For example, the formula in cell
F8 should read =sum(D8:E8).
Absolute cell references, on the other
hand, do not "move" relative to the location of the referencing formula. Look at
the following illustration:
To make a cell reference absolute, place a
dollar sign ($) before the column and row designations. In the above graphic,
the original formula in cell F4 reads =$E$1*E4. Copying the formula from
cell F4 through F9 will always use the tax value in cell E1, because we have
used an absolute cell reference. The formula in cell F9 should read
Use AutoSum to quickly sum a row or column
of numbers. To use AutoSum, select the cell that will contain the formula result
and click the AutoSum button on the Standard toolbar.
Excel 2000 will "guess" a range to be
included in the AutoSum result. If Excel "guesses" wrong, simply restructure the
cell range by clicking and dragging with your mouse. Press ENTER to
compute the sum.
Use Paste Function to insert a function
To use Excel 2000's Paste Function feature,
select the cell that will contain the formula result and then click the Paste
Function button on the Standard toolbar.
In the Paste Function dialog box,
select a function by first choosing a category under Function category:
and then selecting a specific function beneath Function name:. The lower
portion of the dialog box provides function syntax information. Click OK
The next dialog box should look familiar to
you. It is the same dialog box that you see if you press the Edit Formula
button in the formula bar. The lower portion of this dialog box provides
additional formula syntax instructions. If you are still confused as to how a
particular function works, run a search on the formula name in Excel 2000
Use basic functions (AVERAGE, SUM, COUNT, MIN, MAX)
All formulas in Excel 2000 have the same
Look at the following graphic to get an
idea as to what duties the AVERAGE, SUM, COUNT, MIN and MAX functions perform in
Enter functions using the formula palette
The formula palette is invoked when you use
the Edit Formula button on the Formula bar, or during Step 2 when using
the Paste Function feature. See the Excel Help article "About using the Formula
Palette to enter and edit formulas" for an excellent, detailed description on
how to use the formula palette.
Use date functions (NOW and DATE)
To place the current date and time in a
cell, enter the formula =NOW(). This formula will update when the
document is printed or the next time the document is opened.
As regards the DATE worksheet function, the
syntax =DATE(year,month,day) returns the specified date in date format.
Date format is important so Excel 2000 can perform calculations using dates as
Use financial functions (FV and PMT)
FV stands for "Future Value." Suppose that
you wanted to save money for a new notebook computer. You begin by depositing
$500.00 into your savings account that earns 7% annual interest, compounded
monthly. For the next six months, you plan to deposit $250 into your savings
account on the first day of the month. How much money will you have in your
savings account at the end of the 6 months?
The syntax for the FV function is as
=FV(rate, nper, pmt, pb, type),
rate = interest rate per period (7%
annually in this example)
nper = total number of payment periods (6
in this example)
pmt = payment made each period
pb = present value ($500)
type = If 0, then payment is due at the end
of the period. If 1, then payment is due at the beginning of the period (1 in
Therefore, the function we need is
=FV(7%,6,-250,-500,1), and gives us a total of $2,663.87. Cash
paid out in Excel is represented in negative numbers, in case you were wondering
why we used some negative numbers in this function.
PMT stands for "Payment." Suppose that you
take out a bank loan to pay for that notebook computer. The requested loan
amount is $2,000 at an annual interest rate of 10.5%. The period of the loan is
24 months. What will be your monthly payment?
The syntax for the PMT function is as
=PMT(rate, nper, pv, fv, type),
rate = interest rate per period (10.5%
annually in this example)
nper = total number of payment periods (24
in this example)
pv = present value ($2,000)
fv = future value ($0, when the loan is
type = If 0, then payment is due at the end
of the period. If 1, then payment is due at the beginning of the period (0 if
this value is omitted from the function)
Therefore, the function we need is
=PMT(10.5%/12,24,2000), which gives us a monthly payment of
Use logical functions (IF)
The IF function is excellent for testing
conditions and making decisions based upon those conditions. The syntax for the
IF function is as follows:
=IF(logical test, value if true, value
Take a look at the following illustration
for a good example of using the IF function:
The plain-English explanation of the above
formula might read "If the value of the appropriate Average cell is
greater than or equal to 65, then display the word "PASS." Otherwise, display
the word "FAIL."
Using charts and objects
Preview and print charts
To print a chart located on its own tab,
simply bring that tab to the front, and print the active sheet as
To print an embedded chart object, click
the chart object once to select it. Next, click File =>
Print.... Ensure that Selected Chart is marked under Print
Use Chart Wizard to create a chart
To create a chart in Excel 2000, first
select the range of cells to be included in the chart. Next, click the Chart
Wizard button on the Standard toolbar.
In Step 1 of the Chart Wizard dialog
box, you can select a chart type and sub-type. Click Press and Hold to View
Sample to check out the chart with your data plugged into it.
In Step 2 of the Chart Wizard dialog
box, you verify your data range and decide whether you would like the data
series presented by rows or by columns. If you need to change the data range,
click the Collapse Dialog button next to Data range:.
In Step 3 of the Chart Wizard dialog
box, you can customize the appearance of your chart by adding X- and Y-axis
labels, gridlines, a legend, data labels, or even a small data table that will
appear directly below your chart.
In the final step of the Chart Wizard
dialog box, decide whether you want your new chart placed on its own
worksheet or as an embedded object in the current worksheet. Use the drop-down
box next to As object in: if you would like your chart embedded in
another worksheet in the active workbook.
Modifying a chart element is as simply as
right-clicking on the chart element that you want to customize, and choosing
Format X... (where X is the specific chart element) from the
appropriate shortcut menu.
For the Excel 2000 Core MOUS exam, spend a
lot of time familiarizing yourself with the different chart elements. Try this:
click an area of the chart, and learn its name by reading it from the Chart
Objects drop-down list on the Chart toolbar.
Insert, move, and delete an object (picture)
To insert a piece of ClipArt from the
ClipArt Gallery into your Excel 2000 document, click Insert =>
Picture => Clip Art.... In the Insert ClipArt dialog
box, enter a search string in Search for clips: to help you find the
ClipArt you need. Once you find it, click the art and then click Insert
clip from the pop-up menu to insert the ClipArt.
To insert a non-clip art picture, click
Insert => Picture = From File.... In the Insert
Picture dialog box, browse until you have found the desired image, and then
click Insert to insert the picture into your Excel 2000
To move inserted ClipArt or images, simply
click anywhere within the image and drag with your mouse. By default, inserted
art floats on a layer above any other data on the worksheet. To resize art
proportionally, click and drag the art from any corner resize handle. If
you click and drag the art by its top or side resize handles, than you will warp
To delete inserted art, select the art and
Create and modify lines and objects
To gain access to Excel 2000's line art
tools, you must first invoke the Drawing toolbar. To show the Drawing toolbar,
click the Drawing button on the Standard toolbar.
By default, the Drawing toolbar will
appear docked to the bottom part of your screen.
To use a drawing tool, click the
appropriate toolbar button, click, and drag the mouse in the worksheet area to
size the new drawing object. To draw a perfectly straight line or a perfect
square or rectangle, hold down the SHIFT key while dragging the mouse.
To modify a line or a drawing object,
right-click the line or object and choose Format AutoShape... from the
shortcut menu. In the Format AutoShape dialog box, you can adjust fill
color, line style and color, size and scaling, and so on.