Search This Blog

Tuesday, March 8, 2011

Excel: Right the wrong range!

When you begin a function, sometimes Excel does not automatically select the appropriate range of cells, as in the example below. The moving border indicates the range of cells included in the selection.

  
In this case, the range selected includes the Team Total in cell B15, which should not be included in the results when trying to calculate the average!

How to quickly fix it?? If you have not already completed the function, using your mouse, click and drag to select the appropriate cells. A moving border will indicate the newly selected range. Touch the Enter key to complete the function and view your answer!
  


But what if you have already completed the function before you notice the error?

 
Click into the formula bar next to the function. This will show a solid blue border that indicates the range of cells included in the result.


The range can be adjusted by using your mouse. Hover over any corner of the range box to find the sizing handle which appears as a double headed arrow (). Now click and drag to adjust the range border and correct the selection.
By the way, if you see a 4 way arrow, you will be moving the selection box instead of re-sizing. This also comes in handy at times! 
Touch the Enter key to complete the function and see your adjusted (and hopefully correct) result!



When editing any formula or function, if you run into trouble selecting the appropriate range, use the Esc key to avoid reference errors and start over. Keep in mind when working in Excel: just because you get an answer doesn't always mean it is the correct answer. Examine the results to be sure!





No comments:

Post a Comment