A quick trick showing how you can make a formula point to a new range without typing the range. Episode 575 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welome back to the MrExcel netcast. I'm Bill Jelen.
I have some random numbers here today.
I decide that I I need more random number, so I want to add some random numbers around the existing random numbers.
I'm going to select that entire range and hit the F5 key for "go to" and choose special and then going to choose blanks that will select all the blank cells and now I can enter my number equal and between five and thirty.
I'm gonna hit Ctrl enter and that will enter this formula in all of the selected cells.
so basically all the blanks now pick up that extra number.
change those random numbers from formulas to Values control C and then pay special value in order to do that.
Now the problem is I'm had a formula that was averaging all of my random numbers. Let's take a look at this formula.
I'll choose the formula and hit the F2 key to put me in edit mode and it shows that I was averaging everything from A1 to C9.
Now, what's interesting is that we have the blue selection handle around the range.
I could go in there and type A1 through E11 but it's also possible to grab one of the handles on the selection and just simply drag to say that I now want to average those numbers instead.
I've shown this trick with the charts before how to add new data to the chart using the blue fill handle.
I never realized that You could do it with editing formulas as well.
There you have it. Next time you need to edit a formula, Just simply grab the blue handle and drag the range to specify the new range.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
I have some random numbers here today.
I decide that I I need more random number, so I want to add some random numbers around the existing random numbers.
I'm going to select that entire range and hit the F5 key for "go to" and choose special and then going to choose blanks that will select all the blank cells and now I can enter my number equal and between five and thirty.
I'm gonna hit Ctrl enter and that will enter this formula in all of the selected cells.
so basically all the blanks now pick up that extra number.
change those random numbers from formulas to Values control C and then pay special value in order to do that.
Now the problem is I'm had a formula that was averaging all of my random numbers. Let's take a look at this formula.
I'll choose the formula and hit the F2 key to put me in edit mode and it shows that I was averaging everything from A1 to C9.
Now, what's interesting is that we have the blue selection handle around the range.
I could go in there and type A1 through E11 but it's also possible to grab one of the handles on the selection and just simply drag to say that I now want to average those numbers instead.
I've shown this trick with the charts before how to add new data to the chart using the blue fill handle.
I never realized that You could do it with editing formulas as well.
There you have it. Next time you need to edit a formula, Just simply grab the blue handle and drag the range to specify the new range.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
