Introducing Excel Formulas
Microsoft Excel is, at its core, a calculation engine. Like a calculator, it accepts a question (such as 2+2) and gives you an answer. When you’re working with a calculator, these questions are called mathematical operations. In Excel, you call these formulas.
Excel allows you to use formulas to perform not only mathematical operations but also a myriad of other complex actions, such as parsing textual values, searching for certain values in a range of data, performing recursive calculations, and much more.
To leverage the full power of Excel formulas, you need to understand how Excel formulas work as well as some of the ground rules for working with formulas. The goal of this chapter, therefore, is for you to get acquainted with the fundamentals of using Excel formulas.
Tip: Feel free to skip over this chapter if you already have a solid understanding of formula mechanics and want to get right into real-world examples
Creating and Editing Excel Formulas
An Excel spreadsheet is simply a collection of cells that you can use to enter data. Each cell is given a name based on its position in the spreadsheet. The top-leftmost cell is called cell A1. It’s the cell located in column A, row 1. When you enter a value in cell A1 (for example, the number 5), that cell’s value becomes 5. If you enter the number 10 in cell B1, that cell’s value becomes 10. You can use then use these values in a formula.
For instance, you can click cell C1 and begin typing =A1+B1, as shown in Figure 1-1. Note how you can see your formula in both the cell you’re typing in and the Formula Bar above the column headings.
Figure 1-1: Entering a basic formula.
After you press the Enter key on your keyboard, Excel recognizes what you’re asking and performs the calculation that gives you the result of 15 (5+10 = 15).
The reason Excel recognizes that you were entering a formula and not just another value is the equal sign (=). Entering the equal sign followed by other values tells Excel that you’re starting a formula. In this example, we used what are known as cell references (A1 and B1). These cell references are just one of the types of values that you can use in your formulas.
Excel formulas accept the following types of values:
Constants: You can use hard-coded numbers directly in a formula. For example, you can enter =5+10 directly into a cell to get the answer 15.
Operators: These include symbols that perform addition (+), symbols that perform multiplication (*), symbols that compare values (>), and symbols that join values together (&). For example, entering =15>10 into a cell returns TRUE as the result because 15 is indeed greater than 10.
Cell references: These include any value that points back to a single cell or range of cells. As you’ve already seen, entering =A1+B1 in a cell tells Excel to use the values in those two cells in the formula.
Text strings: Any text string can be used as an argument in a formula as long as it’s wrapped in quotation marks. For example, entering =“Microsoft”&”Excel” in any cell results in the joined text Microsoft Excel.
Methods for entering formulas
You have several ways to actually enter a formula into a cell:
Enter the formula directly into a cell: Simply click a cell, begin typing your formula, and then press the Enter key on your keyboard.
Enter the formula into the Formula Bar: Click inside the Formula Bar found above the column headers, type your formula, and then press the Enter key on your keyboard.
Enter the formula using the mouse: If your formula involves cell references, you can use the mouse to help reduce the amount of typing you need to do. For instance, instead of typing =A1+B1, you can type the equal symbol, use your mouse to click cell A1, type the plus symbol, use the mouse to click cell B1, and then press the Enter key.
Enter the formula using the arrow keys: If your formula involves cell references, you can use the arrow keys on your keyboard to help reduce the amount of typing you need to do. For instance, instead of typing =A1+B1, you can type the
Microsoft Excel is, at its core, a calculation engine. Like a calculator, it accepts a question (such as 2+2) and gives you an answer. When you’re working with a calculator, these questions are called mathematical operations. In Excel, you call these formulas.
Excel allows you to use formulas to perform not only mathematical operations but also a myriad of other complex actions, such as parsing textual values, searching for certain values in a range of data, performing recursive calculations, and much more.
To leverage the full power of Excel formulas, you need to understand how Excel formulas work as well as some of the ground rules for working with formulas. The goal of this chapter, therefore, is for you to get acquainted with the fundamentals of using Excel formulas.
Tip: Feel free to skip over this chapter if you already have a solid understanding of formula mechanics and want to get right into real-world examples
Creating and Editing Excel Formulas
An Excel spreadsheet is simply a collection of cells that you can use to enter data. Each cell is given a name based on its position in the spreadsheet. The top-leftmost cell is called cell A1. It’s the cell located in column A, row 1. When you enter a value in cell A1 (for example, the number 5), that cell’s value becomes 5. If you enter the number 10 in cell B1, that cell’s value becomes 10. You can use then use these values in a formula.
For instance, you can click cell C1 and begin typing =A1+B1, as shown in Figure 1-1. Note how you can see your formula in both the cell you’re typing in and the Formula Bar above the column headings.
Figure 1-1: Entering a basic formula.
After you press the Enter key on your keyboard, Excel recognizes what you’re asking and performs the calculation that gives you the result of 15 (5+10 = 15).
The reason Excel recognizes that you were entering a formula and not just another value is the equal sign (=). Entering the equal sign followed by other values tells Excel that you’re starting a formula. In this example, we used what are known as cell references (A1 and B1). These cell references are just one of the types of values that you can use in your formulas.
Excel formulas accept the following types of values:
Constants: You can use hard-coded numbers directly in a formula. For example, you can enter =5+10 directly into a cell to get the answer 15.
Operators: These include symbols that perform addition (+), symbols that perform multiplication (*), symbols that compare values (>), and symbols that join values together (&). For example, entering =15>10 into a cell returns TRUE as the result because 15 is indeed greater than 10.
Cell references: These include any value that points back to a single cell or range of cells. As you’ve already seen, entering =A1+B1 in a cell tells Excel to use the values in those two cells in the formula.
Text strings: Any text string can be used as an argument in a formula as long as it’s wrapped in quotation marks. For example, entering =“Microsoft”&”Excel” in any cell results in the joined text Microsoft Excel.
Methods for entering formulas
You have several ways to actually enter a formula into a cell:
Enter the formula directly into a cell: Simply click a cell, begin typing your formula, and then press the Enter key on your keyboard.
Enter the formula into the Formula Bar: Click inside the Formula Bar found above the column headers, type your formula, and then press the Enter key on your keyboard.
Enter the formula using the mouse: If your formula involves cell references, you can use the mouse to help reduce the amount of typing you need to do. For instance, instead of typing =A1+B1, you can type the equal symbol, use your mouse to click cell A1, type the plus symbol, use the mouse to click cell B1, and then press the Enter key.
Enter the formula using the arrow keys: If your formula involves cell references, you can use the arrow keys on your keyboard to help reduce the amount of typing you need to do. For instance, instead of typing =A1+B1, you can type the
equal symbol, use the arrow keys on your keyboard to move the cursor to cell A1, type the plus (+) symbol, use the arrow keys on your keyboard to move the cursor to cell B1, and then press the Enter key.
Editing a formula
If you find that you need to edit a formula, you can do so in three ways:
Edit directly in the Formula Bar: Select the cell that contains your formula, go up to the Formula Bar, and start editing the formula there.
Double-click the formula cell: You can edit the formula directly in the cell it’s in by double-clicking the cell. Double-clicking the cell gets you into Edit mode, where you can edit the formula as needed.
Press F2: Select the cell that contains your formula and then press F2 on your keyboard to get into Edit mode. As stated previously, you can then edit the formula as needed.
Using Formula Operators
As mentioned earlier in this chapter, you can use symbols known as operators to define the operation your formula will accomplish. Some of these operators are mathematical operators that simply add, subtract, and multiply. Other operators allow you to perform more complex actions such as comparing values. For example, you can determine whether an employee has met his or her quota by using a comparison operator to see if actual sales are greater than or equal to a predetermined quota.
Table 1-1 lists the operators you can use in your Excel formulas.
Table 1-1: Operators for Excel Formulas
+ The plus symbol adds two or more numeric values.
- The hyphen symbol subtracts two or more numeric values.
/ The forward slash symbol divides two or more numeric values.
* The asterisk symbol divides two or more numeric values.
% The percent symbol indicates a numeric percent. Entering a percent sign after a whole number divides the number by 100 and formats the cell as a percentage.
& The ampersand symbol is used to join or concatenate two or more textual values.
^ The carat symbol is used as an exponentiation operator.
= The equal symbol is used to evaluate whether one value is equal to another value
> The greater-than symbol is used to evaluate whether one value is greater than another value.
< The less-than symbol is used to evaluate whether one value is less than another value.
>= The greater-than symbol used in conjunction with the equal symbol evaluates whether one value is greater than or equal to another value.
<= The less-than symbol used in conjunction with the equal symbol evaluates whether one value is less than or equal to another value.
<> The less-than symbol used in conjunction with the greater-than symbol evaluates whether one value is not equal to another value.
Understanding the order of operator precedence
It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.
Consider this basic example. The correct answer to (2+3)*4 is 20. However, if you leave off the parentheses, as in 2+3*4, Excel performs the calculation like this: 3*4 =
12 + 2 = 14. Excel’s default order of operator precedence mandates that Excel perform multiplication before addition. Entering 2+3*4 gives you the wrong answer. Because Excel evaluates and performs all calculations in parentheses first, placing 2+3 inside parentheses ensures the correct answer.
The order of operations for Excel is as follows:
Evaluate items in parentheses.
Evaluate ranges (:).
Evaluate intersections (spaces).
Evaluate unions (,).
Perform negation (-).
Convert percentages (%).
Perform exponentiation (^).
Perform multiplication (*) and division (/), which are of equal precedence.
Perform addition (+) and subtraction (-), which are of equal precedence.
Evaluate text operators (&).
Perform comparisons (=, <>, <=, >=).
Note: Operations that are equal in precedence are performed left to right. Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the 2nd power as a formula, Excel returns 100 as the answer. If you enter -10^2,
If you find that you need to edit a formula, you can do so in three ways:
Edit directly in the Formula Bar: Select the cell that contains your formula, go up to the Formula Bar, and start editing the formula there.
Double-click the formula cell: You can edit the formula directly in the cell it’s in by double-clicking the cell. Double-clicking the cell gets you into Edit mode, where you can edit the formula as needed.
Press F2: Select the cell that contains your formula and then press F2 on your keyboard to get into Edit mode. As stated previously, you can then edit the formula as needed.
Using Formula Operators
As mentioned earlier in this chapter, you can use symbols known as operators to define the operation your formula will accomplish. Some of these operators are mathematical operators that simply add, subtract, and multiply. Other operators allow you to perform more complex actions such as comparing values. For example, you can determine whether an employee has met his or her quota by using a comparison operator to see if actual sales are greater than or equal to a predetermined quota.
Table 1-1 lists the operators you can use in your Excel formulas.
Table 1-1: Operators for Excel Formulas
+ The plus symbol adds two or more numeric values.
- The hyphen symbol subtracts two or more numeric values.
/ The forward slash symbol divides two or more numeric values.
* The asterisk symbol divides two or more numeric values.
% The percent symbol indicates a numeric percent. Entering a percent sign after a whole number divides the number by 100 and formats the cell as a percentage.
& The ampersand symbol is used to join or concatenate two or more textual values.
^ The carat symbol is used as an exponentiation operator.
= The equal symbol is used to evaluate whether one value is equal to another value
> The greater-than symbol is used to evaluate whether one value is greater than another value.
< The less-than symbol is used to evaluate whether one value is less than another value.
>= The greater-than symbol used in conjunction with the equal symbol evaluates whether one value is greater than or equal to another value.
<= The less-than symbol used in conjunction with the equal symbol evaluates whether one value is less than or equal to another value.
<> The less-than symbol used in conjunction with the greater-than symbol evaluates whether one value is not equal to another value.
Understanding the order of operator precedence
It’s important to understand that when you create a formula with several operators, Excel evaluates and performs the calculation in a specific order. For instance, Excel always performs multiplication before addition. This order is called the order of operator precedence. You can force Excel to override the built-in operator precedence by using parentheses to specify which operation to evaluate first.
Consider this basic example. The correct answer to (2+3)*4 is 20. However, if you leave off the parentheses, as in 2+3*4, Excel performs the calculation like this: 3*4 =
12 + 2 = 14. Excel’s default order of operator precedence mandates that Excel perform multiplication before addition. Entering 2+3*4 gives you the wrong answer. Because Excel evaluates and performs all calculations in parentheses first, placing 2+3 inside parentheses ensures the correct answer.
The order of operations for Excel is as follows:
Evaluate items in parentheses.
Evaluate ranges (:).
Evaluate intersections (spaces).
Evaluate unions (,).
Perform negation (-).
Convert percentages (%).
Perform exponentiation (^).
Perform multiplication (*) and division (/), which are of equal precedence.
Perform addition (+) and subtraction (-), which are of equal precedence.
Evaluate text operators (&).
Perform comparisons (=, <>, <=, >=).
Note: Operations that are equal in precedence are performed left to right. Here is another widely demonstrated example. If you enter 10^2, which represents the exponent 10 to the 2nd power as a formula, Excel returns 100 as the answer. If you enter -10^2,