IntroductionWelcome| 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 FormulasFamiliarizing 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| 00:00 | Excel formulas enable you to summarize
the data contained in your worksheets,
| | 00:04 | which helps you discover
important information about your business.
| | 00:07 | In this lesson, I'll show you the three
main ways to create a formula in Excel:
| | 00:11 | by typing, by using the controls on
the Formula tab of the Ribbon User
| | 00:15 | Interface, and by using the
Insert Function dialog box.
| | 00:19 | When you're ready to create a formula
by typing, you click the cell where you
| | 00:22 | want to create the formula and start typing.
| | 00:26 | First, you type an equal sign, which
indicates to Excel that it's to treat
| | 00:30 | the remainder of the text in the cell as a
formula as opposed to a number or text value.
| | 00:35 | In this case, I want to find the
average of the values in cells B3 through B10,
| | 00:40 | so I will start typing the word Average.
| | 00:43 | When I type the first few letters, Excel
displays a list of functions that start
| | 00:47 | with the letters I just typed.
| | 00:48 | This feature is called Formula AutoComplete.
| | 00:51 | If you don't see a list of functions
when you start typing, someone must have
| | 00:54 | turned off Formula
Auto Complete in your copy of Office.
| | 00:58 | I'll press these the Escape key twice,
the first time to get rid of Formula
| | 01:01 | AutoComplete list and the second time
to stop entering data into the cell.
| | 01:05 | Now I can click the Office button
and click Excel Options to display the
| | 01:11 | Excel Options dialog box.
| | 01:13 | On the Formulas page of the Excel
Options dialog box, you'll see in the Working
| | 01:17 | with formula section a
checkbox for Formula AutoComplete.
| | 01:22 | If this check box is cleared, you
should check it and then click OK.
| | 01:27 | When you do, you will turn on the
Formula AutoComplete and you'll be able to
| | 01:30 | complete the rest of the lesson.
| | 01:32 | Now, to enter the Average formula you
type an equal sign followed by aver.
| | 01:39 | When you do, you display the list of
functions that start with those letters.
| | 01:43 | The Average function is first on
the list and it's already highlighted.
| | 01:46 | If you wanted to select another one
of the visible functions, you could use
| | 01:50 | the Down Arrow key to highlight it, but
in this case I want to use Average so
| | 01:55 | I'll use the Up Arrow key to move back up
and now that it's highlighted, I can press Tab.
| | 02:01 | When you do Excel inserts the word
Average, which is the name of the function
| | 02:05 | you want to use and also hopefully
gives you your left parenthesis, so you can
| | 02:08 | start typing in your arguments.
| | 02:11 | Now that I have the function ready to go,
I can type in the cell range, which is
| | 02:14 | B3 through B10, type a right
parenthesis to end the function, hit Enter and
| | 02:24 | my average appears in cell E3.
| | 02:26 | To enter a formula using the controls
on the Formula tab of the Ribbon user
| | 02:29 | interface, you click the Formulas tab
to display the controls available to you.
| | 02:34 | The Excel 2007 Ribbon breaks out the
functions by the type of calculation
| | 02:38 | they perform, including financial
calculations, Date & Time and Math and Trigonometry.
| | 02:43 | I always thought that trig was part of math,
but it's been a while since my last class.
| | 02:48 | In this case I'll click
AutoSum and then click Average.
| | 02:51 | When I do, Excel creates an Average
formula and it identifies cell E3 as
| | 02:58 | a potential value for me to average.
That's actually not the value range that
| | 03:01 | I want to summarize.
| | 03:02 | That's B3 through B10.
| | 03:04 | So, I will type in that cell range B3:b10.
And upper/lowercase doesn't matter, so
| | 03:12 | I don't need to correct that and I
already have my right parenthesis, which
| | 03:15 | indicates the end of the function.
| | 03:18 | I can press Enter and Excel gives me
the same value as before. Just another way
| | 03:22 | to create the formula.
| | 03:24 | The final way to create a formula in Excel
is to use the Insert Function dialog box.
| | 03:28 | There are several ways to open
the Insert Function dialog box.
| | 03:32 | The most prominent is on the Formulas tab
and it is here at the left side of the Ribbon.
| | 03:37 | You can also click the Insert Function
button just to the left of the Formula Bar.
| | 03:42 | It doesn't matter which one you click;
you end up in the same dialog box.
| | 03:45 | So I'll click the Insert
Function button here by the Formula Bar.
| | 03:49 | You can use the controls in the Insert
Function dialog box to find functions
| | 03:53 | that you want use in your formulas.
| | 03:55 | If you want to search for a function,
you can type what you want to do in the
| | 03:59 | Search for a function box.
| | 04:00 | So let's say that I wanted to find an Average.
| | 04:05 | That's what I want to do.
| | 04:07 | When I click Go, Excel updates the
Insert Function dialog box here in the Select
| | 04:12 | a function list to indicate which
functions help me find an average.
| | 04:17 | The one at the very top is
the one I want to use, Average.
| | 04:20 | So it's selected and when I click OK,
Excel displays the Function Arguments dialog box.
| | 04:29 | Now, when you create an average, you can
specify which cells that you want to average out.
| | 04:34 | So in this case I want to average every
cell from B3 through B10 except for cell B6.
| | 04:41 | So I'm going to skip the
value for the year 2006.
| | 04:43 | So to create my arguments, I will
type in the cell range B3 to B5.
| | 04:51 | That's the first range that I want to
summarize and you'll notice that Excel is
| | 04:55 | filling in the values in
the cell range over here.
| | 04:59 | That's a great way to check yourself as
you're typing in your formula using the
| | 05:03 | Function Arguments dialog box.
| | 05:05 | If the values don't look right, then
you probably have your reference wrong.
| | 05:09 | I want to summarize another range,
which is B7 through B10, so in the Number2 box
| | 05:17 | I'll type B7 through B10.
| | 05:20 | Those are the only cell ranges that I
want to summarize, so I can press OK and
| | 05:28 | Excel creates the formula for me.
| | 05:30 | Regardless of how you choose to enter
your formula, you'll find that Excel has
| | 05:33 | several ways to make the process easier for you.
| | Collapse this transcript |
| Introducing arithmetic operators| 00:00 | Many Excel formulas include arithmetic
operators such as the plus sign and the
| | 00:04 | minus sign, but Excel doesn't just
read your formula from left to right and
| | 00:07 | take the operations in order. Instead
Excel has a strict order in which it
| | 00:12 | performs the operation.
| | 00:14 | This worksheet displays the list of
operators you can use in Excel and the order
| | 00:17 | in which it processes them.
| | 00:19 | I'll explain what the symbols mean
and how they work within formulas.
| | 00:23 | First, Excel checks whether a value is negative.
| | 00:25 | That is if it has the minus sign in front of it.
| | 00:28 | After that it checks if the
value is expressed as a percentage.
| | 00:31 | If the value is expressed as a
percentage, then Excel treats it as if it were
| | 00:35 | divided by 100. In other
words 75% is the same as .75.
| | 00:39 | So just by a way of a quick example.
| | 00:42 | If I type in the value -15,
Excel treats it as a value. -15.
| | 00:49 | If I were to create a formula where
I have -15 plus 75% then I will get
| | 00:59 | the result -1425.00%.
| | 01:05 | Now there are two things to
note about this calculation.
| | 01:08 | The first is that I wasn't taking 75%
of 15 or in this case -15. Instead what
| | 01:14 | I was doing was adding 75% or .75 to a
negative number, so the answer would be -14.25.
| | 01:23 | However, when you add, subtract,
multiply or divide a number by a percentage,
| | 01:28 | Excel expresses the result as a percentage.
| | 01:31 | So -14.25 is actually -1425%, because
again you're multiplying the value by 100.
| | 01:40 | After verifying whether value is
negative or whether it's expressed as a
| | 01:44 | percentage, Excel starts
with the other operations.
| | 01:47 | The first priority after the negative
sign and percentage is exponentiation
| | 01:53 | where the value is multiplied by
itself a specified number of times.
| | 01:57 | The symbol for an exponent is the caret,
which you type by pressing Shift+6 or
| | 02:02 | as I think of it a capital 6.
| | 02:04 | The formula equals two raised to the power
of 3, 2 ^ 3, would produce the result of 8.
| | 02:12 | That's 2x2x2.
| | 02:15 | If I were to type in the formula
= 2^4, I would get the result of 16.
| | 02:23 | The next operations in order are
multiplication and division followed by
| | 02:27 | addition and subtraction.
| | 02:29 | So let's say that I wanted to multiply 3x4.
I would get 12 and if I subtract 3 from 4, I get 1.
| | 02:43 | But what happens when you combine one
or more of these operations, especially
| | 02:47 | operations at a different
level into a single formula?
| | 02:50 | When you do, Excel examines the formula
before it starts making any calculations
| | 02:54 | and if it finds two operators at the
same level, it performs those operations in
| | 02:59 | left to right order.
| | 03:00 | That is if you type in the formula
=10/2*9+3, 10 divided by 2 would evaluate to 5,
| | 03:12 | multiply that result by 9, which is 45,
plus 3 which is the last operation in
| | 03:19 | priority order, you type
Enter and get the result of 48.
| | 03:24 | You can change the order of
operations in a formula by adding parentheses.
| | 03:28 | As an example, let's take the formula =9*2+3.
| | 03:35 | In this case it would be 9 times 2, which is
18, add 3 to the result and you would get 21.
| | 03:41 | If you want to change the order so
the addition happens first and then you
| | 03:44 | multiply that result by 9, you would write
the formula =9*(2+3), which equals 9 times 5 or 45.
| | 04:00 | Finally, you can use the comparison
operators equal, greater than, greater than
| | 04:05 | or equal to, less than, less than or
equal to and does not equal to create
| | 04:10 | formulas that return a result of true or false.
| | 04:12 | For example the formula =10=9
would return the result of FALSE.
| | 04:20 | The formula =10 is not equal to,
that's the less than sign and greater than sign,
| | 04:25 | 9 would return a result of TRUE and
finally 10>9 would return the result of TRUE.
| | 04:35 | So when would you want to create a
formula using these comparison operators?
| | 04:39 | Well, comparing 10 to 9 isn't really
a meaningful comparison, because it's
| | 04:43 | obvious that 10 is greater than 9.
| | 04:44 | Where you might use this type of
comparison formula is if you have one cell
| | 04:49 | that contains your sales goal for a
month and another cell that contains your
| | 04:54 | actual sales results.
| | 04:56 | If your sales results are greater than
your sales goal, then you could print
| | 04:59 | TRUE to indicate that
you should receive a bonus.
| | 05:02 | If your sales results are less than
your goal, then Excel could indicate FALSE
| | 05:07 | which tells your supervisor that
you don't get a bonus this month.
| | 05:11 | Always be sure to verify that your
formula's operations occur in the desired order.
| | 05:15 | If you're getting an answer you don't
expect, that's a great first place to
| | 05:18 | look for an error.
| | Collapse this transcript |
| Using cell references in formulas| 00:00 | When you create an Excel formula, it is
possible to type values such as interest
| | 00:04 | rates or commission
percentages directly into the formula.
| | 00:07 | But in almost every case, it's a better idea
to draw the value from a cell in your worksheet.
| | 00:11 | Putting a value in the cell both makes
the value visible instead of hidden in a
| | 00:14 | formula and enables you to change
the value without editing the formula.
| | 00:19 | I'll show you how to edit a formula
later in this chapter, but for now I'll show
| | 00:22 | you how to use cell references effectively.
| | 00:25 | The most direct way to enter a cell
reference into a formula is to type the reference.
| | 00:29 | Let's say that you want to use the
values from cells B4 and B5 in a formula to
| | 00:33 | calculate your revenue for those months.
| | 00:36 | To create that formula you'll type an
equal sign and then type B4+B5 press Enter
| | 00:45 | and you get the result.
| | 00:47 | You can also enter a cell's address into
a formula by clicking the cell when you
| | 00:50 | come to the point of the
formula where you want to put it.
| | 00:53 | For example, if you wanted to find half
of your revenue for a month, you could
| | 00:56 | calculate that amount by typing an
equal sign, clicking cell B5, which adds the
| | 01:04 | cell reference to the formula, typing
the multiplication operator, which is an
| | 01:10 | asterisk or a capital 8 and then multiplying by .5.
| | 01:14 | With that formula in place you can
press Enter and you get your result.
| | 01:19 | Now, let's say that you want to find
your total revenue for two periods, January
| | 01:23 | through March of 2008 and July
through September of the same year.
| | 01:27 | Those cells aren't all neighbors on
the worksheet so you'll have to refer to
| | 01:30 | them separately within your formula.
| | 01:33 | To do that, you can create a sum formula for
typing = SUM and now you type in the references.
| | 01:43 | So January through March of 2008 are
cells B3 through B5 and now I type a comma
| | 01:52 | indicating that there is going to be
another range and you'll notice that when I
| | 01:56 | typed the comma, Excel updated the
tooltip here beneath the formula that I'm creating,
| | 02:02 | where it says SUM then I have
my number 1 which is B3 through B5 and
| | 02:07 | now when I start typing in after
the comma, I'll be typing in 2.
| | 02:12 | So I wanted July through
September, so I have B9 through B11.
| | 02:21 | Also notice that Excel outlines those
groups of cells to indicate where they
| | 02:27 | fit in the formula.
| | 02:28 | B3 through B5 are outlined in blue.
The reference is within the formula
| | 02:33 | highlighted in blue, and notice that
these cells B9 through B11 are in green and
| | 02:40 | they are outlined in green
on the body of the worksheet.
| | 02:43 | So now when I type a right
parenthesis, I hit Return and I get my total.
| | 02:50 | You could create the same cell
references by clicking and dragging the cells
| | 02:53 | when you get to the right point of the formula.
| | 02:55 | So let's say for example that I type in
=SUM and I want to select B3 through B5,
| | 03:03 | I can just click and select type a
comma to indicate that I want to enter
| | 03:08 | another cell range and select B9 through B11.
| | 03:13 | I've created the formula, type a right
parenthesis to close the function and
| | 03:18 | I get the same result.
| | 03:21 | So that's how to add
disconnected cell ranges to a formula.
| | 03:24 | But what if, for some reason, you want to
perform a calculation on an entire row or column?
| | 03:28 | For example, to find the total
of every value entered into row 9.
| | 03:33 | To find the sum of all values in a row,
you type in this type of formula.
| | 03:38 | So I'm going to try to find the
value through row 9, so I don't want with
| | 03:41 | my formula in row 9.
| | 03:42 | If I type in =SUM, I can create a
reference to an entire row by typing in
| | 03:49 | the number of the row, in this case row 9,
followed by a colon, followed by the 9 again.
| | 03:57 | You notice when I did that Excel
highlighted all of the visible cells in row 9
| | 04:02 | and notice you'll just have to trust me
but believe me when I tell you that that
| | 04:05 | selection extends all the way to
the right edge of the worksheet.
| | 04:08 | So I can type a right parenthesis to close
the function, hit Return and I get my result.
| | 04:15 | One thing I should point out is that
the value in cell A9, which is July, is a
| | 04:20 | text value so it's not
included in the summary operation.
| | 04:24 | You can do the same thing to find the
total of all the values in a column.
| | 04:27 | So let's say that I wanted to get the
total of all the values here in column E.
| | 04:33 | to do that I create the formula =SUM(E:E),
which indicates that I want to all the
| | 04:41 | values in column E, hit
Return and I get my value.
| | 04:47 | Once again if you do create a formula
that includes all of the values in a row
| | 04:50 | or column, do not put the formula
in a cell in that row or column.
| | 04:55 | You'll create what's called a circular
reference because the formula would refer
| | 04:58 | to its own cell as part of its
calculation and generate an error.
| | 05:02 | Including cell ranges and formulas by
creating effective cell references makes
| | 05:06 | your job much easier.
| | 05:07 | Whether you select cells or enter their
addresses by hand, you can be sure you
| | 05:11 | are summarizing the proper
data when your references right.
| | 05:14 | In the next lesson, I'll show you how
to create cell references you can copy
| | 05:17 | safely from cell to cell.
| | Collapse this transcript |
| 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| 00:00 | Excel worksheets evolve over time to
reflect the nature of your business and how
| | 00:04 | you analyze its data.
| | 00:05 | So you'll almost certainly end
up changing some of your formulas.
| | 00:09 | You can edit Excel formulas in two ways:
within the cell and on the Formula Bar.
| | 00:14 | Editing a formula within a cell lets
you change the formula without moving your
| | 00:17 | mouse all the way up to the Formula Bar,
so it can save your few seconds every
| | 00:21 | time you want to make a change.
| | 00:22 | To edit a formula within a cell, you
double-click the cell that contains the
| | 00:28 | formula you'd want to edit, click in
the formula where you want to place the
| | 00:31 | cursor and then Backspace or Delete
over the text that you want change and
| | 00:37 | in this case I will change the formula
from a SUM function to an AVERAGE function.
| | 00:44 | I have it spelled correctly and I can
press Enter and now instead of having
| | 00:48 | the sum of the values from B3 and B4, I've
edited the formula so that it finds the average.
| | 00:53 | If you'd rather edit a formula on the
Formula Bar where you can see more of the
| | 00:57 | formula at a time without the
distraction of other worksheet elements, all you
| | 01:00 | need to do is click the cell that
contains the formula, click the text on the
| | 01:05 | Formula Bar and you can click
anywhere on the Formula Bar that you want.
| | 01:10 | You don't have to do it near or
in the formula and then you can edit.
| | 01:14 | So let's just say that I want to
change back to the SUM function, type it in,
| | 01:20 | everything looks right, hit Return and
I've edited the cell so it now finds
| | 01:25 | the sum of the values in cells B3 and B4.
| | 01:28 | Finally, if you're in the middle of
editing a cell and decide you don't want to
| | 01:31 | keep any of the changes you've made,
you can press the Escape key to revert to
| | 01:35 | the cell's starting condition.
| | 01:37 | So for example, if I were here and I
started typing in AVERAGE and I thought,
| | 01:45 | no, I really want to keep SUM,
the original formula that I had, I can press the
| | 01:50 | Escape key once to get rid of the
Formula AutoComplete display and then press
| | 01:55 | the Escape key again and I'm out of the cell
and it has reverted to its original formula.
| | 02:00 | If you do press Enter after you
change a cell's contents, you can always
| | 02:03 | press Ctrl+Z or click the Undo button on the
Quick Access toolbar to remove your changes.
| | 02:09 | Editing a formula helps you refine
your worksheet's contents to reflect your
| | 02:12 | changing business needs.
| | 02:14 | If you make a series of changes that
won't be easy to reverse, you should make a
| | 02:17 | backup copy of your workbook so you
can have it around in case you want to
| | 02:20 | revert to the original formulas or if
you and your colleagues need to review the
| | 02:24 | changes you've made.
| | Collapse this transcript |
| 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 FormulasCreating a SUM or AVERAGE formula| 00:00 | You use lots of different functions
in your workbooks, but two of the most
| | 00:04 | useful summary functions in
Excel 2007 are SUM and AVERAGE.
| | 00:09 | They operate as you'd expect,
calculating the sum and average of a series of
| | 00:12 | worksheet values, but I'd like to go
over each function in detail so you'll
| | 00:15 | have a greater appreciation of how they work.
| | 00:18 | The SUM function adds a
series of values together.
| | 00:21 | You can express those values as numbers,
such as the number 6, or as cell ranges where
| | 00:25 | the cells contain numbers.
| | 00:27 | As an example, consider a formula where
you add the yearly sales for three years.
| | 00:31 | The values appear here in cells, B2
through B4, so you can create a SUM formula.
| | 00:36 | I'll start that out by typing =SUM(.
| | 00:45 | Note that after you type the parenthesis,
a tooltip appears below the formula.
| | 00:49 | This tooltip gives you information
about the function's syntax, which is the
| | 00:53 | type of information the function needs
to perform as calculations and the order
| | 00:56 | in which it needs to receive these inputs.
| | 00:59 | The SUM function expects you to enter a
series of numbers or references to numbers.
| | 01:03 | If there's more than one reference,
such as if you wanted to add the values
| | 01:06 | in B2 through B4 and the value in D2, you
could separate the references using a comma.
| | 01:12 | In this case, the formula you
create would be =SUM(B2:B4,D2).
| | 01:23 | When you type the right parenthesis and
press Enter, Excel gives you the result.
| | 01:27 | The AVERAGE function is very similar to
the SUM function in that it allows you
| | 01:31 | to enter in a series of values as cell
ranges or numbers separated by commas.
| | 01:35 | In this case, you can calculate the
average yearly income for the three years
| | 01:38 | listed in cells B2 through B4 by
creating the formula =AVERAGE(B2:B4).
| | 01:48 | The Excel programming team created the
AVERAGE function so it added the values
| | 01:52 | from the cells, and then divided the
result by the number of individual values,
| | 01:55 | resulting in the answer
generated by those formulas.
| | 01:58 | You might wonder what would happen if
you try to create a sum or average formula
| | 02:02 | to summarize non-numeric values,
such as the labels in A1 or D1.
| | 02:06 | The answer is that the SUM function
treats the values as zero so adding them
| | 02:09 | makes no difference.
| | 02:10 | In other words, if I create the
formula =SUM(A1,B2), Excel just returns the
| | 02:21 | value in cell B2, 942,000, and it
treated the value in cell A1, the text Yearly
| | 02:28 | Revenue, as if it were zero.
| | 02:30 | The AVERAGE function
behaves slightly differently.
| | 02:33 | If there is a number in at least one
cell, the AVERAGE function ignores the
| | 02:36 | non-numeric values and counts the
number of cells that contain numbers.
| | 02:40 | So for example, if I were to type
=AVERAGE(A1,B2) and again I'm separating A1
| | 02:49 | and B2 with a comma to indicate that
they are separate ranges, right parenthesis, and
| | 02:55 | I get the average of 942,000.
| | 02:57 | Even though I summarized two cells,
Excel only counted one numeric value.
| | 03:02 | So it only divided 942,000 by 1.
But let's see what happens if I type in an
| | 03:09 | average formula that only refers
to cells that contain text values or
| | 03:13 | non-numeric values.
| | 03:15 | So I have A1 and D1, when I press
Return, Excel displays a #DIV/0! error.
| | 03:23 | The reason that it was trying to divide
by zero is that it counted no cells that
| | 03:28 | contain a numeric value.
| | 03:29 | Because of that it tried to
divide by zero and it showed the error.
| | 03:35 | The SUM and AVERAGE functions enable you
to summarize your data quickly and once
| | 03:38 | you understand how they work, you can
apply that knowledge to other functions
| | 03:41 | available to you in Excel 2007.
| | Collapse this transcript |
| 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 DataSummarizing 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| 00:00 | In the previous lesson, I showed you
how to create named ranges that refer to
| | 00:03 | groups of cells and make your
formulas easier to understand.
| | 00:07 | The one major limitation of those named
ranges is that they can't change in size
| | 00:11 | to reflect new data added to the list.
| | 00:13 | As an example, suppose you have a list
of sales figures in column C and you want
| | 00:17 | to add a value to reflect new results.
| | 00:19 | If you do, the cell that contains the
new value won't be included in the named
| | 00:23 | range unless you either edit the named
which I'll show you how to do it later in
| | 00:27 | this chapter or create a dynamic named
range that will change in size when you
| | 00:31 | add or remove data creating a dynamic
named range that will change in size when
| | 00:34 | you add or remove data.
| | 00:35 | Creating a dynamic named range is
somewhat advanced technique that uses a
| | 00:36 | formula to define the
cells included in the range.
| | 00:39 | But the good news is that you don't
need to know exactly what the formula does.
| | 00:43 | You just need to know enough to modify the
formula to reflect the contents of your worksheet.
| | 00:48 | In this case, the cells that currently
contain the data you want to summarize
| | 00:51 | using the named range
appear in cells C2 through C5.
| | 00:55 | To create a dynamic named range that
refers to those cells, on the Formulas tab
| | 01:00 | of the Ribbon, click Define Name
to display the New Name dialog box.
| | 01:06 | In the Name field type SalesTotal and
then in the Refers to box at the bottom,
| | 01:16 | you type this formula =OFFSET left
parenthesis sheet1, which is the name of the
| | 01:27 | active worksheet, explanation point,
which allows you to identify the cells
| | 01:31 | within the worksheet that will be
included into this dynamic named range.
| | 01:36 | Now, I can type in $C, $2,
which is an absolute reference to
| | 01:45 | the cell C2, 0,0 and again, you don't
need to know what these arguments do.
| | 01:51 | Just all you need to know is how to
implement this formula. Comma, COUNTA, which counts
| | 01:58 | all the cells that contain any value
left parenthesis sheet1 again, the current worksheet,
| | 02:05 | !$C: $C, which defines the
entire C column on this worksheet,
| | 02:16 | right parenthesis, -1 and I'll tell you why the
-1 is there in a second, 1 and a right parenthesis.
| | 02:26 | If I typed everything correctly, when I
press OK, Excel creates a dynamic named range.
| | 02:32 | So let's go back and review that formula
one more time from within the Name Manager.
| | 02:37 | What the formula does is starting
with cell C2 on Sheet 1, count the number
| | 02:42 | of cells in columns C that contain a
value, subtract 1 from that count to
| | 02:46 | ignore the label in cell C1, and
defines the range by counting down that
| | 02:50 | number of cells from C2.
| | 02:51 | For example, if there were four values it would
create the range from C2 to C2 + 3 cells, or C5.
| | 02:59 | When you click OK, Excel creates the named
range, which you can now use on a formula.
| | 03:05 | I'll close the Name Manager.
| | 03:07 | I'll do that here in cell F1 by typing
=SUM(SalesTotal) and the named range of
| | 03:17 | appears in the formula Auto
Complete list, but I'll just type it in.
| | 03:21 | So we have SalesTotal and notice that
we're referring to every cell in column C.
| | 03:27 | When I hit Return, I get the total.
| | 03:30 | Now let's see what happens when I
type a value in cell C7 and press Enter.
| | 03:35 | So lets say that I have a result for 2010,
Quarter number 1, and the Sales were 500,000.
| | 03:42 | When I press Enter, Excel updates the
result in cell F1, and the reason that
| | 03:48 | happened is because it is referring to
the dynamic named range SalesTotal and
| | 03:53 | cell C6, which now contains a value,
is part of that named range now.
| | 03:58 | There are two things you need to watch
out for when using dynamic named ranges.
| | 04:02 | The first is that you can't have any blank
cells in the list of values to be summarized.
| | 04:06 | If you do, Excel will count the non-blank
cells and end up one cell short of the list.
| | 04:11 | For example, if I were to type a value
into cell C8, Excel wouldn't be able to
| | 04:15 | include that cell as part
of the dynamic named range.
| | 04:18 | If I were to move the value back to
cell C7, it would be able to because
| | 04:22 | there's no black cell.
| | 04:23 | Now let's see what
happens if I delete the value.
| | 04:25 | So I added A6 through C6.
| | 04:30 | If I were to delete the values in those
cells, Excel changes the dynamic named
| | 04:34 | range definition again, and updates
the result of the SUM formula, SUM of
| | 04:38 | SalesTotal, to reflect the new
state of the dynamic named range.
| | 04:42 | The other consideration with dynamic
named ranges is that they don't show up in
| | 04:46 | the Name Box's list.
| | 04:48 | In this case, clicking the down arrow
would not display any named range.
| | 04:53 | I don't know why that's the case, but it is.
| | 04:55 | Dynamic named ranges help you
summarize changing list of data while still
| | 04:59 | creating human readable formulas.
| | 05:01 | What's more, unlike Excel tables, which I'll
cover later in this chapter, dynamic named
| | 05:05 | ranges are compatible with Excel 2003
and earlier versions of the program.
| | Collapse this transcript |
| 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| 00:00 | Excel 2007 makes it possible for you to
summarize large data list effectively,
| | 00:04 | but part of analyzing your data is
limiting the display to the groups of data
| | 00:08 | that interest you the most.
| | 00:09 | If you want to summarize your data and
be able to narrow down which data Excel
| | 00:13 | displays, you can create subtotals.
| | 00:16 | To create subtotals for your data list,
the data must meet certain criteria.
| | 00:20 | First, the data must be in a series
of columns, each column must have a
| | 00:24 | header, there may be no blank rows in
the list and the data must be sorted in
| | 00:28 | ascending order using the leftmost
column as the first sorting key, the second
| | 00:33 | as the second and so on.
| | 00:35 | The data in this list meets those requirements.
| | 00:38 | It is an unbroken list, the columns
have headers and there are no blank rows.
| | 00:43 | In addition, the data is sorted by year,
by a quarter, which is a group of three
| | 00:47 | months, and then by month.
| | 00:49 | With the data in shape, you can click
any cell in the list and then on the Data
| | 00:53 | tab of the ribbon, in the
Outline group, click Subtotal.
| | 00:58 | When you do, Excel
displays a Subtotal dialog box.
| | 01:02 | The first selection you can make is
how many subtotals you want to create.
| | 01:06 | The default is to create a subtotal
for each change in the value in the first
| | 01:09 | column, in this case Year.
| | 01:11 | I will leave it there for
the moment and come back to it.
| | 01:14 | You can also select which
summary function you want to use.
| | 01:17 | The most common choice is Sum but you
can select from several other functions.
| | 01:21 | Finally, you identify which columns
contain the values you want to summarize.
| | 01:25 | In, this case, the Revenue column is
the only one that contains values that can
| | 01:28 | be summarized meaningfully but if you
had another column that listed the number
| | 01:32 | of transactions in a month, you could
add a subtotal to that column as well.
| | 01:37 | The three checkboxes at the bottom
of the dialog box indicate whether you
| | 01:40 | want to replace the existing subtotals,
you do, Create a page break between
| | 01:44 | groups, you usually don't, and whether to put
the summaries below the data to which it refers.
| | 01:49 | I find it easier to read summaries that
are below the data it summarizes, so I
| | 01:52 | also leave that box checked.
| | 01:54 | When you're ready, click
OK to create the subtotals.
| | 01:58 | As your choices in the Subtotal dialog
box indicated, Excel created a subtotal
| | 02:02 | formula below the last value for the
year 2009, another below the last value for
| | 02:06 | 2010 and a Grand Total below the entire list.
| | 02:10 | The outline bars at the left edge
of the worksheet indicate where each
| | 02:13 | group begins and ends and it gives you
controls that you can click to hide or show data.
| | 02:18 | To hide a group, you click the
collapse button, which looks like a box with a
| | 02:22 | minus sign in the middle of it.
| | 02:23 | Clicking it hides the details for the
group, but showing only the summary.
| | 02:27 | If you click to Expand button, which
is a box with the plus sign, it restores
| | 02:31 | the details to the display.
| | 02:33 | Now let's take a look at
the subtotal formula itself.
| | 02:36 | The formula in Cell D14 is =Subtotal(9,D2:D13).
| | 02:43 | The function name is easy enough to
understand as this cell range D2:D13, but
| | 02:48 | what does the first argument, the number 9, mean?
| | 02:50 | The answer is that is a reference to
the function the subtotal formula uses to
| | 02:54 | summarize your data.
| | 02:56 | Unlike in previous versions of Excel,
where you needed to memorize which
| | 02:59 | function number meant which summary
operation, Excel 2007 displays them for you.
| | 03:04 | To see the list, edit the formula
and backspace over the number 9.
| | 03:08 | There are two sets of functions
you can use in the subtotal formula.
| | 03:12 | The first set, numbered 1 through 11,
operates on all values in the list whether
| | 03:17 | they are hidden or not.
| | 03:18 | The second set, numbered 101 through
111, summarizes only the values currently
| | 03:23 | displayed in the list.
| | 03:24 | Now let's go back and create our
subtotals at a different grouping level.
| | 03:27 | I will click Escape to get out of the Edit
and then on the Data tab, click Subtotal.
| | 03:34 | This time I will create the groups of a
Quarter level and click OK, and again,
| | 03:40 | I have the Replace current subtotals
checkbox selected, so I'll be replacing the
| | 03:45 | existing subtotals instead of adding to them.
| | 03:48 | When I click OK, Excel outlines my data.
| | 03:52 | Notice that there are subtotals after
each quarter but not after each year.
| | 03:56 | In other words, there is a subtotal
after quarter number 4 from 2009, but there
| | 04:00 | is no grand total or supplementary
subtotal for the year of 2009, the summaries
| | 04:06 | are only organized by quarter.
| | 04:08 | Now I will edit the Grand Total formula,
which is at the bottom of the outline,
| | 04:15 | so that it only summarizes visible data.
| | 04:18 | To do that, I will change the summary
operation from 9, which summarizes all
| | 04:22 | cells to 109, which summarizes all
visible cells, and I have 109 and the
| | 04:28 | formula looks correct. I'll click OK.
| | 04:30 | I will press Enter.
| | 04:33 | When I do, you notice the Grand Total
formula result didn't change but I do have
| | 04:38 | my new subtotal formula.
| | 04:39 | Now when I hide a group using the
collapse controls on the Outline bar, such as
| | 04:43 | for the second quarter of 2010, Excel
hides those values and it updates the
| | 04:51 | result of the Grand Total formula.
| | 04:53 | If I click the Expand button, Excel
restores the rows to the data list and
| | 04:59 | updates the Grand Total formula again.
| | 05:01 | Finally, I'd like to point out that if
you want remove the subtotal outlining
| | 05:04 | from your data list, you click any cell
on the list and then on the Data tab of
| | 05:09 | the Ribbon in the Outline group,
click Subtotal, and click Remove All.
| | 05:15 | Creating subtotals helps you
summarize your data quickly based on the
| | 05:18 | subdivisions you define.
| | 05:20 | In the next lesson, I'll show you how
to define custom outline groups so you
| | 05:24 | don't have to rely on
Excel to create them for you.
| | Collapse this transcript |
| 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| 00:00 | In Excel 2007 these list objects became
Excel tables, which are much more useful
| | 00:05 | when analyzing data in Excel.
| | 00:07 | Creating Excel tables makes it
easier for you to manage your data.
| | 00:11 | You create an Excel table by clicking
any cell in the data list and then on the
| | 00:15 | Home tab of the Ribbon in the Styles
group, clicking the Format as Table button,
| | 00:21 | and then selecting a table format.
| | 00:23 | I will select this one.
| | 00:26 | Take a look at the values in the Format
as Table dialog box, verify that Excel
| | 00:31 | has correctly identified the data to be
included in the table, and that it has
| | 00:34 | identified your table has a
header row, and then you can click OK.
| | 00:40 | If you ever want to change an Excel
table's formatting, you can click any cell
| | 00:43 | in the table, and then on the Design
contextual tab of the ribbon click the
| | 00:48 | Table Styles gallery's More
button and select the new format.
| | 00:54 | Excel creates tables with names such
as Table1 and Table2, but you can and
| | 00:59 | should change those names to
describe the data the table contains.
| | 01:02 | To rename an Excel table, you click
any cell in the table and then on the
| | 01:07 | Design contextual tab of the ribbon,
in the Properties group, you type a new
| | 01:12 | name in the Table Name box.
| | 01:15 | In this case, I have company sales,
so I will call the table CompanySales.
| | 01:23 | Press Return and it
changes the name of the table.
| | 01:27 | Every Excel table in a
workbook must have a unique name.
| | 01:30 | If you want to add a row of data to
an Excel table, you have two options.
| | 01:33 | The first option is to click the cell
at the bottom right corner of the table,
| | 01:38 | and then press the Tab key.
| | 01:41 | When you do, Excel creates
an entirely new table row.
| | 01:44 | So I'll just type in some data.
| | 01:46 | 2011, Quarter number 1, the month of January,
and a value of 500,000, and press Return.
| | 01:59 | Note that in column D where I typed
in the sales figure, Excel applied the
| | 02:03 | formatting from the cell above
and gave me a currency format.
| | 02:07 | You can also start typing in a row
directly below the existing table.
| | 02:11 | So for example, if I were in the row 27
and I started typing 2011 and pressed Tab
| | 02:17 | to move to the next cell to the
right, Excel creates a new table row.
| | 02:22 | So, for this I'll click 1, February
and I'll type in a result of $520,000.
| | 02:30 | Press Return and I have a new row in my table.
| | 02:33 | Another great feature of Excel tables
is that you can display the total row to
| | 02:37 | summarize each column.
| | 02:38 | To display the total row you click any
cell in the Excel table, and then on the
| | 02:43 | Design tab, in the Table Style
Options group, you check the Total Row box.
| | 02:50 | When you do, Excel displays the total
row at the bottom of the Excel table.
| | 02:55 | The cell to the right edge of the
total row usually contains numeric data.
| | 02:58 | So, Excel creates a subtotal formula
there. Unlike when you create subtotals
| | 03:02 | using the controls on the Data tab
of the ribbon, where Excel creates a
| | 03:06 | subtotal formula that summarizes every
cell in the column, when you create a
| | 03:09 | table, Excel summarizes the column using a
subtotal formula that considers only visible cells.
| | 03:14 | So, in the previous lesson where we
created a subtotal outline for a data
| | 03:19 | list, but not an Excel table, when you
created the subtotal formula, Excel made
| | 03:24 | this argument 9, which was to find the
sum of all cells regardless of whether
| | 03:29 | they were hidden or not.
| | 03:30 | When you create an Excel table, Excel
uses a function number 109, which finds
| | 03:35 | the sum for only those cells that are visible.
| | 03:38 | You can't create subtotals for an Excel
table, but you can group and ungroup rows.
| | 03:43 | To do that, you select cells and the
rows that you want group, say March and
| | 03:46 | April, and then on the Data
tab of the ribbon click Group.
| | 03:52 | Verify that you want to
group the rows and click OK.
| | 03:56 | You can then use the expand and collapse
controls to hide or display the data in
| | 04:01 | the rows that you grouped.
| | 04:02 | Now notice that I grouped March and April 2009.
| | 04:06 | When I hit those values Excel updated
the formula result here, the subtotal
| | 04:10 | formula, because it only
summarizes cells with the visible values.
| | 04:14 | When I click the Expand control, Excel
redisplays those two cells, those two rows,
| | 04:20 | and updates the subtotal formula.
| | 04:23 | I will remove that group. Click OK.
| | 04:28 | There is another way to limit the data that
appears in an Excel table, which is filtering.
| | 04:33 | When you create an Excel table filter
arrows appear at the right edge of each
| | 04:36 | cell in the header row.
| | 04:38 | Clicking a column's filter arrow displays
the tools at your disposal to create filters.
| | 04:43 | You can create two kinds
of filters on Excel tables.
| | 04:45 | Selection filters and rule filters.
| | 04:49 | A selection filter, as the name implies,
enables you to identify individual
| | 04:53 | values you want to include or exclude.
| | 04:56 | In this list for example, I can clear
the Select All checkbox for the Year
| | 04:59 | column and then select the year 2009.
| | 05:04 | When I click OK, Excel limits
the data in the Excel table.
| | 05:08 | So, it only displays values from the year 2009.
| | 05:11 | Note that Excel hides the rows from 2010,
and 2011, and updates the total of the
| | 05:18 | subtotal formula here.
| | 05:19 | To clear the filter, I'll click the
arrow and click Clear Filter from Year,
| | 05:26 | when I do, Excel restores the data.
| | 05:28 | I can also filter it by rule.
| | 05:30 | Let's say that I only wanted to display
months with sales levels over $400,000.
| | 05:35 | To create that filter, I would click
the Sales columns filter arrow, point to
| | 05:40 | Number Filters and then click Greater Than.
| | 05:43 | So, I have a custom filter and I only
want to show rows where Sales is greater
| | 05:49 | than 400,000 everything looks good.
| | 05:53 | I'll press Enter, and Excel filters
the data in the Excel table, again,
| | 05:58 | displaying only those rows that
contain values greater than 400000, and
| | 06:03 | updates the result of the
subtotal formula in the Total Row.
| | 06:06 | If I want to remove the filter, I'd
click the Sales fields filter arrow button,
| | 06:12 | and click Clear Filter from Sales.
| | 06:15 | One downside of Excel tables is
that they're not compatible with
| | 06:18 | previous versions of Excel.
| | 06:20 | If you find that you and your colleagues have
problems during workbook that contain Excel tables,
| | 06:24 | you can convert the table to normal cell range.
| | 06:26 | To do that you click any cell in the
Excel table, and then on the Design
| | 06:31 | contextual tab, in the Tools
group click Convert to Range.
| | 06:37 | Excel displays a dialog box
verifying that this is what you want to do.
| | 06:40 | When you click Yes, Excel retains your
formatting, but changes the Excel table
| | 06:45 | over to a normal cell range.
| | 06:47 | Excel tables help you summarize your
data effectively and as you'll learn in
| | 06:51 | the next lesson, also help you create
formulas that are easy for you and your
| | 06:54 | colleagues to interpret.
| | Collapse this transcript |
| Referring to Excel table cells in formulas| 00:00 | Excel tables provide a structure within your
workbooks that you can use to store your data.
| | 00:05 | One major benefit of using Excel tables
is that you can refer to table columns
| | 00:08 | in your formulas using the table and
column names, which makes your formulas
| | 00:12 | easier for you to interpret.
| | 00:14 | Excel tables consist of
rows and columns of data.
| | 00:17 | You can include an entire column of data
in a formula by creating what is called
| | 00:20 | a structured reference, which names
the table column you want to include.
| | 00:25 | To refer to Excel table data in the
formula, you type in = sign, followed by the
| | 00:30 | function you want to use, in this case
I will just use SUM and type a left parenthesis.
| | 00:36 | Now I can type in the name of the table.
| | 00:38 | This tables name is SalesData, so I
will start typing in Sal and you will
| | 00:42 | notice that the table name appeared
in the Formula AutoComplete list.
| | 00:47 | The name is selected, so I can
press Tab to add it to the formula.
| | 00:52 | Now I need to name the column, and
in this case I want to use Sales.
| | 00:57 | To name a column, I type a left square bracket,
| | 01:01 | and when I do, Excel displays a list of
items that I can use in this formula.
| | 01:06 | In this case, I want to summarize the SalesData
column, so I type in Sales, type a right square bracket
| | 01:15 | to close the reference to the table column,
type a right parenthesis to close the formula
| | 01:20 | function and hit Return.
| | 01:22 | The remainder of the items in the
Formula AutoComplete list represents
| | 01:25 | different parts of the table.
| | 01:26 | So I will just show you what they are,
I'll go back in and edit the formula.
| | 01:30 | I do need the left square bracket.
| | 01:34 | All, as the name implies,
refers to the entire table.
| | 01:38 | Data refers to everything except the header row.
| | 01:41 | Headers refers to just the header row.
| | 01:43 | And Totals refers to the
contents of the total row.
| | 01:47 | Finally, This Row refers to the table
row that is in the same worksheet row as
| | 01:51 | the cell that contains the reference.
| | 01:53 | I'll show you how that works.
| | 01:55 | Let's say that I wanted to create a
formula here in cell F5 that summarizes all
| | 02:00 | the numerical values in
row five of the worksheet.
| | 02:03 | In other words, these cells here.
| | 02:08 | Now the result in this case isn't going
to make any sense, because I'm adding a
| | 02:11 | year to a quarter to a sales figure
but I just like to illustrate how the
| | 02:15 | formula would work, if the
values did make sense to add together.
| | 02:18 | So to create this formula, type = SUM,
left parenthesis and now I can type the table name,
| | 02:26 | which is SalesData. It's selected.
| | 02:28 | I will press Tab to accept it.
| | 02:30 | Type a square bracket and now I'll type This Row.
I've clicked it and press Tab to accept it.
| | 02:40 | Type a square bracket to close the column reference.
Type a right parenthesis to close the formula's function.
| | 02:49 | Click Return and there we have all
the values in row 5, 498,073 + 2 + 2009.
| | 02:59 | Like dynamic named ranges, which I
described in the creating a dynamic named
| | 03:03 | range lesson earlier in this chapter,
| | 03:05 | formulas that refer to entire table column's updates
| | 03:07 | reflect any data that's
added or taken away from that column.
| | 03:11 | If I were to add another data point to
the column, so let's say go for 2011,
| | 03:19 | Quarter number 1, Month of
January and type in $500,000,
| | 03:26 | Excel would update the result of the
formula that finds the sum of every value
| | 03:30 | in the SalesData table's Sales column.
| | 03:34 | Referring to Excel tables in your
formulas makes your formulas easier for you
| | 03:37 | and your colleagues to understand.
| | 03:39 | But remember that Excel tables can
only be used in workbooks created in
| | 03:42 | Excel 2007 and later.
| | 03:44 | If you collaborate with users who still
have Excel 2003 or earlier versions of
| | 03:48 | the program you should use dynamic
named ranges to refer to data columns that
| | 03:52 | could expand or contract.
| | Collapse this transcript |
| Finding data using VLOOKUP and HLOOKUP formulas| 00:00 | When you maintain lists of data in
Excel, you'll find it useful to be able to
| | 00:03 | lookup values in those lists.
| | 00:05 | Rather than page through your data by
hand or use the Find feature, you can
| | 00:09 | create formulas that enable you to enter
a lookup value in a cell and have Excel
| | 00:13 | return related information
from a list or an Excel table.
| | 00:16 | The lookup formula you use to find data
in a table depends on whether your data
| | 00:21 | is laid out by column, in
which case you use VLookup.
| | 00:24 | The V stands for vertical, or by
row in which case you use HLookup.
| | 00:28 | The H stands for horizontal.
| | 00:30 | VLookup formulas let you type a value
into a cell, and then search the leftmost
| | 00:34 | column of the data list or
Excel table to find that value.
| | 00:37 | When it does, Excel returns a value
from the column you specify in the same row
| | 00:42 | where it found the Lookup value.
| | 00:44 | As an example, here's a table named
Parts Data that contains several part
| | 00:48 | numbers, part name, and the price for the parts.
| | 00:51 | You create a VLookup formula by
typing an equal sign followed by VLookup.
| | 00:57 | When you type the left
parenthesis, you'll get a list of the
| | 00:59 | functions arguments.
| | 01:00 | The first three arguments, lookup_value, table
_array, and col_index_num, are all required.
| | 01:07 | The final argument, range_lookup,
determines whether the VLookup formula can
| | 01:11 | return an approximate match to the lookup
term, or whether you require an exact match.
| | 01:16 | Setting the argument to True or
leaving it blank allows an approximate match,
| | 01:20 | while setting it to False requires
the formula to find an exact match.
| | 01:24 | The first argument asked for the cell
that contains the value you want to lookup.
| | 01:28 | In this case that value will appear in
cell F1, so that is the cell address I
| | 01:33 | will type for the first argument, and
then type a comma to indicate that I'm done,
| | 01:37 | and I'm ready to move
on to the second argument.
| | 01:40 | The second argument asks for a reference
to the cells that contain the data list
| | 01:43 | you are working with.
| | 01:45 | You can type the reference as a cell
range, such as A3 to C7, or you can type
| | 01:50 | the name of an Excel table.
| | 01:52 | In this case I know the table is named
Parts Data, so that is what I'll type in.
| | 01:59 | The third argument specifies the table column
where I want the formula to find its result.
| | 02:03 | In this case, that's the second column,
so I will type the number 2 to indicate
| | 02:08 | the second column from the
left and then type a comma.
| | 02:11 | I don't want Approximate Matches, in
other words, I want to have an Exact Match
| | 02:15 | for the part number that I type in.
| | 02:17 | So I'll set the range_lookup argument to False.
| | 02:22 | Type a right parenthesis to close
out the function, and hit Return.
| | 02:26 | I haven't typed a value in cell F1 yet, so
the VLookup formula returns an #N/A error.
| | 02:31 | However when I type the part number
P001 into cell F1, and hit Return, Excel
| | 02:38 | returns the value Spark plug.
| | 02:40 | And again, just to walk through the
formula, the VLookup formula finds a value
| | 02:45 | in F1, in this case P001.
| | 02:48 | It looks in the cell range, in this
case the Parts Data table, which is here.
| | 02:53 | It looks in the first column by default
to find the part number, which is P001,
| | 02:59 | and then it looks in the second column
in the same row where it found P001, and
| | 03:04 | it finds the result Spark plug, which
is the value it would put in cell F2,
| | 03:08 | which is the cell that
contains the VLookup formula.
| | 03:11 | If I change the value in F1 to P002, Excel
updates the result of the VLookup formula.
| | 03:20 | Now I have P002, which is here in the
Parts Data table, and the value in the
| | 03:26 | second column of that row is Rotor,
which is the value that appears in cell F2.
| | 03:31 | If I type the value P100 into cell F1
and hit Return, Excel returns the #N/A
| | 03:38 | error that we saw before.
| | 03:40 | The reason is that I'm requiring exact
matches and because there is no exact
| | 03:44 | match of the term P100,
Excel displays the error.
| | 03:47 | Now let's look at a case where it's
okay to allow approximate matches in
| | 03:51 | the VLookup formula.
| | 03:52 | I'll use a different example this
time on the Discounts worksheet.
| | 03:56 | And this is an Excel table that shows
the discount available for customers who
| | 03:59 | order a given quantity of auto parts.
| | 04:01 | The Discount levels are at 1,
10, 100, 1000 and 10,000 units.
| | 04:06 | Obviously very few of your customer's
orders will be in exactly those quantities.
| | 04:10 | So, you need to allow approximate matches.
| | 04:13 | Remember that if you'll leave the
range_lookup argument blank, or set it to
| | 04:16 | True, then the VLookup formula can find
either an exact match, or an approximate match.
| | 04:21 | In other words, setting the range_lookup
argument to True tells the formula yes,
| | 04:24 | you can return an approximate match.
| | 04:26 | If the formula doesn't find the exact
value entered into your input cell,
| | 04:30 | it returns to the next largest value
that is less than the Lookup value.
| | 04:34 | Note that this table is sorted into
ascending order based on the contents of
| | 04:39 | the leftmost column, which is column number 1.
| | 04:42 | If the table isn't sorted by the
values in the leftmost column and you allow
| | 04:45 | approximate matches, the VLookup
function will generate an error.
| | 04:50 | You can sort the table's contents by
clicking any cell in the leftmost column,
| | 04:53 | and then on the Data tab of the Ribbon,
click the Sort Smallest to Largest,
| | 04:58 | which is sorting in ascending order.
| | 05:00 | So now, let's create our VLookup formula.
| | 05:05 | In this case, creating the formula the
VLOOKUP(F1, which is the cell that will
| | 05:12 | contain the value I am looking up,
and then the name of the table, which is
| | 05:15 | DiscountList, which appears in my
Formula AutoComplete list, comma and then we
| | 05:21 | have 2, which is the column from which
I want to return the value, type Return,
| | 05:27 | type a right parenthesis.
| | 05:29 | Hit Return and Excel
displays the familiar #N/A error.
| | 05:33 | But in this case that's okay
because I don't have a value in F1.
| | 05:36 | Now I can start typing values into F1 and
show you how the formula returns its results.
| | 05:41 | Let's say that I wanted to have an order with
the quality of 10. When I type in 10, I get 5%.
| | 05:47 | Now let's see what happens if I type in
the value of 11, which is greater than
| | 05:51 | 10 but less than 100, so it
doesn't qualify for a 10% discount.
| | 05:55 | Type in 11, hit Return and I still get 5%,
which is correct for an order of the quantity.
| | 06:02 | If I type in 100 and hit Return, I get
10% discount, which as you can see from
| | 06:08 | the table is correct.
| | 06:09 | Now, let's say that I have an order
quantity of 50,000, which is larger than the
| | 06:16 | largest value in this table.
| | 06:18 | When I hit Return, I get 20% discount,
which is the maximum discount allowable.
| | 06:24 | And finally I'll show you how to
generate an error using this type of formula.
| | 06:28 | The lowest value in this table,
in the leftmost column is 1.
| | 06:33 | So, if I type a value in that's
less than 1, I should get an error.
| | 06:37 | So, I have a quantity, I'll type in 0,
which is less than one, hit Return and
| | 06:42 | I get the #N/A error.
| | 06:44 | The final function I would like to
show you is the HLookup function.
| | 06:48 | Where VLookup uses vertical list, in
other words, lists that are arranged by
| | 06:51 | columns, HLookup uses lists
that are arranged in rows.
| | 06:55 | So I'll go to the HLookup worksheet,
and I'll show you the table that I have.
| | 07:01 | Well, it's not really a table, but it is
a selection of values that are laid out
| | 07:05 | in rows instead of in columns.
| | 07:07 | So, we have our headers, Shelves,
Washers and Bolts, and this is the number of
| | 07:11 | parts that you would need to put together a
bookcase with a particular number of shelves.
| | 07:15 | If you want to know the number of
washers required for a bookcase with five shelves,
| | 07:18 | you would create
this formula in cell I2.
| | 07:23 | That is =HLOOKUP(I1, which is the cell
that will contain the number of shelves,
| | 07:32 | comma, A3:F5, which is the table where I
want to look up the data, comma and then 2.
| | 07:43 | 2 is the row from which I
want to return the value.
| | 07:46 | In other words, the HLookup function
finds a value in the first row, as opposed
| | 07:51 | to the first column, and then it finds the
value in a corresponding row that you name.
| | 07:56 | In this case, it's row number two,
so we will find the number of washers.
| | 08:01 | Type the right parenthesis, hit Return
and I get the #N/A error, because there
| | 08:05 | is no value in cell I1.
| | 08:09 | I'll type in 5, which is the number of
shelves, hit Return and you'll see that
| | 08:13 | it returned the correct value.
| | 08:15 | I was looking for the number of washers that I
would require for a bookcase with five shelvess.
| | 08:21 | Here in F3, we have the number five,
indicating five shelves, and the number of
| | 08:26 | washers is 20, which the
formula returned correctly.
| | 08:29 | Note that in this HLookup formula, I did
not type a final argument, which is the
| | 08:34 | range_lookup argument.
| | 08:35 | Setting that argument to True or
leaving it blank allows an approximate match.
| | 08:39 | While if you require an
exact match, you set it to False.
| | 08:43 | That argument works exactly the same
way in HLookup as it does in VLookup.
| | 08:47 | The only difference between the two
formulas is that the first row must be
| | 08:51 | sorted in left to right order,
if you allow approximate matches.
| | 08:55 | In practical terms, you'll use VLookup
much more often than you use HLookup,
| | 08:59 | but it's good to know that HLookup is available
you if you need that type of functionality.
| | Collapse this transcript |
|
|
4. Preparing Data for Analysis in ExcelImporting data into Excel| 00:01 | If you work with colleagues who don't
have access to Microsoft Excel, you can
| | 00:04 | have them export their data to a text
file or a Comma Separated Value File.
| | 00:09 | As the name implies, a text file
contains a series as numbers or individual
| | 00:12 | values are separated by spaces,
tabs or some other character.
| | 00:16 | The most common separating
character is the Comma, hence the name Comma
| | 00:20 | Separated Value File.
| | 00:22 | To bring data from a text to a Comma
Separated Value File into Excel,
| | 00:25 | you click the Data tab on the ribbon and then in
the Get External Data group click From Text.
| | 00:32 | When you do, Excel displays
the Import Text File dialog box.
| | 00:36 | In this case my file that I want
to bring in is in my Exercise Files
| | 00:41 | folder, Chap04, DataSource.
| | 00:47 | When I double-click the file or if I
just click it and click Open then Excel
| | 00:51 | displays the Text Import Wizard.
| | 00:54 | The first Text Import Wizard screen
shows a preview of your data and asks if the
| | 00:57 | source data file contains data that is
delimited, that is separated by specific
| | 01:02 | characters or whether each
data field is of a fixed width.
| | 01:06 | Most modern applications generate Comma
Separated Value Files, but you will find
| | 01:10 | a lot of older applications
particularly those in the payroll departments,
| | 01:14 | they create files for each field
has a fixed number of characters.
| | 01:17 | In this case, the source file is
delimited by commas, so I will leave the
| | 01:21 | Delimited option button selected and click Next.
| | 01:25 | On the second page of the Text Import
Wizard, you get to select which character
| | 01:28 | delimits or separates one value from another.
| | 01:32 | In this case, it's the Comma, so I will
clear the Tab Delimiter checkbox and check Comma.
| | 01:40 | When I do, the Wizard updates the
Data Preview pane to indicate how it will
| | 01:43 | separate the data when it brings it into Excel.
| | 01:45 | The preview looks correct, so I will click Next.
| | 01:49 | The final Text Import Wizard page
lets you assign data types to the
| | 01:52 | imported files columns.
| | 01:54 | Leaving the General option button
brings in numeric values as numbers, dates as
| | 01:57 | dates and everything else as text,
which works for the vast majority of files.
| | 02:01 | So, I will leave the General
option selected and click Finish.
| | 02:05 | After I click Finish, the Import Data
dialog box appears asking where you want
| | 02:10 | to import your data.
| | 02:11 | You can click the RefEdit button at the
right edge of the existing worksheet box
| | 02:15 | to select the cell you want at the
upper left corner of the imported data.
| | 02:18 | Usually that's cell A1 or select the
New Worksheet option to create a new
| | 02:22 | worksheet and import your data there.
| | 02:24 | Clicking the Properties button at
the bottom left corner of the dialog box
| | 02:27 | displays the External Data
Range Properties dialog box.
| | 02:30 | I will show you how to control your
Data Connections properties in the next
| | 02:33 | lesson, Managing an External Data Connection.
| | 02:36 | When you are done, click OK
to bring the data into Excel.
| | 02:40 | When you work with colleagues who, for
whatever reason, can't send you their
| | 02:42 | data in an Excel workbook, always
ask for a Comma Separated Value file.
| | 02:46 | They are the easiest external
files to import into your worksheets.
| | Collapse this transcript |
| Connecting to an external data source| 00:01 | When you import data into Excel 2007,
you don't just copy the data once and then
| | 00:05 | lose the connection to the original file.
| | 00:07 | Instead, Excel creates a data
connection to the source file and if you tell it
| | 00:11 | to do so, checks the file to see if
its contents have changed, and your
| | 00:15 | worksheet needs to be updated.
| | 00:17 | You get to control how Excel updates
your worksheet data by displaying the
| | 00:20 | External Data Range Properties dialog
box, but first I would like to show you
| | 00:25 | what happens when you open a workbook
that contains a link to an external file.
| | 00:29 | To do that I will click the Office
button and open ManageLinks, which is the
| | 00:35 | file that I want to use.
| | 00:38 | You will notice that when I open the
file, a Security Warning appeared and that
| | 00:41 | is that Data connections have been disabled.
| | 00:45 | Excel 2007 and Office 2007 as a whole
paid a lot more attention to security
| | 00:50 | settings, because links to external
files can present problems, such as through
| | 00:54 | viruses or Trojan Horses,
anything along those lines.
| | 00:58 | Then Excel, based on your
Security Settings, indicates that data
| | 01:02 | connections have been disabled.
| | 01:04 | If you want to re-enable this connection,
you can click the Options button and
| | 01:08 | then in the dialog box that appears,
verify that the file path is correct and
| | 01:13 | then click Enable this content.
| | 01:15 | When you click OK, the warning goes away
and you can work with your data normally.
| | 01:20 | To manage with the Connection
Properties of an existing data connection,
| | 01:23 | you click any cell in the imported
data and then on the Data tab of the
| | 01:27 | ribbon, click Properties.
| | 01:30 | The External Data Range Properties
dialog box provides controls you can use to
| | 01:34 | determine how and when Excel updates
your workbook's contents by checking the
| | 01:37 | source file for changes.
| | 01:39 | The default settings Save the Query
Definition, which maintains the link to the
| | 01:42 | file, asks you for the name of the file
you want to check for changes, preserves
| | 01:47 | any formatting present in the source
file, adjust the width of the columns in
| | 01:50 | your worksheet to display all the
imported values and insert cells for new data
| | 01:55 | and delete unused cells if the
number of rows in the source file changes.
| | 01:58 | I want to maintain my data connection
for the rest of this exercise, so I won't
| | 02:02 | show you exactly what happens when
the Save Query Definition check box is
| | 02:06 | cleared, but what would happen is
Excel would remove the connection to the
| | 02:10 | external data file which
has good news and bad news.
| | 02:13 | The good news is that you wouldn't
have to wait for Excel to update your
| | 02:15 | workbook's contents if the external
data file had changed, but the bad news is
| | 02:20 | that any updates to the source
file wouldn't appear in your workbook.
| | 02:23 | If there is a possibility that the
source file from which you are drawing
| | 02:26 | external data could change, always leave
the Save Query Definition check box selected.
| | 02:31 | Now I would like to describe how some of
the other settings in the dialog box work.
| | 02:35 | For example right now, I have selected the
Prompt for file name on refresh check box.
| | 02:41 | Whenever you refresh a data connection
with the Prompt for file name on refresh
| | 02:45 | check box selected, Excel will display
a dialog box asking you which file you
| | 02:49 | want to draw the data from.
| | 02:51 | One case where you might want to do
that is if you have a worksheet that
| | 02:53 | displays the current week's payroll information.
| | 02:56 | If the name of the file could change,
such as from week1.csv to week2.csv and so on,
| | 03:01 | then you should have the
Prompt for file name checkbox selected.
| | 03:04 | If the file name will always be the
same then you can clear that box and
| | 03:08 | save yourself a step.
| | 03:10 | If there is a possibility that your
source file will update continuously or at
| | 03:14 | least very often, then you can have
Excel look for changes in the source file
| | 03:18 | at regular intervals.
| | 03:19 | To do that check the Refresh every box
and then you can use the Spin control
| | 03:24 | to either type in or select a value
that tells Excel how many minutes
| | 03:27 | it should go between refreshes.
| | 03:30 | You can also have Excel check the source file
for changes whenever you open your workbook.
| | 03:34 | To do that, check the Refresh
data when opening the file box.
| | 03:38 | If you want Excel to remove the
imported data that was already in the workbook
| | 03:41 | whenever you close the workbook, make
sure you check the Remove external data box
| | 03:45 | that activates after you check the
Refresh data when opening the file box.
| | 03:50 | The three inactive boxes under Data
formatting and layout only become available
| | 03:54 | when you bring in data from a database.
| | 03:56 | The Include field names box is checked
by default and it's the only one of the
| | 04:00 | three options you really need to select.
| | 04:02 | Excel worksheets have their own row
numbers and you can sort and filter the
| | 04:05 | data in Excel, so you don't need the
other two options selected to work with
| | 04:09 | your data effectively.
| | 04:11 | Now I'll refer to the final
set of controls down here.
| | 04:14 | If it's possible that the number of
rows in the source document could change,
| | 04:17 | I always select the first option,
which is Insert cells for new data and
| | 04:21 | delete unused cells.
| | 04:22 | That's also the default selection.
| | 04:25 | The other two options can lead to
errors by overwriting cells or rows.
| | 04:29 | The first option is simpler and less
prone to error if your hard disk doesn't
| | 04:32 | spin quickly enough to keep up with the changes.
| | 04:35 | Finally, if you have formulas in the
column to the right of the last column of
| | 04:38 | imported data, you should select the
final checkbox to have Excel create those
| | 04:42 | formulas next to every cell that contains data.
| | 04:45 | Excel 2007 maintains data connections
to your external files, so you don't have
| | 04:49 | to rerun the Text Import Wizard every
time you want to update your worksheet
| | 04:52 | with new data from outside sources.
| | Collapse this transcript |
| Cleaning up data imported into Excel| 00:01 | After you bring your data into Excel,
you will need to ensure that you can use
| | 00:04 | it for mail mergers or other operations.
| | 00:08 | To do so, you will need to get rid of
extra characters and to make sure the
| | 00:10 | words are properly capitalized.
| | 00:13 | It can be a bit of a drag but Excel has
a number of built-in functions that you
| | 00:16 | can use to clean up your data.
| | 00:18 | The first step I go through when I
clean up imported data is to get rid of any
| | 00:21 | characters that don't
usually appear in an address.
| | 00:24 | I will allow periods which often
appear in street abbreviations and names and
| | 00:28 | the number sign, which some folks use
to indicate apartments or post office boxes,
| | 00:32 | but I definitely
want to get rid of commas.
| | 00:35 | Commas are useful when you write out an address
by hand but I don't want them in my data file.
| | 00:39 | I want to place the commas myself in the
letter that I use as a target for my mail merge.
| | 00:44 | To get rid of any commas in this data set,
I will use the Find and Replace function.
| | 00:49 | To display the Replace page of the
Find and Replace dialog box, you can
| | 00:53 | display the Home tab of the ribbon
and then in the Editing group click Find
| | 00:59 | and Select and click Replace.
| | 01:03 | On the Replace page of the Find and
Replace dialog box, you type the character
| | 01:07 | you want to get rid of in the Find what box.
| | 01:10 | In this case, that would be the comma.
| | 01:12 | I will leave the Replace with box
blank, so Excel deletes the character.
| | 01:16 | In other words, I am
replacing the comma with nothing.
| | 01:20 | When I click Replace All, Excel
displays a confirmation message.
| | 01:24 | I can click OK and you see that the
commas have disappeared from the data set.
| | 01:30 | I am done with my Find and
Replace, so I will click Close.
| | 01:34 | In addition to Find and Replace,
there are three functions you can use to
| | 01:37 | prepare you data, Clean, Trim and Proper.
| | 01:41 | The Clean function removes any
unprintable or otherwise invisible
| | 01:45 | characters from your data.
| | 01:46 | You don't run into these characters
much anymore but they were very common when
| | 01:50 | you brought data from mainframe
systems into early versions of Excel.
| | 01:53 | For example, in some early word
processors the character sequence Ctrl+H
| | 01:58 | represented one press of the Backspace key.
| | 02:00 | The Clean function gets rid of all
those characters so they don't sneak up on
| | 02:03 | you when you work with your data as a
string in Excel and as an example, there
| | 02:07 | is one of the invisible characters right there.
| | 02:10 | The Trim function removes extra
spaces from a text string, leaving a single
| | 02:14 | space between each word.
| | 02:16 | The Trim function is particularly
useful when you scan a paper document
| | 02:19 | into your computer.
| | 02:20 | Some typist still put two spaces after
a period, but the more common style for
| | 02:24 | electronic documents is to have a single space.
| | 02:27 | Finally, the Proper function
capitalizes the first character of each word.
| | 02:31 | Some folks type in all capital letters
and others don't use capitals at all but
| | 02:35 | the Proper function changes the text
so the first letter's always capitalized.
| | 02:40 | You will need to verify that the
first letter and only the first letter
| | 02:42 | should be capitalized for each
person's name but if your data has a lot of
| | 02:46 | transgressions against proper capitalization,
you will save time by using the Proper function.
| | 02:51 | So here's how I implement them.
| | 02:53 | I like to combine the three functions
into a single formula such as =PROPER
| | 03:00 | and then nesting the functions I put
TRIM and then another left parenthesis
| | 03:07 | then CLEAN and then the address of the cell
that contains the data I want to transform.
| | 03:14 | So in this case that is
cell A2, which is my address.
| | 03:18 | To the left. That's not my real address.
| | 03:21 | Type a right parenthesis,
type a right parenthesis again.
| | 03:25 | Type a final right parenthesis
to close all of the functions.
| | 03:27 | You will notice that when I created
the Clean function in the middle of my
| | 03:33 | formula, Excel displayed the left
parenthesis that opens the function
| | 03:37 | arguments in purple.
| | 03:39 | When I closed it, it also
displayed the right parenthesis in purple.
| | 03:43 | So, I know that those two
parentheses are a match.
| | 03:46 | After Trim, I have a green left
parenthesis and the right parenthesis that
| | 03:50 | matches it is over here and finally
when I created Proper, which was my
| | 03:54 | outermost, my first function, there is a
left parenthesis in black and the right
| | 04:00 | parenthesis in black appears
here at the end of the formula.
| | 04:05 | The first and last parenthesis in
your formula should always be black.
| | 04:09 | That is the default color that
Excel uses for the outermost function.
| | 04:13 | When the colors match you are ready to go.
| | 04:16 | So, when I press Enter,
Excel displays my address.
| | 04:20 | Now notice that everything worked.
| | 04:22 | Excel got rid of my extra spaces,
which were the only mistakes that I made in
| | 04:26 | this particular address, but note that Excel
made two changes I am going to have to undo.
| | 04:30 | The first was to change North-West, NW
both capitals to N lowercase w and changed
| | 04:37 | DC to D lowercase c. So, I will
need to go and clean that up later.
| | 04:42 | Here's my formula but note that because
I used a relative reference for cell A2,
| | 04:48 | when I copy the formula down to cells
B2 and B3, Excel will update the formula
| | 04:53 | so that it affects the
cells to the left A3 and A4.
| | 04:57 | So, when I do that, Excel updates
the formulas and it works with my data.
| | 05:03 | Now that I have the corrected text
in column B, I can delete the original
| | 05:06 | incorrect data in column A and
use the cleaned up data in my tasks.
| | Collapse this transcript |
| Creating data validation rules| 00:01 | Your calculations are only as
good as the data in your worksheets.
| | 00:04 | You can't do a lot to control the data
you get from outside sources, but you can
| | 00:07 | help your staff enter data
correctly by creating Data Validation rules.
| | 00:12 | To create a Data Validation rule, you
select the cell or cells into which you
| | 00:15 | want to put the rules and then on the
Data tab of the Ribbon in the Data Tools
| | 00:20 | group, click the body of the Data Validation.
| | 00:24 | When you do, the Data
Validation dialog box appears.
| | 00:28 | You can use the controls in the Data
Validation dialog box to define your rules.
| | 00:32 | To display the rules that you can
create click the Allow box's down arrow.
| | 00:37 | The Any value option essentially turns
off any validation, but the other types
| | 00:41 | of rules that you can create require
the user to enter a whole number, decimal
| | 00:44 | number, value from a predefined list,
date, time, text of a particular length,
| | 00:49 | or a value that meets a custom rule you've defined.
| | 00:52 | In this lesson, I'll show you how to
create a rule for a whole number value, and
| | 00:56 | for text of a particular length.
| | 00:58 | In the next lesson, I'll show you how
to require a user to enter a value from
| | 01:01 | the predefined list.
| | 01:03 | To require a user to enter a whole
number value, click the Allow box's down
| | 01:07 | arrow, and then click Whole number.
| | 01:10 | When you do, the Data box updates to
display the types of rules that you can
| | 01:13 | create, between, less than,
greater than and so on.
| | 01:18 | In this case I want to require the user
to enter a value between 1 and 100, so
| | 01:22 | I'll leave the rule type as between.
| | 01:25 | Now I can either type the values I
want in the Minimum and Maximum boxes or
| | 01:29 | I can create formulas that define these values.
| | 01:31 | For example, if I had a daily order
limit in cell G1, I could type the formula
| | 01:37 | =G1 in the Maximum box to set
that cell's value as the maximum.
| | 01:41 | In this case, I want the minimum to be 1,
which I'll type in the Minimum box and
| | 01:46 | the Maximum to be 100, which
I will type in the Maximum box.
| | 01:50 | When you set a Data Validation rule,
you can require the user to enter a
| | 01:53 | value into the cell.
| | 01:55 | To do that you uncheck
the Ignore blank checkbox.
| | 02:00 | If you'll leave that box checked, the
user can skip entering a value into the cell.
| | 02:04 | If you want to require a
value, always uncheck that box.
| | 02:09 | After you've defined the rule you should
provide some information for your users.
| | 02:13 | Clicking the Input Message tab allows
you to display a message box when the cell
| | 02:17 | is selected, but the user has to click
OK to clear the Input Message box before
| | 02:21 | entering any values.
| | 02:22 | I personally find the Input Message
box to be more trouble than it's worth.
| | 02:25 | So I never create them.
| | 02:27 | What I do create are error alerts, which you
can define by clicking the Error Alert tab.
| | 02:32 | An error alert let's the user know
that they have made a mistake, and if you
| | 02:36 | create a message for them and leave the
Show error alert checkbox selected,
| | 02:40 | it lets them know what sort of
data is accepted in the cell.
| | 02:44 | You can create three types of
alerts, Information, Warning, and Stop.
| | 02:50 | An Information alert lets the user
know the data is incorrect, but lets
| | 02:53 | them enter it anyway.
| | 02:55 | A Warning alert lets them know the data
incorrect and highlights the cell, but
| | 02:59 | allows the user to ignore the alert,
and press Enter or Tab to keep the value.
| | 03:03 | A Stop alert display error message
and prevents the user from entering
| | 03:06 | the incorrect value.
| | 03:08 | So I will create a stop warning,
because I prefer not to allow the data to be
| | 03:13 | entered if it's incorrect.
| | 03:15 | After you define your error style,
you can create a custom error message to
| | 03:19 | display to your users when
they enter an incorrect value.
| | 03:22 | It's best to keep the message positive
and tell them what values are acceptable.
| | 03:26 | In this case the title might be Value
Outside Acceptable Range, and the error
| | 03:32 | message itself could read, Cells in this
column must contain the values between 1 and 100.
| | 03:42 | When I am happy with the error alert and the
rest of the rule, I can click OK to create it.
| | 03:46 | And now let me show you how it works.
| | 03:49 | If I type an acceptable value into cell
D2 where I created the rule such as 10,
| | 03:55 | and I hit Return or press
Tab, Excel accepts the value.
| | 04:00 | If I were to type in a value of 200
Excel would display the error alert,
| | 04:06 | indicating that the value is outside the
acceptable range and display my message.
| | 04:10 | If I type Retry Excel
allows me to enter another value.
| | 04:14 | This time I'll make another error, 210,
press Return, and I'll show you what
| | 04:19 | happens if I click Cancel. Excel
reverts the cell to its original value or if
| | 04:24 | there was no value in the
cell, it reset it to blank.
| | 04:27 | One neat thing about Excel tables is
that if you create a Data Validation rule
| | 04:31 | for a cell in the table, Excel will apply
the rule to the next cell in that column
| | 04:35 | when you create a new table row.
| | 04:37 | So for example when I pressed Enter to
accept the data earlier, Excel created a
| | 04:41 | new table row and applied the
Data Validation rule to this cell.
| | 04:46 | If I were to type in 100, which is
an acceptable value, and press Return,
| | 04:50 | Excel allows the entry.
| | 04:51 | If I were to add 200,
Excel displays the error alert.
| | 04:55 | If I click Cancel, it
reverts to the original value.
| | 05:00 | You can modify or delete Data Validation
rules by selecting a cell that contains
| | 05:03 | the rule and then on the Data tab of
the Ribbon in the Data Tools group,
| | 05:07 | clicking the Data Validation dialog box.
| | 05:11 | If you want to modify the rule, you
can change any of the settings including
| | 05:14 | changing the type of rule, changing
the data comparison, Minimum and Maximum.
| | 05:19 | You can change any or all of it.
| | 05:21 | If you want to turn off the Data
Validation rule, you can click any value from
| | 05:27 | the Allow list and then
when you're done click OK.
| | 05:30 | If you wanted to get rid of all of your
Data Validation rules, you can click Clear All.
| | 05:35 | When you click OK,
Excel gets rid of the rule.
| | 05:40 | Data Validation rules help reduce
data entry errors, which are the bane of
| | 05:43 | spreadsheet users everywhere.
| | 05:45 | But be sure to add an explanatory
error message that lets your user know what
| | 05:49 | range of value is acceptable.
| | 05:51 | That way they will understand why the
value they tried to enter is incorrect.
| | Collapse this transcript |
| Using lists to limit values entered into a cell| 00:00 | Data validation rules help limit the
data your users can enter into a cell, but
| | 00:05 | they can be frustrating
if the rules aren't clear.
| | 00:07 | One of the clearest ways to require your
users to enter one of the set number of
| | 00:11 | options is to define a list of
acceptable values for a cell.
| | 00:15 | To require a user to select a value
from an existing list, display the Data tab of
| | 00:19 | the ribbon after you click the cell
where you want to add the rule and then in
| | 00:23 | the Data Tools group, click
the Data Validation button.
| | 00:27 | Then in the Data Validation dialog box,
click the Allow box's down arrow and select List.
| | 00:35 | If you're willing to let the users skip
entering a value in this field, you can
| | 00:38 | leave the Ignore blank checkbox selected.
| | 00:41 | However, if you want to require them
to enter a value you should clear it.
| | 00:46 | The other option you can select is
whether to have an in-cell dropdown list.
| | 00:50 | I always make a dropdown list
available to save the user the time it takes to
| | 00:53 | type in the value and to prevent them
from becoming frustrated if they can't
| | 00:57 | remember the value they want
or if they make a typing error.
| | 00:59 | So I will leave this checkbox selected.
| | 01:03 | Now that you have made your other
selections, you can define a list of values
| | 01:06 | that you'll allow in the cell.
| | 01:08 | You can click the RefEdit button at
the right side of the Source box and then
| | 01:13 | select the cells that contain your list.
| | 01:14 | In this case, those are in G1 through G5.
| | 01:18 | I will click the RefEdit control again.
| | 01:22 | I can verify that the proper cells G1
through G5 appear in the Source box.
| | 01:28 | Rather than selecting a group of cells,
I can also type in the name of a regular
| | 01:32 | or dynamic named range.
| | 01:35 | The advantage of using a named range
is that you don't have to be on the same
| | 01:38 | worksheet as the cell for your users
entering the data and if it's a dynamic
| | 01:42 | named range you can add or
remove values as your needs change.
| | 01:46 | I also always add a Stop Error message
to my list data validation rules with a
| | 01:51 | message that reads something like,
Please select a value by clicking the down
| | 01:54 | arrow at the right edge of the cell.
| | 01:56 | This message is non-confrontational
and lets the user know what they have to
| | 01:59 | do to enter value into the cell, but it also
prevents them from typing in an arbitrary value.
| | 02:04 | So now I have my List here.
I can click OK to create the rule.
| | 02:10 | And now when I click the Down Arrow at
the right side of the cell I can select
| | 02:14 | the category that I want
without any possibility of a miscue.
| | 02:18 | So if I click Engine that
value appears in cell C2.
| | 02:23 | Data validation lists are powerful
rules that help prevent spelling errors and
| | 02:26 | limit your users data entry
to a list of approved values.
| | 02:29 | That way you get the data you expect.
| | 02:32 | Be sure to listen to your users though
to find out if you need to add or edit
| | 02:36 | items on the list to reflect their
needs and the realities of your business.
| | Collapse this transcript |
|
|
5. Auditing Worksheet FormulasManaging 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 AnalysisCreating scenarios and applying them to an Excel worksheet| 00:01 | Excel enables you to
analyze your data effectively.
| | 00:03 | One little known but very
useful tool is the scenario.
| | 00:07 | The scenario is an alternative
dataset you can use to perform What-If
| | 00:10 | analysis on your worksheet.
| | 00:12 | To define a scenario, you click the Data
tab on the ribbon and then in the Data
| | 00:17 | Tools group, click What-If
Analysis and then click Scenario Manager.
| | 00:22 | In the Scenario Manager dialog box,
click Add to begin creating your scenario.
| | 00:28 | First thing you should do is type in a
scenario name, and for this exercise,
| | 00:33 | I will call it LoanPossibilities.
| | 00:37 | Next you select which cells you want to change.
| | 00:40 | You can either type the addresses of
the cells in the changing cells box, or
| | 00:44 | click the RefEdit control at the
right edge of the box and select the cells
| | 00:47 | you want to change.
| | 00:48 | For example, if I wanted to change
cells B3 and B4, I can click here in the
| | 00:54 | Changing cells box, and then select B3
and B4, and those references appear here.
| | 01:02 | If I wanted to type the
values in, I could type in B3, B4.
| | 01:08 | In either case, the result will be the same.
| | 01:11 | Leaving the Prevent changes checkbox
selected prevents other users from altering
| | 01:15 | the cells for which you've defined scenarios.
| | 01:18 | Also, if you don't want other users
to see the cells you've changed,
| | 01:21 | you can check the Hide box.
| | 01:23 | I've never used that option,
but it's there if you want it.
| | 01:26 | After you've set all of your options, click OK.
| | 01:30 | After you click OK, the Scenario Values
dialog box appears with a box for each
| | 01:34 | cell you wanted to change.
| | 01:36 | You can type an alternative value in
the boxes and then click OK to accept the changes,
| | 01:40 | and close the Scenario Manager
or click Add to accept the changes, and
| | 01:44 | create another scenario.
| | 01:46 | In this case, I'll just click
OK after I enter in the values.
| | 01:50 | So let's say that I have a loan amount
of $1 million instead of $15 million and
| | 01:56 | a term of 5 years instead of 10 years.
| | 01:59 | That's the only thing that I
want to create. So, I'll click OK.
| | 02:01 | After you create a scenario, it appears in
the Scenarios list of the Scenario Manager.
| | 02:07 | To apply the scenario, click the scenario
so it's highlighted, and then click Show.
| | 02:13 | If you want to apply another
scenario, click it and then click OK.
| | 02:17 | If the second scenario affects any of
the same cells as the first scenario,
| | 02:20 | the second scenario's
values will take precedence.
| | 02:23 | Now, this is important.
| | 02:25 | Note that there is no Hide
button in the Scenario Manager.
| | 02:29 | After you apply a scenario, the only
way to get rid of it is to click the Undo
| | 02:33 | button or press Ctrl+Z. So if I close
the dialog box, I get the Undo button back.
| | 02:40 | When I click it, Excel restores
the values to their original state.
| | 02:44 | One word of caution about scenarios.
If you apply a scenario and then save
| | 02:49 | and close a workbook, Excel overwrites the
workbook's original data with the scenario data.
| | 02:54 | What that means is that you should
always make backup copies of any workbooks to
| | 02:57 | which you plan to apply scenarios, just
in case you change your data and forget
| | 03:01 | to close the workbook
without saving your changes.
| | Collapse this transcript |
| 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 |
|
|
ConclusionGoodbye| 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 |
|
|
