Stand: 07.01.2004 

Copyright by Cramsession
MOUS Excel 2000

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 seventh action.

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 To....

 

 

 

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 Clipboard

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 copied.

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 menu.

 

3. Place the cursor in the destination location.

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 => Formats.

 

 

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 button.

 

 

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.

 

 

Create hyperlinks

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

Use Save

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 CTRL+S.

 

 

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 template.

 

 

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 Template Gallery 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.

 

 

Formatting worksheets

Apply font styles (typeface, size, color and styles)

To quickly adjust the font style of selected cells, use the appropriate buttons on the Formatting toolbar.

 

 

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 one.

 

 

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 formatting toolbar.

 

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!

 

Apply AutoFormat

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 selected cells.

 

 

 

Merging cells

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 list.

 

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 Excel.

 

 

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 Normal view.

 

 

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 Preview.

 

 

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, click Selection.

 

 

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 mode.

 

 

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 Break.

 

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 headings)

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 resolution.

 

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 => Columns.

 

 

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 => Unhide.

 

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 columns first.

 

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.

 

Check spelling

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 Excel Help.

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.

 

 

Revise formulas

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 =$E$1*F9.

 

Use AutoSum

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 to continue.

 

 

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 Help.

 

 

Use basic functions (AVERAGE, SUM, COUNT, MIN, MAX)

All formulas in Excel 2000 have the same basic structure:

=Formula name(arguments)

Look at the following graphic to get an idea as to what duties the AVERAGE, SUM, COUNT, MIN and MAX functions perform in Excel 2000:

 

 

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 numbers.

 

 

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 follows:

=FV(rate, nper, pmt, pb, type), where

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 ($250)

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 this example)

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 follows:

=PMT(rate, nper, pv, fv, type), where

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 paid off)

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 $92.75.

 

 

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 if false)

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 usual.

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 what.

 

 

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.

 

 

 

Modify charts

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 worksheet.

 

 

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 the image.

To delete inserted art, select the art and press DELETE.

 

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.