Use Mouse to Edit Formula Ranges [quick tip]

Posted on May 5th, 2009 in Learn Excel - 10 comments

Here is an interesting way to edit formula ranges. This time using mouse.

Editing Formulas using Mouse - Excel Tip1. Select the formula for which you want to edit the range.

2. Press F2.

3. Now you see borders around all the ranges for that formula. Just select one of the ranges by clicking on its border and start dragging / resizing it.

4. When you are done, press Enter.

5. That simple.

It seems like a fun way to edit formulas.What do you think ?

More on formulas: 5 areas where mouse kicks keyboard’s butt | Edit formulas in bulk using Find / Replace | Excel Formulas Online Help

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

10 Responses to “Use Mouse to Edit Formula Ranges [quick tip]”

  1. Stružák says:

    Nice one. I had no idea this could work. :-O Thanks for tip. 🙂

  2. Chandoo says:

    @Struzak... You are welcome.

  3. Doug Jenkins says:

    What?

    Why did no-one tell me this before?!

    Nice tip 🙂

    Is there a way to do this when the ranges are on different sheets?

  4. Robert says:

    Chandoo,

    nice tip (as always). I am using this all day long.

    A short amendment: the technique works with the data source of a chart as well. Instead of F2, simply click on the data series of the chart and you will see the colored border around the data source cell range (as long as chart and data source are on the same worksheet).

    A time-saving feature if you have to create a lot of charts (same format, different data sources): Create one chart, format and resize it the way you need it, copy and paste the chart and simply change the data source of the new chart by dragging the colored border to the desired cell range.

  5. hwsris says:

    thank nice method.
    in 4 you can click other cell replce press Enter when done.

  6. John Franco says:

    I feel identified with Robert; the best use I give to this feature is to change data source in charts.
    Other way to use it is when you insert rows above a SUM formula and need to expand the sum range.

    A final note: be aware that this method does not work with named ranges

  7. Charly Ott says:

    Thank You so much for all your hard work gathering & documenting this wealth of information.
    Most of all THANK YOU for sharing.
    These busy days don't allow much time to study.
    Over time You have been there & answered questions & taught me a great amount of understanding.
    You are a respectable value!
    All the best,
    Charly

  8. [...] the first chart. Format it completely. Now select the chart and press CTRL+D to duplicate it. Now, using the mouse adjust the source data ranges of this new chart. That is [...]

  9. Jay says:

    Chan,

    How do you make the gif animations which you display in your posts to illustrate your examples? Please write a tutorial on that also.

    thanks

    Jay

  10. Keith says:

    Ironically, I learned this technique when I was teaching a coworker some intermediate Excel training. She edited a formula this way and it blew me away. I asked her where she learned this and she replied, "I don't know, I'm kinda new at Excel and have always edited formulas this way". I guess you can learn new things from just about anyone!

    Keith

Leave a Reply