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.
- Introduction To Excel Formulas & Functions
- Inserting Formula, Function & Named Range In Excel
- Mathematical Functions In Excel
- Text Functions In Excel
- Cleanup & Case Functions In Excel
- Logical Functions In Excel
- Lookup Functions In Excel
- Date & Time Functions In Excel
- Max & Min Functions In Excel
Inserting Excel Formula
A formula can be inserted by simple entering an equal to sign (=) followed by the formula.
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.
= 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.
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.
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.