Microsoft Office Tutorials and References
In Depth Information
Working with Functions
As you develop formulas and worksheets, you might want to quickly switch a cell
reference from relative to absolute or mixed. Rather than retyping the formula, you can
switch the reference in editing mode by selecting the cell reference and pressing the
F4 key. As you press the function key, Excel cycles through the different reference types,
starting by changing a relative reference to an absolute reference, then to a mixed
reference with the row absolute, then to a mixed reference with the column absolute, and
then ﬁ nally back to a relative reference.
You’ll use the F4 key to cycle through the different types of references as you enter the
remaining formulas with the income for the summer months.
To insert the remaining references to the couple’s monthly income:
1. Click cell H19 , type , and then click cell . The formula =E5 appears in the cell, E5
which remains in Edit mode. This formula enters Diane’s income for the summer
2. Press the key. The formula changes to =$E$5, which is an absolute reference. F4
Trouble? If the formula shows anything other than the absolute reference, you
probably pressed the F4 key too many times. Continue to press the F4 key to loop
through all of the cell reference types until the formula returns to =$E$5, which
contains the absolute reference.
3. Press the Enter key. The formula is entered and 2,100 (Diane’s monthly income in
the summer) appears in cell H19.
4. In cell H20, enter the formula =$E$6 . This formula uses an absolute reference to
enter Glenn’s monthly income in the summer.
5. In cell H21, enter the formula =H19+H20 . This formula adds Diane and Glenn’s
income for June; their combined monthly income in the summer is 4,700.
6. Copy the range H19:H21 , and then paste the copied formulas into the
range I19:J21 . The summer take-home pay values appear for the months of June
You’ll complete the monthly income values for the remaining school months.
7. Copy the range C19:C21 , and then paste it into the range K19:N21 . The couple’s
monthly income is entered for all months of the year.
Working with Functions
Excel functions provide a quick way to calculate summary data such as the total, average,
minimum, and maximum values in a collection of values. You’ll use these functions to
summarize Diane and Glenn’s income and expense data at the top of the Budget worksheet.
Understanding Function Syntax
Every function has to follow a set of rules, or syntax , which speciﬁ es how the function
should be written. The general syntax of all Excel functions is
FUNCTION argument1 argument2 ,ƒ...) ,ƒ