Seasonal Savings: 20% off selected memberships for a limited time. Give now

lynda.com author Curt Frye

Excel formulas and functions

from Introduction to Formulas and Functions by Curt Frye
Click to launch the video trailer

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.

Suggested video courses
on Excel

Click to view the Excel 2010 Essential Training course page Excel 2010 Essential TrainingBob Flisser
Click to view the Excel 2007 Essential Training course page Excel 2007 Essential TrainingLorna Daly
Click to view the Excel for Mac 2011 Essential Training Training course page Excel for Mac 2011 Essential TrainingCurt Frye
Click to view the Managing and Analyzing Data in Excel with Facebook and Twitter course page Managing and Analyzing Data in ExcelDennis Taylor
Click to view the Excel 2010: Working with Dates and Times course page Excel 2010: Working with Dates and TimesAnne-Marie Conceptión
Click to view the Excel 2010: Financial Functions course page Excel 2010: Financial Functions in DepthCurt Frye
Click to view the Excel 2010 Power Shortcuts course page Excel 2010 Power ShortcutsDennis Taylor
Click to view the  Up Your Excel Data course page Cleaning Up Your Excel DataDennis Taylor
Click to view the Excel 2010: Advanced Formatting Techniques course page Excel 2010: Advanced Formatting TechniquesDennis Taylor
See all business courses »

Learn Excel formulas at lynda.com

lynda.com helps anyone learn software, creative, and business skills to achieve personal and professional goals.

  • unlimited access to a full-course library
  • high-quality, engaging video tutorials
  • just-in-time answers to questions
  • video tutorials by expert teachers
  • complete courses from start to finish
  • training for all levels and objectives

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.