# Excel 2007: Introduction to Formulas and Functions

## with Curt Frye

Excel 2007: Introduction to Formulas and Functions shows how to build impressive spreadsheets with the application's built-in capabilities. Excel expert and Microsoft Most Valuable Professional Curt Frye shares his vast knowledge of Excel, demonstrating how to build various arithmetic and statistical analysis formulas. Curt explains how to summarize values in sets of cells, create conditional formulas using IF and other related functions, and use validation rules to make data entry more accurate. He even teaches how to search for data within a list or table using the VLOOKUP and HLOOKUP functions. Exercise files accompany this course.
Topics include:
• Copying and pasting the value results of a formula
• Creating formulas to count cells that meet certain conditions
• Connecting Excel to an external data source
• Building a two-input data table
• Summarizing data using an Excel table
author
Curt Frye
subject
software
Excel 2007
level
Intermediate
duration
2h 26m
released
Nov 11, 2009

Keep up with news, tips, and latest courses.

• ### FAQs

Search the closed captioning text for this course by entering the keyword you’d like to search, or browse the closed captioning text by selecting the chapter name below and choosing the video title you’d like to review.

Introduction
Welcome
 00:00 (Music playing.) 00:03 Hi! I'm Curt Frye. 00:05 Welcome to Excel 2007: Introduction to Formulas and Functions. 00:09 In this course, I'll show you how to use formulas and built-in functions to 00:12 summarize your data effectively. 00:14 I'll begin by explaining the difference between formulas and functions. 00:18 Then using that knowledge as a base, I'll demonstrate how to create formulas 00:21 that find the sum or average of groups of numbers. 00:23 I'll also show you how to discover more information by using Excel Help system. 00:28 We'll enhance your ability to analyze your data by showing you how to summarize 00:31 cell data that meets conditions you define, round cell values up and down, and 00:35 summarize your data using named ranges, data lists, and Excel tables. 00:39 I'll show you how to find data in your worksheets using LOOKUP functions, 00:43 create data validation rules to make data entry more accurate, find and fix formal 00:47 errors and perform What-If analysis by creating alternative data scenarios that 00:51 don't disturb your original workbook. 00:53 In short, I'll show you how to summarize and analyze data using Excel's powerful 00:57 array of formulas, functions, and other analytical capabilities. 01:01 Let's get started with Excel 2007: Introduction to Formulas and Functions. Collapse this transcript
Using the exercise files
 00:00 If you are a premium member of the lynda.com Online Training Library, you have 00:04 access to the Exercise Files used throughout this title. 00:07 Exercise Files for this title are arranged by chapter. 00:10 I have six chapters and then all you need to do is double-click the chapter 00:14 folder and you'll see the files that are used in that chapter. 00:18 I have my Exercise Files folder on my desktop, but you can put it anywhere you want. 00:23 If you're a monthly or annual subscriber to Lynda.com, you don't have access to 00:27 the Exercise Files, but you can follow along with the movies or create your own 00:31 assets and work from there. Collapse this transcript
1. Introducing Excel 2007 Functions and Formulas
Familiarizing yourself with functions and formulas
 00:00 Many Excel commentators, myself included, tend to use the terms function and 00:04 formula as if they meant the same thing. 00:07 In fact, functions and formulas are quite distinct. 00:10 In this lesson, I'll describe the difference between formulas and functions and 00:13 show you how both work to help you summarize your data. 00:17 A formula is a statement you enter into an Excel worksheet cell that 00:21 calculates a value. 00:22 In Excel, you always begin a formula by typing an equal sign. 00:26 When you do, Excel knows that it is to treat the rest of the text you type into 00:29 the cell is part of the formula. 00:32 As an example, you can click a cell on your worksheet, type = 1+2, press Enter, 00:38 and get the result of 3. 00:41 You can also refer to worksheet cells in a formula. 00:43 In this worksheet for example I have values and cells B3 and B4 which represent 00:48 revenue for the years 2009 and 2010. 00:51 If you want to find the sum of the values in those two cells, you could type 00:57 =B3+B4, press Enter and you get the result. 01:03 A worksheet cell displays the formula's result, but if you click the cell and 01:07 look at the Formula Bar, which is just above the regular worksheet grid, you can 01:11 see the text of the formula. 01:13 Now, let's say that you want to perform a slightly more advanced calculation 01:16 such as finding the average of the two values in cells B3 and B4. 01:20 The long way to do that would be to type a formula in cell E2 such as =(B3+B4)/2 01:31 and what this does is it adds the two values in B3 and B4 and then divides it by 01:35 2, which is the number of values. 01:37 When you press Enter, you'll see the average. 01:40 The problem with this approach is that it takes a long time to type in those 01:43 formulas and it's really only practical to create formulas by hand from very 01:47 simple mathematical operations. 01:50 In many cases, it's much easier to use functions. 01:53 A function is a built-in set of steps or algorithm that Excel can follow to find a result. 01:58 To find the average of the values in cell B3 and B4 for example, I could type 02:02 in an equal sign and then start typing the name of the Average function and press 02:08 a left parenthesis. 02:10 Now that I've pressed a left parenthesis, I can indicate which cells contain the 02:13 values I want to add. 02:15 Those cells are B3 to B4. 02:20 When I type a colon it indicates to Excel that I'm defining a cell range. 02:24 I'll talk about that more later on in this chapter in lesson 4, Using Cell 02:27 References and Formulas. 02:30 After you enter the cell references, you can type the right parenthesis and 02:33 press Enter to end the function and you get the result. 02:36 Now it's the same result as before, but you didn't have to remember how to 02:39 compute an average or count the number values you were averaging. 02:43 Now that you understand how functions and formulas work in Excel, you can dive 02:46 into the main body of the course and start creating your own formulas. Collapse this transcript
