If you're fairly new to Microsoft Excel, you've probably learned to enter data into cells to provide consistent formatting, and give yourself an easy way to update information, such as your office budget. However, many people brand new to Excel don't realize how much easier the power of Excel's formulas can make your job of keeping information current and accurate.

A formula is a recipe that lets you make calculations in Excel based on any number of factors. A formula can do something as simple as adding 1+1, or can be complex enough to look up data in a database and return a result that is dependent upon data that you enter into the spreadsheet. By the end of this document, you will know how to create your own formulas and become familiar with a few of Excel more useful formulas.

You should already know how to enter data into a cell in an Excel spreadsheet.

The Equal (=) sign, the formula bar, and formula parts

Let's start with a very simple formula:  =100+50.

Note that the formula above starts with an equal (=) sign. This is the signal to Excel that you're not going to enter ordinary data into a cell, but, rather, you will be entering a formula into the cell—a formula for which Excel will then need to calculate a result. If you were to enter =100+50 into a cell in Excel, you wouldn't see the text "=100+50" appear on the screen. Rather, the value of the formula—in this case 150—would appear instead. So as not to completely confuse you, Excel does show you the actual contents of the cell rather than the value, though… it's just in a different area of the screen called the formula bar. In Figure A below, the formula bar is highlighted in green. Note the value of 150 in the selected cell and then note the contents of the formula bar.

Figure A

The formula bar shows you the full contents of the cell—formula and all. The cell reflects the value of the calculated formula.

Now you know what the equal sign is for and where you can actually see the contents of your cell, formula and all.

Cell references

The formula presented in the last example is very simple and really not all that useful. You might as well just type 150 into the cell. However, suppose you have other cells in your spreadsheet and you want to calculate a sum based on the values of those cells. As an example, let's add up the contents of two cells. In this case, cell A1 has a value of 100, and cell B1 has a value of 50. For a formula, we'll use =sum(A1:B1) as shown below in Figure B. Now, look at Figure C to see what happens when we change the value of cell A1 to 500.

Figure B

This formula adds up all the numbers between and including A1 and B1.

Figure C

This example uses the exact same formula, but the source cell contents have changed.

This is a simple example, but a whole lot has happened here. You might not know it yet, but we've added up a range of cell values, and you've made changes to your source cells, forcing Excel to automatically recalculate the formula based on the new values. You've also used an Excel function to make it all happen.

First, to be clear, in these examples, I made no changes to the formula. It was always =sum(A1:B1). All I changed was the contents of cell A1. Excel did the rest.

Now, about cell ranges. You can always tell when Excel is using a range of cells rather than a single cell for a calculation. Whenever this is taking place, the formula has a colon sign separating two cell references. If you read this literally (A1:D1, for example), it reads "the contents of every cell between A1 and B1, inclusive of A1 and B1". This would include cells A1, B1, C1 and D1.

If you want to add up specific cells, but don't want to use a range, separate the cell references with commas instead. For example, the formula "=sum(A1,C1) will add up the contents of just cells A1 and C1. In contrast, if you use a colon, i.e. "=sum(A1:C1)", you will add up the contents of cells A1, B1 and C1. See Figure D for an example.

Figure D

Note the difference in the results in this example, contrasting the difference between the comma and the colon cell reference separator.

I also used the term function earlier in this section. The word "sum" in the formulas used in these examples is one of Excel's many functions. In this case, the sum function is used to add up the values of the cells that you specify in the functions arguments—the values used by the function to make its calculation. The cell references in the sum function, such as A1, B1 and A1:B1, are examples of arguments.

One more note before I move on to a few useful functions: As you type in your formula, notice that Excel highlights the cells that you use in your formula. Each cell reference gets a new color that corresponds to the cell reference in your formula. If you use a cell range in your formula, the entire range is highlighted with a single color. This highlighting feature makes it much easier to quickly identify problems in your formula later on. Figures E and F show you example of this feature in action.

Figure E

In this shot, cell A1 is highlighted in blue while C1 gets a green box. Look in cell C1 and notice that the cell reference matches the color of the box surrounding that particular cell.

Figure F

This formula uses a cell range and, thus, the range gets a single color that matches the one used in the formula.

You can get access to the entire Excel series using this link to the excel anatomy tag

Useful Excel functions

Excel includes dozens and dozens of functions, all of which can prove useful in specific situations. For this section, I will go over three useful Excel functions that are general purpose in nature, and show you a way that you can quickly get a list of all of Excel's functions in an easy-to-use screen.

Sum

=sum(number or range, number or range,…)

The sum function takes up to thirty numbers or ranges as arguments. The function's purpose is to add up the numbers provided and calculate the total.

Average

=average(number or range, number or range,…)

Like the sum function, you can provide up to thirty arguments for the average function. As you probably guessed, the average function calculates the average (mean) for the numbers you provide. I list this function here for a reason. While you could use a variation of the sum function to find an average, I wanted you to know that Excel includes this useful function. The same is true for a number of other functions.

Count

=count(number or range, number or range,…)

Again, up to thirty arguments are accepted by this function. This function, however, only counts the number of cells that actually contain values. For an example, see Figure G.

Figure G

Counting

In this example, notice that the first count function resulted in a value of 4. That's because only four of the five cells in the range A1 to A5 contained values. The second count function had a value of 7—four from the D1 to D5 range, and three more as a result of the three extra numbers I put directly into the formula.

The count function is extremely useful when you have a situation in which you need to just count the number of cells that have data. For example, suppose you're creating a spreadsheet for an event you're hosting and you want to keep track of the number of people that have paid their deposits. If you enter their deposits in Excel, you can use the sum function to add up the total amount of the deposits, and use the count function on that same information to get the number of deposits that have been made.

More functions

To get a complete list of all of the functions included in Excel, go to the Insert menu and choose Function. The Insert Function dialog box opens, as shown in Figure H.

Figure H

The Insert Dialog box helps you locate the function you need

The Insert Dialog box has a window at the top of the screen that lets you try to tell Excel what you want to do. Excel will in turn try to locate the function that best suits your need. In this case, I told Excel that I wanted to find the largest number in a range. Excel looked at its function database and told me that the MAXA function was my best bet.

Alternatively, if you don't want to have Excel look for a function on your behalf, you can use the Category drop down box to try to narrow down your function search and go through the list of individual functions in each category on your own. Figure I shows the contents of the Category drop down.

Figure I

The Category box has a number of selections to make it easier for you to find the function you want

Once you locate the function you want, double-click it, or choose OK. The next screen of the Insert Function process opens. This is where things get really useful. Basically, using this screen, you don't need to remember anything about Excel functions. Excel pops up a form and tells you to fill it in and even tells you what kind of information you need to enter. A sample of this screen is shown in Figure J, but shows the complex function called getpivotdata. I only chose this function here to give you a look at how Excel handles more complex functions on this screen. The sum, average, and count functions are fairly simple and a shot of the count function's arguments in shown in Figure K.

Figure J

Notice that Excel tells you, in white letters, what kind of data is needed for each argument

Figure K

The count functions needs only one parameter, but you can enter more.

Also note that the result is actually calculated right on this window. I've highlighted it in green for you in this screenshot.

Summary

Excel formulas and functions are what make the application work its magic. In future parts of this training series, I'll show you how to create more complex functions, automate some processes and make your work look spectacular. After all, if you spend a lot of time creating a budget spreadsheet for your boss, it might as well look good, too!