Advanced formulas and references

I am going to show you how to write a basic formula to demonstrate a couple of important concepts.

First, to help us keep everything straight, let's click this + sign to add a new worksheet. Then, double-click the worksheet tabs and type names.

Now, we can add the data.

The formula we are going to write will go in this column and use the amounts in these two columns and a percentage amount.

We could format the data as a table, but let's leave it as is for this demonstration.

The first thing we need is a cell to hold the percentage amount.

Pick a cell outside the data and type .1.

Then, come up here to the Number group and click the % button to format the cell as a percentage.

When we are finished, we'll be able to change this number, if we want to calculate a different Cost buffer amount with our formula.

Now, we can enter the formula.

The formula adds the Material and Labor cells, and then, multiplies that amount by the buffer percentage cell.

The asterisk is used to multiply. Press Enter to see what happens.

Well, it's pretty obvious that this amount is not 10 percent of the first two columns. And the reason it's not is because I didn't consider calculation order.

This is the order in which Excel calculates the parts of a formula.

You don't need to remember all the details.

Just keep in mind that multiplication and division are calculated before addition and subtraction. And in our formula, we wanted Excel to add the first cells, then multiply.

To tell Excel to add first, we simply insert parentheses. Now instead of multiplying, then adding, Excel will add the first two numbers, then multiply.

Now we can use AutoFill to add the formulas to the other cells.

But now we run into another problem.

In the first cell, the formula gets its buffer percentage from D20.

But in the next cell, the formula refers to D21. And since D21 is empty, the formula multiplies by zero, and returns a value of zero.

But why did Excel change this cell reference to D21?

Because when you use AutoFill or copy a formula, Excel uses relative references by default. Here's how that works.

In the first cell, the formula refers to these cells in row 6 and D20. When you use AutoFill, Excel changes the cell reference based on location to row 7 and D21.

In most cases, this is what you want.

But in this case, we want the formulas to always use the value in this particular cell. To make that happen, we need to change the cell reference to an absolute reference.

In the formula, click D20, and then type dollar signs in front of the D and the 20.

The dollar signs tell Excel that the formula must always refer to the cell in column D in the 20th row. Press Enter.

Now when you AutoFill the cell, all the formulas point to D20. And we can change the percentage to calculate a different buffer amount.

The last thing we'll do is add an Estimated Cost column that uses a function.

With G6 selected, click AutoSum.

Excel automatically assumes we want to add the 3 columns to the left.

So, press Enter. And then AutoFill the formula to the other cells.

In this course, you have learned the basics of creating your first Excel workbook.

Where do you go next? Well, try exploring Excel 2013 on your own. For example, you can quickly create charts and use these buttons to add formatting.

To dig even deeper into Excel features, tools, functions, and formulas, check out the links in the course summary.