In Episode 761, a look at three cool custom icons you can add to your toolbar in Excel 2007 or 2003. See how easy it is to use Select Current Region, Select Visible Cells, and Set Print Area.
This blog is the video 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 blog is the video 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.
I want to talk about customizing toolbars today.
In 2007, of course we can right click the quick access toolbar and choose CUSTOMIZE, and there's hundreds of icons that we can add to the toolbar.
Back in 2003, a similar process, we would go to TOOLS, CUSTOMIZE, and then while the CUSTOMIZE dialog box is displayed, we can take any icon and add it to the toolbar, or we could even create a brand new toolbar by going to the NEW button on the TOOLBARS tab, and we'll call it PODCAST761, click OK, and now we have a floating toolbar, and, again, we can drag buttons to this toolbar.
So, as you start to drag buttons to the toolbar, a couple of buttons that are just phenomenal that you should consider having are in the EDIT command, something called SELECT CURRENT REGION and SELECT VISIBLE CELLS, and then back on the FILE tab, one that I do all the time that is fairly well buried, is SET PRINT AREA.
Alright.
We'll click CLOSE.
So, now we have our new little toolbar with this area.
The SELECT CURRENT REGION is great because what it does is, if you have one cell selected and press SELECT CURRENT REGION, it basically goes in all directions until it encounters a blank.
So, here, I have some data in E, F, and G.
When I do SELECT CURRENT REGION, it selects the entire contiguous range of data.
If I would just have a single column of data, it'll extend the selection just for that column, or here, where I have a large data set, it will extend all the way out.
Now, if you would happen to not have blank columns between sections of your report -- so, let me delete column D and select something here -- now, when I do SELECT CURRENT REGION, you see it's going to go all the way out until the edge of all of the regions.
So, not necessarily what you do, but if you build your worksheets with each section kind of separated by columns or rows, then this is going to work excellently.
After you've selected the current region, you can then in many cases just set the print area, you know.
So, lots of times, we'll have a data set in A through whatever and then some other little temporary tables over here.
To get those out of the print area, select the original data set, select the current region, and set the print area.
Much easier now, 2 clicks, to do what used to take several clicks.
Visible cells is great if you're a big fan of subtotals.
So, if I do DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, add the SUM function to the REVENUE column, and then collapse that down to the number 2 view, you'll see that we have just the CUSTOMER totals.
Well, if I select the current region and then SELECT VISIBLE CELLS, that's a really fast way to select just those customer totals.
CONTROL+C to copy, go to new workbook, CONTROL+V to paste.
If you didn't use SELECT VISIBLE CELLS only, it would bring all the detail rows along as well.
So, these are 3 icons that are not on the standard or formatting toolbar, or are not easy to get to in Excel 2007.
If you consider adding these as custom icons in neither the quick access toolbar in 2007 or just to any toolbar in Excel 2003 and earlier, you'll have one-click access to some very cool tricks.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I want to talk about customizing toolbars today.
In 2007, of course we can right click the quick access toolbar and choose CUSTOMIZE, and there's hundreds of icons that we can add to the toolbar.
Back in 2003, a similar process, we would go to TOOLS, CUSTOMIZE, and then while the CUSTOMIZE dialog box is displayed, we can take any icon and add it to the toolbar, or we could even create a brand new toolbar by going to the NEW button on the TOOLBARS tab, and we'll call it PODCAST761, click OK, and now we have a floating toolbar, and, again, we can drag buttons to this toolbar.
So, as you start to drag buttons to the toolbar, a couple of buttons that are just phenomenal that you should consider having are in the EDIT command, something called SELECT CURRENT REGION and SELECT VISIBLE CELLS, and then back on the FILE tab, one that I do all the time that is fairly well buried, is SET PRINT AREA.
Alright.
We'll click CLOSE.
So, now we have our new little toolbar with this area.
The SELECT CURRENT REGION is great because what it does is, if you have one cell selected and press SELECT CURRENT REGION, it basically goes in all directions until it encounters a blank.
So, here, I have some data in E, F, and G.
When I do SELECT CURRENT REGION, it selects the entire contiguous range of data.
If I would just have a single column of data, it'll extend the selection just for that column, or here, where I have a large data set, it will extend all the way out.
Now, if you would happen to not have blank columns between sections of your report -- so, let me delete column D and select something here -- now, when I do SELECT CURRENT REGION, you see it's going to go all the way out until the edge of all of the regions.
So, not necessarily what you do, but if you build your worksheets with each section kind of separated by columns or rows, then this is going to work excellently.
After you've selected the current region, you can then in many cases just set the print area, you know.
So, lots of times, we'll have a data set in A through whatever and then some other little temporary tables over here.
To get those out of the print area, select the original data set, select the current region, and set the print area.
Much easier now, 2 clicks, to do what used to take several clicks.
Visible cells is great if you're a big fan of subtotals.
So, if I do DATA, SUBTOTALS, AT EACH CHANGE IN CUSTOMER, add the SUM function to the REVENUE column, and then collapse that down to the number 2 view, you'll see that we have just the CUSTOMER totals.
Well, if I select the current region and then SELECT VISIBLE CELLS, that's a really fast way to select just those customer totals.
CONTROL+C to copy, go to new workbook, CONTROL+V to paste.
If you didn't use SELECT VISIBLE CELLS only, it would bring all the detail rows along as well.
So, these are 3 icons that are not on the standard or formatting toolbar, or are not easy to get to in Excel 2007.
If you consider adding these as custom icons in neither the quick access toolbar in 2007 or just to any toolbar in Excel 2003 and earlier, you'll have one-click access to some very cool tricks.
Well, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
