Dave from San Francisco suggests you should use Excel without the mouse and challenges you to convert a formula to values in three keystrokes. Episode 958 shows various attempts to solve the problem without using the mouse.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically we start out with massive amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! Now, I have a quiz for you today.
I'm gonna ask the quiz first, that we can think about it.
Tell you the story behind the quiz.
The quiz comes from David in California.
David passed this tip along.
We have a cell here, the cell has a formula.
I want you to convert that formula to values using exactly three keystrokes.
Not, [ ctrl + C ], edit, paste special values, which is 5.
How can convert that formula to values in 3 keystrokes?
Now, let me tell you how this came about.
You know, I'm on Facebook and LinkedIn and so on and a few weeks ago.
I found my manager from 1988.
The guy kind of responsible for starting my whole Excel career.
His name is Mark.
He worked up in our finance department and he hired me out of the IT department and that's really...
For the next several years.
Where I started to use spreadsheets a lot and became very, very good at Excel and Mark now works in Chicago.
I was doing a seminar in Chicago, a few blocks from Mark's office and so Mark said yeah!
Come on out, and we had a great time catching up.
But then Mark called in three of his sharpest Excel guys.
And I challenged him to a little competition.
He introduced me as MrExcel and said that if they could stump me.
They would win an expensive dinner.
Now, of course you know, so that it's game on.
Right and I try to pull out some of my favorite Excel tricks.
You know like how do you copy that formula down to all of the cells.
Well, you know, as you know if you've watched this podcast.
You double click the fill handle and the three guys are like, well that doesn't count.
What do you mean? It doesn't count?
He says well, you're using the mouse.
Hey! You don't need to use the mouse when you use Excel.
I'm like you're right.
You don't need to use the mouse when using Excel.
You're actually a lot faster, so there it was kind of like playing dueling Excel, right.
So, they said you don't have to use the mouse at all equal 2 + 2 and that formula.
Here's what they do [ left ] [ end ] [ down ] [ right ] [ X ] or anything and [ up ] [ ctrl + C ].
And then [ ctrl + shift + down arrow ] and because we put the X there, we select the whole range and [ ctrl + V ].
I'm using the mouse you know, it would actually be interesting to see which is faster double-click the fill handle Or do that whole keystrokes.
If you did the keystrokes time and time again, I bet there'd be a very fast way to do that.
Okay!
So, you know I had to come up with some other trick, that didn't involve the mouse and then Dave from California said all right! Here's one.
How do you convert that formula to values in three keystrokes.
Now, I had to think about it for a second.
I came up with it.
I thought it was an interesting trick.
So, I did put that on Facebook, the next day.
You know, here's the quiz.
How can you do this and a few people came up with interesting ideas.
A lot of them had it.
Although, what I do like about this, is Greg says he uses application dot [ on ] key to do it in one keystroke.
Hooray to Greg!
Now, let's come back and take a look at Dave's solution and then we'll also take a look at Greg's solution.
So, here's what we're going to do.
We're gonna press the [ F2 ] key.
The [ F2 ] key puts that cell, in edit mode and then once we have a cell in edit mode if you press [ F9 ], it converts it to the current value and then press [ Enter ] and it will convert that cell to Magus.
Let's take a look at Greg's method which you know, is actually even better.
Greg goes into visual basic and in the workbook open, of his personal macro workbook.
He uses application dot On key, and it assigns it to a key.
So, I used here [ ctrl + shift + V ] and said that when we do that.
We're going to run a procedure called paste values and then in module one, it's a one line macro, right here, paste values.
So, I need to get rid of that.
It's a one line macro selection dot value equals selection dot value.
So, now here we will have this formula.
So, I have the formula, just press [ ctrl + shift + V ] and I convert those formulas to values.
So, there you have it.
A couple of different ways to very quickly convert a single cell from formulas to values and of course, let's just for completeness here.
I'm going to show you my favorite way of doing this.
If we have a whole bunch of formulas.
Now, this is using the mouse, go to the right edge right click and drag that somewhere because we right click and drag we then have a new pop up menu with copy here as values only.
If you need to convert them to values, right where they are, drag right drag left copy here as values only.
So, there you have it.
Want to send a shout-out to Mark.
The manager who got the whole thing started.
Also his three Excel guys and Dave for the cool Excel tips, today.
We should actually declare you know, "a national use Excel without the Mouse day" and watch how fast everyone that goes.
It's true if you don't have to reach out to the mouse, once you learn all those great keyboard shortcuts.
It's a much faster way to go.
Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically we start out with massive amount of data.
How are we gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Okay! Now, I have a quiz for you today.
I'm gonna ask the quiz first, that we can think about it.
Tell you the story behind the quiz.
The quiz comes from David in California.
David passed this tip along.
We have a cell here, the cell has a formula.
I want you to convert that formula to values using exactly three keystrokes.
Not, [ ctrl + C ], edit, paste special values, which is 5.
How can convert that formula to values in 3 keystrokes?
Now, let me tell you how this came about.
You know, I'm on Facebook and LinkedIn and so on and a few weeks ago.
I found my manager from 1988.
The guy kind of responsible for starting my whole Excel career.
His name is Mark.
He worked up in our finance department and he hired me out of the IT department and that's really...
For the next several years.
Where I started to use spreadsheets a lot and became very, very good at Excel and Mark now works in Chicago.
I was doing a seminar in Chicago, a few blocks from Mark's office and so Mark said yeah!
Come on out, and we had a great time catching up.
But then Mark called in three of his sharpest Excel guys.
And I challenged him to a little competition.
He introduced me as MrExcel and said that if they could stump me.
They would win an expensive dinner.
Now, of course you know, so that it's game on.
Right and I try to pull out some of my favorite Excel tricks.
You know like how do you copy that formula down to all of the cells.
Well, you know, as you know if you've watched this podcast.
You double click the fill handle and the three guys are like, well that doesn't count.
What do you mean? It doesn't count?
He says well, you're using the mouse.
Hey! You don't need to use the mouse when you use Excel.
I'm like you're right.
You don't need to use the mouse when using Excel.
You're actually a lot faster, so there it was kind of like playing dueling Excel, right.
So, they said you don't have to use the mouse at all equal 2 + 2 and that formula.
Here's what they do [ left ] [ end ] [ down ] [ right ] [ X ] or anything and [ up ] [ ctrl + C ].
And then [ ctrl + shift + down arrow ] and because we put the X there, we select the whole range and [ ctrl + V ].
I'm using the mouse you know, it would actually be interesting to see which is faster double-click the fill handle Or do that whole keystrokes.
If you did the keystrokes time and time again, I bet there'd be a very fast way to do that.
Okay!
So, you know I had to come up with some other trick, that didn't involve the mouse and then Dave from California said all right! Here's one.
How do you convert that formula to values in three keystrokes.
Now, I had to think about it for a second.
I came up with it.
I thought it was an interesting trick.
So, I did put that on Facebook, the next day.
You know, here's the quiz.
How can you do this and a few people came up with interesting ideas.
A lot of them had it.
Although, what I do like about this, is Greg says he uses application dot [ on ] key to do it in one keystroke.
Hooray to Greg!
Now, let's come back and take a look at Dave's solution and then we'll also take a look at Greg's solution.
So, here's what we're going to do.
We're gonna press the [ F2 ] key.
The [ F2 ] key puts that cell, in edit mode and then once we have a cell in edit mode if you press [ F9 ], it converts it to the current value and then press [ Enter ] and it will convert that cell to Magus.
Let's take a look at Greg's method which you know, is actually even better.
Greg goes into visual basic and in the workbook open, of his personal macro workbook.
He uses application dot On key, and it assigns it to a key.
So, I used here [ ctrl + shift + V ] and said that when we do that.
We're going to run a procedure called paste values and then in module one, it's a one line macro, right here, paste values.
So, I need to get rid of that.
It's a one line macro selection dot value equals selection dot value.
So, now here we will have this formula.
So, I have the formula, just press [ ctrl + shift + V ] and I convert those formulas to values.
So, there you have it.
A couple of different ways to very quickly convert a single cell from formulas to values and of course, let's just for completeness here.
I'm going to show you my favorite way of doing this.
If we have a whole bunch of formulas.
Now, this is using the mouse, go to the right edge right click and drag that somewhere because we right click and drag we then have a new pop up menu with copy here as values only.
If you need to convert them to values, right where they are, drag right drag left copy here as values only.
So, there you have it.
Want to send a shout-out to Mark.
The manager who got the whole thing started.
Also his three Excel guys and Dave for the cool Excel tips, today.
We should actually declare you know, "a national use Excel without the Mouse day" and watch how fast everyone that goes.
It's true if you don't have to reach out to the mouse, once you learn all those great keyboard shortcuts.
It's a much faster way to go.
Wanna thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
