What's new


Excel 2007/2010/2013
File tab
Home tab
Insert tab
Page Layout tab
Formulas tab
Data tab
Review tab
View tab
Developer tab

Chart - Insert a second axis
Create a half-moon chart
Create a demographics chart
Conditional formatting
Data table
Pivot table
Scenario Manager


Excel 2003
Excel 2007/2010/2013


Demonstration files
Texte en français


By e-mail
Join our Newsletter

Share this page

AddThis Social Bookmark Button

Excel - The Formulas tab

This page describes the options available under the Formulas tab. You may find a description of many functions on the Excel functions page.


Function Library

Insert Function
Autosum Exercices
Recently Used
Date & Time
Lookup and Reference
Math and Trig
More Functions

Defined Names

Name manager
Define Name
Use in Formula
Create from selection

Formula auditing

Trace Precedents
Trace dependents
Remove arrows
Show formulas
Error checking
Evaluate formula
Watch Window


Calculation Options
Calculate Now
Calculate sheet


Excel 2007: Formules-introduction

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

Excel 2007 : Formules - bibliotheque de function

The function library gives you access to all the functions in Excel. As mentioned above, they are regrouped into categories. It makes it easier to find a function if you know where to look.

Insert Function

Excel 2007 : Formules - insert une 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 Insert 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're 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're 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're 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 separate the aurguments.


Excel 2007 : Formule - somme automatique

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're trying to add. But it's not perfect. Always check the range of cells it proposes.

Exercices Follow this link to view the exercise for this command

Recently Used

Excel 2007 : Formule - utilise(s) recemment

Excel remembers the functions you used recently and expects that you may need them again soon. They are quickly available under this tab.


Excel 2007: Formules-Financier

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.


Excel 2007 : Formules - logique

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.


Excel 2007: Formule-Texte

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

Excel 2007: Formule-Date et heure

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 how many open office days there are between two dates amonth many other options.

Lookup and Reference

Excel 2007:Formule-recherche et référence

The lookup and reference functions help you search and find information that located in your workbook in many ways.

Math and Trig

Excel 2007: Formule-Math eat trigomométrie

Most mathematical and trigonometry functions that you know will be under this tab.

More Functions

Excel 2007: Formule-Plus de fonctions

Even Microsoft's new Ribbon isn't long enough to show every function catagories available. That's why you will find more categories under the More functions button. These functions are very important is you need to do any statistical or engineering work. You will also find functions to work with cube databases, information concerning the content of a cell, old functions avalaible in the Compatibility section and a new Web functions for Excel 2013.

Defined Names

Excel 2007 : Formules - noms definis

Microsoft Excel allows you to place a name on a cell or group of cells. You can after use these named cells in your formulas. It's much easier to remember and use a formula = quantity * unit_price than = A125 * B23. You must remember that the name you wish to give cannot contain any spaces. That's why the underline (Shift + - = _) is often used to connect words. Another option is to use an uppercase for the first letter of a new word: UnitPrice.

There are three ways to name a cell or a range of cells: Define Name, the name box and Create from selection. You will find a description on how to use these options below.You can manage the list of named cells from the Name Manager.

Name manager

Excel 2007 : Formules - gestionnaire de noms

The first way to name a cell is by using the Define name command.

Define Name

Excel 2007 : Définir name

*Go to the Formula tab.
*Place the cursor in the cell or the range of cells that you want give a name.
*From the group of commands Defined names, select Define Name.

Excel 2007 - Nouveau name

Excel will suggest a name according to the content of the cell to the left if it contains some text. The software will replace the spaces with underlines (Shift and - keys). You can always write the name of your choice in the Name box. However, Excel will not accept names that contains spaces. As mentioned before, either use underline to connect words or use an uppercase for the first letter of a new word: date_of_birth, DateOfBirth.

*Enter the name of your choice in the Name box.

The zone box determines that range were the named cell will be applied. Usualy, it would be for the entire workbook. But it can be just for the current worksheet. This is practical if you wish to use the same name but to a different cell in another worksheet with a different value.

