|
|
|
Stand: 07.01.2004 |
Copyright by Cramsession ----- Excel 2002 ----- Working With Cells and Cell DataInsert, delete, and move cellsTo insert cells, select the cell or cell range and click Insert => Cells.... Alternatively, right-click inside your selection and choose Insert from the shortcut menu. In the Insert dialog box, choose whether to shift the selected cells either to the right or down. Select Entire row or Entire column if you want to insert an entire row or column, respectively, into your worksheet instead of selected cells.
Another way to insert a row
is to select the row beneath the row where you want the new row to appear by
clicking the appropriate row heading (for example:
To clear cell contents, select the cell or cell range and press DELETE. Note that this procedure removes only the cell contents. To remove cell formatting or both cell contents and cell formatting, click Edit => Clear, and then choose either All, Formats, Contents or Comments from the submenu.
To actually delete cells, select the cell or cell range and click Edit => Delete.... Alternatively, right-click in the selected cell and choose Delete... from the shortcut menu. In the Delete dialog box, determine whether you want to shift the surrounding cells either left or up.
To move cells, highlight the cell or cell range and hover your mouse pointer anywhere on the selected cell border. The mouse pointer will turn into a four-way arrow symbol:
Simply click and drag to relocate the cell or cells to a new location on the worksheet.
Enter and edit cell data including text, numbers, and formulasSimply select a cell and start typing in order to enter data. You will find that Excel left-aligns text data and right-aligns numerical data, including dates and times.
As you enter data into a cell, notice that Excel presents the data both in the cell in which you are typing, and the formula bar. When entering formula, a ToolTip with the appropriate function’s syntax will appear as you enter the formula. The Cancel button will clear your work; the Enter button will complete your data entry but keep you in the current cell; the Insert Function button will open the Insert Function dialog box, which will help you complete a formula for which you forgot the proper syntax.
Check spellingTo check spelling in a document, press F7, or click Tools => Spelling.... The Spelling dialog box will present each possible misspelling that it finds. Click Ignore Once or Ignore All if you want to keep your existing data. Add to Dictionary will tell Excel to use your spelling in the future. If you want to take one of Excel’s suggestions for a term, click either Change or Change All. AutoCorrect will allow you to have Excel automatically substitute a word from its dictionary whenever it sees a particular misspelling.
Find and replace cell data and formatsTo locate and replace data in a worksheet, click Edit => Find or Edit => Replace, or press CTRL+F or CTRL+H, respectively. In the Find and Replace dialog box, use the Find and Replace tabs for whether your query is a simple find or a find and replace operation. Enter your appropriate search and replace string and click Find Next to locate the first instance of the specified string. Replace All and Replace allow you to make your changes. In the example below, I am searching for instances of “Key Lime Pie,” and replacing them with “Lemon and Lime.” Notice that I specified Match case. That means that an instance of “Key lime pie” will not be picked up. Notice also that I selected Match entire cell contents. This means that the entire cell must match “Key Lime Pie.”
Excel 2002 made some pretty significant advances to this function. Press Find All to snag all instances of a search string simultaneously.
Additionally, click Format... to add formatting constraints to your search. Click Format... in the drop-down menu list to open the Find Format dialog box. The Find Format dialog box allows you to pick and choose which cell format or formats you need in your search. Click Choose Format From Cell... to literally select a sample cell with your mouse. Excel will then automatically add the selected cell formatting elements to your query. Pretty neat stuff, eh?
Work with a subset of data by filtering listsTo filter a list, select any cell within the list and click Data => Filter => AutoFilter. Each column head will have a small black drop-down arrow icon next to it. Click any icon to expand a list of filtering possibilities for that column.
For example, in the example above I could view all of the Harter’s Yogurt Shoppe employees from the Production department simply by selecting Production from the AutoFilter drop-down list.
To view all records again, open the AutoFilter menu and select All. Alternatively, you could click Data => Filter => Show All.
To filter a data by specifying multiple criteria within a single column, perform an AutoFilter, open up the AutoFilter menu for the desired column and choose Custom.... In the Custom AutoFilter dialog, plug in your arguments. Use the And comparison operator to pick up multiple criteria. For example, in the example below I have specified that I want to see all employees from either the Marketing department or the Sales department.
The method for performing advanced AutoFilters has changed dramatically between Excel 2000 and Excel 2002. To perform a complex AutoFilter, you must first insert at least three blank rows above your list, and create a mini-list that contains your AutoFilter parameters. For example, in the graphic below you can see that I want to see all the employees who both belong to the Marketing department and work in the Myrtle Beach office.
Next, select any cell within your list and then click Data => Filter => Advanced Filter.... In the Advanced Filter dialog box, specify whether you want the filter results to appear in the same location as your list (this is what happens when you execute an AutoFilter), or if you want the filter results to be copied to another, blank location on your worksheet. The List range: value should map to your list. The Criteria range: value will map to the mini-list you created that contains your advanced filter criteria. Click the Collapse Dialog button and then select the range of cells that corresponds to your criteria range. In my example, the range C3-D4 corresponds to my criteria range. Then press ENTER to execute the advanced filter operation.
Here is the result of my sample advanced filter:
Managing WorkbooksManage workbook files and foldersTo open an existing Excel
workbook, click the Open
By default, Excel filters the file listing in the Open dialog box for Excel files. To look for files of other types, select another file type from the Files of type: drop-down list.
Once you have found your file, you can use the Open button to view it; notice also that you can open the Open button, so to speak, to open the file read-only, open a copy of the selected file, preview the file in a browser (HTML files only), or open and repair a possibly damaged document.
To save a file for the first
time, click the Save
Subsequent uses of CTRL+S or the Save button will only update changes to the saved file; you will need to click File => Save As... in order to save a copy of the file or save the file with a new name and/or in a new location.
Create workbooks using templatesWhen you open Excel, or if
you use the New
Notice that you can build a new workbook based on an existing workbook by clicking Choose workbook... beneath New from existing workbook. To create a new workbook based upon a template besides the Excel default, click General Templates... beneath New from template. The General tab of the Templates dialog box contains the blank document template and any user-created templates; the Spreadsheet Solutions tab contains some sample documents that you can use as a springboard for your own work. In any case, select the appropriate template icon from the window and click OK to complete the process.
Save workbooks using different names and file formatsTo save a workbook by using a different name and/or a different file format, click File => Save As.... In the Save As dialog box, specify a new location and/or a new name for the file, and click Save.
To save an Excel 2002 file under an alternate file format, open the Save As dialog box and specify a new file type in the Save as type: drop-down list box.
Formatting and Printing WorksheetsApply and modify cell formatsThe “quick and dirty” method for applying formats to cells is through the buttons on the Formatting toolbar. However, you are pretty much limited to default options.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
To get real control over your formatting, select the cell(s) to be formatted and click Format => Cells..., or alternatively right-click inside the selected cell range and choose Format Cells... from the shortcut menu.
The Number tab allows you to format the data type that is contained in a cell. For example, the following screen capture will format a cell to display currency style with two decimal places (e.g.: $1,234.00).
The Alignment tab allows you to configure how data is oriented in a cell. One handy option here is Wrap text. This will force text to wrap when the text reaches the right cell border rather than just continuing over the subsequent cells. The Orientation area is also pretty cool. You can use this tool to make your list headings slanted.
The Font tab works much like the Font dialog box in Microsoft Word— in it you can adjust the font, font style, size, color, etc. of any cell data.
The Border tab allows you to put a border on any part of your worksheet. I find it easiest to select a line style and simply click in the Border preview area in the relative location where I want a border to appear.
The Patterns tab allows you to add fill effects to your worksheet cells. Using patterns well can really add to your worksheet’s visual appeal.
Modify row and column settingsTo adjust row height, place the mouse pointer beneath the row that you want to modify. For example, to modify the height of row 7, place the mouse pointer at the junction between rows 7 and 8. Then simply click and drag either up or down in order to change the height of the row. Excel 2002 will display the row height in pixels as you adjust the height. Do the same thing to adjust column width: place the mouse after the column that whose width you want to modify.
To be more precise about it, you can also modify row height by selecting a cell in the desired row and clicking Format => Row => Height.... Alternatively, right-click in the row heading and select Row Height... from the shortcut menu. In either case, the Row Height dialog box will appear, allowing you to insert a numeric value (in pixels).
It is important to know that the same process works for changing column width (that is, click Format => Column => Width..., or right-click in the column heading and choose Column Width... from the shortcut menu). The only difference is the unit of measurement: with columns, you specify a value in digits displayed. That is, a value of 10 will display 10 digits in whatever font is being used as the default font in the document.
You can AutoFit any row or column. Simply double-click the right-border of any column, or bottom border of any row.
Modify row and column formatsTo revert any column to standard width, click Format => Column => Standard Width. Frankly, I think the “Modify row and column formats” exam objective has been addressed fully in the preceding section.
Apply stylesA style is a collection of
formatting elements that can be named, saved, and reapplied several times in a
document. Built-in number styles include the Currency Style, Percent
Style and Comma Style
To create a style, select a cell that contains the proper formatting and click Format => Style.... In the Style dialog box, type a name for your new style next to Style name:. Under Style Includes (By Example), uncheck any formatting elements that you do not want included in your new style. Click Add to create the style.
To apply a style, select the desired cell or cell range and then open the Style dialog box. Select your chosen style from the Style name: drop-down list box and click OK.
To modify or delete a style, open the Style dialog box, select the style from the drop-down list, and click either Modify... or Delete. While you can modify any style (by clicking Modify... opens the Format Cells dialog box), you can delete only user-defined styles.
Click Merge... to import existing styles from another open (the other file MUST be open) workbook.
Use automated tools to format worksheetsTo use Excel 2002’s AutoFormat feature, select a cell within your table and click Format => AutoFormat.... In the AutoFormat dialog box, select a formatting style from the list of prebuilt styles. Click Options... to be given the opportunity to selectively disable particular formatting elements from the AutoFormat. Click OK to apply the AutoFormat to your table.
To examine Excel 2002’s automatic correction options, click Tools => AutoCorrect Options.... On the AutoCorrect tab of the AutoCorrect dialog box, you can customize what types of text formatting Excel will automatically convert. For example, you can have Excel 2002 automatically capitalize the first letter of sentences.
The AutoFormat As You Type tab is a running list of automatic replacements, such as the replacement of Internet or network paths to Internet/intranet hyperlinks.
The Smart Tags tab allows you to customize smart tag behavior. Smart tags are a brand-new addition of Office XP. Basically, a smart tag is an indication that Excel recognizes the type of data you are entering, and it offers you the ability to perform some action on the data. For example, typing a name into Excel that appears in your Outlook Contacts list will produce a smart tag offering you the ability to send that contact an e-mail message directly from Excel 2002.
Smart tags are an extensible technology, which means that as time goes by, more smart tags will be developed by Microsoft and other third-party vendors. The new smart tags can then seamlessly be imported into Office XP. Click More Smart Tags... to be directed to the Microsoft Web site, where you can learn more about them. As it is, smart tags aren’t as richly implemented in Excel as they are in, say, Word 2002.
Modify Page Setup options for worksheetsTo 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:.
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.
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].
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
To 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 2002 repeat a title row or title column by marking that row
or column. In the Print titles area, click the Collapse Dialog
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. By Comments:, open the drop down and select whether you would like any included comments displayed at the end of the sheet or as displayed on your worksheet. Finally, next to Cell errors as:, open the menu and decide how you want Excel to display errors for which Excel has detected errors.
Preview and print worksheets and workbooksTo use Print Preview, either
click the Print Preview
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
To print one copy of the
active worksheet using your default printer, click the Print
Modifying WorkbooksInsert and delete worksheetsTo insert a new worksheet into an Excel 2002 workbook, right-click the worksheet that you want to appear after the newly inserted sheet, and select Insert... from the shortcut menu. Another way to do this is to select the proper worksheet and click Insert => Worksheet.
In the Insert dialog box, ensure that the Worksheet icon is selected. Click OK to insert the new sheet.
To delete a worksheet, right-click the desired worksheet tab and select Delete from the shortcut menu. If no data exists on the sheet, then Excel 2002 will just go ahead and delete the sheet. If data does exist on the sheet, then Excel 2002 presents the following confirmation dialog box:
Clicking Delete will permanently delete the sheet and all corresponding data. Note that deleting a worksheet is not an undo-able action.
Modify worksheet names and positionsTo move a worksheet, simply click and drag the worksheet tab to its new location. Hold down the CTRL key to copy the sheet instead of move it. In either case, your mouse pointer will look like this:
When you CTRL-drag a sheet, your mouse pointer will show a small plus sign (+) to indicate a copy procedure rather than a move procedure.
If you don’t like the “click and drag” approach, you can also move or copy a worksheet by right-clicking the desired worksheet tab and selecting Move or Copy... from the shortcut menu.
In the Move or Copy dialog box, select another open workbook in the drop-down list beneath To book: if you want to move or copy the selected worksheet to another Excel 2002 file. Otherwise, indicate which sheet you’d like to come after the moved or copied worksheet by selecting the proper choice beneath Before sheet:. By default, Excel 2002 will move your worksheet; to copy instead, you must select the Create a copy check box before you click OK.
To rename a worksheet, right-click the worksheet tab and select Rename. You can also rename a worksheet simply by double-clicking the appropriate worksheet tab.
Recoloring worksheet tabs is a new feature in Excel 2002. To recolor a worksheet tab, right-click the desired worksheet tab and select Tab Color... from the shortcut menu.
In the Format Tab Color dialog box, select the new tab color from the color table and click OK. That’s all there is to it!
The following graphic shows you some different tab colors. Please excuse me if I labeled the colors incorrectly— I am profoundly colorblind. J
Use 3-D referencesImagine you had monthly sales data in the same cell address on multiple sheets. A 3-D reference allows you to compose a formula that references these cell addresses or cell ranges on multiple worksheet tabs. Note that the data must reside in the same relative location on the multiple sheets. That is to say, you could sum the data in the range C5:C10 on worksheets 1, 2 and 3, but you could not sum the data from C5 on worksheet 1 and C5 on worksheets 2 and 3 by using a 3-D reference. So in other words, a 3-D reference is a specialized reference form that you may or may not use much in “real life.” However, I want to assure you that you need to know how to do it for the Excel 2000 Expert of Excel 2002 Core MOUS exams.
To create a 3-D reference, start the formula in the cell on the worksheet that will display the formula result. Next, select the cell or cell range on the first worksheet to be included in the cell reference.
Here’s the kicker: hold down the SHIFT key and select the worksheet tab of the last worksheet to be included in the 3-D reference. Note that all intervening sheets will be selected. 3-D references only work for contiguous sheets.
Finally, press ENTER to execute the formula. The following formula displays the results of summing the range B5:B16 on worksheets 1, 2 and 3 in a workbook:
Create and revise formulasAll functions (formulas) in Excel 2002 have the same basic structure:
=function-name(function-arguments)
You can specify the range of cells to be included in a formula by selecting them. For example, type =sum(. At this point Excel 2002 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.
Notice also that Excel 2002 presents a helpful ScreenTip as soon as it figures out which formula you are using. This ScreenTip is beneficial in cases where you might be fuzzy as to the syntax of a particular formula.
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 normally happens if you press ENTER).
Note the activity of the formula bar. Use the Cancel button to erase your work but remain in the current cell. Use the Enter button to execute the formula and remain in the current cell. Use the Functions drop-down list to change the formula you are using on-the-fly. Finally, use the Insert Function button to get more help on using the current function or to insert a new function into the current cell.
Let's examine the Insert Function button. Let's say we wanted to average a range of numbers. First, select the appropriate cell; next, click the Insert Function button on the formula bar.
Under Search for a function:, enter what you what type of action you want the function to perform. Excel 2002 will try to interpret "plain language" search strings, such as "locate the highest number." Click Go after you enter your search string.
Excel 2002 will then present a list of formula matches. Click the desired function; you should see detailed syntax information as well as a Help on this function hyperlink, which provides additional formula advice. Click OK to execute the function.
Whoops-- almost forgot. Excel 2002 includes a mind-boggling number of formulas. At times you may know the type of function that you need, but you are unable to come up with an adequate search string. In this case, use the Or select a category: drop-down in the Insert Function dialog box.
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 statistical, date and time, financial, and logical functions in formulasFor our purposes, "statistical" formulas will refer to the SUM, AVERAGE, COUNT, MIN and MAX functions. Look at the following graphic to get an idea as to what duties the SUM, AVERAGE, COUNT, MIN and MAX functions perform in Excel 2002:
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
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 2002 can perform calculations using dates as numbers.
We will have a look at two 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:
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:
Therefore, the function we need is =PMT(10.5%,12,24,2000), which gives us a monthly payment of $94.36.
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. In the example below, any student with a cumulative average of less than 75 receives a grade of FAIL; any cumulative grade equal to or greater than 77 is considered a pass. Therefore, the expression =IF(F4<75,"FAIL","PASS") is used to compute the students' final grade.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
Creating and Modifying GraphicsCreate, modify, position, and print chartsTo create a chart, first select only the data in your worksheet that will be included in the chart. It is not necessary to try to select column headings- you will have the opportunity to specify axis labels later in the process. In the following graphic, I have selected only the range A5:B16 to be charted.
Click the Chart Wizard
Click Next to continue. In Step 2 of the Chart Wizard dialog box, verify the selected data range, and decide whether you want to use rows or columns for your data series. Note the helpful preview window.
In Step 3 of the Chart Wizard dialog box, you have the opportunity to really trick out your chart. Use the various data tabs to add chart titles, axes, gridlines and legend options, data labels, or a data table. Go nuts!
In Step 4 of the Chart Wizard dialog box, simply specify whether you want the new chart embedded on a new worksheet or as an object embedded in an existing sheet. If you select As new sheet:, be forewarned that the chart will eat up the ENTIRE sheet, whereas if you select As object in: you can move or resize the chart object at will.
Here is a simple bar chart that was created by using the sample data shown in an earlier graphic.
Positioning an embedded chart object is as easy as clicking an area within the chart object and dragging the chart to a new location on the worksheet. As you perform the operation, you will see that the mouse pointer has taken the shape of a four-way arrow.
To resize a chart object, click the object once to select it. Notice the eight resize handles? Click any of them and drag to resize the chart object.
To place the chart in its own closable window, right-click the chart background and select Chart Window from the shortcut menu.
Further, if you had initially decided to place the chart as an embedded object on an existing worksheet and now want Excel to put the chart on its own worksheet, right-click the chart background and select Location... from the shortcut menu. You will then be presented with the opportunity to make that choice of location again.
When you modify existing data in your table, Excel updates the chart automatically. However, if you add or subtract data from your table, Excel breaks the link between the data and the chart. What you need to do to manually update the chart is right-click inside the chart area and select Source Data... from the shortcut menu.
Click the Collapse Dialog
In terms of modifying the chart's format, what I've always told my students is to simply right-click the part of the chart that you wish to format, and choose the appropriate Format command from the shortcut menu. For instance, to adjust the font in the legend, right-click inside the legend and click Format Legend.... Similarly, to change the color of a data series column, right-click the desired column and choose Format Data Series... from the shortcut menu.
Of course, you can always access the entire chart formatting options at once by right-clicking the chart background and selecting Chart Options... from the shortcut menu.
Another thing: you can always change the chart type on the fly. Again, right-click the chart background, this time selecting Chart Type... from the shortcut menu.
Of course, most chart formatting options are accessible via the Chart toolbar. The following graphic introduces you to the Chart toolbar's functionality.
To print a chart, the key is to make sure that you have selected the chart object before clicking File => Print.... In the Print dialog box, ensure that Selected Chart is selected under Print what.
To delete a chart, simply select the chart and press DELETE. If the chart is on its own tab, right-click the tab in question and choose Delete from the shortcut menu. Note that chart deletions are not undo-able.
Create, modify, and position graphicsThe first step to create graphics in Excel 2002 is to display the Drawing toolbar. You can either click the Drawing button on the Standard toolbar, or you can manually invoke the toolbar by clicking View => Toolbars => Drawing. No matter what the shape type, the procedure for creating the objects is the same: click the appropriate tool (once), draw out an initial size for your object, and let go of the mouse button. Your mouse pointer will take the shape of a tiny crosshair while you are drawing out the shape.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
To modify a graphic, click the object once to view the object's resize handles. Next, click and drag any corner of an object to resize. It's important that you resize by using a corner resize handle so the image will not warp.
To move a graphic, click and drag anywhere inside the graphic. Your mouse cursor should take the shape of a four-way arrow.
Some hints: hold down SHIFT while you are drawing a shape- this will make the line or shape perfectly straight or symmetrical, as the case might be. Hold down CTRL while you draw a shape in order to draw a line or shape from the center outward.
Click Draw => Rotate or Flip => Free Rotate on the Drawing toolbar to rotate a line or object up to 360 degrees. Highlight the desired image, click the button to turn Free Rotate on, then click and drag one of the shape's four Free Rotate resize handles. Fun stuff!!
To insert clip art into your worksheet, click the Insert Clip Art button on the Drawing toolbar, or click Insert => Picture => Clip Art.... The Insert Clip Art task pane appears.
If you know the type of image you are looking for, type in your search text and click Search. The following graphic displays the result of a keyword search for "books."
Hover your mouse pointer over a piece of clip art to see a ScreenTip displaying file name, file type and file size information. Click the black downward-pointing arrow to be given clip art options. Click Insert from the menu in order to add the art to your current worksheet.
If you would rather use the more "traditional" Microsoft Clip Organizer to browse the clips, click Clip Organizer ... under See also in the Insert Clip Art task pane.
Basically the Microsoft Clip Organizer gives you a Windows Explorer-like view of your installed clip art files. Expand clip categories in the left-hand pane; browse individual clips in the right-hand pane. Clips are inserted into your Excel 2002 worksheet simply by dragging the desired clip from the Microsoft Clip Organizer interface into the worksheet. No muss, no fuss, no menus, and no greasy aftertaste!
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
The procedure for moving and resizing clip art is exactly the same procedure for moving and resizing any other object in an Excel 2002 worksheet. To adjust a clip art's properties, right-click the art and choose Format Picture... from the shortcut menu. You can also do some incredible things with the Picture toolbar, which should be visible by default. Remember that you can always right-click an existing toolbar and select Picture from the shortcut menu in order to display the Picture toolbar.
To insert a picture into your worksheet, click Insert => Picture => From File.... In the Insert Picture dialog box, browse to find the desired picture. Click Insert to slam-dunk the image into your worksheet.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
Whoops- probably should have mentioned the Views button in the Office XP common dialog box. Note that you can view thumbnails of graphics by choosing Thumbnails from the Views drop-down menu.
Workgroup CollaborationConvert worksheets to Web pagesTo convert a worksheet to a Web page, open the worksheet and click File => Save as Web Page.... In the Save As dialog box, ensure that Selection: Sheet is selected. Checking Add interactivity allows the page viewer to add or delete data from the published Web page data. Notice that Excel 2002 automatically appends the .HTM file extension to your file.
Click Change Title... to change the page title that will appear in the viewer's browser title bar. Next, click Save simply to save the page, or Publish to specify additional Web page publishing options, such as how much data on the worksheet should be converted to HTML, the level of user interactivity desired, and whether Excel should open the resultant page in a browser window upon closing the Publish as Web Page dialog box.
The following graphic displays sample Excel data that has been converted into HTML and is displayed in Microsoft Internet Explorer 5.5.
If your browser doesn't support inline frames click HERE to view the full-sized graphic.
Create hyperlinksYou can create a hyperlink
to just about anything in Excel 2002. To create a hyperlink, select the cell to
be used as a hyperlink and click the Insert Hyperlink
First, let's create a hyperlink to a Web page. In the Insert Hyperlink dialog box, enter the hyperlink text in the text box next to Text to display:. Ensure that the Link to: bar has Existing File or Web Page selected. Enter the Uniform Resource Locator (URL) of the Web page next to Address:. Click OK to create the hyperlink.
Next, let's create a hyperlink to another location in the workbook. Open the Insert Hyperlink dialog box, and click Place in This Document under Link to:. Enter the hyperlink text next to Text to display:. Under Type the cell reference:, you can use A1 just to display the entire worksheet, or you can specify a discrete cell range to have Excel jump to that defined range. Finally, select the sheet name (or range name, if you defined any named ranges in the workbook) under Or select a place in this document:.
Now let's create a hyperlink that will open a new Excel workbook. Open the Insert Hyperlink dialog and select Create New Document under Link to:. Next to Text to display:, add the requisite hyperlink text. Next, specify a name for the new document to be created under Name of new document:, and specify the full path to this new document. Finally, decide whether the user will have access to the newly created document immediately, or if the new document will just be created but not immediately opened.
Finally, we'll create a hyperlink to an e-mail address. You know the drill: open the Insert Hyperlink dialog box and click E-mail Address. Enter the hyperlink text. You can then specify the e-mail recipient's address and the subject line of the new e-mail message.
View and edit commentsTo add a comment to an Excel worksheet, right-click the cell to which you want to add a comment and choose Insert Comment... from the shortcut menu. Alternatively, you can click Insert => Comment. Type the comment text. You will notice that cells with comments appear with a small, red triangle in their upper-right corner.
To view a comment, simply hover your mouse over the comment indicator. Excel uses whatever name you've specified on the User Information tab of the Options dialog box in Microsoft Word.
To view more than one
comment in a document, open the Reviewing toolbar (by clicking View =>
Toolbars => Reviewing, or by right-clicking a visible toolbar and
selecting Reviewing from the shortcut menu) and use the Previous
Comment and Next Comment
One cool new addition to
Excel 2002 is the ability to view all comments in a worksheet simultaneously.
Simply use the Show All Comments
To edit a comment,
right-click the cell that contains the comment and select Edit Comment
from the shortcut menu. The comment balloon will appear again, this time
allowing you to edit the comment text. By the way, you can also use the Edit
Comment
To delete a comment,
right-click the cell that contains the comment and choose Delete Comment
from the shortcut menu. Alternatively, you can use the Delete Comment
|
|
|
).
Next, right-click and choose Insert from the shortcut menu. You can
insert a column by selecting a column label (for example: 
),
right-clicking, and choosing Insert from the shortcut menu.
button on the Standard toolbar, click File => Open... or click CTRL+O.
Use the Places Bar to quickly navigate to the My Documents or Desktop folders.
Alternatively, you can use the Look in: drop-down menu to browse drives,
files and folders in search of your desired file.
button on the Standard toolbar, click File => Save, or click CTRL+S.
Use the Places Bar or the Look in: drop-down list to find a location in
which to save your document. Use the Create New Folder 
button to create a new folder, on the fly, for your new Excel workbook. Finally,
click Save to actually save the file.
button on the Standard toolbar, Excel gives you a blank workbook based upon the
default template. In order to make use of alternate templates, you must click File
=> New.... Take a close look at the new Excel 2002 New Workbook
task pane to see some of what you can do:
buttons on the Formatting toolbar.
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.
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.
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.
button on the Standard toolbar to begin the chart creation process. In Step 1 of
the Chart Wizard dialog box, specify the chart type and sub-type. Click Press
and Hold to View Sample in order to get a rough idea as to how your data
will look in chart form.
button in order to get the Source Data dialog box out of the way. Next,
re-select the new data range that will be represented by your chart. Click OK
to have Excel redraw the chart.
button on the Standard toolbar. Alternatively, you can press CTRL+K, or
right-click inside the cell and choose Hyperlink... from the shortcut
menu.
buttons to navigate through all the comments in the document.
button on the Reviewing toolbar. To hide all comments, simply click the same
button again. It's a toggle switch, actually.
button from the Reviewing toolbar.
button on the Reviewing toolbar to accomplish the same goal.