lets you format your data in just the way you need. Formatting options are on the Home Ribbon, in the Number category.
Figure 1-12 shows how formatting helps in the readability and understanding of a worksheet. Cell B1 has a monetary amount and is formatted with the Accounting style. Cell B2 is formatted as a percentage. The actual value in cell B2 is .05. Cell B7 is formatted as currency. The currency format displays a negative value in parentheses. This is just one of the formatting options for currency. Chapter 5 explains further about formatting currency.
Figure 1-12: Formatting data.
Besides selecting formatting on the Home Ribbon, you can use the familiar (in previous versions) Format Cells dialog box. This is the place to go for all your formatting needs beyond what’s available on the toolbar. You can even create custom formats. You can display the Format Cells dialog box two ways:
✔ On the Home Ribbon, click the drop-down list in the Number category and then click More Number Formats.
✔ Right-click any cell and select Format Cells from the pop-up menu.
Figure 1-13 shows the Format Cells dialog box. So many settings are there, it can make your head spin! I discuss this dialog box and formatting more extensively in Chapter 5.
Figure 1-13: Using the Format Cells dialog box for advanced formatting options.
Excel is complex; you can’t deny that. And lucky for all of us, help is just a key press away. Yes, literally one key press – just press the F1 key. Try it now.
This starts the Help system. From there you can search on a keyword or browse through the Help table of contents. Later on, when you are working with Excel functions, you can get help on specific functions directly by clicking the Help on This Function link in the Insert Function dialog box. Chapter 2 covers the Insert Function dialog box in detail.
Gaining the Upper Hand on Formulas
Okay, time to get to the nitty-gritty of what Excel is all about. Sure, you can just enter data and leave it as is, and even generate some pretty charts from it. But getting answers from your data, or creating a summary of your data, or applying what-if tests – all of this takes formulas.
To be specific, a formula in Excel calculates something or returns some result based on data in the worksheet. A formula is placed in cells and must start with an equal sign (=) to tell Excel that it is a formula and not data. Sounds simple, and it is.
Look at some very basic formulas. Table 1-1 shows a few formulas and tells you what they do.
Table 1-1 Basic Formulas
Ready to enter your first formula? Make sure Excel is running and a worksheet is in front of you, and then follow these steps:
1. Click an empty cell.
2. Type = 10 + 10.
3. Press Enter.
That was easy, wasn’t it? You should see the result of the formula – the number 20.
Try another. This time you create a formula that adds the value of two cells:
1. Click any cell.
2. Type any number.
3. Click another cell.
4. Type another number.
5. Click a third cell.
This cell will contain the formula.
6. Type =.
7. Click the first cell.
This is an important point in the creation of the formula. The formula is being written by both your keyboard entry and your clicks of the mouse. The formula should look about half complete, with an equal sign immediately followed by the address of the cell you just clicked. Figure 1-14 shows what this looks like. In the example, the value 15 has been entered into cell B3 and the value 35 into cell B6. The formula was started in cell E3. Cell E3 so far has =B3 in it.
8. Type +.
9. Click the cell that has the second entered value.
In this example, this is cell B6. The formula in cell E3 now looks like this: =B3 + B6. You can see this in Figure 1-15.
10. Press Enter.
This ends the entry of the function. All done! Congratulations!
Figure 1-14: Entering a formula that references cells.
Figure 1-15: Completing the formula.
Figure 1-16 shows how the example ended up. Cell E3 displays the result of the calculation. Also notice that the Formula Bar displays the content of cell E3, which really is the formula.
Figure 1-16: A finished formula.
References abound in Excel formulas. You can reference cells. You can reference ranges. You can reference cells and ranges on other worksheets. You can reference cells and ranges in other workbooks. Formulas and functions are at their most useful when you’re using references, so you need to understand them.
And if that isn’t enough to stir the pot, you can use three types of cell references: relative, absolute, and mixed. Okay, one step at a time here. Try a formula that uses a range.
Formulas that use ranges often have a function in the formula, so use the SUM function here:
1. Enter
