Microsoft Excel: Autosum Doesn't Always Predict My Data Correctly

Problem: When using the AutoSum button, Excel sometimes predicts the wrong range of data to total. In Fig. 353, the AutoSum worked fine in F2 and F3, but in cell F4, Excel gets fooled into thinking that you want to total the rows above F4.

Strategy: After hitting the AutoSum button, the provisional range address is highlighted in the provisional formula. Using your mouse, highlight the right range.

As shown in Fig. 353, AutoSum will work correctly in B2 and B3. It will predict that you want to sum the data in that row. However, in cell B4 Excel has a choice: do you want to sum the two cells in that column or the four cells in the row? Excel always chooses to sum the two cells above in this situation.

After hitting the AutoSum button, note that F2:F3 is highlighted in the formula. This allows you to enter the correct range. There are three methods:

1) With the mouse, highlight B4:E4 and hit Enter.

2) With the keyboard, type B4:E4.

3) Using the arrow keys, hit the Left Arrow to move to E2. Hit the Down Arrow twice to move to E4. While holding down the Shift key, hit the Left Arrow three times to highlight B4:E4, as shown in Fig. 354.

4)

Additional Details: The problem described in this section will always happen in the third and fourth rows of the data. When you try using the AutoSum button in F6 and beyond, Excel will correctly sum all the data in that row. AutoSum can also fail when one number in your range contains a SUM formula. The provisional formula will offer to sum a formula extending up to but not including the previous SUM formula.

Alternate Strategy: You can choose to enter all of the totals at one time by using the AutoSum button. This is faster and will eliminate the problem described above. Follow these steps.

1) Highlight the entire range that needs a SUM formula as shown in Fig. 355.

2) Hit the AutoSum button. Excel makes a prediction and fills in the total formulas automatically, as shown in Fig. 356. Excel does not show the provisional formula. So, check one formula to see that it is correct.

Summary: The AutoSum function does not always correctly predict the range to be totaled. It is easy to use the mouse or keyboard to show Excel the correct range.

Functions Discussed: =SUM()

Microsoft Excel: Autosum Doesn't Always Predict My Data Correctly

Share to Facebook