*For this example, leave the Scope to Worksheet.

A comment would be appreciated. After all, will you remember in six months why you named the cell the way you did? Chances are that you wont. Leave yourself a reminder.

*In the Refers to box, make sure that the cell address or the range of cells is correct.
*Press the OK button.

There is another way of give a name to a single cell or range of cells from the name box.

*Place the cursor in the or the range of cells that you want give a name.

Donne name à une cellule

*Place the pointer in the Name box located to the left of the formula bar.
*Enter the name of your choice (without any spaces).
*Press the Enter key.

The name box has two interesting options. First it will mention the cell address where the active cell is located. Is that cell is named, that name will appear in the Name box. At the end of the name box, there is a small arrow pointing down. Pressing on that arrow will show the list of all the named cells. You can select one from the list and the active cursor will go directly to that cell. It makes is easier to go directly to the important cells of your workbook.

Create from selection

Excel 2007 : Formules - Creer a partir de la selection

As fun as it's to name some cells, it's a long process when you wish to name a lot of them. That's why Excel offers an option to name many cells at the same time.

*Select a range of cells of two columns. The name appear in the left column. The cells you want to name with the values you want are in the right column.
*From the Formula tab, in the Defined Names group of commands, select Create from selection.
*From the list of options, select Left column.

THe name you want to apply are in the left column. They will be applied to the cells on the right side of your selection.

*Press the OK button.

Check to make sure that cells are properly named.

Use in Formula

Excel 2007: Noms définis-Utiliser dans la formule Once a cell is named, you can also use it in your formulas. You may also notice that when you start a formula with the = sign, a list of Excel's functions will appear. To that that list, you will also see the list of named cells that you will be able to use.

Formula auditing

Excel 2007 : Formules - Audit de formules

Sometimes, a formula in your model will not work the way you intended. But the cause of the problem isn’t the formula but the values in another cell that your formula depends on. The Audit tools allows you to view the dependence between the cells. For example, to determine the gross profit, you must first calculate the total of revenues and also the total of charges. The next exercice will show the dependence between the cells by utilisant the Audit tool.

Trace Precedents

Excel 2007 : Formule - repere the antecedents

By tracing the precedent cells, you're looking for the cells that are required for your current formula. You can also press the Trace Precedents button many time to see what cells are required for the previous cells.

Trace dependents

Excel 2007 : Formule -reperer the dependants

By tracing the dependants cells, you're looking for the cells that require directly the active cell in their formulas.

Remove arrows

Excel 2007 : Formule - supprimer the fleches

After a time, there will be so many arrows pointing in every direction that it will because impossible to read. You can remove all the arrows and start over a another point that will be closer to the problem that your have encountered.

Show formulas

Excel 2007 : Formule - afficher the formules

You can always see the formulas in the formula bar. This option allows you to see all the formulas in its cell instead of the result. It may help you to dertermine a problem or to see how you can expand your formulas in your model.

Error checking

Excel 2007 : Formule - verification des erreurs

Excel always checks your formulas to check for any errors.

Evaluate formula

Excel 2007 : Formule - evaluation de la formule


Watch Window

Excel 2007:Audit de formules-Fenbe espion

Another tool avantageux is the window Espion. Elle allows you to « of espionner » many cells éloignées the a of the autre. For example,  you can immediately viewthe impact of a changement for the cells importantes of your model.

*Go to the tab Formulas.
* Press the Watch Window.

Fenbe espion vide

*From the window Espion, press the button Add watch.

Ajouter un espion

*Select the cell or the éten the of cells of your choice.
* Press the Add.
* Change the content of a cell that will affect the "watched" cell.

Fenbe espion avec des résultats

Immédiatement, the value will change in the cell and also in the watch window.


Excel 2007 : Formules - calcul


Calculation Options

Excel 2007 : Formules - options des calculs2



Calculate Now

Excel 2007: Calcul-Maintenant

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

Excel 2007: Calculer la feuille

This option will only recalculate the current worksheet.




You like what you read? Share it with your friends.


This site is hosted by 1&1.com