Creating Formulas

Read this overview of creating formulas in Excel. Pay close attention to the figures showing how to enter formulas in a budget spreadsheet.

The second section explains relative referencing. Excel will adjust the cell reference used in a formula when you copy and paste the formula into a new location. For example, if you are summing numbers in column A, you may have a cell with the formula =SUM(A1:A10). Copying the formula cell into the next column (column B) will automatically change to =Sum(B1:B10). This feature is useful when performing the same type of calculation on multiple data sets.

The third section discusses the order of operations. When performing complex calculations, your formula must follow the correct sequence. For example, to calculate (2+8)/5, calculate the parentheses (2+8) first and divide its result by 5. Spreadsheet programs have formatting requirements for the order of operations. Pay special attention to Table 2.3, which shows the order of operations in Excel.

The final section discusses auditing formulas, which allows you to check or review the formulas you use in a spreadsheet using a simple command. You can also see which cells are referenced in each spreadsheet formula.

Relative References (Copying and Pasting Formulas)

Once you type a formula into a worksheet, you can copy and paste it to other cell locations. For example, Figure 4 shows the formula output entered into cell C3. However, you need to perform this calculation for the rest of the cell locations in Column C. Since we used the D3 cell reference in the formula, Excel automatically adjusts the cell reference when you copy and paste the formula into the rest of the cell locations in the column. This is called relative referencing and is demonstrated as follows:

  1. Click cell C3.
  2. Click the Copy button in the Home tab of the Ribbon.
  3. Highlight the range C4:C11.
  4. Click the Paste button in the Home tab of the Ribbon.
  5. Double-click cell C6. Notice that the cell reference in the formula is automatically changed to D6.
  6. Press the ENTER key.

Figure 5 shows the outputs added to the remaining cell locations in the Monthly Spend column. For each row, the formula divides the value in the Annual Spend column by 12. You will also see that cell D6 has been double-clicked to show the formula. Notice that Excel automatically changed the original cell reference of D3 to D6. This is the result of relative referencing, which means Excel automatically adjusts a cell reference relative to its original location when pasted into new cell locations. The formula was pasted into eight-cell locations below the original one in this example. As a result, Excel increased the row number of the original cell reference by a value of one for each row it was pasted into.

Relative Reference Example - expense plan worksheet showing cell reference =D6/12 changing due to relative referencing.

Figure 5 Relative Reference Example


Relative Referencing

Relative referencing is a convenient feature in Excel. When you use cell references in a formula, Excel automatically adjusts the cell references when the formula is pasted into new cell locations. If this feature were unavailable, you would have to manually retype the formula when you want the same calculation applied to other cell locations in a column or row.