Excel 2002 Office XP PC

What's New in Excel 2002 (Office XP)

Introduction

Excel 2002 is the version of Excel packaged with the Office XP suite. This document outlines many of the new features of Excel 2002 and how it differs from Excel 97. It is written for people who are familiar with Microsoft Excel 97 and who are starting to or wish to use version 2002. Office XP will be distributed to faculty and staff offices based on upgrade requests.

Excel 2002 is, once you get used to it, very similar to Excel 97. It includes both brand new features as well as old features that have simply changed location, name, or appearance. Both Excel 2002 and 97 use the same file formatting, which means that Excel 2002 can open, read, and edit Excel 97 (and 2000) files. Files created in Excel 2002 on a PC should also be fully cross-platform compatible with Macintoshes running either Excel 98 or 2001, provided that the filename is relatively short and does not contain any punctuation characters.

Specific differences between Excel 97 and 2002 are detailed below.

Getting Started

To launch Excel 2002, Click on the Start menu and select Microsoft Excel from the list. If Microsoft Excel is not available in the initial Start menu list, please look under Start > Programs for Microsoft Excel.

Table of Contents

Navigating the New Layout

The toolbars and menus appear in the same locations at the top of the screen as they did in Excel 97/98.

The Task Pane

The Task Pane (as pictured on the right) is a rectangular window that appears on the right side of the screen when Excel opens. It acts as an open menu, giving you a list of functions and commands to perform depending on what you are doing at the moment.

Opening/Closing Task Pane

• To open theTask Pane, go to the View menu and select Task Pane. If you do not want it to launch every time Excel opens, uncheck the Show at startup option in the bottom of Task Pane.
• To close theTask Pane, click on the X located on the far right corner of the pane.

Other Task Panes/Functions

The other Panes can be opened by clicking on the down-arrow that appears on the right in the title bar circled in red.

1. The Clipboard contains a list of items you have recently cut, pasted, or copied.
2. The Search option can help you search the hard drive for files with content matching the search keyword. The options also allow search by location or file type.
3. The Insert Clip Art function allows you to search the Clip Art Gallery using keywords and displays the Clip Art in thumbnails for you to choose.

Return to Table of Contents

Smart Tags and Other Markers

A "smart tag" is a button that appears after certain actions have taken place, such as a copy-and-paste. When you click on the button, a menu of options pop down that help you control the result of the action.

List of Smart Tags and Functions

• Trace Error Option button will appear is the formula of the cell is incorrect. A green triangle in the upper left corner of the cell will identify the cell as having some sort of error either in the formula or its format.
  • To Open/Use the Trace Error Options
    • Hover over the cell that has a green triangle in the upper left corner
    • Click on the Trace Error button.
    • Select one of the options in the list that appears (pictured to the right).
• AutoCorrect Options button will appear as a small blue box if you point near the text that was corrected. The button give you the option of undoing the changes it automatically made to the cell.
  • It can automatically capitalize a lowercase letter or convert a network path to a hyperlink.
  • To Personalize AutoCorrect Options
    • Go to theTools menu and select AutoCorrect Options....
    • Select the AutoCorrect tab in the AutoCorrect window.
    • Check all boxes that you want to have automatically corrected and click OK to apply the settings.
• Paste Options button gives you greater control and flexibility in choosing the format of a pasted item. It allows you to link cells, change source column width, etc... to better customize whatever you want to paste.
• Insert Options button appears next to inserted cells, rows, or columns. When you click the button, a list of formatting options will appear for you to customize the inserted cells.
• AutoFit Options button appears below a selection you have filled. Clicking on the button will give you more formatting options to fill the text or data.

Return to Table of Contents

Menus and Toolbars

Personalize The Toolbar

• Ways to Open the Customize Window
  1. In the Tools menu select Customize.
  2. Click on the black arrow circled in red at the end of the toolbar. Click on Add or Remove Buttons and select Customize.
  3. Go to the View menu, select Toolbars and select Customize... at the bottom of the list.
     

• Using the Customize Window
  1. Under the Toolbars tab, check off any Toolbars to add the functions to your toolbar.
  2. Under the Commands tab there are two windows. Select a category in the Categories window. Click on the commands in the Commands window and drag it to the toolbar. The commands will appear immediately in the place you drag to on the Toolbar.
  3. To delete a command in the toolbar, just drag the icon outside of the toolbar and let go.
  4. To get a description of the command, click on the command in the Commands window. Then on click the Description button.

• Ways to Close the Customize Window
  1. Click on the Close button on the bottom right corner of the Customize Window.
  2. Click on the X button on the upper left of the Customize Window.

Return to Table of Contents

Review Toolbar

The Reviewing toolbar is used to edit, navigate through, display/hide, and delete comments.

To Display the Reviewing Toolbar:

