Excel 2000 is the version following Excel97. It has been expanded to offer more options while attempting to create a more intuitive environment. It starts you off with a workbook file. This file stores up to 255 sheets. These sheets can be spreadsheets (which are the same as worksheets), charts, macros, or pivot table sheets. By default, when you open a new workbook, there are three sheets available for use. If you wish to change the default number of sheets in your workbooks, go to the Tools menu and select Options. From the Options tab box, choose General and look for the Sheets in New Workbook field to change the number of sheets. If you wish to see a list of the new features offered in Excel 2000, go to the Help menu and select Microsoft Excel Help . In the right hand window click on the underlined phrase, Finding out whatís new in Excel 2000 .
To move between the sheets in a workbook, use the sheet tabs located on the left side of the horizontal scrollbar at the bottom of the page. These tabs display the name of each sheet in the workbook. The sheet name, shown in bold on a white background, is the currently active sheet. You may use the scroll buttons at the left of the sheet tabs to scroll quickly among the sheets in your workbook.
In a new workbook, sheets are given the default names of "Sheet1", "Sheet2", etc. To change the name of a sheet, double-click on the tab and type in a new name. Sheet names must adhere to the following rules:ï no more than 31 characters in length (you can use spaces!).
ï must be unique within the workbook.
ï must not use the following special characters: \, /, *, ?, :, [ or ] .
Clicking once with the right mouse button on any sheet tab will bring up a shortcut menu which displays all options for working with your sheet: insert, delete, copy, move, rename, select all sheets, and view code.
To insert a sheet into a workbook, click on the sheet tab to the right of your desired insertion point. Using the right mouse button, bring up the tab shortcut menu and choose Insert. Select the type of sheet you want to insert. To delete a sheet from a workbook, click on its tab and select Delete from the shortcut menu.
It's easy to move sheets from one location to another within a workbook by dragging and dropping the sheet's tab at a new location. Likewise, you can make a copy of a sheet in the current workbook by selecting the sheet to be copied, holding down the Ctrl key and dragging the sheet to the new location. You can also move or copy a sheet to another workbook by dragging the sheet tab to the other workbook. However, you must first have the other workbook open and tiled in the same window. The Move or Copy Sheet... dialog box under the Edit menu allows you to do the same thing.
Excel's workbook approach makes it easy to setup or edit files sharing common information or a common format. This is done by using group-editing techniques. To edit multiple sheets, you must first group these sheets together. There are three methods for grouping worksheets:ï To group adjacent worksheets, select the first sheet, and holding down the Shift key, click on the last sheet.
ï To group non-adjacent worksheets, select the first sheet and, holding down the Ctrl key, click the tab of each additional sheet.
ï To select all worksheets in a workbook, activate the tab shortcut menu and choose Select All Sheets .
Once the sheets are grouped, you will note that nearly everything you do in one document is repeated in the others. This is convenient for applying formats, entering text or formulas, changing column widths, etc. When you are finished making your changes, activate the shortcut menu and choose Ungroup Sheets .
At times you might want to view and have access to more than one sheet or part of a sheet, or more than one workbook at a time. The Window menu provides you with options to make this easier. The New Window command creates an additional window for the active workbook, so you can view different parts of it simultaneously. This is helpful if you need to compare two sheets within the workbook at the same time. If you need to work with more than one workbook at once, open the workbooks and use the Arrange. . . command to display equal portions of the workbooks on your screen. This makes it easy to transfer information between workbooks.
The Split command under the Window menu splits the active sheet into two or four panes and allows you to scroll multiple panes simultaneously. To split a window into vertical or horizontal panes, switch to the worksheet window you want to split. For vertical panes, select a column. For horizontal panes, select a row. From the Window menu, choose Split. To adjust the split panes, drag the split bar or the split box to the desired position. To remove splits from a window, switch to the worksheet window from which you want to remove the split. Double-click any part of the split bar, or go to the Window menu, place the cursor on the double arrow at the bottom of the drop-down list and choose Remove Split after it appears in the menu .
The formatting toolbar, which appears every time you open Excel, makes formatting much easier. The sections below discuss how to change the sizes of columns and rows, how to apply formatting to cells and their contents, and how to use formatting shortcuts.
With the MouseIt is easy to adjust the width or height of a single column or a row by using the mouse to point to the gridline of the column or row and dragging the line to the desired size. To change the size of multiple columns or rows, select all the ones to be adjusted and, using the mouse, drag a line of any of the selected rows or columns to the desired size. You may also size columns (or rows) so that they adjust to the width of the longest entry in each column. This is called AutoFit. To AutoFit a column or row, select it, double-click on the border of the heading of the column/row, and its size will align around the largest entry.
From the Menu: First, select the column/row, choose Format, then Column or Row to make adjustments as follows:ï Choose Width (for column) or Height (for row) to type in a column width or a row height.
ï Choose AutoFit to adjust a column around the longest entry, or a row around the largest font.
ï Select Hide or Unhide commands to hide or show columns or rows.
ï Select Standard Width to change the selected columns or rows back to their default size.
You can apply formatting changes to a cell, a range of cells, or even to individual characters within a cell. To format characters within a cell, double-click in the cell (or click in the formula bar) and select those characters or words that will be affected by the formatting change. Then use the formatting toolbar, or go to the Format menu and select Cells... This will activate the Format Cells tab box where you can select any of the following formatting categories: Number, Font, Alignment, Border, Patterns, or Protection.
The Number tab helps you determine how numeric information is shown in the selected cells. You can use one of the built-in number formats, or you can create your own custom formats by selecting Custom from the Category list. Category offers a variety of number, date, and time formats. When you select Custom, the built-in and custom formats are displayed in the Type box. Type lists all available format codes for a selected category. All cells in a new worksheet are formatted with the General format. When you type a number in a cell formatted as General, Excel assigns the number a built-in format based on what you typed. To add a custom format, edit the one shown in the Format box or type a new one. The custom format is added to the Custom category. The Sample box displays a sample of the format selected in the Type box. Delete removes a selected custom format displayed in the Type box. You cannot delete built-in number formats. If you are choosing from among various fraction, percent, or accounting formats, you can specify the number of decimal places you would like to appear on the worksheet using the up or down arrow keys. You can also choose built-in formats for displaying negative values in red or in parentheses.
The Alignment tab box provides options for aligning the contents of cells. The Horizontal option adjusts the left/right orientation. The General button, which is the default setting, aligns text to the left and numbers to the right. Fill repeats the contents of the selected cell until the cell is full. If blank cells to the right also have the Fill format, they are filled as well. Justify aligns text within a cell to the right and left. Center Across Selection centers a cell entry across selected cells. Wrap Text allows text to wrap in multiple lines within a cell. The Vertical option aligns cell entries with the top, center, or bottom of a cell. The Orientation options rotate selected cell entries. You must adjust row height to the length of the rotated text.
Formatting options found in the Font tab box include: choice of font, font style (bold, italic, etc.), font size, underlining, text colors, and effects such as strikethrough, superscript and subscript. Any time you wish to change text back the default font, simply check Normal Font in this dialog box.
The Patterns and Border tab dialog boxes help you set off portions of your spreadsheet by applying borders or patterns. The Protection dialog box allows you to secure cells either by locking them so that they cannot be changed or by hiding the formulas in cells so that others cannot see them.
You can use the Format Painter button on the Formatting toolbar to quickly and easily copy formatting elements from one area of your spreadsheet to another. To use the Format Painter , select the cell containing attributes you want to copy, click on the Format Painter icon, then click and drag through the cells to which you want to apply the format and release the mouse. If you would like to use this feature for cells that are not adjacent, you must double click the Format Painter . To exit the Format Painter mode, simply click the icon again.
Excel offers a variety of editing techniques to make copying or changing the data and formulas in your cells easier. These include tools and techniques such as AutoFill, Finding and Replacing , and the Toolbars.
Excel provides a wonderful shortcut menu for any copying or moving functions you may want to perform. Select the desired cell or range of cells, and point with your mouse to the border surrounding the selection. The mouse pointer will display a white arrow when pointed to any border or selection. Press the right mouse button and drag the selection where you want to copy or move it. You will be presented with a shortcut menu where you can choose to copy, move, copy values, copy formats, or copy/move with the option of shifting cells.
If you want to copy a range of cells with values created by a formula or function, select Paste Special... from the Edit menu or shortcut mouse menu, and click on the desired paste output (e.g., all values plus formulas, values, formats, notes, or all except borders ). NOTE: if you do not use Paste Special... when attempting to paste values created by a formula onto another worksheet, you will get a #REF error message. If you want to paste a column of cells into a row or vice versa, check the Transpose box.
AutoFill is a tool that allows you to create a series of fixed or incremental values on a worksheet by dragging the fill handle (the small cross box on the corner of the selection border) with the mouse.
Copying with AutoFillIf you want to copy a formula or fixed value from the active cell into the five cells below it, click on the fill handle and drag to cover the five cells. The fixed value will be copied to all five cells. AutoFill is an excellent tool to use when you want to copy to adjacent cells. You can also clear or "unfill" data within a selection by dragging the fill handle up or to the left within the selection and stopping within the selected cells. The data to be cleared appears in gray as you drag the fill handle.
Creating a Series with AutoFillType "Monday" into a cell, select it, and drag the fill handle to contiguous cells to fill in other days of the week. Other preset series you can create include months of the year and quarters. If you wish, you can also create numeric series such as 1970, 1975, 1980, ... or 7, 14, 21... To do this, simply type in the first two numbers in the series in two adjacent cells, and select both cells. Drag the fill handle in the desired direction, and you create the numeric series.
The AutoFill Shortcut MenuWhen you use the right mouse button to drag the fill handle you can get into an AutoFill shortcut menu. This menu makes it easier to perform tasks such as filling formats, and developing linear or growth trends.
Adding or Importing a Custom List or SeriesYou can create a custom AutoFill list such as Small, Medium, Large or High, Medium, Low. To create such a list, go to the Custom Lists tab under the Tools menu in the Options dialog box. You can then type a list directly into the List Entries box and select Add to save the list. If you have already entered a list onto your worksheet, click the Import button and select the range of cells containing the list to be imported directly from the worksheet. Click OK to add the imported list.
You can have Excel search for cells containing specific text, formulas, formats, values or range names, and you can also have it replace what it finds with something else. To find characters, select the area of the worksheet you want to search. If only one cell is selected, Excel searches the entire worksheet.
From the Edit menu, choose Find. In the Find what box, type the characters you want to find. Search specifies the direction of the search, by rows or by columns. Under Look in , select Formulas, Values, or Comments. Excel searches for the characters you choose only in the locations you specify. Select the Match case check box if you want Excel to use the exact combination of uppercase and lowercase characters you entered in the Find what box. Choose the Find entire cells only box if you want to be assured that the cell contents found in the search are an exact match with the criteria specified. To start the process select Find Next . You can choose this again to find another occurrence of the string.
To replace characters, choose Replace from the Edit menu. In the Find what box, type the characters you want find. You can include any letter, number, punctuation mark, or wildcard character in your search. In the Replace with box, type the characters you want to use instead. The Search, Match case , and Find entire cells only options operate in the same manner as with the Find command (see above). Choose the Replace All button if you want Excel to automatically find and replace all occurrences. Choose the Find Next button if you want to see consecutive occurrences - Excel automatically finds them.
Excel offers several options to make it easier to move around your worksheet.
Excel gives you the option of naming regions of the worksheet so that you can move to them rapidly without scrolling. You can name ranges in a couple of different ways. You can select the range you want to name, and choose Name, Define... from the Insert menu. Another way to do this is to select the range and enter the name in Name Box . If you want to change the name, select the text and type the new name. Spaces are illegal, so replace them with the underscore symbol (_). For instance, "Last Budget" must be typed as "Last_Budget." If the upper-left cell of the selection is a label, Excel will use that as the name; thus, if the upper-left cell of the selection reads "Italian 002 Students," the proposed name will be "Italian_002_Students." Use Go To... from the Edit menu to select a named region from a scrolling list, or click on the Name Box to bring down a scrolling list of available regions. Excel will immediately go to and select that region, allowing you to move around the worksheet quickly and efficiently.
The Freeze Panes command found in the Window menu freezes the top or left panes, or both, on the active sheet. This is useful for keeping row or column titles on the screen while you scroll to other parts of the worksheet. The panes that are not frozen do not scroll into the frozen part of the worksheet. To freeze and unfreeze panes, switch to the window in which you want to freeze the panes. If the window is not split, select a place to split the window. From the Window menu, choose Freeze Panes . To unfreeze panes, switch to the window that you want to unfreeze, and in the Window menu, choose Unfreeze Panes .
Toolbars are collections of tools to help you work faster and more easily. The Standard and Formatting toolbars are the default toolbars displayed and they contain tools for some of the most frequently used commands. Excel has thirteen toolbars. You can display any toolbar by choosing the Toolbars command from the View menu or by using the toolbar shortcut menu. You can reach the shortcut menu by clicking with your right mouse button on any currently displayed toolbar.
By default, Excel 2000 displays both the Standard and the Formatting Toolbars on a single line. You can see all of the formatting options by clicking once on the double-arrows on the right end of the toolbar.Perhaps you would rather have the formatting options displayed in full. Go to the Tools menu, and select Customize. At the Options tab, click the check next to Standard and Formatting toolbars share one row . Press the Close button, and you now can apply any of the formatting options on the toolbar with the touch of a single button.
To display a toolbar, choose Toolbars from the View menu. In the Toolbars box, select the toolbar you want to display. To hide a toolbar, choose Toolbars from the Options menu. In the Toolbars box, select the toolbar you want to hide and make sure you turn it off. If the toolbar is displayed in a separate toolbar window with a border, you can hide it by clicking the close box.
The shape of a floating toolbar is changed in the same way you change the size of a document window. Drag the size box at the lower right of the toolbar window. Excel automatically wraps the tools to fit in the new toolbar shape. If you move the toolbar back to the toolbar dock, Excel aligns the tools to fit in the horizontal or vertical dock.
To create a new toolbar, go to the View menu, choose Toolbars, then Customize. In the Toolbars tab, click the New button, give your new toolbar a name and click OK. Excel displays a new blank to the left of the dialog box. (Look hard; it's just a small box.) Excel also displays the Customize dialog box so that you can add tools to the toolbar. Click on the Commands tab, and in the Categories box, select the category of tools from which you want to add tools to the new toolbar. Excel displays the tools in the Commands box. To display a description of the tool, click the tool in the Commands box. Drag the tool from this box to the position where you want it to be on the new toolbar.You can also drag tools from a displayed toolbar to the new toolbar. This action moves the tool from the original toolbar to the new toolbar. Hold down Ctrl while you drag the tool to place a copy onto the new toolbar without affecting the original toolbar. Continue this until you have added all the tools you want. If you change your mind, you can remove the tool from the toolbar by dragging it off the toolbar and placing it anyplace where there is no toolbar. Choose the Close button.
Notice that each toolbar has a tiny downward-pointing arrow at its rightmost end. If you click on the arrow, an option displays for ìAdd or Remove Buttons.î Clicking on this option gives a list of buttons that you can toggle on or off. You can make changes more readily by choosing the Customize option at the bottom of the menu (see ìCreating a New Toolbar,î above).
If you have customized a built-in toolbar, and want to restore it to its original configuration, choose Toolbars from the View menu, or the toolbar shortcut menu. In the Toolbars box, select the toolbar you want to restore. Choose the Reset button. (If this button has changed to a Delete button, you have selected a custom toolbar, not a built-in toolbar. You cannot reset a custom toolbar .) Click the Close button.
Clicking on the tiny down arrow at the end of a toolbar, choosing ìAdd or Remove Buttons,î and then selecting ìReset Toolbarî is an even more straightforward method.
To delete a custom toolbar, choose Toolbars from the View menu. In the Toolbars box, select the custom toolbar you want to delete and choose the Delete button. When you're done, choose the Close button.
It is sometimes difficult to get the desired results when printing your spreadsheet. The following sections address some options available for addressing printing problems.
Many print options can be accessed from the Page Setup command in the File menu. Inside the Page Setup dialog box, you can make changes to margins, page settings, header and footers, and sheet settings by selecting the appropriate tab.Choosing the Page tab allows you to select the page orientation you want to use--portrait or landscape. You can also determine whether or not you would like to change the scale of your page to have it print out larger or smaller than normal. An option called Fit to allows you to determine how many pages you want this sheet to use, and will automatically scale it down to fit on the specified number of pages. The Paper size field allows you to specify the paper size you wish to use. Print quality allows you to choose the dots per inch (if using a dot matrix printer). First page number will allow you to indicate an initial page number other than one.
To change the left, right, top, and/or bottom margins, select the Margins tab. This box also allows you to determine the amount of blank space between the edge of the paper and your headers and footers. You set this in the From Edge fields. The Center on Page fields are very nice options, especially if your sheet doesn't totally fill a page. You can choose to center the sheet contents horizontally and/or vertically on the page.
The Header/Footer tab box allows you to make a header and/or footer for your document. You can select from preset headers or footers by scrolling through the options in the Header or Footer boxes. Or you can create a Custom Header or Custom Footer . To do this click on the Header/Footer tab, then choose the button Custom Header... or Custom Footer... . From here you can enter text in the desired section (left, center or right). There are also buttons located above these fields that allow you to: change the font, enter the page number, total pages, date, time, file name or sheet name.
Finally, the Sheet tab provides options to set your print area or print titles. The Print area field allows you to select an area in the current sheet to print. You can click in this box and then drag in your worksheet to select the desired range. Click on the tool button on the far right of this box to minimize the dialogue box. This allows you greater ease in selecting the desired range of your sheet that you wish to print. The Print titles fields allow you to define text in selected rows and columns of your worksheet as titles to be printed on every page. The text selected can include any number of adjacent rows or columns, or a combination. To set titles for columns, click in the Columns to repeat at left box, and then select cells in the rows you want to use for column titles. If you leave the Columns to repeat box blank, Excel will not set print titles for columns. To set titles for rows, click in the Rows to repeat at top box, and select the cells you want to use for row titles. If you leave this box blank, Excel will not set print titles for rows. To remove print titles, make sure that the Rows to repeat at top and Columns to repeat at left boxes are both empty. You can also determine whether or not you wish to print Gridlines, Comments, Draft quality , Black and white cells, and Row & column headings from this dialog box.
You can use the Page Break command in the Insert menu to set a manual page break. Manual page breaks you set override Excel's automatic page breaks. Manual page breaks appear on the screen as bold dashed lines and are darker than automatic page breaks. Both horizontal and vertical page breaks may be set. To set a horizontal page break only, select the row below the gridline where you want the page to end. From the Insert menu, choose Page Break . To set a vertical page break only, select the column to the right of the gridline where you want the page to end. From the Insert menu, choose Page Break . To remove a manual page break, select any cell directly below or to the right of the manual page break. Page Break changes to Remove Page Break on the Insert menu. Choose Remove Page Break . To remove all page breaks, select the entire worksheet and choose Remove Page Break .