Create and Copy Formulas
Copying cells is a breeze now for you. But be careful! You’ll need to pay
attention when the cells you're copying contain formulas.
Create and Copy a Simple Formula
Let’s imagine that you’re a store manager and you’re creating a sales tracking
spreadsheet for a product.
In this sales spreadsheet, you've entered the first names of the sales staff and
the number of items they’ve sold.
Select cell E4
You’ve entered the prices of the items in cells D4 to D9, so now you want to
calculate the sales figures (Sales in the workbook) for each salesperson, where
Sales = Quantity x Price.
You're going to create a formula to calculate the result.
But this is the first time I’ve created a formula! I don’t know what I’m doing!
Don’t worry! You can create a formula very easily in just a few steps:
1. Select the destination cell, i.e., the cell where you want the result to appear.
In this example, it's cell E4.
2. Type = . Formulas always start with the = sign.
3. Click on cell C4.
Click on cell C4
You can see that “C4” appears in the destination cell and also in the formula
bar.
4. Type the * symbol on your keyboard (equivalent to a multiplication sign in
Excel).
5. Select cell D4.
6. Hit the Enter key on your keyboard to confirm the formula.
And voilà—you've just created your first formula!
Check your first formula in the formula bar
The result appears in cell E4 and the formula =C4*D4 appears in the formula
bar.
Now you want to calculate the sales figures for the other salespeople without
doing it all over again. You can copy cell E4 down to cell E9.
Let’s see what you get:
Use
the fill handle to copy your formula
Notice that Excel has calculated the sales figures for every salesperson!
Now we’ll break down why this worked:
Take a look at this worksheet, which shows the formulas, not the results of the
calculations.
Check your formulas in the Sales column
When you performed the fill series, the formula =C4*D4 was incremented down
to the row below and became =C5*D5 and so on until the last one, =C9*D9.
In Excel, you can carry out a fill series of formulas, which saves you a
significant amount of time and effort when creating worksheets. In a fill series
formula, the references are incremented automatically. These are
called relative references.
Watch the Video Tutorial
Watch how to create and fill a simple formula step by step in the video below:
Create and Copy a Complex Formula
Don’t worry, this process is less complicated than it might seem! Let’s take the
previous example. Given that the tracking sheet only shows one product, you
decide to streamline it: instead of repeating the price several times in column D,
you decide to calculate the sales figures after entering the price in cell D1.
Select cell D4
Now let’s build the formula that will allow you to calculate the sales figures:
1. Select destination cell D4.
2. To start your formula, type = on your keyboard.
3. Select cell C4.
4. Type * on your keyboard.
5. Select cell D1.
6. Confirm your formula with Enter.
After selecting cell D4, you can check the result below and you’ll notice that the
formula appears in the formula bar.
Check your formula in the formula bar
You're now going to copy cell D4 down to cell D9 and see what happens.
Use
the fill handle to copy your formula
Instead of calculating the sales figures for each salesperson, Excel has
generated a number of errors with the term #VALUE!
Oh no! Why didn't it work?
Take a look at this worksheet, which shows the formulas, not the results of the
calculations.
Check your formulas in the Sales column
OK—let’s examine what happened. You used the fill handle to fill the series,
and it did what it expected you to want: =C4*D1 changed in the next row below
to =C5*D2 and so on, until the final entry =C9*D6. Not quite what you expected!
The formula =C5*D2 refers to cell D2, which is empty. And this also explains
why the result in D6 is equal to #VALUE! Because the formula =C6*D3 refers to
a cell that contains text.
Don’t worry, I can show you how to fix this!
In the example above, we’ve used what Excel calls relative references—
references that change when a formula is copied. These are often very useful.
However, we wanted the cell references to stay the same, so we need to use
an absolute reference—a reference that remains the same, wherever it’s
copied to. In this case, it will allow us to refer to cell D1 for all the formulas.
Now that we know this, let’s recalculate each salesperson’s sales figures:
1. Delete the results from D4 to D9.
2. Select destination cell D4.
3. To start your formula, type = on your keyboard.
4. Select cell C4.
5. Type * on your keyboard.
6. Select cell D1, which is currently a relative reference.
7. Once D1 is selected, press the F4 key on your keyboard and you’ll see
the following result: $D$1.
Depending on what brand PC you have, the F4 key can have different
functions, such as display settings, contrast or brightness control. So, if F4
doesn't work for you, you can use the fn (function) key, which is usually towards
the bottom left of your keyboard. While holding down fn and pressing F4, you
should get the same result, i.e., $D$1.
You can also just use the dollar sign on your keyboard when writing a formula
instead of using F4, or fn and F4. I wouldn’t recommend this, though, as it can
cause errors. When using F4, you can be sure that Excel will place the dollar
signs in the correct position in the reference.
8. Hit Enter to confirm.
After selecting cell D4, you can see what happens below and check the formula
in the formula bar.
Check your formula in the formula bar
You're now going to copy cell D4 down to cell D9 and see what happens:
Use the fill handle to copy your formula
Excel has now correctly calculated the sales figures for each salesperson!
Now we’ll break down why this worked.
Take a look at this worksheet, which shows the formulas, not the results of the
calculations.
Check your formulas in the Sales column
When you performed the fill series, cell C4 copied down to the row below and
became C5 and so on until the last one, C9. However, all of the formulas now
contain a constant cell reference that is recognizable by the use of the $.
In a fill series formula, the references are incremented automatically, except for
the constant cell reference. We usually call these absolute references. If
you’re a bit lost, don’t worry. All you need to understand for now is that if you
copy a formula and you don’t get the results you were expecting, it might be
because you should have used an absolute cell reference.
Get into the habit of asking yourself if you need to amend the references so that
they are absolute, and remember that if you want to turn a relative reference,
e.g., D1, into an absolute reference, you just need to press F4 or fn and F4
when the cell is selected. This will make the dollar signs appear in the cell to
give us $D$1.
Watch the Video Tutorial
Recap the different steps required to create and fill a complex formula in the
video below:
Let’s Recap!
• You can create a simple formula to add, subtract, multiply or divide
numerical values in your worksheets.
• A formula is easy to spot, as it always starts with the = sign.
• In a fill series formula, relative references are incremented automatically.
• In a fill series formula, absolute references are not incremented.
• To turn a relative reference into an absolute reference, you can use the
F4 key or the fn key + F4.
You've now created your first formulas in Excel. Well done! Let’s meet in the
next chapter and apply what you’ve learned in this first part of this course.