How to type and run an Excel worksheet function. - Tutorial

What is an Excel Worksheet Function?

Microsoft Excel worksheet functions are basically small computer programs you run from a worksheet formula >and look like =Max(A1:A10) when typed in a formula. Once entered, they run and return a result value through their syntax. So in your mind, when you see Max(A1:A10) visualize a value in its place in the formula and you have the concept.

One key concept to realize is that worksheet functions obey their own set of calculation rules and do not have to follow the rules a standard worksheet formula (i.e. = A1 + A2) does. So be sure to read about a function's capabilities in Excel Online-Help before using it. Some common worksheet tasks that functions allow you to do include:

• Taking the sum, max, min... of 10,000 cell values at once
• Performing table lookups
• Figuring out dates and times
• Running logic decisions
• Math like Sin, Cos, Tan, rounding...
• Text cleaning and parsing operations

the list is quite extensive.

How to run a worksheet function from a formula.

To run a function from a worksheet formula type an equal sign, the function name, a beginning (, its argument list, and an ending ) and press Enter. For example =SUM(A1:A10). This typed syntax (i.e. grammar) is referred to as a Call statement in programming and that is actually what you are doing here, calling and running computer code.

The key to making any function run is its argument list. An argument list is the information the function needs in order to execute and return a value, they are its inputs. Bad inputs = bad result. An argument list is contained between the two ( )'s as just stated and the arguments are separated by commas. If you see 2 commas, you have 3 arguments. The argument list for a function can be viewed in its Excel On-Line Help.

When typing a function call in a worksheet formula, function arguments come in many flavors but the basic ones are these:

• Numbers: 100
• Text: "Text"
• Cell References: A1
• Range References: A1:A10
• Names: item_cost
• Booleans: TRUE or FALSE
• Relational: < , >, <= , >=, <>, =
• Other Functions (called nesting >)
• Other Formulas (A1+A2)

How to type a function from scratch in a formula.

To type a function from scratch in a formula and run it, follow these steps

1. First lookup the function in Excel On-Line Help and review its syntax which is basically its name and argument list. Research the arguments and what can be used for them. Look at the help examples for typing clues.
2. In a formula, type an = sign followed by the function name and a beginning ( like: =VLOOKUP( .
3. At this point press Ctrl+Shift+A which will insert the rest of the function call syntax with the argument names into the formula.
4. Last replace each argument name with an appropriate argument, like the ones listed above, to accomplish your task. Generally cell >, range >, numbers, text and TRUE/FALSE are used as arguments in basic function operations.

Notice that argument names with [ ] brackets around them are optional which means they do not have to be used. Be very careful of optional arguments however. In some cases like SUM, MAX, MIN... optional arguments just represent that you can use up to 255 arguments so no big deal if they are not used. These type arguments will generally be represented by argument names like [number1]. However for functions that have very specific fixed argument lists like VLOOKUP,S UMIF, MATCH... any argument name with [ ] brackets should be investigated.

For example to run the VLOOKUP function in a formula, the syntax for it looks like VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), the last argument is optional. If left out in a formula like =VLOOKUP("A",A1:B10,2) , VLOOKUP will find an approximate match for what you are looking for. This could cause major problems if you wanted an exact match. Including the argument in a formula like =VLOOKUP("A",A1:B10,2, FALSE) tells VLOOKUP to find an exact match.

A lot of people just see a function typed in a formula, copy it, and never investigate it in Excel Online-Help. So always investigate a function and its argument list before attempting to use it or it may return the wrong result. One other important thing, the argument list in a function is always typed in a specific order according to its argument names from left to right, violate that order and the function may not operate correctly or will most likely error out.

Also there is a tool in Excel called Insert Function which is discussed below. The author uses it for a quick help guide. It can also help build the syntax for the function call in a worksheet formula. It is recommend that you do not use it, learn how to type and run a function from a cell on your own from the very start. You will stumble at first just like everybody else has but it takes very little time to get the hang of typing a function. It is the only way later on to build complex formulas in Excel.

A quick way to run a worksheet function's online help.

The complete list of Microsoft Excel functions and their help on how to run them can be quickly found by clicking the fx button at the top of the worksheet headers next to the formula bar (white box). This is the Insert Function tool discussed above. Before clicking this button, make sure you are clicked on an empty cell so you do not overwrite anything when this screen is activated.

When the Insert Function screen appears look through the drop downs, select a function name then click the Help on this Function blue hyperlink in the bottom left hand corner of the screen. This is a very quick way to access a function's help screen. The help screen will describe how to type the function in a formula, called its syntax, and will also describe the uses of the function.

A picture of these screens is illustrated below. Once the help screen is displayed, you can click the Cancel button on the Insert Function screen to cancel the operation but the help screen will remain. Note that you can also get a list of functions by clicking on the Formulas tab in the Ribbon and looking in the Function Library section. If a function name is clicked on in this section, that to will activate the Insert Function tool.