EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

EXCEL'S INSERT FUNCTION

FREE EXCEL HELP

Lesson 21 - How to Use the Insert Function Feature to Create Formulas

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

THE INSERT FUNCTION DIALOG

Called the Paste Function dialog in older versions, but the Insert Function dialog in newer versions, this dialog box is used to insert or paste the selected function into the chosen cell. The big advantage to using this feature comes as you become more comfortable writing Excel formulas. Initially it is most beneficial because it can be used as a step-by-step guide for each argument in a function. What this means is, if you are going to be using a simple function such as the SUM, MIN, MAX etc. , it really serves no purpose. When writing slightly harder functions such as COUNTIF, SUMIF etc. , it can aid greatly.

Let�s display the Insert Function dialog and have a superficial look at it. There are three methods we can use to show this dialog box and which one you use is purely optional. The three methods are:

Going to Insert>Function
Push Shift + F3
Click the Insert Function icon to the left of your Formula bar (Fx), or for older version users, click the Paste Function icon on your Standard toolbar.

Once activated you will see the Insert Function dialog pop up in front of you. Depending on which version of Excel you are using, these heading names may vary slightly in this dialog box.

Search for a Function

Type a brief description of what you want to do in this box, then click Go to view a list of appropriate Functions.

Or Select A Category

In this dialog box you will see the Category Names that the Functions are grouped in. Click All to see a list of All Functions displayed in the Select A Function: box in alphabetical order. Click Most Recently Used to see a list of the last 10 functions used in the Select a Function: box.

Help

In the bottom left hand corner you will see either Excels standard help button (question mark), or the words Help on this Function. If you click this you will be presented with a description of how the selected Function works from Excel's Help.

To see how this works, Select All Under Or Select a Category: then Click on SUMIF under Select A Function.

The help screen that is displayed will give you most of the relevant information for the selected function, in this case SUMIF. All function help descriptions are uniform in that they will show the syntax, a description of the arguments and an example. We strongly recommend that you familiarise yourself with the Function Help as it can be very helpful once you are aware of the terminology used, hence my explanations on arguments, syntax, ranges, text values etc.

Most Excel users shudder at the thought of using the help to get their answers, but this is most likely because they feel intimidated by the jargon Excel uses. Please do your utmost to not become one of these, as the Excel help will always be your best source of help. If there are any terms used by the Excel help you are uncertain of you can always ask us.

For now close the SUMIF help and click the Cancel on the Insert Function dialog box. Let�s try this simple exercise to see how the Insert Function can help in writing a formula. The purpose of this exercise is more to show you how to use the Insert Function as opposed to the SUMIF function itself.

Type the numbers 1, 5, 20, 40, 50, 100, 200 in cell A1:A7 respectively
Type the names Bill, Bob, Dave, John, Fred, Mary, Jill in cells B1:B7 respectively.
Select cell H2 and go to Insert>Function or push Shift+F3 this will display the Insert Function dialog box.
Click the Math & Trig in the Or Select a Cateogry: box.
Scroll down and select SUMIF. At the bottom of the Select a Function: box will be the SUMIF arguments and syntax as well as a brief description of what it does.
Click the OK button to display the Function Arguments dialog for the SUMIF. You will notice there is one box for each argument and each box has the argument written to the left of its box. Notice the last argument (Sum_range) is not bold. This is because the argument is optional. Meaning, if we omit this argument, Excel will use the Range argument as the range to sum as well as the range to meet the Criteria.
Your mouse insertion point should be within the Range argument box, if it's not then click in there. If you look near the bottom you will see a brief description of what the Range argument does, which is the range of cells to evaluate based on the Criteria.
Look in your Formula bar and you will see =SUMIF() this is the SUMIF function with its opening and closing parenthesis. It is within these parentheses that the arguments will be placed.
Click the small box on the right of the Range argument box; this is called the collapse dialog button. The SUMIF dialog will disappear. Click in cell A1 and holding down the mouse button drag down to cell A7.
Now click the small button again, this is now the expand dialog button and the SUMIF dialog will appear again. You should now have A1:A7 as the Range argument.
Click in the Criteria argument box and you will see a brief description of what the Criteria argument expects for its data. We are going to use an expression first.
Type ">50" (including quotations) and you will see = 300 in the dialog box. This is the result of the Function, which in this case is the SUMIF. At the bottom of the dialog box you will see Formula result = 300. In this case the Function result and the Formula result will always be the same. There would only be a difference if we were doing what is known as Nesting. This is something we will look at in a later lesson.
So the result 300 is what we would expect in this instance as the only numbers within our Range greater than 50 are 100 and 200. There was no need to supply any data for the Sum_range in this case because we were using the Range as the cells to sum as well as the cells to match the Criteria.
Delete the ">50" Criteria and replace it with "B*". You will notice both the Function result and Formula result =0 This is because the SUMIF is trying to use the Range cells to match the Criteria and for the cells to sum.
What we need to do now is have Excel look in a different Range to meet our Criteria, we also need to supply a Sum_range argument.
Click on the collapse dialog button for the Range argument and select range B1:B7, click the expand dialog button. We should now have the range B1:B7 as our Range argument. Again the result for both the Function and Formula will still be 0 (zero). Now we need to supply a Sum_range argument!
Click on the collapse dialog button for the Sum_range argument and select range A1:A7, click the expand dialog button. We should now have the range A1:A7 as our Sum_range argument. Click OK we will get the result of 6.

This is because we have told the SUMIF to sum all cells in the range A1:A7 if the corresponding cell in B1:B7 has a word beginning with the letter "B".

The method in which we used the Insert Function for the SUMIF is the same principle we would use for all Functions written by using the Insert function dialog. As I stated before we began the above steps, the point of the exercise was to demonstrate the way in which the Insert Function can aid us in writing formulas.

Go To Free Excel Training Lesson 22 . Back to Previous Lesson

Go to Excel Basic/Level 1 Training Index

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 1 TRAINING INDEX

Instant Download and Money Back Guarantee on Most Software

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft