Numbers are (a little) Different in Excel 2010
Now let’s return to the business of data entry proper, because to date we’ve omitted a rather essential form of data from the discussion—numbers. The mechanics of numerical data entry are identical to those governing text: Just type the number, and then execute one of those moves away from the cell.
Don’t worry about typing commas and dollar signs for now; just type the number. Note by way of introduction that if you type: 56.2 for example, the number will appear just as you’ve typed it. But if you enter: 56.00 you’ll see 56 only, because by default Excel sheds those meaningless zeros—until you format it differently (but if you reformat the number, remember it really remains nothing but 56. A glance at the formula bar will provide that confirmation). Note in addition that a number less than 1, say .78, will appear by default as: 0.78 that leading zero can be removed by a customized format, about which we’ll have more to say later.
And you’ll soon discover a couple of other issues that apply to numbers only. For one thing, and unlike with text entry, Excel will never permit a number to advance into an adjoining column, even if that column is vacant. The reasons are fairly clear. Because our numbers move right-to-left, allowing a number to break into the column to its right would hopelessly misalign a set of numbers streaming down one column. If, for example, a 12–digit number in A4 were to take over some of the space in cell B4 because of its extreme width, and a merely 3-digit number were to populate A5 just beneath it, the “ones” column in the two numbers would be out of whack. The “ones” in A4 would be shunted into B4, even as the “ones” in A5 would remain in A5.
Moreover, if that long number in A4 were allowed to ooze into B4—as happens with text—and a number were then entered directly into B4, you’d find it difficult to know A4’s true value—because some of its columns would be clipped from view, again as with text. The whole thing is just too messy, and as a result, numbers do not enter adjacent columns.
So what does happen, then? That depends. If you type a 12–digit number in a column with the startoff, default column width in effect, say 123456789123 You’ll see this instead: 1.23457E+11 If you haven’t seen a number like that lately, it means you graduated high school a long time ago.
The long number is rewritten in scientific notation, a kind of shorthand that hems the number into its existing column width, thus keeping in it view, though the number as you typed it is displayed that way in the formula bar. But you can reformat the number into the original value you typed—as you almost surely will—and when you do reformat it, Excel then accompanies the process with an Autofit on its own to display the number in its entirety, as you originally entered it (and we’ll see how to apply this and various other number formats in Chapter 4).
f, on the other hand, you type a long number and then for whatever reason narrow its column substantially, you’ll see this: ### —another classic spreadsheet indicator. Seeing those pound (or hash, or number sign, if you live in the UK and other distant locales) signs in a cell means the cell contains a number that is too long for its current width—and you see pound signs there instead of the scientific notation when you actively narrow the cell. The solution: do an Autofit.
elocating the Data: Copying and Moving:
Now here’s another indispensible form of data entry you need to know about, though it isn’t generally characterized in those terms—copying and moving data.
After all, when you copy data you’re reproducing, or entering, more of it, and Excel’s copying options are several, and don’t always resemble the sorts of things you’d do in Word. Let’s explore some of the permutations.
We’ll start of course with the basics. Say I want to copy values or text—and let’s begin with one cell’s worth of data:
• Click the cell whose data you want to copy.
• Click the Copy button in the Clipboard group in the Home tab (or its timehonored keyboard equivalent, Ctrl-C. Ignore the button’s drop-down arrow for now). Note how the cell border is suddenly enlivened by what are called marching ants (I’m not kidding), as seen in Figure 2–27:
Figure 2–27. Text, as copied
• Click the destination cell—the cell to which you want to copy.
• Click the Paste button to the left of the Copy button in the Clipboard group, or Ctrl-V (yes, there are other paste options, but we’re in introductory mode). Or— and this option is exceedingly easy to overlook—press Enter. The item is duplicated.
Note as well, however, that even though you’ve done your job, the marching ants continue to troop around the cell border—at least if you click Copy or press Ctrl-V (but not if you press Enter). That’s not a cue for an exterminator, but rather an indicator to the effect that you can paste the cell again to other cells, with repeated pastes. When you want to turn the process off, just click the Esc key, and the ants recede. Note that if you paste with Enter, the ants immediately disappear. How about copying a range of cells? This time:
1. Select the range and release the mouse (or keys, if you’re pressing).
2. Click Copy.
3. Click the first of the destination cells only. That is, if for example you want to copy cells H2:H5 to say, J6:J9, click J6 only.
xcel is smart enough to know that if you’re copying four cells you’re pasting four, and it merely needs you to tell it where the new destination starts.
4. Execute one of the Paste options described above.
Note that when you paste a copied range Excel preserves the orientation of the cells in question.
That is, if you copy a column of cells, Paste will always paste these in columnar fashion, and a copied row will always paste as a row (yes, you can paste a column into a row orientation and vice versa if you want to, but that’s for a bit later).
And what of moving data? That’s what we really mean by cutting and pasting, and the process is rather easy:
1. Select the cell or cells you want to move.
2. Click the Cut button directly above the Copy button, or its equivalent, Ctrl-X.
The marching ants do their thing, but note that the cell contents don’t disappear, even though you’ve apparently cut them .
3. Again, select the first destination cell.
4. Click Paste, or one of its equivalents, including Enter. You’re done. (Note: moving a cell containing a formula will not change any of its cell references, a point to remember when we discuss relative cell addressing in a later chapter.) Note that with Cut and Paste the marching ants retreat after one Paste. That’s because, well, what’s the alternative? The data have been cut and moved elsewhere; granting users another Paste means they’d want to move the data again immediately—a not terribly likely prospect.
There’s an alternative way to move (and copy) data in cells, though this one requires a bit of delicacy. Select the range you want to move and rest your mouse anywhere along the range’s perimeter, until you see a pair of double-sided arrows. Then click and drag the range to its new destination, and release the mouse. If you do the same thing while holding the Ctrl key down, you can copy the range.
These techniques are fairly easy to mess up, though; releasing the mouse too soon will let the data down in the wrong place.
Now there’s still another way to copy cell contents, this one also mouse-powered, and it works like this: We’ll start with a number in one cell. Click the cell and slide your mouse atop the lower-right corner of the cell pointer border, where you’ll notice a small square lodged in the corner, like a dimple. When you roll the mouse over that little shape, your indicator should remake itself into a slender black cross, as shown in Figure 2–28, not to be confused with the black double-arrow variety you generate when you widen columns:
Figure 2–28. The fill handle When that cross appears, click and hold the mouse button down. Drag as far as you wish, either across a row or down a column, depending on the direction in which you want to copy. Release the mouse when you’ve dragged the desired distance; you’ll see the original number has been copied down or across the range you’ve dragged. You’ll also doubtless take note of the caption that escorts you down/across the range as you drag; it tells you what value will appear in each cell in the copied-to, destination range as you drag. But because, in this case, you’re simply copying the same number to each cell again and again, you may think that the caption tells you something you already know—and you’re right—this time.
We’ve just demonstrated an application of what’s called Auto-fill, a device that can serve you most productively once you learn its capabilities. And that square dimple we dragged is called the fill handle, and you’ll want to handle it with care.
And you’ll notice something else. When you’ve completed dragging, Excel caps the process by appending to the lower-right corner of the new, copied range what’s called an Auto Fill Options button.
Click it and four selections place themselves at your service. We’ll discuss the first two here, because the latter two carry out formatting options, which aren’t our concern here. Take a look at Figure 2–29:
The default selection, Copy Cells, really characterizes what we’ve just done. But assuming we’ve copied the number 3 as per the screen shot, from the D17 source cell down through D30, look then what happens when we click selection number two, Fill Series, as shown in Figure 2–30:
Figure 2–30. A Fill Series I’ll bet that one got you to look up and stop texting. What Excel has done here is add an increment of one to each of the cells in the range to which we copied the original value, 3. That’s a rather cool capability (and it hasn’t debuted in Excel 2010, either—this option goes way back), and we’re just getting started with it.
ow try this: type the numbers 3 and 5 in cells G3 and G4. Then select both cells and release the mouse. Next, Click and drag the fill handle down the G column to G10. You should see this (Figure 2– 31):
Figure 2–31. Getting a handle on the process: A fill series displaying an interval of 2 Here, Excel works with two starting cells, containing the numbers 3 and 5; these alert the worksheet about the interval that will pump up all the numbers in the range.
s per the Auto Options button command, we’ve generated what’s called a Fill Series, and we could have dragged the fill handle down thousands of cells had we wished, with each successive cell displaying a value 2 higher than the preceding one.
And if you start with 5 and 3 instead of 3 and 5 and drag the handle, the numbers will descend in decrements of two, e.g., 5, 3, 1, -1, -3, etc. Nor is Excel intimated by exotic intervals: if you enter starting numbers of say 1.36 and 2.43, Auto-fill is perfectly happy to roll out 3.5, 4.57, 5.64, and the like. Just keep this caution in mind: in order to carry out an Auto-fill properly and avoid a common mistake, don’t, for example, type 3 and 5 and return to the 3 and begin dragging. Rather, you must select 3 and 5, release the mouse, and then return to that selection and drag the fill handle. That is, you must see this, shown in Figure 2–32, before you start the fill process:
Figure 2–32. Learning to let go: release the mouse before you drag here.