|
EXCEL |
Please email questions or
|
EXIT |
Pressing [Tab] in Excel 2000 doesn't indent text in a cell; it simply moves the cell pointer to the next cell. Beginning Excel users might hard-code the indent by pressing the spacebar several times before entering text into a cell. But this approach is fraught with problems.
To indent text in a spreadsheet cell, use the Formatting toolbar's Increase Indent button. Enter your text without any lead spaces, select the cells whose entries you want to indent, and click the Increase Indent button. Each time you click that button, Excel nudges the selected text to the right. If you go too far, you can click the Decrease Indent button to nudge the text back toward the left.
If your text entry extends beyond the right edge of the cell, you'll probably want to change that cell's text alignment so that the text wraps within the cell. To change the text alignment, select the cell and go to Format | Cells | Alignment. In the Text Control section, select Wrap Text and click OK. When you do this, Excel indents all of the lines of text in the cell.
Create cross-sheet references
How to refer to cells within the same worksheet is one of the first lessons beginning Excel 2000 users learn. For instance, if you need to create a dynamic copy of the contents of any given source cell, type a plus sign [+] followed by the cell reference (e.g., to create a mirror copy of the contents in B10, you'd enter +B10 in any other cell).
What baffles some users, though, is how to create a mirror copy of a cell that resides in a different sheet within the same workbook. Once you master the syntax, creating a reference to a cell in another worksheet is easy. A cross-sheet reference consists of three pieces: the sheet name, an exclamation point [!], and the cell reference. So, if you want to display the contents of A4 from Sheet1 in a cell in Sheet3, type =Sheet1!A4 and press [Enter].
If you change the name of the worksheet (double-click the worksheet tab and type a new name), Excel automatically updates existing references to the old sheet name. To create new references to cells in the renamed sheet, you'll need to use the new name.
Avoid accidental overwriting when pasting
Have you ever accidentally pasted a block of cells over data when you really wanted to insert the copied cells instead? One way to avoid that problem is to insert a number of blank rows or columns before you start copying and pasting, but if you insert too few rows or columns, you may still destroy existing data. Excel 2000 provides a sure-fire way to avoid overwriting data when you copy and paste.
Follow these steps:
With this process, Excel automatically makes room for the data you're pasting and existing data is untouched.
Select cells via the keyboard
Here's a question for those of you who use the mouse to select cells for copying, cutting, and formatting: How long it does it take to move your hand from the keyboard to the mouse, click and drag to select a range, and return your hand to the keyboard? If you think it takes too long, consider using these keyboard shortcuts for selecting cells.
This is how these shortcuts work if you key them without first selecting a cell. If you select at least one cell and then press any of the key combinations above, Excel extends the existing selection.
Deconstruct range names
Naming a selected cell or block of cells is handy because then you don't have to remember the cell address when you want to refer to the range in a formula.
However, there are times when you may need to determine a range name's address. For those times, Excel offers several ways to determine the cells to which a range name refers.
Send worksheets via e-mail
Don't waste a lot of time by using the long way to send a worksheet by e-mail. Rather than saving and exiting the worksheet and then launching your e-mail client, take a shortcut. There are two ways to e-mail a spreadsheet without ever leaving Excel.
Go to File | Send To | Mail Recipient (As Attachment). As long as you have a default e-mail client installed on your computer, Excel will talk to that program, open a new message, and automatically attach your worksheet. All you have to do is address the mail, enter your message, and click Send.
Or you can click the Reviewing toolbar's Send To Mail Recipient button. To display that toolbar, go to View | Toolbars and choose Reviewing.
The Reviewing toolbar also provides shortcuts to several functions you'll probably use before you e-mail your Excel file to someone else, including tools for creating a new comment, moving to the previous or next comment, or showing all comments in the worksheet.
Copy Worksheet
To select an entire Excel worksheet, column widths and all, click the blank rectangle in the upper left corner between the cell "1" and the cell "A." This lets you copy and paste the worksheet into other worksheets or applications.
Keep a cleaner desktop when multitasking
Are you one of the many busy Excel 2000 users who frequently have two, three, or more workbooks open at once? If so, you know that kind of multitasking can create a lot of screen clutter. In the Windows taskbar, you'll have an icon for each open workbook. Within the Excel window, you can navigate between workbooks by pressing [Ctrl][F6] or by opening the Window menu and selecting a workbook from the list of open files.
Excel offers an option that can help you avoid screen clutter while keeping as many workbooks open as you like. To try it out, open three or four workbooks and go to Window | Hide. When you do, Excel erases all traces of the current workbook from your screen. The workbook is still open, but you can't see it.
To make a hidden workbook visible again, go to Window | Unhide, and you'll see a list of the hidden workbooks. Select the workbook name that you want to display and click OK.
Don't worry about forgetting that you have a workbook open. If you close Excel while a workbook is hidden and you haven't saved your changes, Excel asks if you want to save the changes to any unsaved workbooks.
Calculate loan payments
Every Excel 2000 user knows you're supposed to be able to determine what the payment would be on a car loan. What frustrates many beginning Excel users is the perceived complexity of the financial functions. However, Excel's PMT function is easier to use than you think.
To try the PMT function, follow these instructions to determine the payment for a $10,000 car loan, at 14 percent interest, with a payment every month over five years.
In cell A3, enter =PMT(A2/B2,C2,D2). The result is an approximate monthly payment. Your exact payment will vary, depending on the way your lender calculates interest and whether the loan is due at the beginning or end of the month.
SIMPLIFY DATA ENTRY WITH EXCEL'S BUILT-IN FORM
If your spreadsheet has more columns than can fit on your computer screen, you can use Excel's built-in data form and spare yourself a lot of scrolling.
To use the data form, select any cell within the spreadsheet range and go to Data | Form. Select New to enter a new record, and Excel will display a blank form. Enter data in the first field, tab to the next, and continue to enter data in each field, tabbing between fields. Press [Enter] to go to the next blank record.
If there are calculations in your spreadsheet, Excel will display only the results on the form. This prevents users from inadvertently overwriting formulas. To delete a displayed record, click Delete. A warning box reminds you that the record will be permanently deleted after you click OK. Follow these steps to search for a record within the form:
If you enter search criteria in more than one field, Excel will find the next record that meets both criteria. The form doesn't support OR searches. Use the scroll bar to browse through the records.
CREATE A TEMPLATE
As with Word, you can create a template in Excel for frequently used worksheets. For example, suppose that you create a new worksheet each month to record the total number of volunteer hours worked in your organization.
Rather than re-create the worksheet, you can delete the text and numbers
entered for the month and save the format and formulas to a template.
Follow these steps:
Excel saves the template to the Templates folder with an .xlt file extension. To use the template, go to File | New and double-click the template's icon.
To make changes to your template, go to File | Open and select the Templates folder from the Look In drop-down list. Select and open your template, make your changes, and click Save. The next time you access your template from File | New, the document will include the updates you made to the template.
LOCK CELLS TO PREVENT OVERWRITING
You can prevent users from overwriting formulas or inadvertently changing labels or formats by protecting those cells. Follow these
steps:
Now users will be able to alter only the cells that are unlocked. If users attempt to alter any other cells, they will receive a message saying they must unprotect the cells to modify them.
In Step 3, you may wish to add a password to prevent unauthorized users from changing a cell's protection level, rather than accepting the default settings.
CREATE A CUSTOM AUTO FILL LIST
The Auto Fill feature can make it easy to enter any data series that frequently appears in your spreadsheets. All you need to do is add the series to Auto Fill's built-in lists.
For example, if you report most operations by sales region (e.g., North America, Southeast Asia, Europe, etc.), you can create a custom fill series for those regions. Follow these steps:
Users can now enter all regions by typing one region and using the Auto Fill feature. You can also use the Custom Lists dialog box to make entries to a new list. Follow these steps:
CHANGE DEFAULT WORKSHEET OPTIONS
By default, Excel includes three worksheets in a new workbook. But if most of your users rarely use more than one worksheet per workbook, you can prevent Excel from generating the two extra worksheets. Follow these steps:
The next time you create a new workbook, it will have only one worksheet. You can also increase the number of sheets per workbook. You can also use the Options dialog box to change the worksheet's default font. For example, to change the default font from Arial 10 to Times New Roman 12, follow these steps:
You must restart Excel for your font changes to take effect.
HIGHLIGHT URGENT ITEMS
Here's a tip for your Excel users who've always wanted to use the Wingdings font in a worksheet but couldn't think of a compelling reason to do so. Suppose they're responsible for maintaining the master to-do list for their team or department, and they e-mail everyone a copy on a weekly basis.
What can they do to make it easier for everyone to notice the most important items on the lists? If the worksheet contains a "severity" or "priority" column, they can make the most important items stand out by sorting them to the top of the list.
Or they can introduce a graphical element to their spreadsheet by flagging those urgent items. For example, the "lit fuse" icon you get when you format a capital M in the Wingdings font is a good choice.
To try out this trick, type M in a cell. With the cell selected, go to Format | Cells | Font. Choose Wingdings from the Font list, select red from the Color list, choose 18 from the Size list, and click OK. The result is an icon that's guaranteed to catch the reader's eye when you really want them to pay attention to that row.
CHECK FOR NUMBERS STORED IN A TEXT FORMAT
Excel can't perform calculations on cell values formatted as text, such
as number values that you've imported as text from other databases into
Excel. Excel 2002 includes a feature that alerts you to these cells and
optionally converts them to numbers.
To activate this feature, follow these steps:
Now, when Excel finds a number in a cell formatted as text, it will place a green error indicator in the upper left corner of the cell. To convert cells to a number format, select the cell, click the Error Checking Options button displayed to the left of it, and select Convert To Number.