Data Groups & Formulas:
Changes & Formulas

Did you want Working with Numbers: 2007,2010,2013,2016  or español

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. You will have to fix these kinds of problems yourself. You have already seen some examples of most of these problems.

Sources of problems in sheets

• 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 be the same 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 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.

  Solution:  Rewrite the formulas to refer to the merged cell instead of the cell that vanished.
   

• Sorting:

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

  Solution: Rearrange the sheet so that formulas reference only cells in their same row or cells that don't move with the sort. If you can't do that, do your sort first and then create the formulas.
   

• Inserted cells

  Problem: Inserted cells, rows, or columns 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 you add a new row above Row 11 at the border of the range, the formula will move down into cell C12 but the range cells do not change! The range will not be updated to include C11. You will have to do that yourself.

  Solution: Surround your data region with blank cells 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 know you will not be adding rows or columns to the data, you do not have to worry about this situation.

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~

Last updated: 02 May 2012