The formula tab allows you to select from over 300 Excel functions. These functions are grouped into these categories: financial, logical, text, date & time, lookup and reference, math & trig, statistical, engineering, cube, information and compatibility.
This tab also gives you the possibility to gives names to cells or a range of cells. It's easier to understand =Total_Revenues - Total_Charges instead of = AZ234 - BW541.
Another interesting option is the ability to verify the cells formulas with an audit. You can also ask Excel to check your formulas. It may find a better way to write them.
You can also check the content of many cells that are far from each other by placing a reference in the watch window.
Usually, Excel recalculates the entire workbook everytime you enter a value, change a value or a formula. But that can be a burden when you have an imposing workbook. The calculation options allows you to choose wether the calculation should be done automaticly or manually. You can also press on the F9 key to recalculate a worksheet or Shift + F9 keys to recalculate the entire workbook.
Function Library
 |
The function library gives you access to all the functions in Excel. As mentionned above, they are regrouped into categories. It makes it easier to find a function if you know where to look. |
Insert Function
 |
As in the previous versions of Excel, there is a function wizard to help you find the function that answers your needs. In the Search for function box, you can enter a description of the function you need. By pressing the Go button, Excel will search among the function and return a list of functions that it believes matches your description. You can place the cursor over each of them to see a description of the function at the bottom of the window. If you need more information about a function, you may press on the link Help on this function located at the bottom of the window. Once you have found the appropriate funtion, you can press the OK button. Otherwise, you may wish to try another description of the function and press the Go button again. |
Most functions need some type of information to work properly. Each bit of information is called an argument. The ones with the name in bold are required to make the function work. The other arguments are optional but add more potential to the function.
The insert function is very practical when you are trying a new function for the first time. It will help you determine the information you need and in what order you need to place them in the function. However, after a bit of practice, you can write directly a function into a cell. Excel expects that a function will always start with the equal (=) sign. After, you need to write the name of the function and open a parenthesis. For example =sum( .
Start your function with the = sign. Excel will then show you a list of functions. Even when you are writing a function, Excel is rying to give you a helping hand by placing under the function a description of the function and the order in which you must enter the arguments.The more letter of the function you write, the more functions will be removed from the list by Excel. You can always select the function you need from the list and press the Tab key (first key of the second row).
Open the parenthesis and start entering the arguments. They can be text, values, cell adresses or even other functions inside the function! The arguments inside brackets ( [] ) are optional. Don't forget to place the argument seperator. It varies depending on the language you are using. Generally, either a comma (,) or a semicolon (;) is used. Check by using the Insert function a first time and check the formula by looking at the content of the formula bar. You will what character is used to seperate the aurguments.
Autosum
 |
The AutoSum function is what made Microsoft Excel more popular Lotus 1-2-3 a long time ago. It tries to find out the range of cells you are trying to add. But it's not perfect. Always check the range of cells it proposes.
 Follow this link to view the exercise for this command
|
Recently Used
recemment.gif) |
Excel remembers the functions you used recently and expects that you may need them again soon. They are quickly available under this tab. |
Financial
 |
The financial category is the one with the most functions. It has functions for amortization, calculate the values of bonds and options that may be given to you, calculates a rate and much more. |
Logical
 |
Logical functions are used to test conditions inside your spreadsheet. There is the IF function. You can also used nested functions (functions inside functions) to make the IF function even more powerful and flexible. For example, you can combine it with the AND or OR functions to test many conditions at the same time. You can also place IF functions inside an IF function to check for condtions over other conditions. There is also the IFerror function to recover when errors in function happens. |
Text
 |
The content of your workbooks contains values, formulas but also text. These functions help you combine, extract and change the text that you have. |
Date & Time
 |
The date and time functions help you extract every bit of information possible. You can check what day it will be in a couple of months time, see comment many open office days there are between two dates amonth many other options. |
Lookup and Reference
 |
The lookup and reference functions help you search and find information that located in your workbook in many ways. |
Math and Trig
More Functions
Defined Names
Name manager
Define Name
 |
 Go to the Formula tab.
|
Place the cursor in the cell or the range of cells dont you want donner a name.
Du regroupment Nom définis, select the option Define Name.
Excel will suggest a name according to the content of the cell to the left if it contains the text. The software will remplace the spaces with underlines (Shift and - keys). You can always write the name of your choice in the Cell address box. However, Excel will not accept names that contains spaces.
Enter the name of your choice in the cell adress box.
In the box Fait reference à, assurez-vous that the cell address or the bloc of cells soit bien choisi.
If you the want, you can asjouter a comment for the name that you have chosen.
Press the OK button.
There is another façon of donner a name to one cell.
Place the cursor in the or the range of cells dont you want donner a name.
Place the pointer in the box Indicateur of position.
Enter the name of your choice (sans espaces).
Press the Enter key.
Use in Formula
Create from selection
Formula auditing
 |
It arrive per moments qu’une formula of your model ne functionne pas comme prévu. But the cause of the problem isn’t the formula but the values of other cells that depends on your formula. The tool Audit allows you to visualiser the dependence between the cells. For example, to connaître the bénéfice brut, il faut have first calculé the total of revenus and also the total of charges of exploitation. The next exercice to view the dependence between the cells by utilisant the tool Audit. |
Trace Precedents
Trace dependents
Remove arrows
Show formulas
 |
You can always see the formula from the formula bar. This options allows you to see the formula in its cell instead of the result. |
Error checking
Evaluate formula
Watch Window
 |
Another tool avantageux is the window Espion. Elle allows you to « of espionner » many cells éloignées the une of the autre. For example, you can immediately viewthe impact of un changement for the cells importantes of your model. |
Go to the tab Formulas.
Press the Watch Window.
From the window Espion, press the button Add a espion.
Select the cell or the éten the of cells of your choice.
Press the Add.
Change the content to une cell that will affecter the cell "espoinnée".
Immédiatement, the value change in the cell and also in the window Espion.
Calculation
Calculation Options
Calculate Now
 |
If the automatic recalculate option has been deactivated, you may press on this button to force Excel to recalculate the entire workbook. |
Calculate the worksheet
 |
This option will only recalculate the current worksheet. |
|
