Formulas & Functions in Action

Quick reference

• 00:04 Let's look at how we can use what we know about formulas and
• 00:08 functions to quickly answer questions from raw data.
• 00:11 In our first example, we operate a small stationary store, and
• 00:15 we've been using Excel to track the number of items sold between January and March.
• 00:20 We'd like to simplify this data by looking at the sales as a quarterly figure.
• 00:25 First, let's add the number of items sold each month and
• 00:28 place that number in column E, Q1 items sold.
• 00:32 Which is better to use a formula or a function.
• 00:37 Well, the sum function was created to do exactly this task, so let's use it.
• 00:42 We start functions with an equal sign, then the function name and
• 00:46 open parentheses.
• 00:48 So in this case, our function name is sum.
• 00:50 We start with our equal sign and the word sum, open parenthesis.
• 00:56 The numbers we want to add are right here, January, February and March.
• 01:01 We can just click on the January number and
• 01:03 drag all the way to March to create a range, and close parentheses.
• 01:08 We hit Enter, and our value is displayed.
• 01:11 If we click on that cell, then we see the formula displayed in the formula bar.
• 01:17 Now we want to do the same thing for each item.
• 01:20 We remember that even though what we're seeing is the result of the formula,
• 01:23 the actual content of the cell is the formula.
• 01:26 So if we copy this cell,
• 01:28 what we're really copying is the formula, not the value of the cell.
• 01:31 That's important.
• 01:33 Now generally speaking, cell references and formulas are relative.
• 01:37 So in our case, our range started three columns away in this same row,
• 01:42 and ended one column away in the same row.
• 01:46 We want Excel to perform the same relative calculation to the cells below.
• 01:51 We can go to the clipboard menu, click Copy,
• 01:56 highlight the cells that we want the formula to be in and then click Paste.
• 02:00 But an easier way is to click on the little square in the lower right
• 02:04 corner of the cell that you want the formula to be copied from.
• 02:07 This is called the fill handle.
• 02:09 And drag that fill handle all the way down to the cells that we want
• 02:12 the formula to be in.
• 02:15 And our formula's copied.
• 02:18 We notice that our first cell, E6, calculated the sum of B6 to D6.
• 02:22 But E8 calculated B8 to D8.
• 02:28 So it copied the relative formula and that's exactly what we want.
• 02:31 So this looks great.
• 02:33 Now we want to know how much money was made from the sale of each item.
• 02:36 Column G is where we want that information to show up.
• 02:39 And how do we get that figure?
• 02:41 You guessed it, we can multiply the sales total by the unit price.
• 02:45 And that's a formula.
• 02:47 Remember that to perform a multiplication task, we use an asterisk for
• 02:51 the multiplication sign.
• 02:53 So our formula is just equals this value times this value.
• 03:01 Excel picks up the cell references E6 and F6.
• 03:05 We hit Enter, and it calculates the value of the formula as requested.
• 03:10 And to copy this formula to the remaining rows, we use the most efficient method,
• 03:14 which may be clicking and dragging on the fill handle.
• 03:17 Or simply double clicking on the fill handle.
• 03:20 It copies all the way to the end of the data set and our values are filled in.
• 03:25 So now we know how to copy a formula from one place to another.

Lesson notes are only available for subscribers.