Jan's Working with Numbers

    Formulas: Changes

Some changes that you make to your data can cause problems that Excel can easily fix by itself. For example, the cell references in formulas are automatically changed when you move the cells. If you rename a sheet, links to cells on that sheet are updated for you.

Other changes can make your sheets give the wrong results or even break formulas entirely. Sometimes there is an error code in the cell but not always! You will have to spot these problems and fix them yourself. You have already seen some examples of most of these problems.

Changes that create problems with formulas

• Copied, not Linked

  Problem: A copy does not change when the original cells change.

  Solution: The cells must be linked to be sure that the copy will always show the same values as the original. A formula should reference the original cell whenever possible to be sure it is using current values.
  

• Merged Cells

  Problem: Any cell or formula that uses values from a cell that vanished in a merge will see only a zero there, not the value that you see on the sheet for the final merged cell. Only the upper left cell of the original selection will actually contain data after a merge. Data in the other merged cells is deleted but the cells are still there. You just cannot see them!

  Solution:  Rewrite the formulas to refer to the top left cell in the merge range instead of to the cell that vanished.
  

• Sorted

  Problem: Sorting data breaks formulas that have relative references to cells not in the same row as the formula. The formulas are not updated after a sort to following the cells to their new location.

  Solution 1:Best choice
  Rearrange the sheet so that formulas reference only cells in the same row as the formula or cells that don't move with the sort.
   
  Solution 2: Sort first and then create the formulas.
  If you later add new data and have to sort again, you must recreate the formulas once again.

  Solution 3: Use absolute reference for cells when possible for cells in the formula that are outside the same row as the formula.
  

• Inserted Cells

  Problem: Inserted cells, rows, or columns may or may not be included in the formula's results.

  If your inserted cells are inside the range used in the formula, all will be well. But if the inserted cells are at the borders of the range, they may not be included.

  Example: Suppose you have a formula in cell C11 to add the column above, =SUM(C5:C10). You insert a new row above Row 8. The cells below Row 8 move down one, so what was C11 is now C12 and C10 is now C11. Excel will change the formula to =SUM(C5:C11) to include the new cell. But, if instead you add a new row above Row 5 at the border of the range, the formula will move down into cell C12 but the range cells will not be updated to include C5. You will have to do that yourself.

  Solution: Have blank cells at the edges of your data region and include the blanks in the ranges for your formulas. Then, when you want to add a row or column to the data, you will be adding it inside the range. Excel will update the formulas in that situation. Of course, if you are SURE that you will not be adding rows or columns to the data, you do not have to worry about this situation.

  Blank cell = Zero: For some formulas you do not want to include blank cells at the borders in the range. The extra zeroes in the calculations will make your results wrong!