Creating a formula
Introducing arithmetic operators
Using cell references in formulas
Copying and pasting formulas and values
 00:00 Copying and pasting Excel formulas is in many ways similar to copying and 00:04 pasting a cell's other contents, but there are some important differences 00:07 you should be aware of. 00:08 In particular there are times when copying a formula from one cell to another 00:12 changes the formula's cell references. 00:14 In this lesson I'll show you how to copy a cell's formula exactly as it appears 00:18 in the current cell, how to copy and paste a formula's result instead of the 00:22 formula itself, how to copy and paste a formula without taking along a cell's 00:26 other formatting and how to create cell references that either don't change or 00:29 change the way you want them to when you copy the formula to another cell. 00:33 If you want to copy a cells formula to another cell without the possibility of 00:37 any of the formula's references changing, you need to copy the cell's formula by 00:40 selecting it on the Formula Bar. 00:43 To do that you click the cell that contains the formula and then click on the 00:48 Formula Bar so that it becomes active for editing. 00:52 Now you can drag over the formula holding down the left mouse button so that 00:57 text is selected, I will click Copy on the Home tab of the ribbon and then press 01:03 Escape to release the cell. 01:04 Now, I can go to another cell, say D4, click Paste and Excel pasted in the 01:11 formula exactly as I copied it from the other cell. 01:14 Now suppose you have a cell that contains a formula and you want to copy that 01:17 cell's result but not the formula itself to another cell. 01:21 To do that you click the cell, press Ctrl+C or click Copy to copy its contents, 01:27 click the destination cell and then again on the Home tab of the ribbon, 01:32 click the Paste button's down arrow and click Paste Values. 01:38 When you do, Excel pastes the value from the formula, the result from the 01:41 formula, but does not pastes the formula itself. 01:44 The downside to this is that any time you change a cell that was providing an 01:49 input for the formula, this value here will not change. 01:53 So let's say for example that I change the value in Cell B5 from 8,100 to 10,000. 02:01 I just increase the value by 1,900. 02:03 These two cells here contain formulas that reflect the contents of B2 through B7, 02:08 but this cell which contains only the result of the previous formula doesn't 02:13 update because it no longer has any connection to these cells. 02:16 It only contains a value. 02:17 Normally, when you copy a cell by pressing Ctrl+C, you copy the cell's value or 02:22 formula and its formatting. 02:24 If you want to copy a formula from a cell without bringing along it's 02:27 formatting, you can do so by using the Paste button's options. 02:30 First you click the cell that contains the formula, copy it by clicking Copy on 02:34 the Home tab or pressing Ctrl+C, click the cell into which you want to paste the 02:39 result and then click the Paste button's down arrow and click Formulas. 02:46 When you do, Excel pastes the formula but it does not bring along the formatting. 02:50 You will notice that something interesting happened. 02:53 In other words not only did the formatting go away but the result changed. 02:57 And when you look up here in the Formula Bar, you see that the formula that I 03:01 pasted into cell D8 is not the formula that I copied from cell D4. 03:06 This formula is SUM(B2:B7) and this one is SUM(B6:B11). 03:12 So it's off by five cells. 03:14 Why did that happen? 03:16 It happened because of the way I wrote the cell references when I created the formula. 03:20 Excel recognizes two types of cell references, relative references, which can change, 03:24 and absolute references, which can't. 03:27 When you write a cell reference such as B4 without adding anything to the 03:30 column letter or row number, Excel assumes that either value can change when you copy it. 03:34 Here is an example where Relative References would come in handy, on the 03:39 Revenue per Sale worksheet. 03:40 Suppose you have a list of data where you want to find the average revenue per sale. 03:44 In this column you have Revenue and in this column you have a list of the number 03:50 of sales that generated that revenue. 03:51 If I wanted to find the average per sale revenue for 2005, I would divide the 03:56 value in B2 by the number of sales in C2. 04:03 When I press Enter, I get the result. 04:06 Now, suppose that I wanted to create a similar formula for the cell D3, D4, D5, D6 and D7. 04:12 The hard way to do this is to create the formula by hand in each of these cells. 04:17 So for example it would be = B3/C3, but you don't have to do that. 04:23 Instead you can copy the formula from cell D2 down through the remaining cells 04:28 and this is where relative references come in handy, because Excel will update 04:32 the formula's cell references as I copy the formula down. 04:35 So how do you to copy the formula? 04:37 To do that, you click the cell and then grab the fill handle at the bottom right corner. 04:43 You know that your mouse pointer is in position when it changes from the white 04:47 Greek cross to a black crosshair. 04:51 When it is, grab the fill handle and drag down until the selection covers all of 04:56 the cells into where you want to paste the formula. 04:59 When you release the left mouse button, Excel updates the formulas. 05:03 So for example the formula in cell D3 refers to cells B3 and C3, D4 refers to B4 05:11 and C4 and so on throughout the list. 05:14 But let's suppose that you want to create an absolute reference, which is a cell 05:18 reference that doesn't change. 05:19 You do that by adding a \$ sign to the front of the column letter, row number or both. 05:24 Adding a \$ sign makes that element of the formula, whether a row or column 05:28 reference, absolute. 05:30 So let's suppose that you enter the formula =\$B\$4 in cell F2. 05:39 It refers to the cell here, the one outlined in blue, press Enter and I get my 05:44 result of 9,100, which is correct. 05:47 Now if I copy this formula down, I get the same results. 05:54 Because I used an absolute reference this formula is B4, this formula is B4 and 06:00 this formula refers to cell B4. 06:02 Rather than type all these \$ signs, there is an easier way to control whether a 06:06 cell reference is relative or absolute. 06:09 To do that, you click on the Formula Bar and then click anywhere in the middle 06:14 of the cell reference that you want change and then you press the F4 key. 06:18 The F4 key will cycle you through the four different possibilities for absolute 06:22 versus relative references. 06:24 If I press F4 now, it makes the column reference relative, meaning it can change 06:29 and it makes the row reference absolute, meaning it can't. 06:31 If I press F4 again, now the column is absolute and the row is relative. 06:36 F4 again and both the row and column references are relative. 06:41 Press F4 again and I'm back to what I had originally which is that the entire 06:45 reference is absolute, both row and column. 06:48 At first copying and pasting Excel formula seems to cause random changes to your worksheet. 06:53 However, once you know how copying and pasting works, you'll find that you have 06:56 a great deal of control over how your worksheet behaves. Collapse this transcript
Editing a formula
Getting help using Excel functions
 00:00 When you work with Excel workbooks you didn't create or that you haven't visited 00:04 for a while, you might find that some of the formulas contain functions you 00:07 either haven't used before or that you can't remember. 00:10 If that's the case, you can use the Excel Help system to get information 00:13 about those functions. 00:15 If you want to display the main Excel Help screen, you can click the Help button 00:18 near the right edge of the Ribbon's tab bar. 00:21 The Help button is a blue circle with a white question mark in the middle. 00:26 You can also press F1. 00:29 When you click the Help button, the Excel Help window appears. 00:33 You can use the controls in the Excel Help window to search for a term. 00:36 Say, for example, if I wanted to find out about averages, I just type in 00:40 average, press Return, and Excel returns results from my searched term. 00:45 If you want to go back to the main page, you can click the Back button here and 00:51 Excel returns you to the main page or whichever page you've visited previously. 00:56 If you're having trouble getting online to view the Office Online Help 00:59 resources, you can click the Show Offline Help Files link to work with only 01:03 those files in your computer. 01:05 It's also possible to view the help files for a specific function you're using 01:09 to create a worksheet formula. 01:10 To do that, you start editing the formula, say here on the Formula Bar, 01:15 you click inside the function, in this case SUM. 01:20 You click the function name, and then on the tooltip that appears below the function, 01:24 point to the function name. 01:27 You'll notice that the function, in this case SUM, is now highlighted in 01:31 blue and underlined. 01:32 That indicates it's a link. 01:34 When you click the link, Excel displays the help file for the SUM function. I'm done there. 01:42 Finally, you can display a function's help file from within the Insert 01:44 Function dialog box. 01:46 When you open the Insert Function dialog box, Excel displays the function 01:51 arguments for the function that you've created. 01:53 At the bottom left corner of the Function Arguments dialog box, you see the Help 01:57 on this function link. 01:59 When you click the link, Excel displays the help file for the function. 02:03 The Excel Help system contains information on all of the functions available 02:06 to you in Excel 2007. 02:08 The Search function can return some strange results at times, but if you type in 02:12 just a few terms that describe what you want to do, you'll find what you need. Collapse this transcript
2. Creating Basic Summary Formulas
Creating a SUM or AVERAGE formula
Creating an AutoSum formula
 00:00 One of the most common formulas you'll create in Excel is a formula that finds 00:04 the sum of a column of numbers. 00:06 Excel enables you to create these formulas called AutoSum formulas with just a 00:10 few clicks of the mouse. 00:12 Creating an AutoSum formula that finds the sum of a column of values is simply a 00:16 matter of clicking the cell directly below the values and then on the Formulas tab, 00:20 in the Function Library group, clicking the AutoSum button. 00:24 So I've already clicked the cell. 00:26 Now I can go to the Formulas tab and then in the Function Library group, I can 00:33 click the AutoSum button. 00:34 Now notice that the AutoSum button is divided into two sections: the button 00:38 itself at the top and then a down arrow at the bottom. 00:41 I'm going to work with the button part on top right now and I'll show you how to 00:45 use the controls on the down arrow list later. 00:48 So I click the button and Excel creates the AutoSum formula. 00:52 The formula is fully formed with parentheses and all, so you can press Enter to 00:56 accept the formula as written. 00:58 Just to verify, we have = SUM, which is what we want. 01:02 The cell references are B3:B10 which are the cells represented by this moving marquee. 01:08 I have a right parenthesis. 01:10 When I press Enter, Excel finds the sum of the values in the column above where 01:14 I created the AutoSum formula. 01:16 I want to create another formula in that cell. 01:18 So I'll press Delete to get rid of the existing formula and show you how to use 01:23 the AutoSum button's down arrow. 01:25 The down arrow contains a list of other functions that you can use in 01:30 your AutoSum formula. 01:32 The functions are Sum, which we just used, Average, which finds the average of 01:36 the values, Count Numbers, which creates a formula that counts the number of 01:40 cells in the group that contain numerical values, Max, which finds the maximum value, 01:45 and Min, which finds the minimum or smallest value. 01:49 If you want to use any other function, you can click the More Functions option 01:53 to display the Insert Function dialog box. 01:56 You can select the function you want to use and click OK, verify the cells 02:00 you want to summarize up here in the Argument fields and click OK to create the formula. 02:04 So let's say that I wanted to find the maximum value and yes, that is on 02:10 the list, but let's just say I wanted to use that in this case, click OK, 02:14 the Function Arguments dialog box appears and my cell range of B3:B10, 02:20 which is here, is correct. 02:22 So I can click OK and create the formula. 02:26 That formula finds the maximum value, which is 1,749,000 and it's in cell B10. 02:33 Creating AutoSum formulas help you summarize your data efficiently and get on 02:37 with the more demanding tasks facing you in your workbook. 02:40 Don't forget that you're not limited to the Sum function though. 02:43 You can select any function you like from the AutoSum button's down arrow list 02:46 or the Insert Function dialog box. Collapse this transcript
