The formulas tab lets you select from
over 300 functions available with Excel 2007. These functions are
grouped in categories Financial, Logical, Text, Date and Time, Lookup
and Reference, Math and Trigonometry, Statistical, Engineering, Cube and
Information. With Excel 2010, 38 functions were "reworked"; optimized and renamed. You will find the "old" functions in the Compatibility folder.
This Formula tab also allows you to manage the names you give to
the cells. It is easier
to understand the formula =Total_Revenues - Total_Charges than =AZ234 -
BW541. You can name
the one or more cells simultaneously.
An interesting option to check the validity of your formulas is to make an
"audit" of your forms to see the cells needed to create the formula or
those cells that depend on the content of the cell. You can also ask to
assess your Excel formulas.
The Watch window allows you
to gather in one place the result of several important units of your
model. It is unnecessary to pass from one cell to another to
see the impact of a change. You can follow them from the watch window.
Normally, Microsoft Excel recalculates your spreadsheet
every time you make a change to the contents of a cell. But this can slow performance if you have a very
cumbersome and imposing model. You have the option to turn off
automatic recalculation and update your model by pressing the F9 key to the workbook or SHIFT and F9 for only the worksheet.
Function Library
 |
In the Function library, you can access
all functions of Excel. They
are grouped by category. It is easier to find the
function you want. As in the
earlier |
versions,
an assistant is available to help you find the function you
want. Press the
Insert Function button to activate the wizard.
Insert Function
 As in previous
versions of Excel, you have access to a wizard to find the function that
meets your needs.
| |
You can enter a
description of the function you want in the first box and press the
OK button to the right of it (not that the bottom of the
window). Make sure the option in the category that you want or the All option.
. Once you press the OK button functions of research, the wizard will display a list of
potential functions. Place the pointer on the name of the
function you want. A description of the
function will appear at the bottom of the list of features.
You can read it and determine if this feature meets
your needs. Otherwise, go to the next function and
read the description.
Press the OK button once you have selected a
function that meets your needs. |
| |
Most functions
require more information, called arguments, to calculate the desired
result. The next window shows the list of arguments to
which you can enter values, refer to cells or simple calculations or
even add other features. A description of
the contents of the function appears at the bottom of the list of
arguments. |
The
argument with a bold title is mandatory. The function will not give you a proper result if you do not insert
values into these boxes. The
other arguments are optional. However, they offer additional
options that can be very handy as the offer more possibilities to that function.
The wizard is useful when you try to use a function
for the first time. However, you can write the
function in cells of Excel. Always start a function by using =, + or - sign. Excel prefers to
start a formula with the character =. Then write
the function name followed by a parenthesis.
 Even when you write directly the function, Excel
provides assistance in showing you the list of functions followed the
order of arguments.
 |
Start with your formula with the = sign. The wizard will then show you a list of functions
according to the letters you write to formula. You can select
the function from the list and press the Tab key (first key to the left in the
second row). |
 |
Open parenthesis and then
enter values for the arguments. Below the formula, the
wizard displays a list of arguments into the formula. Arguments in
brackets [] are optional. |
 |
Remember to enter the separator between the
arguments. By country and
setting your computer, this separator can be different. In this example, it is the semicolon (;). |
Autosum
You will find the Autosum button on the tabs Home and
formulas tabs.
 Follow this link to view the exercise for this command
Recently Used
recemment.gif)
Financial
Logical
Text
Date & Time
Lookup and Reference
Math and Trig
More Functions
 |
There are more
functions available. Statistical
functions include functions such as averages, coefficients, standard
deviations, frequency, max, min, forecasting and others.
Engineering functions include functions to convert from binary,
decimal and hexadecimal complex functions among others.
The cube
functions are to search for information using multiple search
filters or settings.
The Information functions are used to determine the
contents of a cell. |
Defined names
 |
Microsoft Excel allows you to give names
to a cell or range of cells you can use in your formulas. It is easier to understand the formula = quantity
* price than = B23 * A125. You must remember that a given name can never spaces or some reserved characters.
You can use the underscore "_" to group words. |
This can give results as =Total_Revenues - Total_Charges. Depending on the keyboard you use, you
must generally use the SHIFT and symbol - the standard keyboard (not from the
numeric keypad) to display an underscore (_).
Name manager
 |
The name Manager names can
add, edit or remove names to a cell or a range of cells. |
Define Name
 Select the formulas tab.
Place the cursor in the
cell, or range of cells, that you wish to give it a name.
Select the Define
Name.
 |
Excel 2007 will suggest the contents of the cell to the left
if it contains text. The
software will replace the spaces outlined by (Shift and - on most
keyboards). You can always write the name of your choice in
the Name box. However, Excel will not accept name that
contains spaces.
Enter the name of your choice in the Name box.
In the Refers to box, make sure the cell address or block
of cells is well chosen.
If you wish, you can add a comment for the name you
chose.
Press OK.
|
There is another way to
give a name to a cell.
Place the cursor in the
cell or range of cells you wish to name.
Place the cursor in the box position
indicator.
Enter directly the name of your choice
(without spaces).
Press the Enter key.
Use the named cell in a formula
Create from selection
Formula auditing
 |
The are times when your model doesn't work as expected. But the problem is not the formula but values from
other cells in which your formula. The audit tool lets you view
the dependencies between cells. For example, to determine gross profit, we
must have first calculated the total revenue and total operating
expenses. The next year
to see the dependencies between cells using the Audit Tool. |
Trace Precedents
Trace dependents
Remove arrows
Show formulas
Error checking
Evaluate formula
Watch Window
 |
Another helpful
tool is the Watch window. It allows you to "spy"
on several cells that are far from each other. For example, you
can immediately see the impact of change on important cells in your
model. |
Select the formulas tab.
Press the watch window button.
From the Watch window, press the Add Watch.
 |
Immediately, the
value changes in the cell and in the Watch window. |
Calculation
 |
Normally, Microsoft Excel
recalculates your entire workbook every time you enter or edit the
contents of a cell. However, this
can slow data entry if you have a very impressive model.
Microsoft Excel allows you to disable automatic
recalculation and choose when you want to recalculate your
model. |
Calculation options
 |
You find this button under options recalculation of
your workbook. You decide how
that is more appropriate for the size of your model. |
Calculate Now
 This option
allows you to recalculate the entire workbook. You can also press F9 key to
recalculate the entire workbook.
Calculate Sheet
 This option only recalculating the worksheet where to find
the active cells. You can also use the SHIFT and F9 keys.
|
Select the cell or the extent of
cells you want. 
