Add formulas and references

I have added an Over/under budget column to the worksheet to help us track our budget as the actual costs begin to come in.

But to make that column work, we need to add a formula.

In case you had not noticed, Excel has already added some formulas for us, down here, in the Total row.

Tables can be very useful when it comes to formulas.

But to really understand how they work, let's try creating one on our own.

Select the first cell in the column. To let Excel know we are entering a formula, type an = sign.

Now, there are number of ways to write a formula.

We could type the values in each column with a - sign between them, and get the correct result.

But then what would happen if one of the numbers changed? We would have to rewrite the formula.

To solve that problem, we can use cell references.

Instead of typing the value of each cell, we simply type the cell addresses.

To help you keep track of cell references in the formula, Excel puts a colored highlight on the cell.

Press Enter and Excel calculates the formula and displays the result in the cell.

Now because we used cell references, we can change a value and Excel automatically updates the Total.

Keep in mind that even though the cell shows the result, the real content of the cell is still the formula.

If you wonder if a cell contains a formula, click the cell and look in the Formula Bar.

Also, we can use AutoFill.

Just click this little green handle in the cell and drag it down, and Excel automatically fills the formula to the other cells.

It even updates the cell references to point to the right cells.

Note that negative values are displayed in parentheses.

But for an even easier way to enter formulas, we can use functions.

A function does all the formula writing for you. As you saw earlier, you can add a function to the Total row by simply clicking this arrow.

If you select Sum, Excel adds the SUBTOTAL function.

But you can add a function to any cell you want. Select a cell. Then, go to the FORMULAS tab on the ribbon. Here, you have a whole library of functions.

Click AutoSum, click Sum, and the function is added to the cell.

Now, select the cells you want to add and press Enter.

Click More Functions to see all the functions available in Excel.

Up next, we'll do more with formulas and functions.