Creating MIN, MAX, MEDIAN, and MODE formulas
 00:00 When you analyze a data set, it helps to discover information about your data, 00:04 including the highest, lowest, and most common values. 00:07 The Min, Max, Median, and Mode functions help you do just that. 00:12 The Min function, as name implies, finds the minimum or lowest value in the cell range. 00:16 So if I were to create the formula = MIN, which is the minimum function, 00:22 (A2:A10), hit Return and you see the minimum value of 101, which in this 00:32 case appears in cell A2. 00:35 The Max function, by contrast, finds the maximum or highest value in the cell range. 00:41 So I'll just create the same formula, =MAX(A2:A10), using the same cell range, 00:49 hit Return and you find the maximum value of 135, which is in the cell A10. 00:54 You might not be familiar with the Median and Mode functions, which are often 00:58 used in descriptive statistics. 01:00 The Median function finds the middle value in the cell range when the values are 01:03 sorted in ascending order. 01:05 For example, this series of nine values is sorted into ascending order and the 01:09 fifth value, which has four values above it and four values below it is 125. 01:16 So if I were to create the formula =MEDIAN(A2: A10) and hit Return, I get the value of 125. 01:28 The reason that Excel displayed a value from the list is because it contains an 01:32 odd number of values. 01:33 The value in A6 has four values above it and four values below it. 01:37 If you have an even number of values in your list, then the Median function 01:41 returns the average of the two middle values. 01:44 So, for example, if you had a list of ten values, then Excel would average value 01:48 number five and value number six. 01:50 The Mode function finds the most common value in a group of cells. 01:54 In this list of values, the number 120 occurs twice and the number 125 occurs twice. 02:01 So they are tied, but if I type in =MODE(A2:A10) and hit Return, Excel returns a value of 120. 02:16 If there are two or more values that occur an equal number of times, the Mode 02:19 function always returns the lowest of those values. 02:23 You might also have heard of the calculation called the mean, usually, in the 02:26 context of the mean, the median, and the mode of a group of numbers. 02:31 The mean of a group of numbers is its average. 02:33 So you can calculate it using the Average function. 02:36 Finding the minimum, maximum, middle and most common values in the data set can 02:40 give you important information about your data. 02:43 For example, contractors can find the highest, lowest, and most common bid 02:46 prices for raw materials and use that data to forecast their profits based on 02:50 the amount they charge for labor and the cost of their inputs. Collapse this transcript
