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 every time 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 allow you to choose whether the calculation should be done automatically 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 mentioned 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 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.
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're 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 use 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 conditions 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 month's time, see how many open office days there are between two dates among 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
 |
Most mathematical and trigonometry functions that you know will be under this tab. |
More Functions
 |
Even Microsoft's new Ribbon isn't long enough to show every function categories 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 available in the Compatibility section and a new Web functions for Excel 2013. |
Defined Names
 |
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
 |
The first way to name a cell is by using the Define name command. |
Define 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 contain 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 won't. 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.
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
 |
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 appears 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
 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
 |
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 allow 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
 |
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 times to see what cells are required for the previous cells. |
Trace dependents
 |
By tracing the dependents cells, you're looking for the cells that require directly the active cell in their formulas. |
Remove arrows
 |
After a time, there will be so many arrows pointing in every direction that it will impossible to read. You can remove all the arrows and start over with another point that will be closer to the problem that your have encountered. |
Show formulas
 |
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 always checks your formulas to check for any errors. |
Evaluate formula
Watch Window
 |
Another useful tool is the watch window. It allows you to « spy » on important cells far from one another. For example, you can immediately view the impact of a change on the important cells of your model. |
Go to the tab Formulas.
Press the Watch Window.
From the window Espion, press the button Add watch.
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.
Immédiatement, the value will change in the cell and also in the watch window.
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. |
|
This site is hosted by 1&1.com
