Using the Sum function and Ranges in Excel 2007

If you are using the Sum function in combination with a Range you could get some magic that you wouldn't expect. How does the magic goes, well start by filling some numbers in columns and off course use the sum function to calculate the sum of the numbers:

Now, add a number in the D column and see what happens with your sum calculation:

Excel will automatically update the range, isn't that cool? Yeah it is but lots of people doesn't want this functionality. Follow the next steps to disable this feature.

  1. Use the office button and click on excel options:

  2. In the options dialog box choose Advanced and uncheck the 'Extend data range formats and formulas':

There you go, no more range magic J