Its two-for-the-price-of-one-Thursday (sounds like we need a new theme...). Question 1: When editing a long formula, the formula covers up adjacent cells, making it difficult to point to those cells with the mouse. Question 2: When you use Shift Ctrl Right Ctrl Down to select a large range, the Error icon scrolls off the screen. I'll propose solutions to both of these in Episode 859.
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
It's Thursday, and as I mentioned at the end of yesterday's podcast, don't forget to take a look at our new website, MrExcel.com.
Completely redesigned, simplified-- I want you to take a look and just drop me a note, bill@MrExcel.com, let me know what you think, positive or negative.
Are you able to find things easier?
Are you surprised there's stuff there that you didn't know?
Or, you hate it; you love the random disorganization of the old site?
So, drop us note.
Now, today, there's going to be a twofer-- twofer.
I have two questions; think I can cover them both here, in the same netcast.
I'm going to invite you to send in better solutions.
Let's say that we have a formula here, we hit F2 and you're a mouse person; you want to edit this formula and say we want to multiply it by, maybe, this cell-- we click the Times key and then click on that cell, that all works perfectly.
But, what if the Edit has to be in a Cell that's covered up by the formula?
So, we press the F2 key, and I want to multiply it by the cell right to the right-- I mean, I can't get to that cell, there's nothing I can do... it thinks i want to edit the formula.
Now, I have a couple of solutions for this.
The first solution is to click in the cell right underneath, and then press the Up Arrow key to get there, and that'll allow me to point to E32; the other solution doesn't necessarily use the mouse at all.
We press F2, and you see down here in the lower left-hand corner we're in something called Edit mode.
If I press F2 again, it changes to Enter mode and then I can press multiply, and the Right Arrow key-- the Right Arrow key will get me over to that cell that's covered up.
So the question number one, how do we point to a cell that's covered up by the formula.
Another question involves this Warning sign.
The Warning sign, it's a great way to change a number that's stored as text, using Convert to Number.
But let's say that we have this whole big range, and we start here, hold down the Shift key, press Ctrl+Right Arrow, and then Ctrl+Down Arrow, let go of the Shift key.
Okay, well, now we've selected that whole range but, unfortunately, the range is so large we can't see the icon at the top.
Now, sometimes-- it's really strange-- Excel will move that icon down here to the screen, but you see in this particular case, it didn't.
Well, again, I have two solutions for this.
One is to use the Ctrl+Period (Ctrl+.).
Hold down Ctrl+. and it's going to move to each corner of the selection.
So press that and we'll be able to see the icon now and change everything all at once.
My other solution is not to select the data in that particular manner; instead of using Ctrl+Right Arrow, Ctrl+Down Arrow, what we're going to do, is just use Ctrl+Shift+*.
And, actually, if you have a numeric keypad, just Ctrl+* on the numeric keypad will select all the data, and then we have the icon right here where everyone can see it.
Converts a number, solves the problem.
Now, if you have a better way to make that icon float to the bottom of the screen, please, again, drop us a note: bill@MrExcel.com.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
It's Thursday, and as I mentioned at the end of yesterday's podcast, don't forget to take a look at our new website, MrExcel.com.
Completely redesigned, simplified-- I want you to take a look and just drop me a note, bill@MrExcel.com, let me know what you think, positive or negative.
Are you able to find things easier?
Are you surprised there's stuff there that you didn't know?
Or, you hate it; you love the random disorganization of the old site?
So, drop us note.
Now, today, there's going to be a twofer-- twofer.
I have two questions; think I can cover them both here, in the same netcast.
I'm going to invite you to send in better solutions.
Let's say that we have a formula here, we hit F2 and you're a mouse person; you want to edit this formula and say we want to multiply it by, maybe, this cell-- we click the Times key and then click on that cell, that all works perfectly.
But, what if the Edit has to be in a Cell that's covered up by the formula?
So, we press the F2 key, and I want to multiply it by the cell right to the right-- I mean, I can't get to that cell, there's nothing I can do... it thinks i want to edit the formula.
Now, I have a couple of solutions for this.
The first solution is to click in the cell right underneath, and then press the Up Arrow key to get there, and that'll allow me to point to E32; the other solution doesn't necessarily use the mouse at all.
We press F2, and you see down here in the lower left-hand corner we're in something called Edit mode.
If I press F2 again, it changes to Enter mode and then I can press multiply, and the Right Arrow key-- the Right Arrow key will get me over to that cell that's covered up.
So the question number one, how do we point to a cell that's covered up by the formula.
Another question involves this Warning sign.
The Warning sign, it's a great way to change a number that's stored as text, using Convert to Number.
But let's say that we have this whole big range, and we start here, hold down the Shift key, press Ctrl+Right Arrow, and then Ctrl+Down Arrow, let go of the Shift key.
Okay, well, now we've selected that whole range but, unfortunately, the range is so large we can't see the icon at the top.
Now, sometimes-- it's really strange-- Excel will move that icon down here to the screen, but you see in this particular case, it didn't.
Well, again, I have two solutions for this.
One is to use the Ctrl+Period (Ctrl+.).
Hold down Ctrl+. and it's going to move to each corner of the selection.
So press that and we'll be able to see the icon now and change everything all at once.
My other solution is not to select the data in that particular manner; instead of using Ctrl+Right Arrow, Ctrl+Down Arrow, what we're going to do, is just use Ctrl+Shift+*.
And, actually, if you have a numeric keypad, just Ctrl+* on the numeric keypad will select all the data, and then we have the icon right here where everyone can see it.
Converts a number, solves the problem.
Now, if you have a better way to make that icon float to the bottom of the screen, please, again, drop us a note: bill@MrExcel.com.
Well, thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
