Formulas

One of the main functions of a spreadsheet is to make calculations using your data.  Excel is able to perform many mathematical functions automatically, including finding sums, averages, and standard deviations.  This guide will demonstrate the use of these formulas. Newer versions of Excel also include formulas for financial, logical, and reference operations, which in principle work in the same way.

• To enter a formula, select the cell(s) into which the formula will go, then click the [Formula] button to the left of the formula bar.  Select the type of formula you want from the popup window, then select the range of data you want to use in the formula.
• As you become familiar with the formulas you use regularly, you can also enter formulas manually.  Click on the cell, then type “=SUM(C2:C11)” to sum this column, for example.

• For example, to sum a column of numbers, you may select a cell beneath the column.  The Insert Formula box will pop up.  Select the formula you wish to use.  In this case, select SUM, then click [OK]. 

The Function Arguments screen should pop up, displaying the current range of cells in the formula:

Excel will attempt to automatically select the right cells for your formula.  Check to see whether the range is right.  If you need to change the cell range, you can either select the correct cells in your spreadsheet or type the range of cells into the text box. 

• When the right cells are selected, click [OK].

Excel will automatically calculate the result of the formula and display it in the cell.  If the data in any of the cells contributing to the formula changes, Excel will re-calculate the formula and display the adjusted result.

Copying formulas

Excel will allow you to copy a formula to other cells.  For example, you may create a formula to sum one column of numbers, and then copy it to sum the next column(s). 

• Select the adjacent cell(s) into which you want to copy the formula, then click Edit > Fill > Right (or whichever direction you are going from the existing formula.) 

You can also select a cell, then copy and paste it to a new location.  Excel will automatically adjust the cell range to the new column/row of the formula.

Using multiple formulas

You may need to use multiple formulas when working with your data.  For example, after finding the sums of several rows of data, you may need to find the average sum.  You can do this by adding a formula just as you did to find the sums. 

• Click a cell below the column of sums, then click the [Formula] button.  Select Average, then click [OK].  Select the range of cells, then click [OK].

The average of the sums will appear in the cell.  Excel will automatically recalculate the average if any data in any of the cells changes.