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,
you would expect -100 to be the result. Instead, Excel returns 100 yet again. The reason is that Excel performs negation before exponentiation, meaning that Excel is converting 10 to -10 before the exponentiation, effectively calculating -10*- 10, which indeed equals 100. Using parentheses in the formula -(10^2) ensures that Excel calculates the exponent before negating the answer, giving you -100.
Remembering the order of operations and using parentheses where appropriate will ensure that you avoid miscalculating your data.
Remembering the order of operations and using parentheses where appropriate will ensure that you avoid miscalculating your data.
Using nested parentheses
It’s a best practice to use parentheses whenever you can in formulas, even if the use of parentheses seem to be superfluous. The liberal use of parentheses can not only help you avoid calculation errors but also help you better understand what the formula is doing.
You can even nest parentheses in formulas. Nesting means putting parentheses inside of other parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested operations first and works its way out. The following formula uses nested parentheses:
=((A1*B1)+(C1*D1))*E1
This formula has three sets of parentheses. Excel will evaluate the two nested sets of parentheses first, then will add those two results together. The added result will then be multiplied by the value in E1.
Note: Every open parenthesis must have a matching close parenthesis. You can imagine that when you start adding lots of parentheses to your formula, determining which open parenthesis has a matching close parenthesis can get difficult. For its part, Excel offers some help by color coding the parentheses while you’re in Edit mode. Matching open and close parentheses will havethe same color.
Relative versus Absolute Cell References
Imagine that you go to C1 and enter the formula =A1+B1. Your human eyes will define that as the value in A1 added to the value in B1. However, Excel, doesn’t see it that way. Because you entered the formula in cell C1, Excel reads the formula like this: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.
If you copy the formula =A1+B1 from cell C1 and paste it into cell D1, the formula in D1 will seem different to you. You will see =B1+C1. But to Excel, the formula is exactly the same: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.
By default, Excel considers every cell reference used in a formula as a relative reference. That is, it takes no heed of actual column row coordinates. Instead, it evaluates the cell references in terms of where they are relative to the cell the formula resides in.
This behavior is by design and works in situations in which you need the cell references to be adjusted when you copy the formula and paste it to other cells. For instance, the formula shown in cell C1 (see Figure 1-2) was copied and pasted down to the rows below. Note how Excel helps by automatically adjusting the cell references to match each row.
Figure 1-2: Relative references come in handy when you need to apply the same operation to values in different rows.
Note: When you copy and paste a formula, Excel automatically adjusts thecell references. However, if you cut and paste a formula, Excel assumes that you want to keep the same cell references and does not adjust them.
If you want to ensure that Excel does not adjust cell references when a formula is copied, you can lock the references down by turning them into absolute references. You turn them into absolute references by adding a dollar symbol ($) before the column and row reference. For instance, you can enter =$A$1+$B$1 to add the value in A1 to the value of B1.
By adding the dollar symbol to any cell reference and making that reference absolute, you can copy the formula anywhere else on the spreadsheet, and the formula will always point to A1 and B1.
Excel gives you the flexibility to make any part of your cell reference absolute. That is, you can specify that only the column part of your cell reference should be locked but the row part can adjust. Alternatively, you can specify that only the row part of your cell reference should be locked but the column part can adjust.
These different types of absolute references are commonly called Absolute, Row Absolute, and Column Absolute, and here’s how they work:
Absolute: When the formula is copied, the cell reference does not adjust at all. Example: $A$1
Row Absolute: When the formula is copied, the column part adjusts but the row part stays locked. Example: A$1
Column Absolu
It’s a best practice to use parentheses whenever you can in formulas, even if the use of parentheses seem to be superfluous. The liberal use of parentheses can not only help you avoid calculation errors but also help you better understand what the formula is doing.
You can even nest parentheses in formulas. Nesting means putting parentheses inside of other parentheses. When a formula contains nested parentheses, Excel evaluates the most deeply nested operations first and works its way out. The following formula uses nested parentheses:
=((A1*B1)+(C1*D1))*E1
This formula has three sets of parentheses. Excel will evaluate the two nested sets of parentheses first, then will add those two results together. The added result will then be multiplied by the value in E1.
Note: Every open parenthesis must have a matching close parenthesis. You can imagine that when you start adding lots of parentheses to your formula, determining which open parenthesis has a matching close parenthesis can get difficult. For its part, Excel offers some help by color coding the parentheses while you’re in Edit mode. Matching open and close parentheses will havethe same color.
Relative versus Absolute Cell References
Imagine that you go to C1 and enter the formula =A1+B1. Your human eyes will define that as the value in A1 added to the value in B1. However, Excel, doesn’t see it that way. Because you entered the formula in cell C1, Excel reads the formula like this: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.
If you copy the formula =A1+B1 from cell C1 and paste it into cell D1, the formula in D1 will seem different to you. You will see =B1+C1. But to Excel, the formula is exactly the same: Take the value in the cell two spaces to the left and add it to the value in the cell one space to the left.
By default, Excel considers every cell reference used in a formula as a relative reference. That is, it takes no heed of actual column row coordinates. Instead, it evaluates the cell references in terms of where they are relative to the cell the formula resides in.
This behavior is by design and works in situations in which you need the cell references to be adjusted when you copy the formula and paste it to other cells. For instance, the formula shown in cell C1 (see Figure 1-2) was copied and pasted down to the rows below. Note how Excel helps by automatically adjusting the cell references to match each row.
Figure 1-2: Relative references come in handy when you need to apply the same operation to values in different rows.
Note: When you copy and paste a formula, Excel automatically adjusts thecell references. However, if you cut and paste a formula, Excel assumes that you want to keep the same cell references and does not adjust them.
If you want to ensure that Excel does not adjust cell references when a formula is copied, you can lock the references down by turning them into absolute references. You turn them into absolute references by adding a dollar symbol ($) before the column and row reference. For instance, you can enter =$A$1+$B$1 to add the value in A1 to the value of B1.
By adding the dollar symbol to any cell reference and making that reference absolute, you can copy the formula anywhere else on the spreadsheet, and the formula will always point to A1 and B1.
Excel gives you the flexibility to make any part of your cell reference absolute. That is, you can specify that only the column part of your cell reference should be locked but the row part can adjust. Alternatively, you can specify that only the row part of your cell reference should be locked but the column part can adjust.
These different types of absolute references are commonly called Absolute, Row Absolute, and Column Absolute, and here’s how they work:
Absolute: When the formula is copied, the cell reference does not adjust at all. Example: $A$1
Row Absolute: When the formula is copied, the column part adjusts but the row part stays locked. Example: A$1
Column Absolu
te: When the formula is copied, the column part stays locked but the row part adjusts. Example: $A1
Tip: Instead of manually entering the dollar symbols, you can easily toggle between the possible reference styles by highlighting the cell reference in your formula and pressing the F4 key.
Tip: Instead of manually entering the dollar symbols, you can easily toggle between the possible reference styles by highlighting the cell reference in your formula and pressing the F4 key.
Using External Cell References
You may find that you have data in one workbook that you want to reference in a formula within another workbook. In such a situation, you can create a link between the workbooks using an external cell reference. An external cell reference is nothing more than a cell reference that resides in an outside workbook. The benefit of using an external cell reference is that when the data in the outside workbook changes, Excel automatically updates the value returned by the external cell reference.
Creating an external cell reference is relatively easy. Open both workbooks (the workbook that you’re currently working in and the outside workbook). While entering a formula in the workbook you’re currently working in, click the cell that you want to reference in the outside workbook.
As you can see in Figure 1-3, you’ll immediately be able to tell that the cell reference is an external reference due to the full file path and sheet name prefixing the cell reference.
Figure 1-3: An example of an external cell reference.
All external cell references have the same component parts, as follows:
'File Path[Workbook Name]Sheet Name'!Cell Reference
Here’s a breakdown of these parts:
File Path: This part of the cell reference points to the drive and directory in which the workbook is located.
Workbook Name: This part of the cell reference points to the name of the workbook. This part is always enclosed in brackets ([ ]) and always includes the file extension (.xlsx, .xls, .xslm, and so on).
Sheet Name: This part of the cell reference points to the name of the sheet in which the referenced cell resides.
Cell Reference: This part of the cell reference points to the actual cell that is being referenced.
Formula Calculation Modes
By default, Excel is set to recalculate automatically. If you change any of the cells referenced in a particular formula, Excel automatically recalculates that formula so that it returns a correct result based on the changes in its cell references. Also, if the formula that it recalculates is used as a cell reference in other formulas, every formula that is dependent on the newly recalculated formula is also recalculated.
You can imagine that as your spreadsheet grows and gets populated with interweaving formulas, Excel will be constantly recalculating. You may even find that when working with worksheets that contain many complex formulas, Excel slows dramatically as it tries to keep up with all the recalculating it needs to do.
In these cases, you can choose to set Excel’s calculation mode to Manual. You can do this by clicking the Formulas tab in the Excel Ribbon and selecting Calculation Options⇒Manual.
While working in Manual calculation mode, none of your formulas will recalculate until you trigger the calculation yourself. You have several ways to trigger a recalculation:
Click the Calculate Now command on the Formulas tab to recalculate all formulas in the entire workbook.
Click the Calculate Sheet command on the Formulas tab to recalculate only the formulas on the currently active sheet.
Click the Calculate link on the status bar to recalculate the entire workbook.
Press F9 to recalculate all formulas in the entire workbook.
Press Shift+F9 to recalculate only the formulas on the currently active sheet.
You may find that you have data in one workbook that you want to reference in a formula within another workbook. In such a situation, you can create a link between the workbooks using an external cell reference. An external cell reference is nothing more than a cell reference that resides in an outside workbook. The benefit of using an external cell reference is that when the data in the outside workbook changes, Excel automatically updates the value returned by the external cell reference.
Creating an external cell reference is relatively easy. Open both workbooks (the workbook that you’re currently working in and the outside workbook). While entering a formula in the workbook you’re currently working in, click the cell that you want to reference in the outside workbook.
As you can see in Figure 1-3, you’ll immediately be able to tell that the cell reference is an external reference due to the full file path and sheet name prefixing the cell reference.
Figure 1-3: An example of an external cell reference.
All external cell references have the same component parts, as follows:
'File Path[Workbook Name]Sheet Name'!Cell Reference
Here’s a breakdown of these parts:
File Path: This part of the cell reference points to the drive and directory in which the workbook is located.
Workbook Name: This part of the cell reference points to the name of the workbook. This part is always enclosed in brackets ([ ]) and always includes the file extension (.xlsx, .xls, .xslm, and so on).
Sheet Name: This part of the cell reference points to the name of the sheet in which the referenced cell resides.
Cell Reference: This part of the cell reference points to the actual cell that is being referenced.
Formula Calculation Modes
By default, Excel is set to recalculate automatically. If you change any of the cells referenced in a particular formula, Excel automatically recalculates that formula so that it returns a correct result based on the changes in its cell references. Also, if the formula that it recalculates is used as a cell reference in other formulas, every formula that is dependent on the newly recalculated formula is also recalculated.
You can imagine that as your spreadsheet grows and gets populated with interweaving formulas, Excel will be constantly recalculating. You may even find that when working with worksheets that contain many complex formulas, Excel slows dramatically as it tries to keep up with all the recalculating it needs to do.
In these cases, you can choose to set Excel’s calculation mode to Manual. You can do this by clicking the Formulas tab in the Excel Ribbon and selecting Calculation Options⇒Manual.
While working in Manual calculation mode, none of your formulas will recalculate until you trigger the calculation yourself. You have several ways to trigger a recalculation:
Click the Calculate Now command on the Formulas tab to recalculate all formulas in the entire workbook.
Click the Calculate Sheet command on the Formulas tab to recalculate only the formulas on the currently active sheet.
Click the Calculate link on the status bar to recalculate the entire workbook.
Press F9 to recalculate all formulas in the entire workbook.
Press Shift+F9 to recalculate only the formulas on the currently active sheet.
Leveraging Excel Functions
Functions are essentially canned formulas that Excel provides as a way to accomplish common tasks. Some Excel functions perform simple calculations, saving you from having to enter the formula yourself. Other functions perform complex actions that would be impossible to perform with simple formulas alone.
As you go through the chapters in this book, you explore many of the most useful Excel functions. For now, you start with a basic understanding of the role Excel functions play in your formulas.
Why to use Excel functions
One of the key benefits of using Excel functions is that they help simplify your formulas. For example, if you wanted to get the average of the values in cell A1, A2, and A3, you could enter this formula:
=(A1+A2+A3)/3
This particular formula isn’t too bad, but what if you had to get the average of 100 values? How cumbersome would that formula be to create and manage?
Luckily, Excel has an AVERAGE function. With the AVERAGE function, you can simply enter this formula:
=AVERAGE(A1:A3)
If you had to get the average of 100 values, you could simply expand the range:
=AVERAGE(A1:A100)
Another key benefit of using functions is that they help you accomplish tasks that would be impossible with standard formulas. For instance, imagine that you wanted a couple of formulas that would automatically return the largest and smallest numbers in a range of cells. Sure, if your range was small enough, you could eyeball the largest and smallest numbers. But that’s hardly automated.
There is no nonfunction-driven formula you could possibly enter that would automatically return the largest or smallest number in a range.
Excel’s MAX and MIN functions, however, make short work of this task. The MAX function returns the largest number, whereas the MIN function returns the smallest.
=MAX(A1:A100)
=MIN(A1:A100)
Functions can also help save time by helping you automate tasks that would take you hours to accomplish manually. For example, say that you needed to extract the first 10 characters of a customer number. How long do you think it would take you to go through a table of 1,000 records and get a list of customer numbers that contains only the first 10 characters?
The LEFT function can help here by pulling out the left 10 characters:
=LEFT(A1,10)
You can simply enter this formula for the first row of your table and then copy it down to as many rows you need.
Understanding function arguments
Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.
=AVERAGE(A1:A100)
Any input you give to a function is called an argument. The basic construct of a function is:
Function_Name(argument1, argument2,…)
To use a function, you enter its name, open parenthesis, the needed arguments, and then the close parenthesis. The number of arguments needed varies from function to function.
Using functions with no arguments
Some functions, such as the NOW() function, don’t require any arguments. To get the current date and time, you can simply enter a formula like this:
=NOW()
Note that even though no arguments are required, you still need to include the open and close parentheses.
Using functions with one or more required arguments
Some functions require one or more arguments. The LARGE function, for instance, returns the nth largest number in a range of cells. This function requires two arguments: a cell reference to a range of numeric values and a rank number. To get the third largest value in range A1 through A100, you can enter:
=LARGE(A1:A100,3)
Note that each argument is separated by a comma. This is true regardless of how many arguments you enter. Each argument must be separated by a comma.
Using functions with both required and optional arguments
Many Excel functions, such as the NETWORKDAYS function, allow for optional arguments in addition to the required arguments. The NETWORKDAYS function returns the number of workdays (days excluding wee
Functions are essentially canned formulas that Excel provides as a way to accomplish common tasks. Some Excel functions perform simple calculations, saving you from having to enter the formula yourself. Other functions perform complex actions that would be impossible to perform with simple formulas alone.
As you go through the chapters in this book, you explore many of the most useful Excel functions. For now, you start with a basic understanding of the role Excel functions play in your formulas.
Why to use Excel functions
One of the key benefits of using Excel functions is that they help simplify your formulas. For example, if you wanted to get the average of the values in cell A1, A2, and A3, you could enter this formula:
=(A1+A2+A3)/3
This particular formula isn’t too bad, but what if you had to get the average of 100 values? How cumbersome would that formula be to create and manage?
Luckily, Excel has an AVERAGE function. With the AVERAGE function, you can simply enter this formula:
=AVERAGE(A1:A3)
If you had to get the average of 100 values, you could simply expand the range:
=AVERAGE(A1:A100)
Another key benefit of using functions is that they help you accomplish tasks that would be impossible with standard formulas. For instance, imagine that you wanted a couple of formulas that would automatically return the largest and smallest numbers in a range of cells. Sure, if your range was small enough, you could eyeball the largest and smallest numbers. But that’s hardly automated.
There is no nonfunction-driven formula you could possibly enter that would automatically return the largest or smallest number in a range.
Excel’s MAX and MIN functions, however, make short work of this task. The MAX function returns the largest number, whereas the MIN function returns the smallest.
=MAX(A1:A100)
=MIN(A1:A100)
Functions can also help save time by helping you automate tasks that would take you hours to accomplish manually. For example, say that you needed to extract the first 10 characters of a customer number. How long do you think it would take you to go through a table of 1,000 records and get a list of customer numbers that contains only the first 10 characters?
The LEFT function can help here by pulling out the left 10 characters:
=LEFT(A1,10)
You can simply enter this formula for the first row of your table and then copy it down to as many rows you need.
Understanding function arguments
Most of the functions found in Excel require some input or information in order to calculate correctly. For example, to use the AVERAGE function, you need to give it a range of numbers to average.
=AVERAGE(A1:A100)
Any input you give to a function is called an argument. The basic construct of a function is:
Function_Name(argument1, argument2,…)
To use a function, you enter its name, open parenthesis, the needed arguments, and then the close parenthesis. The number of arguments needed varies from function to function.
Using functions with no arguments
Some functions, such as the NOW() function, don’t require any arguments. To get the current date and time, you can simply enter a formula like this:
=NOW()
Note that even though no arguments are required, you still need to include the open and close parentheses.
Using functions with one or more required arguments
Some functions require one or more arguments. The LARGE function, for instance, returns the nth largest number in a range of cells. This function requires two arguments: a cell reference to a range of numeric values and a rank number. To get the third largest value in range A1 through A100, you can enter:
=LARGE(A1:A100,3)
Note that each argument is separated by a comma. This is true regardless of how many arguments you enter. Each argument must be separated by a comma.
Using functions with both required and optional arguments
Many Excel functions, such as the NETWORKDAYS function, allow for optional arguments in addition to the required arguments. The NETWORKDAYS function returns the number of workdays (days excluding wee
kends) between a given start date and end data.
To use the NETWORKDAYS function, you need to provide, at minimum, the start and end dates. These are the required arguments.
The following formula gives you the answer 260, meaning that there are 260 workdays between January 1, 2014, and December 31, 2014:
=NETWORKDAYS("1/1/2014", "12/31/2014")
The NETWORKDAYS function also allows for an optional argument that lets you pass a range containing a list of holiday dates. The function treats each date in the optional range as a nonworkday, effectively returning a different result (255 workdays between January 1, 2014, and December 31, 2014, taking into account holiday dates).
=NETWORKDAYS("1/1/2014", "12/31/2014", A1:A5)
Don’t be too concerned with completely understanding the NETWORKDAYS function. The take-away here is that when a function has required and optional arguments, you can elect to use the function with just the required arguments, or you can take advantage of the function’s additional utility by providing the optional arguments.
To use the NETWORKDAYS function, you need to provide, at minimum, the start and end dates. These are the required arguments.
The following formula gives you the answer 260, meaning that there are 260 workdays between January 1, 2014, and December 31, 2014:
=NETWORKDAYS("1/1/2014", "12/31/2014")
The NETWORKDAYS function also allows for an optional argument that lets you pass a range containing a list of holiday dates. The function treats each date in the optional range as a nonworkday, effectively returning a different result (255 workdays between January 1, 2014, and December 31, 2014, taking into account holiday dates).
=NETWORKDAYS("1/1/2014", "12/31/2014", A1:A5)
Don’t be too concerned with completely understanding the NETWORKDAYS function. The take-away here is that when a function has required and optional arguments, you can elect to use the function with just the required arguments, or you can take advantage of the function’s additional utility by providing the optional arguments.
Finding out which arguments are needed for a given function
An easy way to discover the arguments needed for a given function is to begin typing that function into a cell. Click a cell, enter the equal sign, enter the function name, and then enter an open parenthesis. Recognizing that you are entering a function, Excel activates a tooltip (see Figure 1-4) that shows you all the arguments for the function. Any argument that is shown in brackets ([ ]) is an optional argument. All others shown without the brackets are required arguments.
Figure 1-4: The function tooltip is a handy way to find out the required and optional arguments for a function.
Getting Help from the Insert Function Wizard
If you find that you’re stuck on which function to use, or aren’t sure of the syntax needed for a particular function, you can use Excel’s Insert Function feature.
Place your cursor in the cell you want to enter a function in and click the Insert Function command, found on the Formulas tab. Alternatively, you can press Shift+F3 to call up the Insert Function dialog box, shown in Figure 1-5. The idea here is to find the function you need and double-click it.
Figure 1-5: The Insert Function dialog box.
If you’re not sure which function you need, you can use the search field at the top of the dialog box to find the most appropriate function based on a keyword. Simply enter one or more keywords and click the Go button. The list of functions will change to display those that best match your search criteria. For example, entering the search term “loan payment” results in functions that perform loan calculations.
If you need to use a function that you’ve recently implemented, you can skip the search feature and simply select “Most Recently Used” from the category drop-down box.
The list of functions changes to display those you’ve recently utilized.
You can also use the category drop-down box to select a category of functions. For instance, selecting “Statistical” from the category drop-down box displays all the statistical functions.
When you find the function that you want to use, double-click it to activate the Function Arguments dialog box (see Figure 1-6). This dialog box serves as a kind of wizard, guiding you through the arguments needed for the selected function.
Figure 1-6: The Function Arguments dialog box guides you through creating your Excel functions.
For each required argument, enter an appropriate value or cell reference in the respective input boxes. Note that the required arguments are always listed first and shown in bold type. Do the same thing for any optional argument that you want to utilize.
Click the OK button to apply your newly configured function to your target cell.
An easy way to discover the arguments needed for a given function is to begin typing that function into a cell. Click a cell, enter the equal sign, enter the function name, and then enter an open parenthesis. Recognizing that you are entering a function, Excel activates a tooltip (see Figure 1-4) that shows you all the arguments for the function. Any argument that is shown in brackets ([ ]) is an optional argument. All others shown without the brackets are required arguments.
Figure 1-4: The function tooltip is a handy way to find out the required and optional arguments for a function.
Getting Help from the Insert Function Wizard
If you find that you’re stuck on which function to use, or aren’t sure of the syntax needed for a particular function, you can use Excel’s Insert Function feature.
Place your cursor in the cell you want to enter a function in and click the Insert Function command, found on the Formulas tab. Alternatively, you can press Shift+F3 to call up the Insert Function dialog box, shown in Figure 1-5. The idea here is to find the function you need and double-click it.
Figure 1-5: The Insert Function dialog box.
If you’re not sure which function you need, you can use the search field at the top of the dialog box to find the most appropriate function based on a keyword. Simply enter one or more keywords and click the Go button. The list of functions will change to display those that best match your search criteria. For example, entering the search term “loan payment” results in functions that perform loan calculations.
If you need to use a function that you’ve recently implemented, you can skip the search feature and simply select “Most Recently Used” from the category drop-down box.
The list of functions changes to display those you’ve recently utilized.
You can also use the category drop-down box to select a category of functions. For instance, selecting “Statistical” from the category drop-down box displays all the statistical functions.
When you find the function that you want to use, double-click it to activate the Function Arguments dialog box (see Figure 1-6). This dialog box serves as a kind of wizard, guiding you through the arguments needed for the selected function.
Figure 1-6: The Function Arguments dialog box guides you through creating your Excel functions.
For each required argument, enter an appropriate value or cell reference in the respective input boxes. Note that the required arguments are always listed first and shown in bold type. Do the same thing for any optional argument that you want to utilize.
Click the OK button to apply your newly configured function to your target cell.