Learn the difference between formulas and functions, and how both workbook elements help you summarize your data.
- [Narrator] Many Excel users, myself included, and I've been working with the program for about 30 years, tend to use the terms function and formula as if they meant the same thing. In fact, functions and formulas are quite distinct. In this movie, I will give you an overview of the differences between formulas and functions and what to expect when you work with both. To start, a formula is a statement that calculates a value, and it has one distinguishing feature and that is that it always begins with an equal sign.
As an example, you might want to add 14 and 27. So, you would say, equal 14 plus 27, and it's the equal sign that tells Excel to expect a formula. Note that the equal sign must be the first character in the cell. If you type a space and then an equal sign, Excel will treat the cell's contents as text rather than a formula. In addition to typing in numbers directly, you can also refer to worksheet cells. If you want to refer to cells on the current worksheet, you just type in the cell address.
For example, you might divide the value in D3 by the value in D4. You can also work with values from another worksheet. So, you might divide the value in cell D3 on the current worksheet by the value on Sheet1 in cell D4. You can also refer to cells in other workbooks. So, for example, you might look in the Tracing workbook on Sheet1 and then look at cell D9. Two things to note about writing references to values on other worksheets and workbooks.
The first is that the name of the worksheet is followed by an exclamation point and, also, the name of the workbook is in a pair of square brackets. If there are any spaces in the worksheet name or in the workbook name, then you would enclose the text string, in this case Sheet1 or Tracing.xlsx in double quotes. Now, moving onto functions. A function is a built-in calculation. They also follow a known syntax and, what that means, it's like grammar.
You're expected to enter values in in a specific order. Those inputs are called arguments and are used to calculate the result. For example, let's take a look at the formula =SUM(D3:D5, F3:F5). What this formula does is find the sum of the values in the cell range D3 to D5 as well as the cell range F3 to F5.
As before, the equal sign signifies the start of the formula. SUM is the function name and, again, that is a built-in calculation and D3 to D5 and F3 to F5 are the arguments. Those are the cell ranges that we're going to be working with. And also, to note, in help and tool tips, optional arguments are displayed in square brackets. So, for example, in some formulas you have some arguments that you can enter or you can leave them out. If those arguments can be left out, they're optional and they are displayed in square brackets in the help file and also in tool tips.
Think of the distinction between formula and function this way. A formula is a calculation you type in an Excel worksheet cell, while a function is a built-in calculation that gives you an answer based on the values you provide.
- Using operators
- Use cell references in formulas
- Formulas: SUM, AVERAGE, MIN, MAX, MEDIAN, and MODE
- Counting cells
- Summarizing cells conditionally
- Rounding cell values up and down
- Working with list data
- Finding data using VLOOKUP formulas
- Connecting to an external data source
- Cleaning up imported data
- Validating data using rules
- Tracing precedents and dependents
- Identifying errors
- Finding target values using Goal Seek