Understanding formulas opens the door for you to perform calculations in Excel.

This post introduces you to entering simple formulas to perform calculations like adding two numbers or cells together.  It also introduces you to the use of the $ symbol for formulas.

YouTube Video on this post

Here is a video which shows how to do simple calculations in Excel:

Simple Calculations in Excel

Entering numbers into a formula

To perform a simple calculation in Excel follow this procedure:

1. Click in the cell where you want to perform your calculation.  Cells are found at the intersection of a row reference (e.g. 1 for the first row) and a column reference (e.g. A for the first column).
2. Press = (equals) on your keyboard.  This means that you are entering a formula.
3. Enter the first number of the calculation.
4. Enter the operator for the calculation.  Use + for addition, – for subtraction, * for multiplication and / for division.
5. Enter the second number.
6. Press Enter or press Tab.  This moves you out of this cell and into the next cell.

Excel performs the calculation immediately. (There are some settings in Excel which turn off this automatic calculation but we will assume that it is turned on.)

Using other cells in a calculation

In the above image, the purpose is to calculate the difference between the selling price and the cost price using the values found in columns B and C.  To perform this task follow this procedure:

1. Click in the cell where you want the answer to go.
2. Press = (equals) on your keyboard which means that you are entering a formula.
3. Click in the cell of the first number, which in the example above is C2.
4. Enter the operator, which in this example is – for subtraction.
5. Click in the cell of the second number, which in the example above is B2.
6. Press Enter to exit the cell.

Excel performs the calculation immediately.

Some advantages of pointing to cells rather than entering the numbers again are that it is quicker, there is less chance of making a typing error and the formula can be easily copied to other cells.

Copying a formula

There are several different ways of copying a formula down.  One method is to use the copy and paste method.

1. Click once in the cell to be copied and copy it.  For example, right mouse click and select Copy from the shortcut menu.  Alternatively, press Ctrl + C.
2. Select the cells that you want to copy the formula into by clicking in the first cell and dragging your mouse.
3. Paste what you copied.  For example, right mouse click and select Paste from the shortcut menu.  Alternatively, press Ctrl + V.

When the formula is pasted into the other cells, the formula updates so that it refers to the two cells in the same row, like the original formula did.  These sorts of cell references are called relative references.

Copy a formula down

 

My favourite method of copying the formula down is:

1. Click once in the cell to be copied.
2. Move the mouse to the bottom right hand corner of this cell, making sure that the mouse pointer changes to a thin black cross.
3. Double-click. The formula copies down until there is a blank value in the previous column.  Alternatively, click and drag the black cross down as far as you want to go.

Using the $ symbol

Dollar signs can be put before the column reference or the row reference or both.  The purpose of using the dollar sign, $, before the column and/or row is to keep this value fixed when the formula is copied.  For example, the formula for the discounted price is to be copied down into the other rows within this column but each formula needs to always reference the first row in this column.  So a dollar sign is placed in front of the row number to keep the formulas pointing at this row.  In this example, the column reference could also have been fixed, if desired, without causing any problems. A reference that is fixed is also called absolute, as opposed to a relative reference.

There are four different types of cell references in this situation:

1. Both the row and column are fixed, e.g. $E$1.
2. Only the row is fixed, e.g. E$1.
3. Only the column is fixed, e.g. $E1.
4. Both the row and column are not fixed, e.g. E1.

Tip: To cycle through these four different options for a cell when entering a formula press F4.  Pressing F4 again will move you to the next option in the list, and so on.

This site is all about providing solutions using Excel so in the next post we will apply this knowledge of formulas to a real situation.