Use Mouse to Edit Formula Ranges [quick tip]

Learn Excel - 12 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


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

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

12 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:


    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:


    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,

  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:


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



  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!


  11. Dhananjay Jadhav says:

    I had been using this but today its now allowing me to change cell reference using mouse. Is there anything which causes deactivation of this formula

  12. Dhananjay Jadhav says:

    Found the solution!

    If you are unable to edit formula using mouse -

    Go to File manu >> excel options >> Advanced >> Editing options >> Enable Fill Handle and cell drag-and-drop. Activate the flag here.

    This resolves above issue. Now i am able to edit edit formula using F2 & mouse.

    Thanks for providing this platform to gain & share knowledge.

    Dhananjay Jadhav
    Cost & Management Accountant - India.

Leave a Reply

« »