Add formulas and references

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Formulas help you extract useful information from your data. Add cell references and functions to calculate values that update automatically when you change your data.

Want more?

What's new in Excel 2013

Basic tasks in Excel 2013

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.

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×