Simple Formulas and Functions in Excel

Creating Simple Formulas

Formulas can be used to perform simple calculations such as addition and subtraction, or more complicated applications such as complex financial functions and statistics.

All formulas begin with an equal (=) sign. The equal sign tells Excel that the cell data should be interpreted as a formula. Otherwise, Excel interprets the data as a constant value or text and simply inserts exactly what you enter into the cell. For example, to create a formula to add the contents of cells C5 through C7, you enter =C5+C6+C7.

Creating a Formula Manually

To create a formula manually:

  1. Select the cell where you want the formula results to appear
  2. Type =
  3. Type the rest of the calculation. You can click into the cells or type in the cell reference
  4. Press ENTER

The answer will update if the cells it refers to are changed.

Formulas follow standard mathematical rules in the sequencing of the different calculations in a formula.

Using The AutoSum Button

Another way to perform calculations on your data is by using functions. Functions are built-in formulas that you can use to make your worksheet computations easier.

The AutoSum button is an easy way to create the functions Sum, Average, Max, Min and Count, as well as being one method to access other functions.

To use the AutoSum button with any of the functions listed:

  1. Select the cell where you want the formula results to appear
  2. Click the dropdown arrow beside the AutoSum button
  3. Choose the function you want to use
  4. Check the cells chosen for the formula are correct. If incorrect select the cells you do want to use
  5. Press ENTER

An alternative method:

  1. Select the cells you want to use in the formula and the cell you want the formula results to appear in
  2. Click the dropdown arrow beside the AutoSum button
  3. Choose the function you want to use
  4. The result will appear

This method also allows you to add totals to an entire table in one click:

  1. Select the table of data and the row and/or columns to include the totals
  2. Click the dropdown arrow beside the AutoSum button
  3. Choose the function you want to use
  4. The results will appear

Editing A Formula

Formulas should be edited in the formula bar.

The Formula Bar

To edit a formula:

  1. Select the cell containing the formula
  2. Click into the formula in the formula bar and make the required changes
  3. Press ENTER to confirm the changes

Copying Formulas

The formulas you have built can be copied to other locations in your spreadsheet.

Any copy and paste method can be used on formulas, as can AutoFill.

If you put a formula into a table, the table will automatically replicate the formula down the whole column.

Defining Relative, Mixed And Absolute References

When you use a reference to a cell in Excel it is a relative cell reference. This means that Excel sees it as a location relative to the position of the formula. When the formula is then copied to another location, the cell reference then changes.

Relative Cell References

If the formula needs to refer to a fixed cell rather than changing as you copy the formula then you need to use an absolute cell reference.

Absolute Cell Reference

To create an absolute cell reference:

  1. Create the formula as normal
  2. Click into the cell reference to be fixed and press the F4 key

OR

  1. Enter a $ sign in front the letter and number of the cell reference so A1 would be $A$1

If you only need to fix one half of the cell reference, so the row or column reference only stays fixed and the other part changes, this is called a mixed cell reference. It only needs the $ sign in front of the part that is to stay fixed.

Mixed Cell References

To create a mixed cell reference:

  1. Create the formula as normal
  2. Click into the cell reference to be fixed and press the F4 key

OR

  1. Enter a $ sign in front of the part of the cell reference to be fixed

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s