Excel 2010 Formulas Tab Lesson-6

Excel 2010 Formulas Tab Lesson-6

Formulas Tab:-We use the Formula tab to insert functions, define names, create name ranges, review formulas. In ribbon, Formulas tab has very important and most useful functions to make dynamic reports.

Count and Sum :- The most used function in excel are the functions that count and sum . You can count and sum based on one criteria or multiple criteria

Logical:-Learn how to use excel’s logical functions such as the IF,AND and OR function.

Cell References:- Cell references in excel are very important . Understand the difference between relative, absolute and mixed references, and you are on your way to success.

Date & Time:-To enter a data in excel, use the “/” or “-” characters. To enter a time, use the “.” (Colon). You can also enter a date and a time in one cell.

Text:- Excel has many functions to offer when it comes to manipulating text strings.

Lookup & Reference:-  Learn all about Excel ‘s lookup & reference functions such as the VLOOK UP , HLOOK UP MATCH, INDEX AND CHOOSE Function.

Financial :-this chapter illustrates excel ‘s most popular financial functions.

Statistical:- An Overview of some very useful statistical functions in excel. 

Round:- This Chapter illustrates three function to round number in excel the ROUND, ROUNDUP and ROUNDDOWN FUNCTION .

Formula Errors:- this chapter teaches you how to deal with some common formula errors in excel 

Array Formulas:- This Chapter helps you understand array formulas in excel Single cell array formulas perform multiple Calculations in one one cell. 

We start with the category Count & Sum, the most used functions in excel are the functions that count and sum You can count and sum based on one criteria or multiple criteria

Count:-To count the number of cells that Contain Numbers, use the COUNT Function.

Count A :-To Count the Value only of cells use the Count A Function. 

Count if:- To  Count cells based on one criteria (for example, higher than 9), use the SUM function.

Sum if :- To sum cells based on one criteria (for example, higher than 9),use the following SUMIF function two arguments to sum cells based on one  criteria (for example, green ) use the following SUMIF function e arguments, last argument is the range to sum. 

Name Manager:-

Go to Formulas tab > Defined Names group, then click the Name Manager. Alternatively, we can just press Ctrl + F3 (the shortcut for Name Manager)

For a new named range, click on the “New” button. 

On clicking the “New” button, you will see the below window. 

                                                             

Type in the name that you want to give to your range, as well as the cells it will refer to in the “Refers to” section.After this, you can see the name “Near” created when you click on the “Excel Name Manager.”

You can see the other options like edit & delete. Let’s suppose you want to edit the cell reference. Then just select the relevant named range (here “near”), click on “Edit,” and change the configuration.

Similarly, for deleting, select the relevant named range & click on “Delete.”

In case you want to delete multiple named ranges at once, all you need is to select the relevant ones by pressing the “Ctrl” button. All the relevant ones will be selected, and then you just need to click on “Delete.” To delete all the names, select the first one, press the Shift button, and then click on the last “named range.” In this way, all will be selected, then just click “Delete.”

Excel name Manager also has the filter functionality to filter out the relevantly named ranges. Please see the screenshot below.

Here, you can see the relevant criteria for filtering the relevantly named ranges. Select the one you want to restrict to and then do whatever you want.

Define Name

Another way to make a named range in Excel is this:

Select the cell(s).

On the Formulas tab, in the Define Names group, click the Define Name button.

In the New Name dialog box, specify three things: 

In the Name box, type the range name.

In the Scope dropdown, set the name scope (Workbook by default).

In the Refers to box, check the reference and correct it if needed.

Click OK to save the changes and close the dialog box.

Note. By default, Excel creates a name with absolute references. If you'd rather have a relative named range, remove the $ sign from the reference (before you do this, make sure you fully understand how relative names behave in worksheets). 

Use in Formula: - Drop-Down. The drop-down contains a list of all the named ranges in the workbook (25 with scrolling) and Paste Names. The Paste Names command displays the "Paste Name" dialog box.

Create from Selection :- Displays the "Create Names from selection" dialog box. This enables you to name a selected range of cells using a row or column title that you've entered.

Defined Name

Trace Precedents:- Displays arrows that indicate what cells affect the value of the currently selected cell.

Trace Dependents :- Displays arrows that indicate what cells are affected by the value in the currently selected cell.

Remove Arrows :- Button with Drop-Down. The button removes all the arrows drawn by the trace precedents and trace dependents. The drop-down contains the commands: Remove Arrows, Remove Precendent Arrows and Remove Dependent Arrows.

Show Formulas :- (Ctrl + '). Toggles the display of the formulas rather than the result.

Error Checking :- Button with Drop-Down. The button displays the "Error Checking" dialog box. The drop-down contains the commands: Error Checking, Trace Error and Circular References. The Circular References extension will only be enabled when the active workbook contains at least one circular reference.

Evaluate Formula :- Displays the "Evaluate Formula" dialog box. This allows you to step through a formula calculation.

Watch Window :- Displays the Watch Window. Allows you to view the contents of cells and their results as you make changes.

Calculation Options :- Drop-Down. The drop-down lets you quickly change the calculation setting in the active workbook between Manual, Automatic and Automatic except for Data Tables. This setting is in fact an application setting. For more information please refer to the Calculation page.

Calculate Now :- (F9). Provides a shortcut to the (Excel Options)(Calculation tab, "Calc Now"). This option no longer appears on the Excel Options dialog box.

Calculate Sheet :- (Shift + F9). Provides a shortcut to the (Excel Options)(Calculation tab, "Calc Sheet"). This option no longer appears on the Excel Options dialog box.