fbpx
Search
Close this search box.

Use Mouse to Edit Formula Ranges [quick tip]

Share

Facebook
Twitter
LinkedIn

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

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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:

    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

  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.

    Reg
    Dhananjay Jadhav
    Cost & Management Accountant - India.
    dhananjaypjadhav@gmail.com

Leave a Reply