Page Contents |
MS Office Contents | Home | Excel Basics, Page 2 |
Worksheet BasicsAn electronic spreadsheet, at a very basic level, is simply a collection of empty cells in which you can record and keep track of almost any kind of data, from lists of names and addresses to budgets, financial predictions, and market trends. Electronic spreadsheets in the Windows environment also make it easy to produce complex reports that look professional and can include graphs, 3-D charts, graphics, clip art, and notes. A Microsoft Excel spreadsheet is called a worksheet and consists of a single 'page' that is 256 columns wide by 65,536 rows long (compared to 16,384 rows in the previous version), for a total of 16,777,216 empty cells just waiting to be filled! A workbook is a collection of worksheets that are 'bound' together and are stored in the same file, extremely convenient if you keep records on several separate but related subjects. For example, you can use a separate sheet to record sales, expenses, and other information for each month of the year AND have additional sheets for quarterly and annual summaries, all stored together in a single file. When you create a new workbook it includes 3 worksheets. You can change the default number from one sheet to as many as your project dictates and your computer's memory will allow (see Customizing Excel).
Moving Around in Excel 97From The Keyboard:Use the following keystrokes to quickly move around in a worksheet. NOTE: If you have trouble with these keyboard commands, see the article on Customizing Excel (Transition tab) to make sure the program isn't set up to act like Lotus 1-2-3! If you still have trouble, try turning off NumLock.
With the Mouse:Scrollbars: Click on the up, down, right, or left scrollbar arrows to scroll one row or column at a time. Drag the vertical or horizontal scroll box to move greater distances up or down, left, or right. New In Excel 97:When you drag the vertical scroll box, the row number is displayed on a pop-up tag. If you want to move from Row 78 to Row 19, for example, drag the scroll box until you see the Row 19 tag, then release the mouse button. Row 19 will be at the top of the screen.
Scroll Boxes: The scroll boxes in Excel 97 are slightly different from those in previous versions. Instead of being a standard size, the scroll box changes size, with the size of the box representing the percentage of the document that is currently visible in the display window. If the scroll box is about 80% of the size of the scroll bar, it means that you're seeing roughly 80% of the spreadsheet in the window (cells that don't contain data and never have don't count!). When the scroll box is very small, you're only seeing a small percentage of the file. In a spreadsheet with 1,000 rows of data, the vertical scroll box will be quite small: you're only seeing about 32 rows out of a possible 1,000 - a small fraction of the total, which is reflected in the scroll box size. Note: Scrolling changes your view of a document, but it doesn't change the location of the active cell. To change the location of the active cell, click the mouse pointer where you want to enter data. To the left of the first worksheet tab at the bottom of the screen is a set of 4 small buttons. Clicking on the inner buttons (with the left and right arrows) will scroll the display of worksheet tabs to the left or right, one tab at a time. The outer two buttons (with the left or right arrow and a vertical line) will move the display to the first (left arrow) or last (right arrow) worksheet tab.
WorksheetsSelecting A Worksheet:Click once on the tab at the bottom of a worksheet to make it the active worksheet. The active worksheet is the one on top and has a white tab, while the tabs of the inactive sheets are colored according to the color scheme you've chosen. Once you have clicked on a worksheet tab, you can work in it or review it. From the keyboard, use the [Ctrl + Page Down] keys to move to the next worksheet, or [Ctrl + Page Up] to move to the previous sheet. Adding A Worksheet:Click on Worksheet in the Insert menu. A new sheet will be inserted before (to the left of) the currently active sheet. Deleting A Worksheet:Click on the tab of the sheet you want to delete, then click on Delete Sheet in the Edit menu. The sheet will be permanently removed. You cannot use the Undo command to recover a sheet once you've deleted it. Changing The Label On A Worksheet Tab:When you open a new workbook or insert a sheet, the sheets are labeled Sheet1, Sheet2, ...etc. It's easy to change the labels on the tabs to something a bit more meaningful. Just double click on the worksheet tab and type a name that will identify the contents of the sheet. Sheet tab names can be up to 31 characters long and can include upper and lower case text, spaces, and numbers. Sheet names cannot be enclosed in square brackets, and cannot include the following characters:
If you try to type any of these characters in a sheet name, Excel 97 will simply refuse to insert them (no, your keyboard isn't broken!). Just remove the illegal characters and try again.
Moving Worksheets:To change the order in which sheets appear, click on the tab of the sheet you want to move, but don't release the mouse button. The pointer will change to an arrow with a little piece of paper attached to it. As you start to move the mouse, you'll notice a small black triangle just above the worksheet tabs. Move the pointer until the black triangle is where you want to move the sheet to, then release the mouse button. The sheet will be inserted wherever the black triangle was when you released the mouse button. Copying Worksheets:You can copy just the contents of a worksheet by clicking on the Select All button (the empty button above the row 1 header and to the left of the column A header (see illustration). This will select the entire sheet. With the mouse pointer inside the highlighted area, click on the right button to display the context menu, then select Copy. This method will copy the contents of the cells, which can then be pasted in a new location. It will not duplicate column widths that have been changed in the original worksheet or most other formatting. See the Help article below for how to duplicate a worksheet, including column and row sizes. "Make a Duplicate Worksheet with the Ctrl Key" To copy only part of a worksheet, click on the top left cell in the range you want to copy and drag the pointer to the last cell at the lower right of the range. With the mouse pointer inside the highlighted area, click the right mouse button to display the context menu, then select Copy. Keyboard Shortcut: If the range to be copied is a rectangle or square, select the cells by holding down the [Shift] key and using the arrows keys, then press [Ctrl + C] to copy the selected area.
Pasting Copied Sheets Or RangesTo paste a whole sheet: Click on the tab of the sheet where you want to paste the copied sheet, then click on cell A1. With the mouse pointer inside cell A1, click the right mouse button to display the context menu, then left-click on the Paste command. To paste a range of cells: Click on the cell where you want to place the upper left cell of the copied range. With the mouse pointer in the cell, right-click to display the context menu, then left-click on Paste. Keyboard Shortcut: Click on the cell into which you want to insert the copied data (cell A1 to paste a whole worksheet, or the cell where you want to locate the top left corner of the copied range) and press [Ctrl + V]. If you need to move data from one place to another on a sheet, use Cut and Paste:
Entering & Editing DataWhen you enter data in a cell in Excel 97, the data is displayed in the cell and in the formula bar. You can edit data in either place. To edit in the formula bar, simply click and edit as you would any other text. When you're finished, press [Enter] to keep the changes you made. To edit the data in the cell, double click in the cell to insert the cursor, then edit the entry. In the picture at the right, the displays in the formula bar and the cell are different. When you enter a formula in a cell then press the [Enter] Key, the result of the formula is displayed in the cell. The formula bar displays the formula itself. If you double click in the cell, the formula will be displayed there too, so you can edit it in either place. The cursor location, C4, is shown in the Name Box. Notice also that the row 4 and column C headings are bold and have a darker border around them for another way of seeing the cell address at the cursor location.
Entering Data in a Single Cell:Click on the cell and type the entry. As you type, the entry will appear both in the cell and in the formula bar just above the worksheet. If you make an error, click in the formula bar and correct it there, or double-click in the cell and make your corrections. Press the [Enter] key to move the active cell outline/selection to the next cell down in the same column, or use the right or left arrow keys to move the selection one column in either direction. New in Excel 97If you enter data straight across a range of cells then press the [Enter] key, the cursor will return to the first cell in the next row. For example, if you've just finished entering a record in cell Q27 and press [Enter], the active cell will become A28. Data Entry Form: Do you know about Excel's automatic data entry form? It's been there all along and can make entering a large amount of data a lot easier. First you need to set up your spreadsheet with column headings. If you're creating a customer list, for example, your column headings might include LastName, FirstName, Title, Company, Address1, Address2, etc. Once you've set up the headings, select Form... from the Data menu. The Office Assistant might ask you to specify which row you want to use for headers, and then will create a data entry form using the headers in your sheet. It's significantly easier to enter data this way than it is to move around in a large spreadsheet. NOTE: To change the direction that the selection moves when you press [Enter], click on Options in the Tools menu, and select the Edit tab. Click on the checkbox in front of Move Selection After Enter to insert a check if there isn't already one there, then select a direction from the dropdown list.
Cell RangesA range is a block of cells that you select by clicking and dragging (or by holding down the [Shift] key and using the arrow keys). To specify a range like the one shown above, click on the top left cell (B2) and hold down the left mouse button while you drag through the bottom right cell (E6). Notice that the whole range is outlined and everything but B2 is highlighted. To simplify data entry in a range, DO NOT PRESS ANY OF THE ARROW KEYS, the Backspace key or the Delete key. Doing so will remove the highlight. Just begin typing. Your first entry will go into B2. Press [Enter] to move to B3, then B4, etc. When you reach B6, type in your data, and press [Enter], the selection moves to cell C2; from C6 it will move to D2, and so forth. To move backward through the range, use [Shift + Enter] (but don't use the arrow keys or the highlight will vanish). To enter all of the row 2 data before moving to row 3, use [Tab] instead of [Enter]. The active cell will move from B2 to C2, D2, and E2, then to B3, etc. Use [Shift + Tab] to move backward through the range. You can use the same technique to enter data into ranges with non-adjacent cells. A non-adjacent range contains cells that are not necessarily all located next to each other. To select cells, hold down the [Ctrl] key as you click on or drag through non-adjacent cells. Hot Tip: Save yourself some typing when you have to enter the same thing in a number of cells: first, select all of the cells where you plan to enter identical data (a formula to total a number of rows or columns is one example). Without using any of the arrow keys or the [Backspace] key (they'll remove the highlighting), enter the data into the active cell and press [Ctrl + Enter]. Whatever you entered into the cell will be copied into all the other cells in the range.
|