- name the sheet
- understand references
- enter information
- activate a cell
- enter row headings
- enter column headings
- make a column wider
- enter values
- calculate a difference
- calculate a percent diff
- sum some values
- calculate net income
- copy formulas
- copy and paste
- use the fill handle
- change a value
- extra bits
This chapter is from the book
This chapter is from the book
This chapter is from the book
extra bits
name the sheet p. 24
-
As you'll see in Chapter 8, you can instruct Excel to automatically display a sheet name in a printed report's header or footer. That's a good reason to give a sheet an appropriate name.
activate a cell p. 27
-
When you use the point-and-click method for activating a cell, you must click. If you don't click, the cell pointer won't move and the cell you're pointing to won't be activated.
enter row headings p. 28
- When you enter text in a cell, Excel's AutoComplete feature may suggest entries based on previous entries in the column.
To accept an entry, press Enter (Windows) or Return (Mac OS) when it appears. Otherwise, just keep typing what you want to enter. The AutoComplete suggestion will eventually go away.
)
make a column wider p. 30
-
You can't change the width of a single cell. You must change the width of the entire column the cell is in.
enter values pp. 31–32
-
You can enter any values you like in this step. But if you enter the same values I do, you can later compare the results of your formulas to mine to make sure the formulas you enter in the next step are correct.
-
Do not include currency symbols or commas when entering values. Doing so will apply number formatting. I explain how to format cell contents, including values, in Chapter 6.
-
If you use the arrow keys to move from one cell to the next, the selection area disappears. Although you can enter values without a selection area, using a selection area makes it easier to move from one cell to another.
-
If, after entering values, you discover that one of the values is incorrect, activate the cell with the incorrect value, enter the correct value, and press Return or Enter to save it.
-
A dialog like the one shown below may appear when entering values in a worksheet. Clicking Yes in this dialog converts the worksheet into an Excel list. Click No to dismiss the dialog without making the conversion.
)
calculate a difference p. 33
-
In Excel, all formulas begin with an equals sign (=).
-
Although you can write a formula that subtracts one number from another, using cell references in the formula ensures that the formula's results remain correct, even if referenced cells' values change.
-
As our formula is written, if the actual amount is lower than the budgeted amount, the difference appears as a negative number. You can make this appear as a positive number by switching the order of the cell references so the formula is =B3-C3.
calculate a percent diff p. 34
-
The number of decimal places that appear in the results of the formula depends on the width of the column the formula is in.
-
Don't worry that the percentages Excel calculates don't look like percentages. Later, in Chapter 6, we'll format the worksheet so the numbers look like percentages.
-
If the budgeted amount in a cell is 0, the formula for the percent difference will display the error message #DIV/0! Enter this formula in cell E3 to prevent that error: =IF(ISERR(D3/B3),0,D3/B3) This rather complex formula uses logic to determine whether the formula results in an error and, if it does, results in 0.
sum some values pp. 35–36
-
The SUM function is probably Excel's most used function. It can be used to add up any range of values.
-
Excel is not case-sensitive when evaluating functions. You can type SUM, sum, Sum, or even sUm when you write the formula and Excel will understand.
-
Excel 2004 for Mac OS includes a background error-checking feature that may mark cell B27 with a green error indicator. Activate the cell and click the button that appears to display a menu of error-checking options.
Since this formula is not in error, choose Ignore Error to remove the error indicator.
)
copy and paste p. 39
-
On Mac OS, you must press the Enter key (as opposed to the Return key) to paste to a selected cell. The Enter key is commonly found on the numeric keypad of a keyboard but may be located elsewhere, especially on PowerBook and iBook keyboards. Pressing Return merely moves the cell pointer down one cell.
-
The Paste Options button appears when you use the Paste command. Clicking this button displays a menu of options you can use immediately after pasting one or more cells.
)
use the fill handle pp. 40–42
-
The Auto Fill Options button appears when you use the fill handle to copy formulas. Clicking this button displays a menu of options you can use immediately after filling cells.
)
change a value p. 43
-
You can use this technique to change any label, value, or formula in a worksheet cell.
-
To delete the contents of a cell, activate the cell, press Backspace (Windows) or Delete (Mac OS), and press Enter (Windows) or Return (Mac OS). Don't use the Spacebar to delete a cell's contents; this merely replaces its contents with a space character.