Discover excel formulas online video tutorials from lynda.com

Excel formulas and functions

from Introduction to Formulas and Functions by Curt Frye

Adding numbers using the Sum and AutoSum functions

From the course: Excel 2010 Essential Training

In this video tutorial, Bob Flisser demonstrates how to use the most common formula, Sum, to add data in any number of cells, rows, and columns. He also shows how Excel can grab the cell selections for you automatically with the AutoSum tool.

Jun 09, 2010

Bob Flisser

6h 21m

What is the difference between Excel formulas and functions?

Many Excel commentators, myself included, tend to use the terms function and formula as if they meant the same thing. In fact, Excel functions and Excel formulas are quite distinct.

A formula is a statement you enter into an Excel worksheet cell in your spreadsheet that calculates a value. In Excel, you always begin a formula by typing an equal sign. When you do that, Excel knows that it is to treat the rest of the text you type into the cell as part of the formula.

As an example, you can click a cell on your worksheet, type = 1+2, press Enter, and get the result of 3. You can also refer to worksheet cells in a formula.

Let’s say I have values in cells B3 and B4, which represent revenue for the years 2009 and 2010. If you want to find the sum of the values in those two cells, you could type =B3+B4, press Enter, and you get the result.

A worksheet cell displays the formula’s result, but if you click the cell and look at the Formula Bar, which is just above the regular worksheet grid, you can see the text of the formula.

Adding advanced calculations

Now, let’s say that you want to perform a slightly more advanced calculation such as finding the average of the two values in cells B3 and B4. The long way to do that would be to type a formula in cell E2 such as =(B3+B4)/2. What this does is add the two values in B3 and B4 and then divides it by 2, which is the number of values. When you press Enter, you’ll see the average.

The problem with this approach is that it takes a long time to type in those formulas and it’s really only practical to create formulas by hand from very simple mathematical operations. In many cases, it’s much easier to use functions.

Spreadsheet cell ranges

An Excel function is a built-in set of steps or algorithms that Excel can follow to find a result. To find the average of the values in cells B3 and B4 for example, I could type in an equal sign and then start typing the name of the Average function and press a left parenthesis. Now that I’ve pressed a left parenthesis, I can indicate which cells contain the values I want to add: B3 to B4. When I type a colon it indicates to Excel that I’m defining a cell range.

After you enter the cell references, you can type the right parenthesis and press Enter to end the function and you get the result. Now it’s the same result as before, but you didn’t have to remember how to compute an average or count the number values you were averaging.

Using AutoComplete to enter Excel formulas

There are three main ways to create a formula in Excel: by typing, by using the controls on the Formula tab of the Ribbon User Interface, and by using the Insert Function dialog box. When you’re ready to create a formula by typing, you click the cell where you want to create the formula and start typing. Just as before, typing in an equal sign indicates to Excel that it’s to treat the remainder of the text in the cell as a formula as opposed to a number or text value. AutoComplete will display a list of functions that start with any letters that you start to type. For example, if you start typing the word average, Excel displays a list of functions that start with the letters just typed.

If you don’t see a list of functions when you start typing, Formula AutoComplete might be turned off. On the Formulas page of the Excel Options dialog box, you’ll see in the Working with formula section a checkbox for Formula AutoComplete. If this check box is cleared, you should check it and then click OK to turn Formula AutoComplete on.

	Excel 2010 Essential TrainingBob Flisser
	Excel 2007 Essential TrainingLorna Daly
	Excel for Mac 2011 Essential TrainingCurt Frye
	Managing and Analyzing Data in ExcelDennis Taylor
	Excel 2010: Working with Dates and TimesAnne-Marie Conceptión
	Excel 2010: Financial Functions in DepthCurt Frye
	Excel 2010 Power ShortcutsDennis Taylor
	Cleaning Up Your Excel DataDennis Taylor
	Excel 2010: Advanced Formatting TechniquesDennis Taylor