While collecting possible tips for my 40th Excel book, I ran across two tricks that were new to me. Shift+F8 lets you select non-contiguous ranges without using Ctrl+Mouse. And Go To Special Row Differences. For more info about the book: MrExcel XL Book - 40 Greatest Excel Tricks
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1945.
Shift+F8 Add to Selection.
Hey, welcome back.
It's a new year here at the MrExcel podcast.
For those of you that were out for the last couple weeks, I want to catch you up.
I'm about to write my 40th book and I'm really, really excited about this book.
You know, for a lot of things, 50 or 100 would be a big milestone.
But if you use the Roman function in Excel, =ROMAN(A4), the fact that “40” is spelled “XL” in Roman, is pretty cool in a geeky way.
Now, as I was growing up, a lot of titles included Roman numerals: Led Zeppelin II, Chicago III, Rocky IV, Star Wars Episode V, even recently Kylie Minogue X for a ”10”.
So, I thought it'd be funny to name a book simply “MrExcel XL”.
I've known about this title for more than a year.
It was a title in search of a book.
What can I put in my 40th book?
I finally decided, I wanted to do something really cool.
Nice and short: the 40 greatest Excel tips of all time.
Now, I wanted this to be a really special book: full color, glossy paper – that requires a big print run, a lot of commitment.
And so, I decided to work together with people to collaborate on this book, using something called “crowdfunding”.
I went out to IndieGoGo, I started this project and I said: hey, I'm asking you to pre-buy this book.
Buy this book before it comes out.
I'll send you an autographed copy and, even better, I'll put you in the book.
Now, even if you don't want to buy the book, if you contribute $4, you get to be a Voter.
$10 for eBook, $24 for an autographed book and a whole bunch of other levels above that.
And my idea was: first to get the money to pay for that print run, and second, to get a whole bunch of people, who would be interested enough in Excel, to vote on which tips are the 40 greatest Excel tips of all time.
I mean, just think about that: anytime someone comes up with a list of the top 10 or top 100, you see this on MTV or VH1 all the time, you know, there are arguments: why did you include that and not include that?
So, rather than me just choosing 40 tips, I wanted a large crowd of people passionate about Excel, to vote on which tips make the best tips.
Now, I want to thank you, my Supporters, the results have been insane.
I didn't get just enough money to print the book in color, it's going to be a larger book.
The results have been fantastic, so I appreciate the financial support.
That's wonderful.
But even better is the amazing synergy of having a lot of people contribute on the book.
This past Friday was January 2nd, most people had the day off.
A few of us were stuck at work, and so, I took to Twitter and started asking people: if you were going to choose one Excel tip to be in the book, what would your tip be?
The results were fun.
I got a lot of things that I expected to get, but then, every once in a while, I would get a tip that I had never heard of before.
For example: Neil Charles (twitter handle @data_monkey, that's great!) says: “Shift+F8 locks the selection, so you can move around and select something else.
It's essential for charting.
(which is F11.
Never via menus).” I have to tell you: this one threw me for a loop.
I had never used Shift+F8.
I had accidentally sometimes hit F8.
You go to hit F9 to recalculate and you press F8, and you're thrown into this horrible Extend Selection mode, where everywhere you click, it just goes from the active cell down to the new click point, right, which… I've never found a use for this, I hate this mode.
And you always have to press F8 to get out.
Except, I can never remember that it’s F8, I'm always like: what the heck did I just accidentally hit to cause that?
But as Neil points out: shift+F8 is completely different and something I've never used before.
Now, if you want to select these three ranges, without using the mouse you can do this using Shift+F8.
I'm going to show to you, so I'm going to start here, and of course hold down Ctrl+Shift+Down arrow+Right arrow, to select the first range.
I think a lot of us already know that, but then, what I would do, is hold down the Ctrl key and reach out to grab the mouse.
And any time you reach out to grab the mouse, things are going to slow down.
So watch this: I press Shift+F8, and look down there: in the status bar at the bottom, next to the word READY, it says that we're in something called ADD TO SELECTION.
And now I'm going to use the arrow keys, so Ctrl+Down arrow, Ctrl+Right arrow, Ctrl+Down arrow, Down arrow.
Now, isn't this amazing, somehow I'm using the arrow keys and it's not getting rid of the original selection.
From here, Ctrl+Shift+Down arrow, Ctrl+Shift+Right arrow.
I've just selected a second selection.
Now, you'll notice down in the status bar, let's look down there, ADD TO SELECTION has gone.
So press Shift+F8 again.
Bam, now, Ctrl+Right arrow, Ctrl+Up arrow, Ctrl+Up arrow, Ctrl+Up arrow, Right, Down to get up to the next corner, Ctrl+Shift+Down, Ctrl+Shift+Right.
I've selected these three cells without ever having to touch the mouse.
Now, the example that Neil actually gave, is: when you're doing charting.
So, maybe we select this range and we want to create a chart from those region names, and the total: Shift+F8, Ctrl+Right arrow, Ctrl+Shift+Down arrow and I've now selected two ranges.
Insert a chart with F11 or Alt+F1, your choice.
Oh me, I love great Excel tricks and this is a beautiful Excel trick, and I wouldn't have run across this beautiful Excel trick, if it hadn't been for this crazy notion: just because the number “40” in Roman numeral spells “XL”, then I came up with the idea for crowdfunding this book.
If you like that one, how about this one from Colleen Young: we have two sets of numbers, they should be the same, someone made… run through and made some changes.
We need to figure out which cells have changed.
Now, normally, I'd build a little formula here: =E2=B2, and look for the FALSEs.
Even better, I'm going to choose this range and then I'll use (Neil strucks again) Shift+F8, Ctrl+Shift+Down arrow, I've now selected both sets of numbers.
Colleen's trick: Find & Select / Go To Special, ask for the Row differences, click OK and the only things that are highlighted are the cells that changed.
Change to red, no need to build a formula.
What an amazing trick.
I love this.
I'm having so much fun with this book, it is going to be a really great book.
I've got a lot of illustrators, who are going to create custom Excel art for the book, it's going to be in full color, we're even going to print a hundred of those books with a special commemorative letterpress cover, down at Hatch Show Print.
I’ll order those covers over the weekend.
So, “MrExcel XL.
The 40 Greatest Excel Tips Of All Time” is going to be a very fun book.
It's going to be available on Amazon, starting September 1st 2015.
But if you want to get the book before that, if you want to participate in voting which tips get into this book, if you want to see how a book is built, I want to invite you to go to mrx.cl/xlbook40.
That'll get you to the IndieGoGo campaign.
Join us, $4, $10, $24, we will have a lot of fun.
And, for being part of this early group, you will have your name in the book.
How geeky is that?
Hey, well, there you have two great new Excel tricks.
Tomorrow I'm heading down to South Carolina, because on January 7th I’m going to do my first ever Live Power Excel Seminar in Columbia, South Carolina.
And then driving on down to Florida.
Hopefully I'll be back on Friday, with maybe some more great Excel tips that I would pick up in Columbia.
Well, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1945.
Shift+F8 Add to Selection.
Hey, welcome back.
It's a new year here at the MrExcel podcast.
For those of you that were out for the last couple weeks, I want to catch you up.
I'm about to write my 40th book and I'm really, really excited about this book.
You know, for a lot of things, 50 or 100 would be a big milestone.
But if you use the Roman function in Excel, =ROMAN(A4), the fact that “40” is spelled “XL” in Roman, is pretty cool in a geeky way.
Now, as I was growing up, a lot of titles included Roman numerals: Led Zeppelin II, Chicago III, Rocky IV, Star Wars Episode V, even recently Kylie Minogue X for a ”10”.
So, I thought it'd be funny to name a book simply “MrExcel XL”.
I've known about this title for more than a year.
It was a title in search of a book.
What can I put in my 40th book?
I finally decided, I wanted to do something really cool.
Nice and short: the 40 greatest Excel tips of all time.
Now, I wanted this to be a really special book: full color, glossy paper – that requires a big print run, a lot of commitment.
And so, I decided to work together with people to collaborate on this book, using something called “crowdfunding”.
I went out to IndieGoGo, I started this project and I said: hey, I'm asking you to pre-buy this book.
Buy this book before it comes out.
I'll send you an autographed copy and, even better, I'll put you in the book.
Now, even if you don't want to buy the book, if you contribute $4, you get to be a Voter.
$10 for eBook, $24 for an autographed book and a whole bunch of other levels above that.
And my idea was: first to get the money to pay for that print run, and second, to get a whole bunch of people, who would be interested enough in Excel, to vote on which tips are the 40 greatest Excel tips of all time.
I mean, just think about that: anytime someone comes up with a list of the top 10 or top 100, you see this on MTV or VH1 all the time, you know, there are arguments: why did you include that and not include that?
So, rather than me just choosing 40 tips, I wanted a large crowd of people passionate about Excel, to vote on which tips make the best tips.
Now, I want to thank you, my Supporters, the results have been insane.
I didn't get just enough money to print the book in color, it's going to be a larger book.
The results have been fantastic, so I appreciate the financial support.
That's wonderful.
But even better is the amazing synergy of having a lot of people contribute on the book.
This past Friday was January 2nd, most people had the day off.
A few of us were stuck at work, and so, I took to Twitter and started asking people: if you were going to choose one Excel tip to be in the book, what would your tip be?
The results were fun.
I got a lot of things that I expected to get, but then, every once in a while, I would get a tip that I had never heard of before.
For example: Neil Charles (twitter handle @data_monkey, that's great!) says: “Shift+F8 locks the selection, so you can move around and select something else.
It's essential for charting.
(which is F11.
Never via menus).” I have to tell you: this one threw me for a loop.
I had never used Shift+F8.
I had accidentally sometimes hit F8.
You go to hit F9 to recalculate and you press F8, and you're thrown into this horrible Extend Selection mode, where everywhere you click, it just goes from the active cell down to the new click point, right, which… I've never found a use for this, I hate this mode.
And you always have to press F8 to get out.
Except, I can never remember that it’s F8, I'm always like: what the heck did I just accidentally hit to cause that?
But as Neil points out: shift+F8 is completely different and something I've never used before.
Now, if you want to select these three ranges, without using the mouse you can do this using Shift+F8.
I'm going to show to you, so I'm going to start here, and of course hold down Ctrl+Shift+Down arrow+Right arrow, to select the first range.
I think a lot of us already know that, but then, what I would do, is hold down the Ctrl key and reach out to grab the mouse.
And any time you reach out to grab the mouse, things are going to slow down.
So watch this: I press Shift+F8, and look down there: in the status bar at the bottom, next to the word READY, it says that we're in something called ADD TO SELECTION.
And now I'm going to use the arrow keys, so Ctrl+Down arrow, Ctrl+Right arrow, Ctrl+Down arrow, Down arrow.
Now, isn't this amazing, somehow I'm using the arrow keys and it's not getting rid of the original selection.
From here, Ctrl+Shift+Down arrow, Ctrl+Shift+Right arrow.
I've just selected a second selection.
Now, you'll notice down in the status bar, let's look down there, ADD TO SELECTION has gone.
So press Shift+F8 again.
Bam, now, Ctrl+Right arrow, Ctrl+Up arrow, Ctrl+Up arrow, Ctrl+Up arrow, Right, Down to get up to the next corner, Ctrl+Shift+Down, Ctrl+Shift+Right.
I've selected these three cells without ever having to touch the mouse.
Now, the example that Neil actually gave, is: when you're doing charting.
So, maybe we select this range and we want to create a chart from those region names, and the total: Shift+F8, Ctrl+Right arrow, Ctrl+Shift+Down arrow and I've now selected two ranges.
Insert a chart with F11 or Alt+F1, your choice.
Oh me, I love great Excel tricks and this is a beautiful Excel trick, and I wouldn't have run across this beautiful Excel trick, if it hadn't been for this crazy notion: just because the number “40” in Roman numeral spells “XL”, then I came up with the idea for crowdfunding this book.
If you like that one, how about this one from Colleen Young: we have two sets of numbers, they should be the same, someone made… run through and made some changes.
We need to figure out which cells have changed.
Now, normally, I'd build a little formula here: =E2=B2, and look for the FALSEs.
Even better, I'm going to choose this range and then I'll use (Neil strucks again) Shift+F8, Ctrl+Shift+Down arrow, I've now selected both sets of numbers.
Colleen's trick: Find & Select / Go To Special, ask for the Row differences, click OK and the only things that are highlighted are the cells that changed.
Change to red, no need to build a formula.
What an amazing trick.
I love this.
I'm having so much fun with this book, it is going to be a really great book.
I've got a lot of illustrators, who are going to create custom Excel art for the book, it's going to be in full color, we're even going to print a hundred of those books with a special commemorative letterpress cover, down at Hatch Show Print.
I’ll order those covers over the weekend.
So, “MrExcel XL.
The 40 Greatest Excel Tips Of All Time” is going to be a very fun book.
It's going to be available on Amazon, starting September 1st 2015.
But if you want to get the book before that, if you want to participate in voting which tips get into this book, if you want to see how a book is built, I want to invite you to go to mrx.cl/xlbook40.
That'll get you to the IndieGoGo campaign.
Join us, $4, $10, $24, we will have a lot of fun.
And, for being part of this early group, you will have your name in the book.
How geeky is that?
Hey, well, there you have two great new Excel tricks.
Tomorrow I'm heading down to South Carolina, because on January 7th I’m going to do my first ever Live Power Excel Seminar in Columbia, South Carolina.
And then driving on down to Florida.
Hopefully I'll be back on Friday, with maybe some more great Excel tips that I would pick up in Columbia.
Well, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
