Having Your Fill in Excel 2010
Now get this. If I type any day of the week in any cell and drag on the fill handle (it’s always there; you just may not have paid it any mind till now), this is what happens (Figure 2–33):
Figure 2—33. Cells-by dates Rather economical, isn’t it? I started here with Tuesday and dragged horizontally, and could have dragged as far as I wanted. (Yes, the column widths may need tweaking, but you know how to do that now.) And if I type any month and drag on the handle, I bet you know what’s going to happen (Figure 2–34):
Figure 2–34. 30-day guarantee…filling the months Moreover, I can do the same with three-letter day or month abbreviations.
Type Wed, drag the fill handle, and you get Thu, Fri, Sat, etc. Type Jul and Aug, Sept, Oct, etc. emerge.
These four Auto-fill routines—day, month, and respective three-letter abbreviations—are built into Excel. But you can construct your own lists, too, such that when you type any one of its names and drag the fill handle, all the other names streak onto the screen. How? Like this. Click the File tab, and then Options (in the left column), and click again on Advanced.
Scroll down the window—pretty far down, until you approach the end and see the Edit Custom Lists button (Figure 2–35):
Figure 2–35. Try this at home…where to start making your custom list Click and you’ll see (Figure 2–36):
Figure 2–36. Note the existing lists, supplied by Excel.
Click in the List entries area and type each name you want to appear in your list, and in the desired sequence, following each entry with Enter, as shown in Figure 2–37:
Figure 2–37. Where the list takes shape When you’re done, click Add, and your list is swung into the Custom Lists column, where it now shares a zip code with Excel’s built-in, default lists.
Then just type any one of your custom names in a cell, drag the fill handle, and your list plays down or across the range.
Just bear in mind that we haven’t exhausted all the possibilities for copying and moving data. That’s because a different kind of cell content—a cell reference—can also be copied or cut, with some new implications. That one is coming soon.
Now on to some concluding observations about basic data entry. Note that this expression: 123 Main Street is considered text.
ndeed, for starters, any data entry containing a non-numerical element, such as:
(212) 555-1212 or say, a social security number: 123-45-6789 is to be treated as text, as opposed to a number. You can’t add a social security number; and Excel won’t treat the number above as a case of subtraction, either. You’ll soon see why.
Now there’s one other basic data entry principle you’ll want to know that will at last shed some light on that pristine white cell you’ll always see topping a range selection. Again, if I go ahead and drag my mouse across a range of cells: There’s that ever-present white cell. And what purpose does that cell serve? It marks the cell that will receive the next bit of data you type.
elect a range, begin to type, and then press Enter. The entry ends up in the white cell.
And if you need any corroboration, select any range, observe the address of the white cell, and glance in turn at the name box.
ou’ll see that very address recorded in the box.
And the white-cell/range selection does something else. If you select any range and begin to type, the first entry stakes the white cell—and if you press Enter, the cell pointer plunges down one row, of course—but the blue range color remains in force.
ry this: select cells D12 through D21, type the number 51, and press Enter. This is what you’ll see (Figure 2–38):
Figure 2–38. Within range—data entry inside the selection Type a number in the current cell—D13—and press Enter, and the number is once again registered in its cell—and the pointer again descends one row, to D14. And so forth. But of course you’ll have a question about all this: Entering data in a cell and pressing Enter always does exactly what I’ve described above—even if you don’t select a range. So what are we gaining here? Here’s the answer: Select this range instead: D12:E21. Start typing and ress Enter. The data locks into D12 and proceeds to D13, etc. But when you reach cell D21—the last cell in the D column—and press Enter, this time the cell pointer won’t drop down to D22—it’ll pop up to E12 instead, which is after all the next cell in the selected range (Figure 2–39):
Figure 2–39. Knowing its place: the white cell remains within the range And when you reach cell E21—the last cell in the range—and enter data there and press Enter, you’ll be taken back to D12.
This, then, is the data-entry advantage of selecting a range, if you need to: the range selection confines your data entry to precisely that area of the spreadsheet and nowhere else. But note, however, that if you follow up each data entry in the range by carrying out some of the other navigational moves instead of Enter, such as pressing the various keyboard arrows, or clicking your mouse, the blue range selection will turn off and the method we’ve just described will likewise be voided—unless you select the range again, of course. If you do want to conduct your data entry within a specified range, these keystrokes work:
• Enter—takes you down one row within the range.
• Shift-Enter—takes you up one row within the range.
• Tab—takes you one cell to the right in the range, or back to the first column and down one row if you’re already in the last column of a range.
• Shift-Tab—takes you one column to the left in the range, or one row up and into the last column if you’re already in the first column of the range.
That concludes our discussion of the basics of data entry—but not to worry; we need to return to the subject. There’s all that formatting to do, after all! But in any case, once you nail down the basics we still need to remind ourselves that no one’s perfect, and in the course of imparting their data to worksheets users make mistakes, change their minds, and have to enter new numbers as events warrant. So once the data is squirreled into their respective cells, we still need to ask: how do you edit all this?