Creating formulas to count cells
 00:00 Sometimes, rather than finding the sum or average of values in a range of cells, 00:04 you'll just want to know how many cells in that range contain a number, 00:07 any value at all, or are bank. 00:10 Excel has a number of functions to count the number of cells in a range that 00:13 contain a particular type of data. 00:15 The Count function counts the number of cells in a range that contain numbers. 00:18 So let's say that you have a worksheet that tracks light bulbs manufactured at 00:22 your facility and lists the bad light bulbs found in a series of samples. 00:26 You can use the Count function to discover the number of samples that have been 00:29 taken during the day. 00:30 So let's say that I create the formula =COUNT(B2:B10, right parenthesis 00:40 to close out the function arguments, hit Return and I see that I had six measurements. 00:45 If you look at the list, you'll notice that I do, in fact, have six numeric values: 00:49 one, two, three, four, five, and six. 00:56 So the result of six for the Count function is correct. 01:00 The CountA function counts the number of cells in a range that contain any 01:04 value, i.e., cells that aren't blank. 01:06 The CountA function is useful if the result of a measurement might not be a number. 01:10 For example, if the tester dropped a box of light bulbs before they could be 01:14 tested for faults, you could put N/A or dropped in the cell to indicate the 01:18 bulbs were pulled but no measurements were taken. 01:21 So if I were to create the formula = COUNTA, referring to the cells (B2:b10). 01:31 Again, capitalization doesn't matter. 01:32 I just usually do it to make my cell references easier to read. 01:37 I have COUNTA(B2:b10, right parenthesis, hit Return. 01:40 You'll see that I have eight cells that contain any sort of data, one, two, 01:46 three, four, five, six, seven, and eight. 01:49 So the answer is correct. 01:51 You can combine the Count and CountA functions in a formula to return some 01:54 interesting results. 01:55 For example, if you subtract the result of the Count function from the result 01:59 of the CountA function, you'll find the number of cells that contain 02:03 non-numeric values. 02:04 The CountBlank function is the opposite of the CountA function. 02:08 It counts the number of cells that are blank or contain no value at all. 02:12 CountBlank function takes a series of cell ranges as its argument and is 02:15 extremely useful for counting the number of values you need to complete an assignment. 02:19 In this case, I want to see how many cells contain no data, which means that 02:23 even though the bulbs might have been pulled, no result was recorded. 02:27 So I'll type =COUNTBLANK and the cell ranges (B2:B10), hit Return, and I have 02:37 an answer of one which is correct because the only blank cell in this range is cell B7. 02:44 One situation where it might be useful to use the CountBlank function is if you 02:48 need to input the number of hours that six employees worked on the contract 02:51 during a given week. 02:52 If the CountBlank function examines the cells where you're going to enter 02:55 the values and that returns an answer of one, then you know there is one 02:58 employee who hasn't turned in their hours and you can start pestering them for their input. 03:03 These three variations on the Count function help you discover the number of 03:06 cells in a given range that contain particular types of values. 03:09 There are two other Count functions, CountIF and CountIFs, which is plural. 03:14 I'll cover those two in the next lesson on summarizing cells conditionally. Collapse this transcript
Creating formulas to summarize cells conditionally
 00:00 Most of the formulas you create in Excel will always perform the same calculation. 00:05 For example, you might need to multiply a sales total by 5% to commute the 00:08 transaction sales tax. 00:10 There will be times, however, when you only want to perform calculations if the 00:13 inputs meet one or more criteria. 00:16 In this lesson, I'll show you how to use conditional functions to perform 00:19 specific calculations when those criteria are met. 00:23 Let's start out by creating an IF formula. 00:26 An IF formula tests the value and uses the result to determine which of two 00:29 actions to take. One if the test is true and one if it's false. 00:33 For example, the salesperson might receive a 10% bonus for exceeding the 00:38 monthly revenue target. 00:39 In that case, you could create the formula, =IF(C2>B2). 00:48 What that comparison does is determine whether the value in cell C2, 00:53 actual sales, is larger than the value in B2, which was the sales goal for the quarter. 01:00 So, there is the test, and now what is the calculation that I want Excel to 01:04 perform if the value is true, in other words, if the test is met? 01:08 In this case, I want to give the salesperson an extra 10% on the commission. 01:12 So instead of getting 10%, I would give them 11%, which is a 10% increase over 01:18 their original commission. 01:19 So, if the value is true, I'll type in C2* 0.11, which represents an 11% commission. 01:30 That looks correct and I'll type a comma to move to the last argument, which is 01:35 what to do if the value is false. 01:38 In other words, if the actual sales are less than or equal to the goal, then we 01:43 give the salesperson a 10% commission. 01:45 So that would be C2*0.1. 01:50 That's the last argument that I'm going to add. 01:52 Type a right parenthesis. Hit Return. 01:56 And you see that in this case, the salesperson would get a 10% commission, which 02:00 is 1/10th of this value. 02:02 Now, when I created this formula, I used relative references. 02:06 That means those references will change when I copy the formula down the column. 02:10 So if I click this cell, which I have, grab the fill handle and drag it down to 02:16 cover the last cell next to the final row in the data list, you'll see that 02:21 we have the commissions calculated here. 02:23 So, Jones exceeded their quota. 02:26 So they got an extra 10% commission. Frye, an outstanding salesperson, exceeded 02:31 his quota and got the extra commission as well. 02:33 175,000 is exactly on target, but does not exceed it, so there was no bonus and 02:40 the remaining values are calculated correctly as well. 02:45 Now let's go on to the other conditional functions that are available to you. 02:49 The first I'll talk about is the SumIF function. 02:52 The SumIF function allows you to identify which rows from a data list you 02:56 want to add together. 02:57 The list in this worksheet shows yearly revenues for a company's three divisions. 03:02 If you wanted to find the sum of the revenue from the North division for the 03:04 year summarized in this list, you could create a SumIF formula. 03:09 You start by typing =, followed by SUMIF, and left parenthesis. 03:16 The first SUMIF function argument is the range argument, which identifies the 03:20 cells you want to examine using a criteria. 03:23 In this case, you want to find rows that contain North. 03:26 So you type in the range B2:B17, which are the cells that could potentially 03:33 contain the division named North. 03:34 Now you type a comma and type in the criteria surrounded by double quotes. 03:40 In this case, I want to search the cells B2 through B17 for the term North. 03:44 So I'll type "North". 03:49 That's the end of criteria, so I will type a comma. 03:52 Make sure that the comma is outside the double quotes or the formula. 03:55 We'll try to match the string in North, instead of just North. 03:59 After the final comma, you type in the range of values that you want to add up. 04:03 Those values occur in cells C2 through C17, which contain the divisional revenue figures. 04:12 Type a right parenthesis to close the formula. 04:16 I have a result, which I will now format using currency of \$2,371,128. 04:26 You can use other comparison operators such as greater than and less than in your criteria. 04:30 For example, if you wanted to find the total of all yearly revenues that are 04:33 greater than or equal to \$750,000, you could change the range argument, which 04:38 names the cells to which you want to apply the criteria to C2 through C17, and 04:50 modify the criteria which currently says North to >=750,000. 05:00 When you type a number in as a criteria, it's important that you do not use a comma. 05:05 If you use a comma, Excel will think that you're trying to put in the next 05:09 argument in the function and it will generate an error. 05:12 So now that we have that formula set up, we can hit Return and we'll get the result. 05:17 The total of all cells that contain values greater than \$750,000 is \$4.8 million. 05:23 There are two other functions that enable you to summarize data using a single criterion. 05:27 Those functions, CountIF and AverageIF, count the number of cells that meet a 05:31 criterion or find the average of the numerical values in rows where value in one 05:35 column meets the criterion. 05:37 In this sample data list, you could create a formula to count the number of 05:40 cells that contain values greater than or equal to \$750,000 and find the average 05:44 of sales for the East division. 05:46 So, for example, if I wanted to count the number of cells that contain values 05:54 greater than \$750,000, I can create a CountIF formula. 05:58 Then we have C2:C17, and the criteria is greater than or equal to \$750,000. 06:12 Close it with a double quote, close it with a right parenthesis, 06:16 hit Return and you see that there are six such cells. 06:20 If I wanted to find the average of those values, I can type in =AVERAGEIF(. 06:28 The range that I want to examine is again C2:C17, the criteria is >=750000, and 06:42 the values that I want to average also occur in the range C2:C17 and yes, you do 06:48 need to type it in twice. 06:49 The reason is that you have the range here, which is the range that Excel looks 06:53 into to determine whether or not to include the row in the calculation. 06:57 This range tells Excel which cell within that row to use. 07:01 So we have C2:C17, right parenthesis to close, hit Return, and you see that the average value is 07:11 just a little bit over \$802,000. 07:15 The AverageIF function is new in Excel 2007 as our three functions that enable 07:19 you to identify data rows using multiple criteria. 07:22 Those new functions are SumIFs, AverageIFs, and CountIFs. 07:27 The S on the end of those function names indicates that you can use multiple criteria. 07:32 As an example, I'll create a SumIFS formula that finds a total of all sales for 07:37 the West division during the year 2009. 07:40 To create that formula, I'll type in =SUMIFS(C2:C17, and that argument 07:50 represents the sum range, in other words the numbers that we'll be adding up as 07:54 part of the SumIFs function. 07:57 Then I'll type in my first criteria range, in other words the column or the 08:00 cell range that I want to examine to determine whether or not to include the 08:04 row in the calculation. 08:05 So the first one I'll create is for the division name that would be B2:B17, comma for 08:12 the next argument, and now I can type in my criteria to apply to that range. 08:17 In this case, I want it to be =West. Now I can type in the criteria range too. 08:27 For that I'll type in A2:A17, those are the years, comma, and the criteria that I want 08:35 to apply is whether or not it's equal to 2009, right parenthesis to close the formula, 08:42 hit Return, and there is the result of my formula. 08:48 The AverageIFs and CountIFs function use the same syntax as a SumIFs function. 08:53 Once you can create a SumIFs formula, you can create AverageIFs or 08:56 CountIFs formulas too. 08:59 Creating conditional formulas might seem confusing at first, but after you 09:02 create a few, they'll become second nature. Collapse this transcript
Summarizing data on the Status bar
 00:00 Creating a formula in Excel 2007 just takes a few seconds, but there might be 00:04 times when you want to find the sum or average of a series of numbers without 00:08 saving the results in your worksheet. 00:10 If that's the case, you can select the cells and have Excel display some 00:13 summaries on the status bar at the bottom of the workbook window. 00:17 When you click a single cell, no summary appears on the status bar. 00:21 That's because there is really nothing to add, the only value you're looking at 00:25 is the value in the selected cell. 00:27 But now look what happens if you select two or more cells. 00:32 When you do, Excel displays a number of summary operations here on the status bar. 00:37 By default, you get the Sum, the Count, which is the number of cells that you've 00:42 selected that actually contain data, and finally, the Average of those values. 00:46 You can change which values are displayed on the status bar by 00:49 right-clicking and using the Tools on the Customize Status Bar pop-up menu 00:53 to control what appears. 00:55 Any item that is checked currently appears on the status bar. 00:58 So, for example, we have Average and also there is value in Count and 01:03 finally, we have Sum. 01:05 If you wanted to show the maximum value, you could click Maximum and Excel 01:09 would add that value to the status bar. 01:11 If you want to remove a value, click it again and it removes the checkbox. 01:17 When you're done, just click anywhere off of the Customize Status Bar pop-up 01:20 menu and it will disappear. 01:23 Summarizing the contents of a few cells on the status bar helps you answer 01:26 questions about your data, without going to the trouble of creating a worksheet 01:30 formula you won't bother to keep around. 01:32 It's a great way to perform quick summaries without altering your workbook or 01:35 disrupting your workflow too much. Collapse this transcript
Rounding cell values up and down
 00:00 You've probably rounded values up quite a few times in your life. 00:03 At a restaurant, you can round the bill up to the nearest \$10 increment and tell 00:07 the server to keep the change, or describe the cost of the new building as about 00:11 \$10 million even though the price is a little bit lower. 00:14 Excel 2007 has several functions you can use to round off worksheet values. 00:18 You should become familiar with them so you can determine which is the best 00:21 function for your particular task. 00:23 The most familiar rounding function is Round, which takes two arguments, the 00:27 value to be rounded, and the number of digits to the right of the decimal to 00:31 which you want to round the answer. 00:32 As an example, suppose you want to round the value 12.875, shown here in cell 00:37 A2, to two decimal places. 00:39 To do that, you would create the formula =ROUND(A2,2. 00:46 Again, A2 represents the cell where the value is that you want to round and 2 00:51 is the number of digits to the right of the decimal point to which you want to round it. 00:56 Type a right parenthesis and hit Return, and you see that Excel rounds the value to 12.88. 01:02 Excel, by default, rounds any value of .5 or greater up. 01:06 So, for example, if you are rounding value to the nearest value of 10, 01:11 the number 5 would be rounded up to 10, and the number 4 would be rounded down to zero. 01:16 You can also use the Round function to round the tens, hundreds, thousands or larger values. 01:20 To round to the tens, which is one digit to the left of the decimal point, 01:24 you would make the second argument -1, and press Enter. 01:31 When you do, Excel rounds of value 12.875 to the nearest value of 10, which is 10. 01:37 The Round function rounds any value of .5 or higher up and rounds the rest down. 01:41 If you'd prefer to have Excel round any number that contains a decimal value to 01:44 the next higher number, you can do so using the RoundUp function. 01:48 This type of calculation is common when a phone company calculates the number of 01:52 minutes you've used on your cellphone, where a partial minute of any length 01:55 counts as a full minute against your plan. 01:58 The RoundUp function takes the same arguments as the Round function. 02:01 So the formula =ROUNDUP(A3, 0, which rounds to the nearest whole number, 02:12 would round up to 12. 02:14 Again, the RoundUp function rounds any decimal value up to the next higher number. 02:20 By contrast, the RoundDown function rounds a value down. 02:23 For example, assume that you want to round a value to the hundredths place. 02:27 To do that, you would create the formula =ROUNDDOWN(A4, -2. Again, the -2 refers 02:39 to the number of digits to the right or left of the decimal point. 02:42 Positive numbers go to the right, negative numbers go to the left. 02:46 So in this case, we have -2, which means that we're rounding to the hundredths. 02:50 Type a right parenthesis, hit Return, and there you have the value. 02:54 Even though 1160 is higher than halfway to the next value of 100, we use the 03:00 RoundDown function, so any partial value is discarded and we round down to the 03:06 closest value, which in this case is 1100. 03:08 Now let's suppose you want to round a number to the nearest one-half or to the 03:12 nearest quarter or to the closest value of seven. 03:16 To do so, you use the MRound function. 03:19 The MRound function takes two arguments: the value to be rounded and 03:23 the multiple to which you want to round it. 03:24 For example, if you round the value in cell A5 to the closest multiple of 03:29 one-half, you would create the formula =MROUND(A5, 0.5) and hit Return, and 03:41 Excel rounds the value 11.6 down to 11.5. 03:45 If the value in the cell were 11.8, Excel would round it to 12, as so. 03:55 Excel rounded the value up to 12, because 12 is the next highest multiple of one-half. 04:00 The rounding functions I've shown you give you a great deal of control over how 04:03 Excel handles your numbers. 04:05 Once you establish the rule by which you want to round your worksheet values, 04:08 you can create a formula that follows that rule. Collapse this transcript
3. Sorting and Filtering PivotTable Data
Summarizing data using named ranges
 00:00 So far all the formulas I have shown you use cell references such as A3 or A2:A5 to 00:06 designate which cell should be summarized in the formula. 00:09 Those references worked just fine. 00:10 But it can make it hard to understand exactly which data your 00:13 formula summarizes. 00:15 You can make your formulas easier to comprehend by assigning names or groups of 00:18 cells and then using those named ranges in your formulas. 00:22 The most straightforward way to create a named range is to select a cell range 00:25 and then type a name for the range in the Name Box at the top left corner of the worksheet. 00:30 The Name Box is the box that normally displays the address of the active cell or 00:34 when you're in the process of dragging to select the cell range, the size of the 00:37 range that you select it. 00:39 Let's say I wanted to create a named range for the cell summarizing divisional 00:42 sales for the years 2009 through 2011. 00:45 To do that, I would select the cells that contain the values, but not the 00:49 labels, click in the Name Box and type Year2009to2011. When I press Enter, 01:00 the Excel creates the named range. 01:02 If I want to select the cells in that named range, I can click the Name Box's 01:06 down arrow and select the named range from the list that appears. 01:12 When I do, Excel selects the cells, and you'll also notice that Excel has 01:17 displayed a summary of the information in those cells here in the status bar. 01:22 So we have the average value, the number of selected cells and also the sum of 01:26 the value in those cells. 01:28 Another, way to create a named range is by displaying the New Name dialog box. 01:32 To do that, you click the Formulas tab on the Ribbon and then in the Defined 01:36 Names group, click Define Name. 01:40 When you do Excel displays, the New Name dialog box. 01:43 You can use the controls in the New Name dialog box to define your range name. 01:48 I'll create another range name that identifies the same cells that I did before. 01:53 So in this case, I want to make the name RevenueTo2011. I can add a comment if 02:01 I like, just Sales revenue and then identify the cells to which the named range applies. 02:08 So in this case I have a Ref Edit control that I can click. I select the cells 02:15 that I want to be in the named range, click the Ref Edit control again, to 02:19 bring back the controls in the dialog box and I see that I have Divisions, 02:24 which is the name of my worksheet, and the cell range C2:13, which were the 02:30 cells that I selected here. 02:32 The Scope field requires some explanation, so I saved it for last. 02:35 A named range of scope refers to where in the workbook you can use the 02:38 named range and formulas. 02:40 The most common choice and the default choice is Workbook, which means you 02:44 can use the named range and formulas anywhere in the workbook where you define the named range. 02:48 If you click the Scope box's down arrow and select Worksheet, where the 02:52 cells of the named range reside, then you can only use the named range and 02:55 formulas on that worksheet. 02:57 So for example, our named range is on the Divisions worksheet. 03:00 If I were to click the list and click Divisions then, I could only use the 03:04 RevenueTo2011 named range in formulas on the Divisions worksheet. 03:10 So I'm done creating my new named range. 03:12 Now I will click OK. 03:14 The final way I will show you how to create a named range is to create the names 03:17 from a selected group of cells. 03:19 Let's say that as I have here on the Sales Revenue worksheet that I have two 03:26 columns of data, one for Revenue and one for Sales Count. 03:30 Notice that each of these columns has a label on the top and this time, I will 03:33 select them along with the data. 03:36 After I select the cells, I can go to the Formulas tab of the ribbon, and then, 03:40 in the Defined Names group, I can click Create from Selection. 03:46 When I do, Excel displays the Create Names from Selection dialog box and Excel 03:50 will assign names to the named ranges it creates, based on the values that 03:54 it finds in the area selected within the dialog box. 03:57 So for example, it can create the named ranges from the Top row, Left column, 04:02 Bottom row or Right column. 04:03 In this case, my data is laid out as a column so that means that my headers 04:08 occur in the top row. 04:10 That is the default choice. I'll leave it selected and I can click OK. 04:14 When I do, Excel creates the named ranges Revenue and Sales Count, which you can 04:19 display by clicking the Name Box's down arrow. 04:25 Now that you've created a named range, how to use it in a formula? 04:28 The easiest way to do so is start creating the formulas such as =SUM, type a 04:37 left parenthesis, and then start typing the name of the named range. 04:42 In this case, I'll make mine Sales_ Count, which you see appears here in the 04:47 Formula AutoComplete list. 04:48 It is highlighted so I can press Tab. 04:51 And now when I type a right parenthesis to close out the formula the total of 04:57 that named range appears as the formula's result. 05:00 Named ranges make your formulas as much easier to understand. 05:04 You should take the time to create a few of them and start using them in your formulas. 05:07 You'll be glad you did. Collapse this transcript
Creating a dynamic named range
Editing and deleting named ranges
 00:00 Just like every other workbook element, you can edit and delete named ranges 00:04 after you create them. 00:05 In Excel 2007, you can work with your named ranges using the Name Manager. 00:11 To edit a named range, click the Formulas tab on the ribbon and then in the 00:15 Named Ranges group, click Name Manager. 00:18 The Name Manager displays all of the named ranges in your workbook, 00:21 their definition, their scope and any comments you might have stored with them. 00:26 You can change the named ranges' definition by editing in the contents of the 00:29 Refers to box, which you display by clicking a named range and then you can edit 00:35 the reference down here in the Refers to box. 00:39 You can also edit the named range by clicking the named range in the list here 00:43 and then clicking the Edit button. 00:46 The Edit Name dialog box is pretty much the same as the New Name dialog box. 00:50 The major difference, aside from title, is that you can't change a named range scope. 00:55 If you want to change a named range scope from Workbook to Worksheet or 00:58 vice-versa, you need to delete the named range and create a new one. Just close that. 01:06 One handy aspect of how Excel manages named ranges is that if you change the 01:10 named range's name, Excel updates all of the formulas using that range to 01:13 reflect the new name. 01:15 It's a nice touch that keeps you from generating slew of errors whenever you 01:18 decide you want to call a named range something different. 01:20 If you want to delete a named range, you can click the range, and then click the 01:26 Delete button and click OK to verify that you want to delete it. 01:30 When you do, Excel removes the named range from your workbook. 01:34 Clicking the Filter button and selecting an option limits the named ranges that 01:37 are displayed in the dialog box. 01:40 You can display just those named scoped to the workbook level, 01:42 at the worksheet level, names that contain errors, and so on. 01:48 When you are done making any changes to your named ranges, click the Close 01:50 button and you'll hide the Name Manager. 01:54 The Name Manager is the control center for your named ranges. 01:57 If you want to create, change, or delete a named range, the Name Manager is the 02:00 tool you want to use. Collapse this transcript
Summarizing list data by creating subtotals
Grouping and outlining list data
 00:00 In the previous lesson, I showed you how to have Excel create subtotals that 00:03 group and outline your list data. 00:05 Creating a subtotal is often a great first step toward creating useful 00:08 outlines, but custom groups give you even more control over how Excel 00:12 summarizes your data. 00:14 This data list is already grouped at the year level and has the quarter level 00:17 available for grouping as well. 00:18 But let's say that your company runs an annual sale during the months of March 00:22 and April, which don't fall within the same quarter. 00:25 To create custom groups for March and April of each year, you select the cells 00:28 for March and April in 2009, just click any two cells in the column, and then, 00:35 on the Data tab of the ribbon, in the Outline group, click the Group button. 00:39 When you do, Excel asks whether you want to group the rows or the columns. 00:43 We want to do the rows, so click OK and Excel creates a group. 00:48 But notice, because this is a data list as opposed to an Excel table, which 00:52 I'll cover later in this chapter, Excel did not apply the same grouping to the 00:58 same months for 2010. 00:59 So to do that, you just select the rows that correspond to the rows you selected here, 01:04 on the Data tab click Group, verify that you want to group the rows and 01:10 click OK to create the group. 01:12 To ungroup a set of rows, select the cells in the rows on the Data tab of the ribbon, 01:17 in the Outline group, click Ungroup. 01:21 You want to ungroup the rows, so click OK, and you can do the same thing up 01:26 here, for March and April of 2009. 01:30 As before, you'll need to ungroup each group individually or you can click 01:34 any cell in the list, click the Ungroup button's down arrow and then click Clear Outline. 01:42 When you do, Excel removes the entire outline but it retains the Subtotal and 01:46 Grand Total formulas. 01:47 If you want to get rid of the subtotals as well, you can click Subtotal 01:53 and click Remove All. 01:55 Grouping and outlining rows in a data list lets you control exactly which data 01:58 you want to display in your worksheet, which is especially helpful in 02:01 presentations where you want to emphasize particular points. What's more, 02:05 grouping and outlining is compatible with Excel 2003 and earlier versions 02:08 of Excel, so you will be able to collaborate with coworkers who might not 02:12 have upgraded to 2007. Collapse this transcript
Summarizing data using an Excel table
Referring to Excel table cells in formulas
Finding data using VLOOKUP and HLOOKUP formulas
4. Preparing Data for Analysis in Excel
Importing data into Excel
Connecting to an external data source
Cleaning up data imported into Excel
Creating data validation rules
Using lists to limit values entered into a cell
5. Auditing Worksheet Formulas
Managing Excel formula error indicators
 00:01 If you create a lot of worksheets, it's inevitable that you will create formulas 00:04 that don't do what you want them to do. 00:06 Excel helps you discover those errors, but sometimes it can be a 00:09 little overenthusiastic. 00:11 In this lesson, I'll show you how to control the ways in which Excel 00:14 displays error messages. 00:16 Excel indicates formula errors in two ways. 00:19 The first is by displaying an error message, such as the one here in Cell 00:22 B7, which is DIV/0. 00:26 In those cases, your formula produces a definite error that prevents Excel from 00:29 calculating the formula's result. 00:31 In other cases such, as the one shown here in Cell G3, Excel displays an error 00:37 flag at the top left corner of the cell to indicate the cell contains something 00:40 that might be an error but might not. 00:43 If you see a flag at the top left corner of a cell and I'll click away just so 00:47 you can see it more clearly here at the top left corner. 00:51 You can click the cell and then hover your mouse pointer over the error 00:55 indicator to the side, click it and see which error Excel thinks you committed. 01:00 In this case, Excel points out that your formula omits adjacent cells. 01:04 So if you look at the formula you'll see that it finds the sum of cells E3 01:08 through E5, which are these three cells here. 01:13 That is the result you wanted, so that means this error indication is 01:16 actually inaccurate. 01:18 But what can you do about it? 01:20 You can identify whether the behavior Excel flag is a real error or whether 01:24 you should ignore it. 01:25 You can control the type of error Excel flags by changing the program's 01:28 error checking rules. 01:30 To do that you click the Office button, click Excel Options and display the 01:36 Formulas tab. Halfway down the page you'll see the Error Checking section. 01:41 Clearing the Enable background error checking checkbox turns off all the error indicators. 01:47 But you can also select which rules to enable or disable by checking the boxes 01:51 in the Error checking rules section. 01:53 You can read through the rules yourself, but I always turn off three rules and 01:57 those are Formulas inconsistent with other formulas in the region, Formulas 02:02 which omits cells in a region and Formulas referring to empty cells. 02:08 Corporate finance worksheets almost always pick and choose data from larger lists. 02:11 So the formulas which omits cells in a region rule tends to identify cells that 02:15 don't actually contain errors. 02:17 Also, because corporate worksheets often have different values in adjacent 02:20 cells to save space, the formulas inconsistent with other formulas in the region 02:24 notation is annoying, not a help. 02:27 Finally, there will be times when I haven't entered a value into a cell, but 02:30 still want to include the cell in my formula. 02:32 So I don't want to activate the Formulas referring to empty cells error, which 02:36 is turned off by default. 02:37 There are two other aspects of error checking you can change. 02:41 If you'd prefer to have Excel use another color to identify cells that contain 02:44 errors click the Indicate errors using this color button and then select the 02:48 color you want Excel to apply. 02:50 Also if you'd like to set the Error checking rules to the default settings, 02:54 you can click the Reset Ignored Errors button. 02:57 Background error checking provides useful information, often enough for me 03:00 to leave it turned on. 03:01 But you should always review which error is Excel flags and turn off the ones 03:04 that provide no useful information. Collapse this transcript
Identifying and tracing errors
 00:01 If you inherit a workbook that contains a lot of errors, you don't have to move 00:04 from cell-to-cell by hand. 00:05 Instead you can speed the process along, by using the controls in the Error 00:09 Checking dialog box. 00:11 To display the Error Checking dialog box, click the Formulas tab of the ribbon 00:15 and then in the Formula Auditing group click the Error Checking button. 00:20 The Error Checking dialog box is a lot like the spelling checker and that it 00:23 identifies individual errors within your worksheet, provides a links to tools 00:27 you can use to fix the problem, and lets you move from error-to-error by 00:30 clicking the Previous and Next buttons. 00:33 Clicking the Help on this error button displays a help file describing what can 00:37 cause the error and how to fix it. 00:39 The information is more complete than the brief notice here on the left side of the dialog box. 00:44 Clicking the Show Calculation Steps button opens the formula in Evaluate Formula 00:48 dialog box, which I'll describe how to use in the Evaluate Formulas Step-by-step lesson 00:54 found later in this chapter. 00:57 If you click the Ignore Error button, Excel will not highlight the current error 01:00 when you use the Error Checking dialog box. 01:03 If the cell does contain an error and you want Excel to highlight it, you'll 01:06 need to delete the existing formula and recreate the formula in the cell. 01:10 Next, clicking the Edit in Formula Bar button opens the cell for editing and 01:15 moves your cursor to the Formula Bar. 01:17 If you want to move back to the Error Checking dialog box, click Resume. 01:21 Finally, clicking the Options button displays the Formula page of the Excel 01:25 Options dialog box, where you can control how Excel highlights errors in your worksheet. 01:30 To move back to the Error Checking dialog box, click Resume. 01:35 One other error checking capability you might find to be useful is the 01:38 Trace Errors feature. 01:40 If you want to identify the cells that provide values to a cell the contains a 01:43 formula error, click the cell that contains the error and then on the Formulas 01:47 tab of the ribbon, in the Formula Auditing group, click the Error Checking 01:51 button's down arrow and click Trace Error. 01:55 When you do, Excel creates tracer errors identifying the cells that provide 01:59 values to the formula. 02:00 You can control these errors using the techniques that I will teach you later 02:03 in this chapter in the Tracking Formula precedents independence lesson. 02:07 The Error Checking dialog box is a powerful tool that comes into its own, if you 02:11 have to analyze a workbook that contains a large number of errors. 02:14 You should move through the worksheet using the Previous and Next buttons before 02:17 you start editing any individual formulas though. 02:20 You might find that a single formula error is causing other errors in your worksheet. Collapse this transcript
Tracing formula precedents and dependents
 00:01 Excel includes a number of very useful tools you can use to ensure your formulas 00:04 use the proper values. 00:06 One of those tools gives you the ability to determine which cells are used in 00:09 which worksheet formulas. 00:12 Cells can play two roles in the formula: as a precedent or dependent. 00:16 A precedent provides its value to another cell. 00:18 A dependent draws its values from another cell. 00:22 Displaying a cell's precedents indicate which other cells provide the formula as input. 00:25 For example, here in cell D3, I've a formula that calculates the number of 00:29 people who came to library within a year. 00:32 If I want to display that formula's precedents, in other words to indicate which 00:35 cells provide values to the formula, I can click the cell and then on the 00:39 Formulas tab the Ribbon in the Formula Auditing group click Trace Precedents. 00:44 When I do, Excel outlines the cells that provide their values to the formula, 00:49 and then draws an arrow to that cell indicating that those cells provide their values. 00:54 If you want to clear the arrow, you can click Remove Arrows in the Formal 00:58 Auditing group on the Formulas tab of the Ribbon. 01:01 Displaying a cell's dependents shows which other cells use the selected 01:05 cell's value as an input. 01:06 So let's say here that I wanted to see which other cells use Sum of Visitors as an input. 01:12 To indicate that I would click the cell and then in the Formula Auditing group 01:17 on the Formulas tab click Trace Dependents. 01:21 When I do you see that the formulas in both cells D6 and D9 use the value in 01:26 cell D3 as part of their calculation. 01:29 And again to get rid of the arrows, you can click Remove Arrows. 01:34 If you have both precedent and dependent buttons in a worksheet at the same time 01:38 and you want to get rid of all of them, you can click the Remove Arrows' down arrow 01:43 and click Remove Arrows, which removes everything. 01:46 If you only want to remove the precedent arrows or the dependent arrows you can 01:50 click that options as well. 01:53 Tracer arrows can also indicate that a formula draws its value from a cell 01:56 on another worksheet. 01:57 So for example, let's take a look at the formula here in cell D9. 02:02 We already know that it draws one value from the cell D3, 02:05 but let's see where else it draws its values from. 02:07 To do that I will click Trace Precedents to indicate which cells provide their 02:11 values to this formula. 02:13 When I do, you see the indicator that tells us that Excel draws its value from 02:17 cell D3, but it also draws its value from a cell on another worksheet. 02:22 That is what this indicator here means. 02:25 A black line drawn to what looks like a worksheet grid. 02:28 If you want to display the other worksheet or workbook that provides the value, 02:33 you can click this line and you will know you're in the right place when your 02:37 mouse pointer changes from the white cross to a pointing arrow, and 02:42 you double-click, Excel displays the Go To dialog box. 02:47 You can click the item in the Go to box and click OK. 02:51 When you do, Excel displays the workbook or worksheet that contains the other 02:55 values used in the formula. 02:57 Identifying a cell's precedents and dependents will help you discover and 02:59 correct many formula inaccuracies. Collapse this transcript
Evaluating Excel formulas step by step
 00:01 Sometimes you'll look over a formula, verify that it uses its proper inputs, 00:04 and still find that the formula returns an incorrect result. 00:08 If that's the case, you can move through the formula one calculation at a time 00:11 to identify the problem. 00:13 In this case, I've setup a formula that tries to find the average revenue per sale. 00:18 But I'm getting a #DIV/0! error. 00:21 This is a simple example, but I'd like to show you how to step through formula 00:24 one step at a time so that you can evaluate it step by step. 00:28 So in this case I will click the cell that contains the formula with the error, 00:32 which is in cell B7. 00:34 Then on the Formulas tab of the Ribbon in the Formula Auditing group, 00:38 I'll click Evaluate Formula. 00:39 When I do, Excel displays the formula as written, B3/B4. 00:45 I can now evaluate the formula by clicking Evaluate. 00:49 When I do, Excel brings in the first value or resolves the first argument 00:53 here in the formula. 00:55 So it's 250,000, which is correct, and I can click Evaluate to move to the next step. 01:01 When I do, Excel displays 0 and I know that's an error because you can't 01:05 divide by zero, and also it corresponds to the error that's displayed in the 01:09 body of the worksheet in cell B7. 01:11 I have located the error, I can click Close and edit the formula. 01:17 The formula actually appears to be correct. 01:19 I am finding the Average per Sale and to do that I'll divide Sales Revenue by 01:24 Sales Count, but I see the source of the error and that's that the Sales 01:27 Count cell is blank. 01:30 I happen to know that the number of sales was 215. 01:33 So I'll type that in. 01:35 When I press Enter, I'd rather have that cell displayed using the Accounting format. 01:41 So I'll click that on the Home tab and display the result. 01:45 Evaluating Excel formulas is a powerful technique you can use to verify that a 01:49 formula produces the correct result. 01:51 You can also use formula evaluation to understand how a formula's logic works. 01:55 If you encounter a formula that uses a function you haven't used before, 01:59 you should take the time to look up the function in the Excel Help files and step 02:02 through the formula, so you'll have a better idea of why the formula is in the 02:06 workbook and how it derives its result. Collapse this transcript
Watching cell values
 00:00 Many worksheets have formulas that draw their values from cells on other worksheets. 00:05 If you change the formula's inputs, you need to display the other worksheet to 00:08 see how the new input affects the formula's value. 00:11 You can speed the process up by creating a Watch, which displays the target 00:14 cell's value in a dialog box. 00:17 To set a Watch on a cell, click the cell, in this case cell D9, and then on 00:23 the Formulas tab of the Ribbon, in the Formula Auditing group, click the Watch Window button. 00:28 When you do, Excel displays the Watch Window. 00:31 To create your watch, click Add Watch and now you can select the cell that you 00:35 would like to watch the value of. 00:37 I have already selected cell D9 on Sheet1, which appears in the dialog box. 00:41 So I can click Add. 00:44 When I do the value and the formula appear in the Watch Window. 00:48 Now this cell draws a value from Sheet2 cell B1. 00:52 So, let's see what happens when I change the value on that cell. 00:56 Click Sheet2 and you see the value here in cell B1. 00:59 Notice that the Watch Window stayed active. It didn't go away. 01:03 So, now I'll change the value in B1 to something much higher. Let's call it 100. 01:08 When I press Return, Excel updates the result of the formula, but you don't 01:12 have to flip back to Sheet1 to see the change. Instead it's displayed here in the Watch Window. 01:19 You can remove a Watch by clicking the Watch within the body of the Watch Window 01:23 and then clicking the Delete Watch button and closing the Watch Window. 01:27 Watching cell values provides an excellent solution for times when you want to 01:31 make multiple changes to a cell's value, but don't want to flip to another 01:34 worksheet after each change. 01:36 You'll save time and get the information that you need to make your 01:38 analysis more accurate. Collapse this transcript
6. Performing What-If Analysis
Creating scenarios and applying them to an Excel worksheet
Editing, deleting, and summarizing scenarios
 00:01 After you create one or more scenarios, you can edit, delete, and summarize them. 00:05 To do any of those things, you need to display the Scenario Manager dialog box. 00:10 To do that, click the Data tab on the ribbon and then in the Data Tools group, 00:15 click What-If Analysis and click Scenario Manager. 00:19 When you do, the Scenario Manager dialog box appears with your scenarios listed 00:23 in the Scenarios list. 00:25 If you want to edit a scenario, you can click it, and then click the Edit button. 00:31 When you do, you get the Edit Scenario dialog box. 00:34 If you want to change the cells that the scenario applies to, you can edit the 00:37 reference in the Changing cells box, either by typing in the new reference or by 00:42 clicking the RefEdit button. 00:44 When you're done there, you can click OK and you can enter in new values for the scenario. 00:50 So in this case, cell B5, the high interest rate that I put in, was 0.07. 00:54 That might be a little bit too high. 00:55 So I will change it to .0625. 01:00 When I'm done adding values, I can click OK and Excel takes me back to the main 01:05 Scenario Manager screen. 01:06 If I want to summarize my scenarios, in other words, if I want to see a single 01:10 listing of the way that each scenario changes my values, I can click Summary. 01:16 Then select the type of summary that I want to create. 01:18 In this case, I want to create a simple scenario summary rather than 01:22 a PivotTable report. 01:24 My result cells that I want to include in the summary are B7. 01:28 Excel notices that B7 contains a formula that uses the cells that I've changed. 01:32 So it guesses that I want to include B7 in the scenario. That's correct. 01:36 So I can click OK. 01:38 When I do, Excel creates a new worksheet that summarizes all the scenarios for 01:43 the active worksheet. 01:44 So you see that I have my Current Values for the Changing Cells, 5.75%. 01:49 That's the base value. 01:50 Then I have the LowInterest scenario of 5% and HighInterest scenario of 6.25%. 01:56 You also see the results for the calculations given in each of those scenarios. 02:00 I have the Result Cell under normal conditions, for the LowInterest scenario 02:04 and for the HighInterest scenario. 02:06 So now if I go back to the worksheet where I created my scenarios, Sheet1, and 02:13 display the Scenario Manager, I can delete one of those scenarios. 02:17 So, for example, if I wanted to delete the HighInterest scenario, I could click it, 02:21 click Delete, and Excel gets rid of this scenario. 02:25 Notice it did not ask if you really wanted to do it. 02:28 It does not verify that you want to get rid of it. 02:31 If you click Delete, the scenario is gone. 02:34 When you're ready to close the Scenario Manager dialog box, click Close. 02:39 I wholeheartedly recommend that you create a summary worksheet whenever you 02:42 create a series of scenarios. 02:44 Even if you do save a workbook with scenarios still applied, the cell's original 02:47 values will appear on the Summary worksheet. Collapse this transcript
Creating a single-input data table
 00:01 One common data analysis scenario is for you to know certain parameters of the 00:04 transaction, such as the amount of a loan and the amount of time you would like 00:07 to pay it back, but to have one element be unknown. 00:11 When businesses shop for a loan, the unknown variable is most often the interest rate. 00:15 If you would like to determine the effect of varying interest rates in your loan, 00:18 you can create what is called a data table. 00:21 To create a single input data table, you need to lay your data out in a 00:24 configuration similar to the one shown here, in this worksheet. 00:28 I am analyzing the effect of interest rates on the monthly payments on a loan of 00:31 \$15 million to be paid back over 15 years. 00:34 I have put the loan parameters in cells B2 through B5 and calculated the monthly 00:41 payment for an interest rate of 6.25%, which I have in cell D2. 00:47 I then added the other interest rates I would like to examine in cells C3 through C6. 00:54 To create this data table, I will select cells C2 through D6, which I 00:58 outlined in the worksheet. 01:00 That range defines a rectangle, which contains the cells with the values I want 01:04 to examine and also the cells where I want to paste the results. 01:07 After I make my selection, I can click the Data tab in the ribbon and then in 01:12 the Data Tools group click What-if Analysis and then click Data Table. 01:18 When I do, the Data Table dialog box appears. 01:21 The cells that contain the values I want use to create the table are laid down 01:24 on a column, so I'll type a value in the Column Input Cell box. 01:28 The cell I want to vary is B3 up here, which contains the interest rate. 01:33 So, I will type B3 in the Column Input Cell box and then when I click OK, 01:38 Excel creates the data table. 01:41 The values that appear in cells D3 to D6 represent the monthly payments for this 01:45 loan at Interest Rates of 5.75, 6, 6.25 and 6.5%. 01:51 Creating a data table helps you analyze a number of different data inputs quickly. 01:56 In the next lesson, I will show you how to create a data table using two inputs. Collapse this transcript
Creating a double-input data table
 00:00 In the previous lesson, I showed you how to create a data table based on 00:03 one variable input. 00:05 In this lesson, I'll show you how to create a two input data table. 00:09 To create a two input data table, you lay out your worksheet like the one shown here. 00:13 The known or assumed parameters are here in the two columns on the left or I 00:17 have the Down Payment and then also the Loan Parameters. 00:22 The variables appear here, on the right, where I have the Interest Rates listed 00:27 here in C3 through C6 and then also the number of years or the term over which 00:32 I would like to pay back the loan. 00:33 Those values are 10, 12 and 15. 00:37 The formula in cell C2 calculates monthly payments given these parameters. 00:42 The cells of a body of this cross table will calculate monthly payments for 00:46 a combination of values. 00:47 So for example, 10 years at a rate of 5.75%, 12 years at a rate of 6.25% and so on. 00:54 To create the data table, I will select cells C2 through F6, which I outlined 00:59 here in the worksheet, and then on the Data tab of the ribbon in the Data Tools group, 01:03 click What-if Analysis and then click Data Table. 01:08 You are creating a two input data table, so you need to put cell references in 01:12 both the Row input cell and Column input cell boxes. 01:15 The row input cell, which contains the original loan term, is cell B4. 01:19 So, I will type that in the Row input cell. 01:22 The column input cell, which contains the original Interest Rate, is cell B3. 01:26 So, I will type that in the Column input cell box. 01:30 When you click OK, Excel creates the data table. 01:33 I resize the columns by double-clicking the right edge of the column header at 01:37 the top of the worksheet. 01:38 So you can see that at a rate of 5.75% over 10 years, you would be paying 01:44 over \$164,000 a month. 01:46 If you stretch that out to 15 years, your monthly payment would only be 01:50 \$124,000 and change. 01:52 The downside of course is that the longer you pay, the more interest you pay. 01:55 So, the total cost of the loan would be higher. 01:58 Data tables are a powerful, but little used Excel tool. 02:01 If you find you need to analyze a transaction that depends on one or two 02:04 variable inputs, creating a data table is much faster than creating the 02:07 formulas by hand. Collapse this transcript
Finding target values using Goal Seek
 00:01 In many cases, you'll know the parameters of a financial transaction such as the 00:04 interest rate and term on a loan. 00:06 So the only other input you need to calculate your monthly payment is the loan amount. 00:10 If you have a target monthly payment you don't want to exceed, you can determine 00:14 the maximum amount you can borrow using Goal Seek. 00:17 I have set up this worksheet to reflect the terms of a loan. 00:20 The desired loan amount is \$15,000,000 the term is 10 years and the interest 00:24 rate is definitely 5.75%. 00:27 The monthly payment of these terms is here in cell B7. 00:30 But your company wants to pay a maximum of \$125,000 a month. 00:34 If you want to determine how much money you can borrow with a payment of at most 00:37 125,000 dollars, you can use Goal Seek. 00:41 To display the Goal Seek dialog box, you click the Data tab on the ribbon and 00:46 then in the Data Tools group, click What -if Analysis and then click Goal Seek. 00:51 In the Goal Seek dialog box, you can define three different values. 00:55 The first is the cell that you want to change. 00:57 Our target payment is \$125,000 a month. 01:02 The payment is calculated in cell B7. 01:03 So, that is the cell that we want to vary. 01:06 I'll click B7, and the cell reference appears in the Set cell box. 01:10 Now I can type in the value to which I want to change it. 01:13 In this case, my target payment is \$125,000 but I can't use a cell reference. 01:18 There is no RefEdit box here at the right side so I need to type the value in directly. 01:23 The final box, the By changing cell box, asks which cell you want to vary to 01:28 produce the result that you want. 01:30 In this case, that is cell B3, the Loan Amount, which is the controlling factor 01:34 of the monthly payment if these two arguments, the term and interest rate, stay constant. 01:38 I click cell B3. 01:41 It appears in the By changing cell box and I click OK. 01:45 When I do, Excel displays the Goal Seek Status dialog box, which tells me 01:50 that it did find a solution and I see that solution here in the body of the worksheet. 01:54 Basically, if my company wants to pay \$125,000 a month at most over 10 years at 02:00 an interest rate of 5.75% then it can borrow about \$11.4 million. 02:06 If I click Cancel, Excel returns the worksheet to its original state. 02:09 If however I click OK, Excel retains the Goal Seek results. 02:14 You can use some fairly advanced algebra to reverse engineer this sort of calculation, 02:18 but it's much easier to let Goal Seek do the work for you. Collapse this transcript
Conclusion
Goodbye
 00:00 Thanks a lot for taking the time to work through Excel 2007: 00:03 Introduction to Formulas and Functions. 00:06 I hope you have learned a lot and I hope that you can use the skills that 00:09 you have learned to develop even more skills as you use Excel. 00:12 Thanks again, and best of luck. Collapse this transcript

Are you sure you want to delete this bookmark?

### Bookmark this Tutorial

#### Description

{0} characters left

#### Tags

Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:
cancel

### bookmark this course

{0} characters left Separate tags with a space. Use quotes around multi-word tags. Suggested Tags:

Error:

go to playlists »

# Create new playlist

 name: description: save cancel

# You must be a lynda.com member to watch this video.

Every course in the lynda.com library contains free videos that let you assess the quality of our tutorials before you subscribe—just click on the blue links to watch them. Become a member to access all 98,468 instructional videos.

Your organization has a limited access membership to the lynda.com library that allows access to only a specific, limited selection of courses.

You're logged in as an account administrator, but your membership is not active.

Contact a Training Solutions Advisor at 1 (888) 335-9632.

# How to access this video.

If this course is one of your five classes, then your class currently isn't in session.

If you want to watch this video and it is not part of your class, upgrade your membership for unlimited access to the full library of 1,894 courses anytime, anywhere.

You can always watch the free content included in every course.

Questions? Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com.

You're logged in as an account administrator, but your membership is no longer active. You can still access reports and account information.

To reactivate your account, contact a Training Solutions Advisor at 1 1 (888) 335-9632.

# Need help accessing this video?

Call Customer Service at 1 1 (888) 335-9632 or email cs@lynda.com for help accessing this video.

site feedback

Thanks for signing up.

We’ll send you a confirmation email shortly.

• new course releases
• special announcements

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