3 fast tips from my recent seminar in Louisville Kentucky: Different way to select current region; different way to Undo; How to overline and underline subtotals. Episode 1009 shows you how.
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.
So,how we're going to analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I am Bill Jelen.
This is a three for today.
Three fast tips came up in recent seminars.
Not long enough for their own podcast.
Let's just talk about him.
First couple ones are shortcut keys.
You know if you select to select any cell, press CTRL+A.
It selects all cells in the worksheet.
CTRL+A selects all cells.
Sure there's out there's another behavior.
If you select a cell inside a range of data, CTRL+A selects the current region.
CTRL+A a second time, selects the whole worksheet.
So, that's kind of cool I'd always use CTRL+ SHIFT+* (Asterisk), but turns out.
You can just use CTRL+A.
You know another point of seminar, I'm always talking about this one technique.
I say alright!
We need to undo and I say what's the fast way to do?
And expect everything audience to say CTRL+Z.
Well, most people said CTRL+Z but this one fellow named Derek said, "ALT+Backspace".
I stopped what?
ALT+Backspace, by sure enough ALT+Backspace is another way to undo.
It Isn't a while second thing.
I said Derek, "Where'd you learn that?| He says, "hey, when you screw up as much as I do, you know everywhere has to undo".
and then the next question came up.
We were talking about automatic SUBTOTALS.
So, SUBTOTALS>At each change in: and customer and Choose all these fields.
And they wanted to apply special formatting to just the SUBTOTAL rows.
So, we collapse it out to the number to view.
And in this particular case, they wanted a line above and below all of the subtotal number.
So, we have to choose the very first SUBTOTAL number.
Now, the one real important thing here is not to include the Grand Total.
If you include the Grand Total, then you have two rows that are adjacent and that causes the trip to not work.
So, select all the numbers from the first total down to the last customer total not including the SUBTOTAL.
Press ALT+;(semicolon) to select the visible cells only.
And then we do CTRL+1 to go in to Format Cells.
Now here in the border Tab, we can choose a top and bottom border.
Click Ok and the Trick becomes evident.
When we click number three, you see that actually has applied that formatting to the top and bottom.
If you try that trick with the grand total selected, it doesn't work you end up with the wrong effect.
So, kind of a very specific question there, but there is a way to do it.
Well, hey, I want to thank you for stopping by And thanks to all the folks from the recent seminars, who passed those tips along.
Of course they all on the Excel master pit.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
So,how we're going to analyze as well plus file up a pivot table.
Let's see if we can solve this problem.
Welcome back to the MrExcel netcast.
I am Bill Jelen.
This is a three for today.
Three fast tips came up in recent seminars.
Not long enough for their own podcast.
Let's just talk about him.
First couple ones are shortcut keys.
You know if you select to select any cell, press CTRL+A.
It selects all cells in the worksheet.
CTRL+A selects all cells.
Sure there's out there's another behavior.
If you select a cell inside a range of data, CTRL+A selects the current region.
CTRL+A a second time, selects the whole worksheet.
So, that's kind of cool I'd always use CTRL+ SHIFT+* (Asterisk), but turns out.
You can just use CTRL+A.
You know another point of seminar, I'm always talking about this one technique.
I say alright!
We need to undo and I say what's the fast way to do?
And expect everything audience to say CTRL+Z.
Well, most people said CTRL+Z but this one fellow named Derek said, "ALT+Backspace".
I stopped what?
ALT+Backspace, by sure enough ALT+Backspace is another way to undo.
It Isn't a while second thing.
I said Derek, "Where'd you learn that?| He says, "hey, when you screw up as much as I do, you know everywhere has to undo".
and then the next question came up.
We were talking about automatic SUBTOTALS.
So, SUBTOTALS>At each change in: and customer and Choose all these fields.
And they wanted to apply special formatting to just the SUBTOTAL rows.
So, we collapse it out to the number to view.
And in this particular case, they wanted a line above and below all of the subtotal number.
So, we have to choose the very first SUBTOTAL number.
Now, the one real important thing here is not to include the Grand Total.
If you include the Grand Total, then you have two rows that are adjacent and that causes the trip to not work.
So, select all the numbers from the first total down to the last customer total not including the SUBTOTAL.
Press ALT+;(semicolon) to select the visible cells only.
And then we do CTRL+1 to go in to Format Cells.
Now here in the border Tab, we can choose a top and bottom border.
Click Ok and the Trick becomes evident.
When we click number three, you see that actually has applied that formatting to the top and bottom.
If you try that trick with the grand total selected, it doesn't work you end up with the wrong effect.
So, kind of a very specific question there, but there is a way to do it.
Well, hey, I want to thank you for stopping by And thanks to all the folks from the recent seminars, who passed those tips along.
Of course they all on the Excel master pit.
See you next time for another netcast from MrExcel.
