Microsoft Office Tutorials and References
In Depth Information
A formula in a cell that contains a reference back to itself is called a circular reference .
Excel often warns you when you create a circular reference. In almost all cases, circular
references are the result of an incorrect formula. A circular reference can be direct
or indirect. For example, placing the formula =A1 in cell A1 results in a direct circular
reference. An indirect circular reference occurs when a formula in a cell refers to another
cell or cells that include a formula that refers back to the original cell.
Employ the Excel built-in functions whenever possible.
Every time you enter
a value into a cell in
the worksheet, Excel
all formulas. You can
change to manual
recalculation by clicking
the Calculation Options
button (Formulas tab |
Calculation group) and
then clicking Manual.
In manual calculation
mode, pressing the F9
key instructs Excel to
recalculate all formulas.
Excel includes prewritten formulas
called functions to help you compute a range of values and statistics. A function takes a
value or values, performs an operation, and returns a result to the cell. The values that
you use with a function are called arguments . All functions begin with an equal sign
and include the arguments in parentheses after the function name. For example, in the
function =AVERAGE(C4:C12), the function name is AVERAGE, and the argument is the
range C4:C12. Become familiar with the extensive number of built-in functions. When
you have the choice, always use built-in functions instead of writing and typing a formula
version of your mathematical expression. Such a practice reduces the possibility of errors
and simplii es the formula used in a cell, resulting in improved readability.
To Enter a Formula Using the Keyboard
The formulas needed in the worksheet are noted in the requirements document as follows:
1. Gross Pay (column F) = Hours Worked × Hourly Pay Rate
2. Federal Tax (column G) = 0.22 × (Gross Pay – Dependents × 24.32)
3. State Tax (column H) = 0.04 × Gross Pay
4. Net Pay (column I) = Gross Pay – (Federal Tax + State Tax)
5. Tax% (column J) = (Federal Tax + State Tax) / Gross Pay
The gross pay for each employee, which appears in column F, is equal to hours worked in column D times
hourly pay rate in column E. Thus, the gross pay for Emily Charvat in cell F4 is obtained by multiplying 65.25
(cell D4) by 20.50 (cell E4) or =D4*E4. The following steps enter the initial gross pay formula in cell F4 using
With cell F4 selected, type
in the cell to display the formula in
the formula bar and in the current
cell and to display colored borders
around the cells referenced in the
formula (Figure 2 – 5).
with equal sign
What occurs on the worksheet as I
enter the formula?
The equal sign ( = ) preceding d4*e4
alerts Excel that you are entering
a formula or function and not
text. Because the most common
error when entering a formula is
to reference the wrong cell in a
formula mistakenly, Excel colors
the borders of the cells referenced
in the formula. The coloring helps
in the reviewing process to ensure
the cell references are correct.
The asterisk ( * ) following d4 is the
arithmetic operator that directs Excel to perform the multiplication operation.
as formula is typed, it
appears in formula bar
and in active cell F4
colored cell borders
indicate cells included
in formula in active cell