What is Formula Tab ? Whare is it in Excel ?
Formula Tab is the most important part of Excel. It is between the Page Layout Tab and Data. We use Formula tab to insert functions, define the name, create the name range, review the formula, etc. In ribbon, Formulas tab has very important and most useful functions to make dynamic reports.
It looks like image below.
It is divided in Four part.
1. Function Library
2. Defined Names
3. Formula Auditing
4. Calculation
1. Function Library:
In Excel, we have 461 functions and they are available in the formulas tab under the function library group. And there is no need to learn every formula for we can find formulas category wise such as:- Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, many more. We will must Discuss this in detail in another article.
Insert Function - Edit the formula in the current cell by choosing functions and editing the arguments. The keyboard shortcut to insert a function is Shift + F3 .
AutoSum - Display the sum of the selected cells directly after the selected cells. The keyboard shortcut for autosum is Alt + = .
Recently Used - Browse and select from a list of recently used functions.
Financial - Browse and select from a list of financial functions.
Logical - Browse and select from a list of logical functions.
Text - Browse and select from a list of text functions.
Date and Time - Browse and select from a list of date and time functions.
Lookup and Reference - Browse and select from a list of lookup and reference functions.
Math and Trig - Browse and select from a list of math and trigonometry functions.
More Functions - Browse and select from lists of statistical, engineering, cube, and information functions.
2. Defined Names:
We use this option to define the name of a cell, range and the list of the already defined name can be viewed in Name manager and we can edit it if we want to change the range or cell reference of any defined name. Also, we can use the defined name in Formulas.
Name Manager - Create, edit, delete, and find all the names used in the workbook. The keyboard shortcut to access the name manager is Ctrl + F3.
Defined Name - Name cells so that you can refer to them in formulas by that name. For example, you might name the cells A20 to A40 "Expenses." Names can be used in formulas to make them easier to understand.
Use in Formula - Choose a name used in the workbook and insert it into the current formula.
Create from Selection - Automatically generate names from the selected cells. Many people choose to use the text in the top row or the leftmost column of a selection. The keyboard shortcut to create from selection is Ctrl + Shift + F3 .
Defined Name - Name cells so that you can refer to them in formulas by that name. For example, you might name the cells A20 to A40 "Expenses." Names can be used in formulas to make them easier to understand.
Use in Formula - Choose a name used in the workbook and insert it into the current formula.
Create from Selection - Automatically generate names from the selected cells. Many people choose to use the text in the top row or the leftmost column of a selection. The keyboard shortcut to create from selection is Ctrl + Shift + F3 .
3. Formula Auditing:
This option helps us to identify the relation of formulas. By using Trace precedent, we get to know on which cell formula cell is based. Trace dependents are used to know if active cell is being used in any formula. When we use trace precedent and trace dependents the arrows get inserted automatically and, to remove the arrows, we use Remove Arrows. Show formula is a self-explained word; it is used to show the formula in the sheet. Error checking is used to check the error in the sheet. Evaluate formula option is used to evaluate the formula step by step.
Trace Precedents - This will show arrows that indicate what cells affect the value of the currently selected cell.
Trace Dependents - This will show arrows that indicate what cells are affected by the value of the currently selected cell.
Remove Arrows - This will remove the arrows drawn by Trace Precedents or Trace Dependents.
Show Formulas - Display the formula in each cell instead of the resulting value. The keyboard shortcut to show formulas is
Ctrl + ' \.
Error Checking - Check for common errors that occur in formulas.
Evaluate Formula - Launch the Evaluate Formula dialog box to debug a formula by evaluating each part of the formula individually.
Watch Window - Monitors the values of certain cells as changes are made to the sheet. The values are displayed in a separate window that remains visible, regardless of what area of the workbook is shown.
Trace Dependents - This will show arrows that indicate what cells are affected by the value of the currently selected cell.
Remove Arrows - This will remove the arrows drawn by Trace Precedents or Trace Dependents.
Show Formulas - Display the formula in each cell instead of the resulting value. The keyboard shortcut to show formulas is
Ctrl + ' \.
Error Checking - Check for common errors that occur in formulas.
Evaluate Formula - Launch the Evaluate Formula dialog box to debug a formula by evaluating each part of the formula individually.
Watch Window - Monitors the values of certain cells as changes are made to the sheet. The values are displayed in a separate window that remains visible, regardless of what area of the workbook is shown.
4. Calculation:
If we want to switch the calculation from automatically to manually calculation and manually to automatically calculation, then we can do it by using the Calculation option. We use Calculate Now and Calculate Sheet option if automatic calculation is turned off.Calculation Options - Specify when formulas are calculated. By default, any time you change a value that affects other values, the new values are calculated immediately.
Calculate Now - Calculate the entire workbook now. This is only necessary if automatic calculation has been turned off. The keyboard shortcut to calculate now is F9 .
Calculate Sheet - Calculate the current sheet now. This is only necessary if automatic calculation has been turned off. The keyboard shortcut to calculate sheet is Shift + F9 .
If you liked our blogs, share it with your friends on Facebook. And Twitter. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Please Comment in on the post.