1. From the View menu, select Toolbars > Reviewing.
2. The Reviewing toolbar appears.
   

Return to Table of Contents

Pointer Shapes

The shape of the pointer changes as you are working with the program. Each pointer shape is communicating something about how Excel is working. The shape of the pointer when you click and drag a cell will greatly influence the results of the click and drag. The following is a table that describes each of the pointer shapes that you may encounter in your Excel work.

Shape	Meaning	Action
	The default pointer shape.	Move cell pointer or select a range of cells.
	When the pointer is on a border (column, row, or window), the pointer changes to a two-headed black pointer. When adjusting row height, the arrow goes up and down. When adjusting column width, the arrows point right to left.	Adjust the column width, row height, or window size.
	When you are editing the contents of a cell, the pointer will change to an I-beam.	Move the insertion point within the cell.
	The pointer turns to a four-headed arrow when you have a graphic that may be moved.	With the pointer over the graphic, click and drag to the new location.
	Appears when you are pointing to the border of a cell.	Click and drag cell to a new location.
	Appears when you are at the "fill corner" of a cell or range of cells.	Click and drag the fill corner to Autofill other cells with similar information.

Return to Table of Contents

File Open and Save

The Open, Save, and Save As windows (pictured below shown below) have improved functions. The windows can be opened by going to File > Open, File > Save, and File > Save As. It gives you the option of quickly navigating folders to find your document or to save it in a specific folder. Located on the left side of the the dialog box, there are five buttons(History, My Documents, Desktop, Favorites, and My Network Places) which will quickly bring you to those chosen locations.

Return to Table of Contents

Printing

Printing Formulas

Troubleshooting your worksheets is easier if you print the formulas. This is especially true if you have a large worksheet or if you are managing a worksheet that someone else created.

In order to print the formulas, they must first be displayed. After you are done printing, you can readjust the view to show the results of the formulas.

Displaying the Formulas:

