When I learn an Excel feature that I think is powerful but I’m not confident I’ll remember how to use it, or I find a way to help people learn a particular Excel feature or concept and want to remember it, I make up a simple example, and save it in an “ex” file. “Ex” is short for “Example,” and I have a bunch of them. Today, we’re going to talk about a more error free way of using the SUM function, although the solution can be applied to a number of other functions, and we’re going to use an “ex” file to demonstrate both the problem and solution.
When we click the AutoSum icon beneath a group of numbers that has a heading, Excel automatically defines the cells to be included in that function. But what happens if a cell(s) is inserted just beneath the heading, and/or above the SUM function but not immediately below the list of numbers? Unfortunately, the SUM function will not automatically expand to include the new numbers.
The fix? Include the heading in the SUM function to make sure that any rows inserted just beneath the heading are included, and add a row just above the SUM function labeled “Insert above this row.” If the file is to be presented formally, the row labeled “Insert above this row” can either be hidden or made extremely small. Click this link to open an Excel 2007 file (zipped, no macros) named “ex SUM More Error Free.xlsx” to see an example of the problem as well as a suggested solution.