Inserting Formula, Function & Named Range In Excel – MS Excel Functions Tutorial Part 2

In previous post, we learnt what are excel functions and formulas and what is the difference between these two. In this post, we will learn how to insert functions and formulas in Excel. We will also learn about Named Ranges in Excel.

Inserting Excel Formula

A formula can be inserted by simple entering an equal to sign (=) followed by the formula.

A formula can be edited by selecting the cell which contains the formula and hitting the F2 key. F2 key reveals the formula and it can then be edited.

Inserting Excel Function

A function can be inserted by typing the equal to sign (=) and entering the name of the function. For example, in order to calculate the average of numbers as in the preceding example there is a function called “average”. Entering the equal to sign in a cell and writing the name of the function as “average” opens a drop down list of all the functions to calculate the average. We can select the required function from the list and the function will be inserted.

A function can also be inserted through the “Insert Function” option in the “Formulas” tab. The formulas tab also has options for inserting functions based on their categories like Mathematical, Financial, Logical etc. We will explore these function types in detail in next posts.

A function can be edited in a similar way like a formula (Pressing the F2 key).

Inserting Named Range In Excel

Named ranges are used in excel for naming or assigning a standard convention to a block of cells. For example let us consider our student database which contains the names of students and their marks.

Now for calculating the average of marks we can enter a function:

= AVERAGE (F6:F10)

We can substitute the cells F6 to F10 by a named range. If we use a common name for defining these cells we can use that name for all our calculations. This makes the process less cumbersome as we simply have to enter the name of the named range whenever we are interested in performing any calculations using this range. Let us follow these steps:

Step 1: Let us replace the cells F6 to F10 by a named range. To create a named range for these cells select the cells and go to the “Formulas” tab in the main menu. In the “Formulas” tab click the option “Define Name”.

Step 2: Clicking the “Define Name” option will open a window. In this window we can enter the name which we wish to assign our named range. We can also define its scope but we should always make sure that it refers to the cell range which we desire. In this case we have named the range as “student_marks”. The named range refers to the cells F6 to F10 or the entire marks column.

Step 3: As soon as we click “OK” our named range will be created. Now we can use this named range to perform many calculations. Let us calculate the average of marks first using this range.

The average can be calculated by entering the name of the function and in the arguments entering the named range. Our named range here is student_marks.

=AVERAGE(student_marks)

This formula will calculate the average of student marks. In a similar way we can calculate the sum of marks by using the sum function and entering the range name in its arguments list.

So we learnt inserting excel functions, forumula and using named ranges in excel. In next posts, we will explore various types of functions available in Excel. Let’s start from Mathematical Functions.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">