1. From the Tools menu, select Options.
2. The Options dialog box appears.
3. Select the View tab.
4. Under Window options, select Formulas. (A check means the option is turned on; an empty box means the option is turned off.
5. Click OK.

Return to Table of Contents

Printing with Color

Printing with color can enhance your finished Excel document.

*NOTE: To print using color, you must have access to a color printer. If you have applied color to your Excel document on screen, but you print to a black and white printer, the document will print in shades of gray. Use Print Preview to be sure that the document will print appropriately.

1. From the File menu, select Page Setup.
2. The Page Setup dialog box will appear.
3. From the Page Setup dialog box, select the Sheet tab.
4. Under Print, make sure that Black and White is not selected. (An empty check box means the option is turned off.)
5. Click OK.
6. From the File menu, select Print Preview.
7. Make sure the document appears in color.
8. Click Close.

Return to Table of Contents

Comments

Adding Comments

Adding a comment to a cell allows you place additional information within that cell. The comment, along with the username of the person who inserted the comment, appears when you point to the cell.

Adding Comments: Menu Option

1. Select the cell to which the comment will be added.
2. From the Insert menu, select Comment.
3. The Comment box appears with your username.
4. Type your comment.
5. Click another cell.
6. A red triangle will appear in the cell indicating that a comment is attached to the cell.
7. When you place your mouse over the cell, the comment will appear.

Cell with comment	Text of comment revealed

Adding Comments: Quick Menu

1. Right click the cell to which you want to add a comment.
2. Select Insert Comment.
3. The Comment dialog box appears.
4. Type your comment.
5. Click another cell.
6. A red triangle appears in the cell indicating that a comment is attached to the cell.
7. When you place your mouse over the cell, the comment will appear.

Adding Comments: Toolbar Option

1. Select the cell you want to add a comment to.
2. On the Reviewing toolbar, click New Comment .
3. The Comment dialog box appears.
4. Type your comment.
5. Click another cell.
6. A red triangle appears in the cell indicating that a comment is attached to the cell.
7. When you place your mouse over the cell, the comment will appear.

Return to Table of Contents

Editing Comments

Editing a comment allows you to add to or change the information you have placed within a cell as a comment.

Editing Comments: Menu Option

1. Select the cell that the contains the comment to be edited.
2. From the Insert menu, select Edit Comment.
3. The Comment box will appear.
4. Make the appropriate changes to the comment.
5. Click another cell.

Editing Comments: Quick Menu

1. Right click the cell which contains the comment.
2. Select Edit Comment.
3. The Comment box appears.
4. Make the appropriate changes to the comment.
5. Click another cell.

Editing Comments: Toolbar Option

1. From the Reviewing toolbar, click Edit Comment .
2. Make the appropriate changes.
3. Click another cell.

Return to Table of Contents

Deleting Comments

Deleting comments will allow you to either remove the comment you have placed within an individual cell or to remove all of the comments you have placed within a document.

Removing Comments from a Single Cell: Menu Option

1. Select the cell with the comment to be deleted.
2. From the Edit menu, select Clear Comments.

Removing Comments from a Single Cell: Quick Menu

1. Right click the cell which contains the comment.
2. Select Delete Comment.

Removing Comments from a Single Cell: Toolbar Option

1. Select the cell with the comment to be deleted.
2. On the Reviewing toolbar, click Delete Comment .

Removing Comments from All Cells: Menu Option

1. Open the sheet with the comments to be deleted.
2. From the Edit menu, select Go To.
3. The Go To dialog box appears.
4. Click Special.
5. The Go To Special dialog box appears.
6. Select Comments.
7. Click OK.
8. All comments on the active sheet will be selected.
9. From the Edit menu, select Clear Comments.

Return to Table of Contents

Displaying/Hiding Comments

To have comments remain on the screen or disappear from the screen when your pointer is not "over" the cell, use one of the following sets of directions.

Displaying/Hiding Comments: Single Cell

1. Right click the cell which contains the comment.
2. To display the hidden comment, select Show Comment.
   OR To hide the displayed comment, select Hide Comment.

Displaying/Hiding Comments: All Cells

1. To display/hide all of the comments within the document, from the View menu, select Comments.
2. Selecting Comments acts as a toggle turning comments on/off.

Return to Table of Contents

Printing Comments

Comments must be displayed before printing. See Displaying Comments for more information.

1. Open the worksheet that contains the comments you want to print.
2. From the File menu, select Page Setup.
3. Select the Sheet tab.
4. To print all comments in a group, in the Print section, from the Comments pull-down list, select At end of sheet.
   OR To print comments as they appear, in the Print section, from the Comments pull-down list, select As displayed on sheet.
   * NOTE: Only displayed comments will print for the second option.
5. Click Print.
6. Click OK.

Return to Table of Contents

Formatting

Text

Font names now are displayed in their font on the font menu in the toolbar, so you can preview a font style before applying it to a worksheet. This new feature eliminates the "trial and error" approach of choosing an appropriate font.

Return to Table of Contents

Wrapping Text

If you have text that appears in a single cell but you want to increase the height of that cell to accommodate all of the words, you can use the Wrap text option.

1. Select the cells that you want to apply Wrap text to.
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. Select the Alignment tab.
5. Under Text Control, select Wrap text.
6. Click OK.
   
   * NOTE: To display all of the text, it may be necessary to adjust row height.

To unwrap text:

1. Select the cell which has wrapped text
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. Under Text Control, deselect Wrap text.
5. Click OK.

Return to Table of Contents

Rotating Pictures

The new Excel 2002 (XP) makes it easier to rotate inserted pictures, autoshapes, and World Art.

To Rotate image

• Select the image by clicking on the image, if it is not already selected.
• Click and hold the mouse button over the green dot at the top of the text box (refer to 'Hi' pictured at the right above). Now the cursor will change into rotating arrows.
• Pull the handle to the right or left and it will change the angle. Let go of the mouse button when the text is at the desired angle.

Return to Table of Contents

Diagrams

Excel now includes a new gallery of common conceptual diagrams. You can customize these pre-drawn diagrams with text, animation effects, and a variety of formatting styles. Choose from diagrams such as Pyramid for showing the building blocks of a relationship, Radial for showing items in relation to a core element, and more.

Ways to Create a Diagram

1. Go to Insert and select Diagram.
2. Click the Diagram button in the Drawing toolbar. (To learn how to add the Drawing toolbar to your window, click here.)
3. In the Diagram Gallery that appears (as shown above to the left), select the chart type and click OK.

To Edit a Diagram

• Click the buttons on the Diagram Toolbar (as shown above to the right), to edit the diagram. Note: the Change to option selected in red will not appear on the Diagram Toolbar for Organization Chart.
• To add more cells to the diagram, choose Insert Shape button .
• To change the overall size of the diagram, choose Layout button .
• Two extra functions for Organization Chart
  1. Choose Select function to selectively change level and branch information.
  2. Choose to change the overall look of Organization Chart.

To Delete a Diagram

• Select the diagram by clicking the mouse within the border of the diagram (the Diagram Toolbar will appear if you have selected the diagram).
• Press BACKSPACE or DELETE on your keyboard to delete the diagram.

Return to Table of Contents

Color-Code Worksheet Tabs

Excel 2002 lets you apply color to worksheet tabs in multibook spreadsheets to better categorize and manage data.

Ways to Add Tab Color

• Using the Menus
  1. Go to Format > Sheet and select Tab Color
  2. Choose the color from the Format Tab Color window and click OK. This applies color to the tab of the active sheet.
• Using the Mouse
  1. right click on the Sheet tab and select Tab Color.
  2. Choose color from the Format Tab Color window and click OK. This applies color to the tab of the selected sheet.

Return to Table of Contents

Watch Window

The Watch Window allows user to keep track of results from multiple cells while working on different sheets or workbooks.

Opening/Closing the Watch Window

• To open Watch Window, go to the Tools menu, select Formula Auditing, and choose Show Watch Window.
• The Watch Window shortcut icon also exists on the Formula Auditing toolbar.
• Click on the X box in the upper right of the Watch Window to close it.

Adding/Deleting A Cell in Watch Window

Adding a cell:

• To add a cell to the watch window, use the mouse to right click on the desired cell.
• Select Add Watch in the menu that appeared.
• The cell that is added to the Watch Window will be the last on the list.

Deleting a cell:

• To delete a cell from the watch window, click on the the cell in the Watch Window.
• The Delete Watch option will be darker.
• Click the Delete Watch option to delete the cell from Watch Window.

Return to Table of Contents

Formula Evaluator

The Formula Auditing toolbar can be used to graphically display or trace the relationships between cells and formulas with blue arrows, monitor, and correct formulas. You can trace the precedents (the cells that provide data to a specific cell) or you can trace the dependents (the cells that depend on the value in a specific cell). A blue tracer arrow indicates which cells are the precedents and which cells are the dependents.

Opening/Closing Formula Auditing Window

• To open the Formula Auditing Window, go to Tools > Formula Auditing and select Show Formula Auditing Toolbar.
• To close the Formula Auditing Window, click the X box on the upper right side of the window.

Functions of the icons on the Formula Auditing Window

• The Error Checking button will search through the worksheet for formulas and display a dialog boxy when a problem is found. Problems can be reviewed in two ways: one at a time like a spelling checker, or immediately on the worksheet as you work.
• The Trace Precedence button will trace the path of a formula using arrows and highlighting.
• The Remove Precedence Arrows button will remove any arrows or highlighting from using Trace Precedence.
• The Trace Dependents button will show you an arrow indicating which other cells depend on the value of the selected cell.
• The Remove Dependent Arrows button will remove the arrows from using the Trace Dependents function.
• The Remove All Arrows button will remove all arrows from Trace Precedence or Trace Dependents..
• The Trace Error button will look for any errors in the formula.
• The New Comment button will allow you to insert a comment to a cell. The comment can be viewed by hovering over the cell. A triangle in the upper right corner of the cell indicates that a comment has been added to the cell.
• The Circle Invalid Data button will find and circle data that does not fit certain patterns.
• The Clear Validation Circles button will undo any markings made by the Circle Invalid Data function.
• The Watch Window icon will open the Watch Window, which allows the user to keep track of results on multiple sheets or workbooks.
• The Evaluate Formula icon will allow user to view the entire formula and make changes for the entire workbook.

Return to Table of Contents

Custom Page Breaks

If your pages are dividing at awkward points when printing, you can start a new page by manually inserting a page break.

1. Click the cell that is below and to the right of where you want to insert the page break.
2. From the Insert menu, select Page Break.
3. The page break is inserted.
   
   

* NOTES:

• If you click a cell in row one, only a vertical page break will be inserted.
• If you click a cell in column A, only a horizontal page break will be inserted.
• If you click any other cell, both a horizontal and vertical page break will be inserted.

Return to Table of Contents

Formatting with the Painter

The typical Copy & Paste will copy the information (text or formula) and the formatting of the cell(s). If you want to copy only the formatting, you can use the Painter option. This will format the destination cell the same as the source cell without changing the content.

Formatting with the Painter: Single Cell

1. Select the cell that contains the formatting you want to copy.
2. On the Standard toolbar, click Format Painter .
3. The pointer changes shape to include a paint brush next to it.
4. Click the cell to which you want the copied format applied.

Formatting with the Painter: Multiple Cells

1. Select the cell that contains the formatting you want to copy.
2. On the Standard toolbar, double-click Format Painter .
3. The pointer changes shape to include a paint brush next to it.
4. Select the range of cells that you want the copied format applied to by clicking and dragging.
5. Repeat step 3 for additional cells in which you want the format applied.
6. When done, to turn off the Painter, click Format Painter again.

Return to Table of Contents

Drawing Borders

The Borders toolbar allows users to easily add borders or delete cell borders in their spreadsheet. It also allows users to change the line color, line style, and even allows users to add grids to the spreadsheet.

Opening/Closing Borders Window

• Click on the down arrow beside the Border icon and select the Draw Borders... option to open the Borders window.
• Click the X box on the upper right corner of the Borders window to close the window.

Using the Borders Window

• Click on the Draw Border icon to draw borders. This option will change the mouse arrow into a pencil.
• To draw grids in the cell, click on the down arrow next to the Draw Border icon and select Draw Border Grid option. (The icon will now change to ).
• To erase the border or grid that you draw, click Erase Border icon .
• To change the type of line you want to draw, click down arrow on Line Style .
• To change the color of the border or grid that you have drawn, click Line Color icon .

Return to Table of Contents

Header/Footer

The improved Header and Footer window makes it easier to format and edit the header and footers. Now pictures and drawings can be inserted directly as headers or footers.

Ways to Open Header or Footer Window

1. Go to the View menu and select Header and Footer. Click Customize Header... or Customize Footer....
2. Go to the File menu and select Page Setup. Then click the Header/Footer tab on the Page Setup window and select Customize Header... or Customize Footer....

Insert Options in Header or Footer Window

• Inserting and Editing Pictures
  1. Click the fields Left section:, Center section:, Right section:. Each option place the picture either in the left, middle, or right part of the header or footer, respectively.
  2. Click the Insert Picture icon .
  3. Select the picture from the Insert Picture window and click Insert.
  4. To edit the size, shape or rotation of the image, highlight the picture and click button e.
• Inserting Text
  1. Select the fields where you want the Text to be placed, e.g. Left.
  2. Click the Text icon .
  3. Select the font, style and size in the Font window that pops up.
  4. Enter text in the text field.
• Other Insert Icons
  1. To insert today's date, click on the Date icon .
  2. To insert the path and file name of the current workbook, click on the File & Path icon .
  3. To insert the name of the current workbook, click on the File Name icon .
  4. To insert the page number, click on the Page Number icon .
  5. To insert the name of the sheet that is being edited, click on the Sheet Name icon .
  6. To insert the current time, click on the Time icon .
  7. To insert the total number of pages for the current workbook, click on the Total Pages icon .

Return to Table of Contents

Sort Text as Numbers and Multiple Columns

The Sort Text as Numbers Option

• Use the mouse to select the cells you want to sort (the cells should be highlighted). Find the the sort option in one of the following ways:
  1. Click on the Sort Ascending icon or the Sort Descending icon .
  2. Click on the Data menu and select Sort.... Then choose either Ascending or Descending of the Sort window in the Sort by field and click OK.

The Sort Multiple Columns Option

• Use the mouse to select the cells you want to sort (the cells should be highlighted).
• From the Data menu select Sort....
• If there is data next to the selected cells a Sort Warning window will pop up.
• If you want to sort the lists next to the selected cells choose Expand the Selection in the Sort Warning window and click Sort....
• If you do not want to sort the cells nearby then select Continue with the current selection in the Sort Warning window and click Sort....

Return to Table of Contents

Merging and Centering Text

1. Type the text in the first cell of the group.
2. Select the text and a cell from each column that you want to center across (e.g., to center across columns A through D in row 2 of the worksheet, you will select cells A2, B2, C2, and D2).
3. From the Format menu, select Cells.
4. The Format Cells dialog box appears.
5. Select the Alignment tab.
6. From the horizontal pull-down list, select Center Across Selection.
7. Under Text Control, select Merge Cells.
8. Click OK.

Return to Table of Contents

Cell Merging Shortcut

Merging Cell

• Use the mouse to select the empty cells you want to merge.
• Click the Merge and Center button to merge the selected cells.

Un-merging Cell

• To undo any merges you have made, click the Merge and Center button .

Return to Table of Contents

Extended AutoSum

Using the Basic AutoSum Option

• Select the cell where you want to place the results of the sum of chosen cells.
• Click the AutoSum button on your toolbar and highlight the cells you want to sum up.

New Extended AutoSum Functions/Properties

• Opening/Using Extended AutoSum Functions
  1. Select the cell where you want the result to be placed.
  2. Click on the down arrow next to the AutoSum button circled in red in the image to the right, located on your Standard Toolbar.
  3. Select the function you want to use, such as Average, Count, etc...
  4. Use the dotted line box to select the cells you want to find the average, max, min, etc. of and press the ENTER key.
• Extended AutoSum Menu
  1. The Sum option will return the sum of selected cells
  2. The Average option will return the average of selected cells
  3. The Count option will count the number of selected cells
  4. The Max option will return the maximum number typed in the selected cells
  5. The Min option will return the minimum number typed in the selected cells.
  6. The More Functions... option will open the Insert Functions Window where you can choose any type of functions that Excel offers.

Return to Table of Contents

Find and Replace

Find and Replace includes new features allowing you to search across worksheets within a workbook, or for a specific cell formatting. Excel can return all the results in a single window and let you pick the format of an existing cell to quickly set the parameters of your search.

Ways to Open Find and Replace Window

1. Go to the Edit menu and select Find. For more options click Replace tab.
2. Go to the Edit menu and choose Replace.

Return to Table of Contents

Function Wizard

Excel 2002/XP has made it easier to search for functions by allowing the user to type in the function name.

Ways to Open the Insert Function Window

1. Click the Function button on the Toolbar.
2. Go to Insert menu and select Function....

To Find the Function

1. Type in the function name or a description of the function in the Search for a Function: field.
2. Choose a category from the Or select a category: field and select the function in the Select a function: field. Then click OK.

Return to Table of Contents

Hiding Columns, Rows, and Cells

At times, there may be information in your worksheet which you no longer need to see. At other times, you might be printing your worksheet and want to print only columns A-F and columns H-J, skipping column G. Rather than rearrange your worksheet for either of these examples, you can simply hide the information. You can also hide information in specific cells.

Hiding Columns

If there is information on your worksheet you do not need to see or if you want to print only certain columns, you can hide those columns you do not want to see or print.

1. Select a cell within the column(s) to be hidden.
2. From the Format menu, select Column > Hide.
3. The column is hidden.

Hiding Columns: Quick Menu Option

1. Right click the column ID (e.g., B).
2. Select Hide.
3. The column is hidden.

Redisplaying Columns

1. Select at least one cell from both of the columns around the hidden column(s) to be redisplayed, e.g., if column B is hidden, select a cell from both columns A and C
   HINT: If you cannot select the appropriate cells, you can use the Go To command.
2. From the Format menu, select Column > Unhide.
3. The column reappears.

Redisplaying Columns: Quick Menu Option

*This option works well for redisplaying column A, since there are not columns on both sides of column A.

1. Hold your cursor over ID for column B toward the left side.
2. The cursor will change to an open, double sided arrow as shown here.
   
3. Right click.
4. Select Unhide.

Return to Table of Contents

Hiding Rows

If there is information on your worksheet you do not need to see or if you want to print only certain rows, you can hide those rows you do not want to see or print.

1. Select a cell within the row(s) to be hidden.
2. From the Format menu, select Row > Hide.
3. The row is hidden.

Hiding Rows: Quick Menu Option

1. Right click the row ID (e.g., 5).
2. Select Hide.
3. The row is hidden.

Redisplaying Rows

1. Select at least one cell from both of the rows around the hidden row(s) to be redisplayed, e.g., if row 5 is hidden, select a cell from rows 4 and 6.
   HINT: If you cannot select the appropriate cells, you can use the Go To command.
2. From the Format menu, select Row > Unhide.
3. The row reappears.

Redisplaying Rows: Quick Menu Option

*This option works well for redisplaying row 1, since there are not rows on both sides of row

1. Hold your cursor over the ID for row 2 toward the top.
2. The cursor will change to an open, double sided arrow as shown here.
   
3. Right click.
4. Select Unhide.

Return to Table of Contents

Hiding Cell Contents

You have the ability to hide the contents of individual cells if you do not need to view their contents or you simply do not want to print certain cells.

1. Select the cell(s) to be hidden.
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. From the Format Cells dialog box, select the Number tab.
5. Under Category, select Custom.
6. In the Type text box, type three semicolons ( ;;; ).
7. Click OK.
8. The cell(s) is now hidden.

To redisplay cell information:

1. Select the cell(s) to be redisplayed.
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. From the Format Cells dialog box, select the Number tab.
5. Under Category, select the appropriate date, time, or number format.
6. Click OK.
7. The cell(s) reappears.

Return to Table of Contents

Worksheet Groups

Grouping Worksheets

1. Click the tab of the first sheet you would like to include in your group.
   
2. To select non-contiguous sheets, hold down the Ctrl key and click the sheets to be included in the group.
   OR To select contiguous sheets, hold down the Shift key and click the last sheet to be included in the group.
   * NOTES:
   - The tabs of the selected sheets will appear white.
   - Unselected sheets tabs will remain unchanged (colors may differ based on your windows color scheme).
   - Additionally, the notation [Group] appears next to the file name.
3. When all the sheets of the group have been selected, release the Ctrl or Shift key.

Return to Table of Contents

Ungrouping Worksheets

When done working with your sheet groups, you will need to ungroup them.

To ungroup all sheets in the group:

1. Right click the sheet label.
2. Select Ungroup Sheets.

To remove individual sheets from the group:

1. Press the Ctrl key and click the sheet label of the sheet you want to remove.
2. The worksheet is removed from the group.

Return to Table of Contents

Formatting Worksheet Groups

If you have multiple sheets within your workbook that require the same formatting, you can format "groups." This option works when the worksheets have similar information in the same relative location. There are many types of formatting that can be done with sheet groups:

To format groups of sheets:

1. Group the sheets.
2. On the current worksheet, select the cell(s) that you want to format.
3. Apply the appropriate formatting.
4. Repeat as necessary.
5. Ungroup the worksheets.
   *NOTE: See Ungrouping Worksheets for more information.

Return to Table of Contents

Printing Worksheet Groups

If you have multiple sheets in your workbook and you want to print select worksheets, you can do this with the Grouping function. This method will print the active area of each sheet (A1 to the last column with information to the last row with information) or the assigned print area. It may result in different ranges being printed for each sheet.

1. Group the sheets.
2. From the File menu, select Print.
3. Under the Print what section, verify that Active sheets is selected.
4. Adjust print settings as appropriate.
5. Click OK.
6. Ungroup the worksheets.
   *NOTE: See Ungrouping Worksheets for more information.

 

Return to Table of Contents

Protection Options

When you are protecting your workbook you have two primary options:

• Prevent data entry for select cells.
  Users can access the worksheet and view the information; however, access for making changes is restricted.
  See Locking & Unlocking Cells.
• Restrict or prevent access to the file.
  Users can be prevented from viewing the worksheet or users can view the workbook but not make changes to it.
  See File Level Protection.

Locking and Unlocking Cells

By default, Excel "protects" cells, graphics, charts, and other worksheet objects. This protection will only take effect by turning on the Worksheet Protection option in the Protect Sheet dialog box. Therefore, if you enable protection, no changes can be made to a cell until you unlock that cell.

Locking Cells:

You can easily lock any cell in a worksheet. After you Unprotect the Worksheet, complete the following instructions:

1. Select the cell(s) to be locked.
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. Select the Protection tab.
5. Select Locked. (There will be a check before it.)
6. Click OK.
7. The cells are locked.

Unlocking Cells:

In order to unlock cells, sheet protection must first be turned off. See Worksheet Level Protection or Workbook Level Protection for more information.

1. Select the cells you want to unlock.
2. From the Format menu, select Cells.
3. The Format Cells dialog box appears.
4. Select the Protection tab.
5. Deselect Locked. (There will no longer be a check before it.)
   
   
6. Click OK.
7. The cells are unlocked.

Return to Table of Contents

Workbook Level Protection

You can prevent a workbook from having its structure and windows modified or resized by another user.

Protecting the Workbook:

1. From the Tools menu, select Protection > Protect Workbook.
   
2. The Protect Workbook dialog box appears.
   
   
3. In the Protect Workbook dialog box, select the appropriate option(s):
   • Structure: prevents the user from changing the order of the sheets within a workbook. This includes adding or deleting worksheets.
   • Windows: prevents the user from being able to resize or move the window.
   • Password: allows only those who know the password to turn the workbook protection off.
4. Click OK.
5. The workbook is protected.

Unprotecting the Workbook:

1. From the Tools menu, select Protection > Unprotect Workbook.
2. The workbook is unprotected. Users can now modify the structure and windows of the workbook.
   *NOTE: If you included a password when you turned the protection on, you must enter the password to turn the protection off.

Return to Table of Contents

Worksheet Level Protection

You can protect a single worksheet from being modified by another user.

Protecting the Worksheet:

1. From the Tools menu, select Protection > Protect Sheet.
2. The Protect Sheet dialog box appears.
3. In the Protect Sheet dialog box, select the appropriate option(s):
   • Protect worksheet and contents of locked cells: prevents changes to locked cells.
   • Password to unprotect sheet: allows only those who know the password to unprotect the worksheet.
   • Allow all users of this worksheet to: Checked boxes are aspects that any user can access.
4. Click OK.
5. The worksheet is protected.

 

Unprotecting the Worksheet:

1. From the Tools menu, select Protection > Unprotect Sheet.
2. The worksheet is unprotected. Users can now modify the worksheet.
   *NOTE: If you included a password when you turned the protection on, you must type the password in the Password text box to turn the protection off.

Return to Table of Contents

File Level Protection

You have two options when saving your workbook file with a password. You can save the file so only those who know the password can open the file or so only those who know the password can modify the file.

Protecting the File:

1. From the File menu, select Save As.
2. The Save As dialog box appears.
3. In the Save As dialog box, from the Tools menu, select General Options.
4. The Save Options dialog box appears.
   
   
   • To prevent unauthorized access to the file, in the Password to open text box, type a password.
   • To prevent unauthorized changes to the file, in the Password to modify text box, type a password.
5. Click OK.
6. The Confirm Password dialog box appears.
7. In the Confirm Password dialog box, type the password(s) again.
8. Click OK.
9. To continue saving the document, click Save.

Removing the Password:

If you no longer need to password-protect the file, you can remove the password.

1. From the File menu, select Save As.
2. The Save As dialog box appears.
3. In the Save As dialog box, from the Tools menu, select General Options.
   
   • In the Password to open text box, press Delete.
   • In the Password to modify text box, press Delete.
     
4. Click OK.
5. The file is no longer password protected.

Return to Table of Contents

Data Recovery, Repair, and Error Reporting

Excel 2002/XP has more new features that will prevent files from being lost during an improper shutdown. It comes with recovery programs that can repair and extract corrupted Excel files. Excel 2002/XP also allows the user to report the error to Microsoft so Microsoft can better diagnose and correct the errors.

Document Recovery

Shutting Down Frozen Excel Programs in a Functioning Window

• Go to Start menu > Programs > Microsoft Office XP > Microsoft Office Tools and select Microsoft Office Application Recovery.
• You can choose to Recover or End the selected application in the Microsoft Office Application Recovery window pictured below.
  

Document Recovery after Restarting a Frozen Computer

• Open Microsoft Excel.
• Select the workbook you were working on before the crash.
• The Document Recovery Window shown to the right will appear.
• Choose the document you wish to recover in the Document Recovery Window.
• It would be best to save the recovered file under a different name rather than the original name.
  

Return to Table of Contents

Error Reporting

The Error Reporting Window will sent information to Microsoft so they can better diagnose the problem. You can choose not to send the information by selecting Don't Send.

When Will the Error Reporting Window Appear?

1. If the program is frozen and you have managed to force quit the program the Error Reporting Window below will appear.
2. If a serious error occurs in any Microsoft Office XP programs or Windows XP and Windows 2000, the Error Reporting Window will appear.
   

Return to Table of Contents

Repair and Extract

To Open the Repair and Extract Window

• Go to the File menu and select Open....
• Single click the file you want to repair so the file is now highlighted.
• Click the down arrow on the Open button in the lower right corner of the Open Window
• Select Open and Repair... boxed in red in the image below.
• Choose Repair first in the Microsoft Excel Window that appears.
• If repair does not work, repeat steps 1 - 4 and choose Extract Data in the Microsoft Excel Window that appears.
  

Return to Table of Contents

Auto Recover

Allows the user to set the time interval for Auto Recover to save their files in case of a crash.

Automatic Recovery Save Function

• Go to the Tools menu and select Options....
• Click the Save tab and make sure that the check-box before the Save AutoRecover info every: field is checked.
• Select the time between AutoRecover saves by using the up or down arrow in the minutes field.

Return to Table of Contents

---

Other New or Modified Features

Help and Office Assistant

Microsoft Excel Help still consists of the Office Assistant (which is automatically hidden) and the browser-based Microsoft Help catalog, but both features have been slightly changed in Excel 2002.

Ways to Access Excel Help

• Click on the Help button on the Standard Toolbar
• Click on Microsoft Excel Help link at the bottom of the Task Pane window
• Go to the Help menu and select Microsoft Excel Help.
  • If the Office Assistant is turned on:
    • It will open first and provide a box in which to type your question.
  • If the Assistant is turned off:
    • the Help Catalog will open.
  • To find help on a specific topic:
    • Click on the AnswerWizard tab in the Help Catalog.
    • Type your question in the What would you like to do? box, and click Search.
• Type in a keyword or phrase in the Ask a Question box located on the right side of the menu bar and press Enter.

The New Look of Excel Help

• The Help catalog has now been organized into one window from which you can access either the Contents, the Index, or an AnswerWizard feature. The window is divided into two columns or frames with the Contents, Indices, or AnswerWizard parameters appearing in the left column and the help text and links appearing in the right column. This setup simplifies navigation in the Help catalog.
• Ask a Question Box is a text-box located on the far right side of the Menu bar. This is a shortcut for searching for a specific topic. Type in your topic and press Enter, and the Help catalog will appear with your information. The textbox is also a pull down menu, which you can use to refer back to previous searches
• The Office Assistant is a character, usually a paper clip person, as pictured on the right. It functions almost exactly the same way as it does in Excel 97 and 2000.
  • Ways To Remove Office Assistant
    • Go to the Help menu and select Microsoft Excel Help. The Assistant will appear with the window help box. Choose the Options button, then the Options tab, and uncheck the "Use the Office Assistant" box.
    • Go to the Help menu and selecting Hide the Office Assistant.
    • Right-clicking on the Assistant and then selecting Hide.
  • To Restore Office Assistant
    • Go to the Help menu
    • Select Show the Office Assistant.

Return to Table of Content

Additional Resources

More detailed instructions and information about Excel 2002 for XP can be found at:

• Excel 2002 Online Help
• Excel 2002 Support Center

Further information on Office XP can be found at:

• Home Page for Microsoft Office XP
• Office XP In-Depth

Some support for Excel 2002 and Office XP on campus is provided through the HelpDesk at x3333. For more detailed instruction on how to use the full features of Office XP, look into purchasing one of the several books that have been published on these topics. Some useful titles are:

• Microsoft Office XP Resource Kit
• Office XP Bible
• Special Edition Using Microsoft Office XP

Return to Table of Contents

Command Changes in Excel 2002

Below is a list of commands that have been changed from Excel 97 or added as new in Excel 2002.

Return to Table of Contents

Marge Rowell mrowell@wellesley.edu
Information Services
Date Created: November 30, 2001
Last Modified: March 6, 2003
Expires: July 1, 2002