Cell references explained in Excel 2010
Now that we’ve gotten this far, we need to remind ourselves of the obvious: Excel is all about doing something with the data once it’s been nestled into its cells—analyzing it, presenting it, and synergizing something new from all those static numbers and text entries; and that reminder takes us to a new domain of have-to-knows—the world of formulas and functions. It’s here, once you learn the fundamentals, that you begin to sense the nearly infinite potential of the application—starting of course with the how-tos for adding those rows and columns.
The very first thing you need to know about formulas is that by that term I’m referring to any expression you can write in a cell which conjures something new from the existing data—and it doesn’t have to work with numbers, either. Learning which, or how many, students in a lecture class have a last name starting with the letter L or how many major league baseball players were born in Nevada may not be what’s keeping you up at night, but if you need to know these things and the basic data are there, there’s a formula-based way to find out.
The second indispensible thing to know about formulas is that they always begin with the equal sign. This, then, is a formula: =3+5 And this isn’t: 3+5 That latter expression is pure text, and won’t “do” anything more than appear in the cell in which you inscribed it. The voice of experience is speaking: if you write a formula, no matter how ingenious and complex, and you leave out the equal sign, what you get is text.
And since I seem to have written a formula just a few lines ago, let’s take a second look at it while it’s on hand. Entering =3+5 in a cell will indeed yield the answer you’ve been looking for: 8, but we need to learn a bit more about the relationship between that answer and the formula that gave rise to it.
Go to any cell, say B6, and type =3+5, followed as usual by Enter or any other navigational optionl such as the checkmark.
First, of course, the number 8 stations itself in the cell. Then click back on B6, and take a look at the Formula Bar, shown in Figure 3–1:
Figure 3–1. Comparing the Formula Bar and its current cell Now we know why the Formula Bar is so named. It opens a window on what is really going on in the cell beneath the surface, and in this case of course what you see in the bar doesn’t correspond to what you see in the cell. If you print the spreadsheet you’ll see the number 8 (by default, anyway), and that’s nearly always want you want to see. But you also may need to know that the number was brought about by a formula, and not a simple act of data entry. To glean that bit of information, click on the cell and view the Formula Bar.
Now, nothing at all prevents you from writing a more protracted formula, such as =3+5+6+78+91+5+12+45+1 Press Enter and you’ll get your answer. Remember, after all—you have 32,767 characters per cell to work with. But for a variety of reasons, spreadsheet users don’t like to enter numbers directly into a formula; it’s inefficient and a pain to edit, and if you content yourself with this approach you’re treating Excel as little more than a PC-based calculator. The far superior way of proceeding is to enter the data you’re going to work with in cells, and to work with cell references.
Cell references explained What’s a cell reference? It’s an expression that, as its name suggests, refers to, or returns, the contents of another cell. Thus if you type the number 45 in cell C6 and proceed to type =C6 in cell C7, that latter cell will display 45 onscreen. If I type Excel in C6, cell C7 will now naturally display Excel. What it won’t display onscreen is =C6, even though that’s what you’ve actually typed in the cell.
Now back to our addition example. If I type the same numbers I added above in separate cells, as in Figure 3–2:
Figure 3–2. Each value assigned its own cell and then subject these to a formula instead, we’ll achieve the same result —246—but we’ll also realize some very important spreadsheet advantages (note that the numbers don’t have to be lined up in one column or row in order to be able to add them; they can be strewn anywhere on the worksheet, but we’re starting simple).
So let’s try to add those numbers. Say you type the numbers above in cells B6 through B14. Then do the following:
• Click in cell B15 and type the equal sign =
• Click on cell B6 and type +. You should see the result shown in Figure 3–3:
Figure 3–3. Plan B: Adding the values with cell references instead:
• Click on each of the remaining cells to be added, followed in each case by +.
• When you’re finished, tap Enter. Your formula: =B6+B7+B8+B9+B10+B11+B12+B13+B14
Your answer should once again come to 246—but once you achieve that result we need to review the process more closely, because you’re asking the obvious rhetorical question: That’s a lot of clicking, isn’t it? And what if we wanted to add 90,000 numbers instead of 9? Stay tuned…but back to the formula itself.
First, and as stressed earlier, the formula must begin with =. We then clicked on each cell to be added, following each click with +, simply because we’re adding the numbers. Had we wanted to subtract some or all of these we would have typed a minus sign instead.
Once we’re satisfied we’ve clicked on the right numbers, we press Enter—or Shift-Enter, Tab, or Shift-Tab, or Ctrl-Enter (or click the checkmark, which in this case actually advances the cell pointer down). And that’s how it works. Type =, click each of the cells you want to include in the calculation, followed in each case by a mathematical operator such as + or – (more on this soon), and wind it all up by tapping Enter, or one of the other possibilities cited above. And remember that you can click on cells dispersed anywhere across the worksheet.
And if we’ve realized our result and then discover we’d made an error in data entry, say, the number in cell B7 is really 15, not 5—all we need do is type the corrected number in that cell, and our formula in cell B15 automatically recalculates to read 256. That’s because formulas don’t work with particular values as such—rather, they work with whatever values have been entered in the cells to which they refer. And this capacity of spreadsheets – —their ability to recalculate a change in data entry without having to redesign the formula which does the calculation – —may stand as their single greatest contribution to Western Civilization.
Now time for a couple of quick but necessary digressions. First, Table 3–1 shows a list of the basic mathematical operators you can apply to formulas:
Table 3–1 List of Operators
