Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Many Excel commentators, myself included, tend to use the terms function and formula as if they mean the same thing. In fact, functions and formulas are quite distinct. In this lesson, I'll describe the difference between formulas and functions and show you how both workbook elements help you summarize your data. To start out, a formula is a statement you enter into an Excel worksheet's cell that calculates a value. In Excel, you'll always begin a formula by typing an Equal sign. When you do, Excel knows that it should treat the rest of the text you type into the cell as part of a formula.
As an example, if you wanted to type in the formula =1+2 and then press Return, Excel would calculate the value 3. You can also refer to worksheet cells in a formula. In this worksheet, I have values in cells B3 and B4. I'm going to get rid of the formula in cell E2 by pressing the Delete key, and now I'll click in cell B5. If I wanted to find the sum of the two values in B3 and B4, I could type =B3+B4, and I tend to type in capital letters, but it doesn't matter; lowercase, uppercase - it's exactly the same.
You'll notice that when I typed in the formula, the cell reference for B4 is in green, and you'll also notice that cell B4 in the worksheet is outlined in green. B3 appears in blue, and that cell is outlined in blue. When I press Return, Excel calculates the sum of the values in these two cells. If you click the cell which contains the value, you'll see the value within the cell, but up on the formula bar, you will see the formula. Now let's say you want to perform a slightly more advanced calculation, such as finding the average of these two values.
To do that, you could type in this formula, which is =(B3+B4). There are two cells. We're finding an average, so we divide by 2. When you press Return, Excel finds the average of those two values, which is 1,537,000. The problem is that it takes a long time to type in that formula, and it's only practical to create formulas by hand for very simple mathematical operations. There is a much easier way, and that is to use functions. A function is a built-in set of steps, or an algorithm, that Excel can follow to find a result.
To find the average of the values in cells B3 and B4, you would type an Equal sign, indicating you're creating a formula, and then you can type in the function name AVERAGE. Once you've typed in the function name, you can type a left parenthesis, which is Shift+9 or capital 9, the way that I think of it. And then type in the reference to the cells that you want to average. In this case, it's B3. Then you use a colon to indicate a range of cells, and then B4. I'll cover cell references in more depth in a later movie in this chapter.
When you're done identifying the cells you want to summarize using the Average function, you close using a right parenthesis, press return and Excel finds the value. Those were the basics of how formulas and functions work in Excel. In future movies, we'll explore formulas and functions in more depth.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91119 Viewers
80 Video lessons · 138161 Viewers
59 Video lessons · 56978 Viewers
52 Video lessons · 70609 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.