|
|
|
|
|
|
|
|
|
|
|
| Troubleshooting Microsoft® Excel 2002 |
|
|
|
|||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||
Chapter : Entering Data
- When I type in the active cell, nothing happens
- Text that I typed in a cell looks cut off by adjoining cells
- I'm tired of entering the same series of labels in my worksheets
- I can't get my data validation rules to work
- The symbol I want to insert doesn't seem to be available
Entering Data
Click to view graphic
When I type in the active cell, nothing happens
Source of the problem
The operative word in this situation is "active." Even people with a reasonable amount of Microsoft Excel experience will make the mistake of attempting to enter data into a cell other than the active cell. The active cell is the cell that has a thick, black border around it, and its address appears in the Name box to the left of the Formula bar. It's very easy to start typing and wonder why the cell you think should change doesn't. Then you discover that another cell was active when you started to type and that content is being added to a cell other than the one you wanted to edit.Why might you become confused as to which cell is active? If you've applied thick, black borders to any cells, if you've changed your cell shading to black (in which case the active cell will have a thick, white border), or if you're concentrating on the particular cell where you intend to make an entry and forget to click it (leaving a previously clicked cell as the active cell), you might have trouble determining which cell is active. The latter situation, forgetting to click the right cell, results in an additional problem—whatever you type ends up in the wrong cell, and if the active cell contains content when you started to type, you overwrite it when you type the new content! To determine which cell is active, you need to take only a few steps.
How to fix it
- Look in the Name Box and note the address of the active cell.
- If the address appearing in the Name Box does not represent the cell in which you want to work, press Esc to undo any changes you might have accidentally made to existing material in that cell.
- Click the cell in which you want to work and then do what you need to do.
Click to view graphic
Watch that mouse
Pressing Enter or Tab after working in a cell is a good habit to get into. Using the mouse button to click another cell after making or modifying an entry confirms cell entries in a manner similar to pressing Enter or Tab, but be aware that you run the risk of leaving a cell active when you're really finished working with it, thereby making it vulnerable to unintended edits when you're trying to work in another cell. If you use the mouse to scroll to another part of your worksheet and then forget to click another cell to make it active, you might overwrite material in the cell in which you were just working without knowing it!
If you get into the habit of clicking another cell—or, better yet, pressing Enter or Tab—immediately after you add or modify a cell entry, you'll be more confident that your entries will stay intact—in the cells where you want them to be!
Text that I typed in a cell looks cut off by adjoining cells
Source of the problem
You've typed your text in a cell, and you've pressed Enter or Tab. You look at the cell you just finished editing, and the end of the word or phrase is missing—cut off at the right end of the cell. What's going on? If the cell to the right of the one you just typed in has content, there's your culprit—but this only applies when there is text in the active cell. If the active cell contains only numeric content, the content of the cell to the right is immaterial. If you type text into a cell and that text overruns that cell's column width, the text that doesn't fit will be obscured, or truncated, by the cell to the right if (and only if) that cell has content.
Click to view graphic
If you type purely numeric content into a cell and type more than will fit into the cell's current width, Excel either expands the column to accommodate the numeric entry or turns the numbers you typed into pound signs (########) to signify that there isn't room for the entire entry. This will happen whether or not there is content in the cell to the right. This prevents your thinking that the number in a cell is only 10000 when it's really 10000000. It could be very confusing if too many numbers in a cell were handled the way too much text is handled. Missing letters in a word draw immediate attention, and you notice what's missing. Cutting off a number could cause major confusion, because you wouldn't necessarily spot the omitted content.
When it comes to text, though, if the cell to the right is empty, a portion of the overflow text will appear to spill into that cell's space, though it won't actually fill the cell itself. (This is good for worksheet titles that normally appear in cell A1. Cell B1 is usually empty, so long titles just flow over any adjoining cells as needed.) Generally, however, you want cell content to fit in the cell it was intended for, and so you need to make your columns wide enough to accommodate a work-sheet's content. If you have already entered content and it's been truncated, you need to widen the column that contains the cell with the overrun text.
How to fix it
To widen a column to accommodate a particular entry, follow these steps:
- Hold the mouse pointer over the right seam of the heading of the column you want to widen. (Note that you don't have to actually select the column to widen it.) Here, Column D is being widened to reveal the entire entry in cell D4.
- When the pointer changes to a double-headed arrow, hold down the mouse button and drag the seam to the right.
- Release the mouse button when the column is wide enough to fit the entry.
Click to view graphic
To widen a column so that none of its entries is truncated by entries in an adjacent column, first point to the column heading's right seam. (You don't have to select a column to widen it.) When the pointer changes to a double-headed arrow, double-click the right seam of the column's heading to widen the column to fit its widest entry.
Don't widen, be happy
Just as it saves time and effort to format a word processing document after the entire document's typed (rather than stopping to format as you type), you can adopt a similarly relaxed attitude about your worksheet's appearance while you're in the process of building the worksheet's content. Wait until all of your content is entered, ignoring any text that doesn't quite fit in the cells, and then select the column or columns you want to adjust. Hold your mouse pointer on the seam between two of the selected columns, and when the pointer changes to a double-headed arrow, drag the seam to adjust the selected columns so that all of the selected columns are widened to a universal size. It's a good idea to use the widest entry in the selected columns as your guide, as shown in the sample.
Click to view graphic
Alternatively, you can select the entire worksheet, right-click any column's heading, and then click Column Width on the shortcut menu. In the Column Width dialog box, type a numeric column width that should apply to all of your worksheet's columns and then click OK. The number you type should equal the maximum number of characters you want to appear across the column width; for example, if you type 20, then the column width will be adjusted to accommodate 20 characters, including spaces. After you adjust the columns in the entire worksheet, you might need to adjust individual columns with entries that are larger or smaller than the global column width you set.
I'm tired of entering the same series of labels in my worksheets
Source of the problem
It can be a real pain in the neck to build worksheets that have content similar to other work-sheets—especially if you find yourself building these similar worksheets frequently. You know you've typed these exact words before—a list of locations, names, or products—and it aggravates you to know you're repeating your efforts and leaving yourself vulnerable to typos and other errors. Well, repeat yourself no more!You might be aware that Excel completes lists automatically. For example, if you enter the heading Quarter 1 in a cell and then use the fill handle to drag that entry through the adjoining cells, Excel fills those cells with Quarter 2, Quarter 3, and Quarter 4. If you keep going, Excel starts all over again with Quarter 1. You can also complete lists of months or days of the week this way.
Click to view graphic
But what if you need a list of items that Excel doesn't know the order of, such as the list of your company's remote offices (Philadelphia, New York, Atlanta, Phoenix, San Francisco, and so on) or a list of products you track every month in a sales worksheet (A578-3B, A578-4B, A578-7C, B345-4E)? Excel lets you create a custom list that you can flesh out simply by typing any item in the list in an individual cell. You can base a custom list on existing content (in a worksheet where you've already typed a whole series of entries) or build a custom list from scratch.
How to fix it
To create a custom list from existing content, follow these steps:
- Select the cells in your worksheet that contain the row or column labels that you want to turn into a custom list.
- On the Tools menu, click Options.
- In the Options dialog box, click the Custom Lists tab.
- Click the Import button to bring in the content of the cell range listed in the Import List From Cells box. (This is the range you selected in step 1.)
- If you need to change a list entry, click next to the entry in the List Entries box and then edit the entry's text as needed. You can also add items to the list this way or rearrange the order of the list.
- Click OK to close the dialog box.
Click to view graphic
To create a custom list from scratch, follow these steps:
- With any worksheet open, click Options on the Tools menu.
- In the Options dialog box, click the Custom Lists tab.
- With NEW LIST selected in the Custom Lists box, click in the List Entries box.
- Type the list, pressing Enter after each item.
- When your list is complete, click the Add button. Your list will appear in the box on the left side of the dialog box, with each item in the list separated by a comma.
- Click OK to close the dialog box.
Once you've created your custom list, you can use it in any new or existing worksheet. Enter any item from the list in a cell, use that cell's fill handle to drag through the adjoining cells (in either the row or column), and release the mouse button to enter the remaining list items. The items will appear in the adjoining cells in the order you entered them in the list. If you drag through more cells than there are items in the custom list, the series will begin again with the first of the list items.
It's also important to note that the series will always fill in in the order in which the items appeared when you imported or typed the list from scratch. For example, if your list contains five cities—New York, Philadelphia, Detroit, Milwaukee, and Seattle—and if you start typing with Detroit and use the fill handle, the next item in the list will be Milwaukee. You won't get to New York until the list begins again or you restart the list manually with that city.
I can't get my data validation rules to work
Source of the problem
Suppose your Excel database requires specific entries in some of the fields, such as complete department names in an employee list (to prevent the use of both "Mktg." and "Marketing" or "Acctg." and "Accounting," which can make filtering and sorting difficult) or only numeric content in an employee number field. To restrict the data that can be entered, you have set up rules, called data validation rules, but they aren't working! Now your database is at risk for inaccurate or inappropriate entries, especially if you're not the only one making entries in the worksheet. What happened?
Two possible scenarios that can prevent data validation rules from working are:
- The cell material wasn't typed directly into the cells, but was copied or cut and then pasted in them.
- You've made an entry in a cell for which no rules were established.
The solution to the first possibility is simple—don't allow anyone to paste content into cells in which data validation rules apply. For the latter possibility, you need to figure out which cells have rules applied to them and then check and correct the rules that are in place, if any corrections are needed.
How to fix it
To check for data validation rules, follow these steps:
- On the Edit menu, click Go To.
- In the Go To dialog box, click Special.
- In the Go To Special dialog box, click the Data Validation option and leave the All default setting in place.
- Click OK to have Excel select all cells with data validation rules applied to them.
Click to view graphic
Once you've determined which cells have rules applied to them, check the rules for omissions or errors by following these steps:
- With the cells with data validation rules still selected, click one of these cells and then click Go To on the Edit menu.
- Click Special, click the Data Validation option, and then click Same below it. Click OK to select the cells where the active cell's data validation rules are also applied.
- On the Data menu, click Validation.
- In the Data Validation dialog box, examine the Allow box and all other applicable settings in the Validation Criteria section to make sure the settings are correct.
Click to view graphic
Rules are meant to be broken...sort of
Data Validation rules control what you can enter into selected cells, and that's a great thing when you're trying to keep people from entering erroneous or inappropriate data in a worksheet. You might encounter a problem with creating lists of acceptable entries, however. When you select the Source cell range in the Data Validation dialog box, the cells you designate as containing the acceptable entries are not protected by the rule, even if they're within the range of cells to which the rule applies.
For example, if you're applying a rule to cells B1 through B25 and cells B1 through B6 are the Source range—meaning they contain the handful of entries that are acceptable for use in cells B1 through B25—the entries you make in cells B1 through B6 will (a) be allowed to violate the rule, and (b) become part of the list of acceptable entries for the range to which the rule is applied, no matter what you enter in those cells. Why is this? Search me. I don't imagine it was an intended part of the feature, because it can certainly blow holes in your validation rules. To eliminate this problem, make your Source range a block of cells that are away from the working part of your worksheet—squirrel them away in some obscure place in the workbook, where no one will possibly make any entries after the rule is created.
The symbol I want to insert doesn't seem to be available
Source of the problem
Symbols can be a very cool thing to add to your worksheets, and before Excel 2002 you couldn't add them to worksheets without pasting them from another application, such as Microsoft Word or Microsoft PowerPoint. How can symbols be useful in a worksheet environment? You can use them for decoration, information, or both.For decorative purposes, imagine you're tracking your stocks. Some are doing well, others aren't. You decide to keep the moneymakers and the losers in two separate columns or worksheets, just to keep them apart. To help you instantly differentiate the two groups, you can insert a picture of a bull or a bear (from the Animals font) into the worksheet. In a cell above the stocks that are doing well, insert the bull and above those that aren't doing so well, insert the bear. You can also access a variety of fun shapes and pictures, such as phones, geometric shapes, bombs (great for using with data that's surprising or potentially earth-shattering), and people.
Click to view graphic
For informative purposes, you can access foreign currency and mathematical symbols. Of course, once inserted, the symbols appear in a cell just like text or numbers—they're actually just the characters within a font other than one that typically offers letters of the alphabet or the Arabic numbers from 0 to 9. Mathematical symbols can't be used in formulas, as only the standard characters +, -, /, and * (addition, subtraction, division, and multiplication, respectively) will work in a formula or function.
Click to view graphic
So with symbols serving an essentially graphical role, what could go wrong? Well, if you try to use the Insert, Symbol command to access a list of symbols and special characters, but the one you want to use isn't there (or you can't find it), you're probably a little annoyed. Where did it go? Was it never there in the first place?
The key to finding the right symbol is knowing what kind of symbol it is (which font contains it) and making sure that the right subset (category, if you will) for the font in question is displayed. Your choices are Unicode and ASCII, and it's worth checking both subsets for any font you choose.
How to fix it
To make sure that you're looking in the right place, follow these steps to peruse the symbol libraries and check the subsets (categories) to make sure you're not limiting the listing and thus making it seem as though the symbol you want is unavailable:
- Choose Symbol from the Insert menu. The Symbol dialog box opens.
- Scroll through the Font list and look for the one that contains the symbol you wanted. If you have no idea which font it's in, poke around through the likely candidates. For example, if you're looking for an arrow, try Arrows1 or Arrows 2 (if you have that font) or Wingdings.
- To make sure you're seeing the entire group of symbols within a particular font library, check the Subset field—by default, it should be on Basic Latin, if this is the first time you've used the Insert Symbol command. Scroll through the list of subsets and see if you can find the symbol you need when you choose an alternative.
- When you've found the symbol you need, click once on it and click the Insert button.
- Click Close to close the dialog box and return to your worksheet. The symbol you inserted appears in the active cell.
Click to view graphic
Symbolically speaking
You can insert some symbols with keyboard shortcuts—for example, pressing Alt+168 on the numeric keypad will give you an upside-down question mark, useful if you're typing a question in Spanish (you'll use a "regular" question mark at the end of the question as well), or if you want to create a unique emoticon using the upside-down question mark as a nose. To see which keyboard shortcut to use for a symbol you want to insert quickly in the future, refer to the Symbol dialog box—at the bottom of the dialog box a Character Code designation appears for any symbol you select. To insert that symbol with the keyboard later, press the Alt key, and with that key held down, type the numeric keys, one at a time. The symbol will appear after you type the last number in the shortcut, and then you can release the Alt key.
Last Updated: April 7, 2002
| Top of Page |