What is a function in Excel?

Microsoft Excel's table structure is a great way to keep organized. But the program is much more than just a series of rows and columns into which you can enter data.

Excel really becomes powerful once you start using functions, which are mathematical formulas that help you quickly and easily make calculations that would be difficult to do by hand.

Functions can do many things to speed up your calculations. For example, you could use functions to take the sum of a row or column of data; find the average of a series of numbers; output the current date; find the number of orders placed by a particular customer within a given period of time; look up the e-mail address of a customer based on his or her name; and much more. It's all automatic — no manual entry required.

Let's take a closer look at functions to see how they work.

The structure of a function

Think of a function like a recipe: you put together a series of ingredients, and the recipe spits out something totally new and different (and, in most cases, more useful, or delicious, than the thing that you put in). Like recipes, functions have three key pieces that you should keep track of:

• First, there's the function name. This is just like the recipe name that you would see at the top of one of the pages of your cookbook. It is a unique identifier that tells Excel what we are trying to cook up.
• Next, there are arguments. Arguments are just like the ingredients of a recipe. They're pieces that you put in that will eventually combine to make something bigger.
• Finally, there's the output. The output of a function is just like the output of a recipe — it's the final product that is ready to be presented to the user (or, in the case of a recipe, eaten).

Writing a function in Excel

When we enter functions into Excel, we use a special character to tell the program that what we are entering is a function and not a normal block of text: the equals sign (=). Whenever Excel sees the = sign at the beginning of the input to a cell, it recognizes that we are about to feed it a function.

The basic structure of a function is as follows:

=FUNCTION_NAME(argument_1, argument_2, argument_3...)
Output: Output

After the = sign, we write the name of the function to tell Excel which recipe we want to use. Then, we use the open parentheses sign (() to tell the program that we're about to give it a list of arguments.

We then list the arguments to the function, one by one, separated by commas, to tell Excel what ingredients we are using. Note that just like recipes, each function has its own specific number of arguments that it needs to receive. Some just take one argument; others take two or even more.

To finish writing a function, wrap up the list of arguments with the close parentheses sign ()) to tell Excel that you're done writing the list of ingredients. Then press the Enter key to complete your entry. You'll see that rather than displaying the text that you entered, Excel shows the output of your completed function.

A practical example

Let's look at a practical example using the SUM function. This is one of Excel's most-used recipes — it takes any number of arguments (all of which should be numerical), and spits out the sum of those arguments. The formula for SUM is as follows:

=SUM(number_1, number_2...)

To recap: the name of the function is SUM. The arguments are number_1, number_2, and as many additional numbers as you want to put in (this particular function takes an unlimited number of arguments, just like a recipe that gets better and better as you throw in more ingredients). When you're done writing the function and press Enter, Excel will show you the output.

Try entering the following into a cell on a blank spreadsheet:

=SUM(3, 7)
Output: 10

Excel outputs 10, because the SUM of 3 and 7 is 10.

Here's another example with even more arguments:

=SUM(1, 2, 3, 4, 5)
Output: 15

Here, Excel outputs 15, the SUM of 1, 2, 3, 4, and 5.

Infinite arguments and optional arguments

Throughout these pages, we'll use a couple different types of notation to denote special cases within functions:

First, some functions, like SUM above, have a theoretically infinite number of arguments. For example, you can take the SUM of an infinite number of numerals. In cases like this, we use three dots (...) to denote an infinite number of additional arguments, like so:

=FUNCTION_NAME(argument_1, argument_2, argument_3...)

If a function has a finite number of arguments, you won't see that ... at the end, like this:

=FUNCTION_NAME(argument_1, argument_2)

Finally, some arguments to functions are optional, just like some ingredients of a recipe might be optional. If one or more arguments of a function are optional, we'll follow them up with an (optional) designator like so:

=FUNCTION_NAME(argument_1, argument_2 (optional))

In most of our function tutorials, we'll explain why something is optional and how you can use it.

That's it! Now that you know what a function is, check out our tutorials on some of Excel's logical functions to get rolling with Excel's most powerful tool.

Comments