When you have created automatic subtotals and want to format the subtotals rows, you will probably be visiting the Edit - Goto - Special - Visible Cells only setting. However, by customizing your toolbar, you can select the subtotals using a single button click. Episode 376 shows you how. Also - see how to move buttons on a toolbar without displaying the customize dialog.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel NetCast, I'm Bill Jelen.
In past netcasts I've talked about how to use the Automatic Subtotals feature to quickly create subtotals by customer.
And then once we have the subtotals, we have the three group and outline buttons that let us see all of the detail records, or just the customer records.
Once you're able to see just the customer totals, a problem that pops up is that we often want to format just the total rows.
And as I've shown in previous podcast, this often involves using Edit, Go To, Special, and then selecting Visible Cells Only.
Well I mentioned, I think, previously, that we can use Alt+; to replicate that entire process of Edit, Go To, Special, Visible Cells Only.
But someone pointed out to me that an even easier way than using Alt+; is to customize the toolbar, and there is an icon available called Select Visible Cells.
Press Select Visible Cells, and then you can apply a special formatting, maybe Bold or a special color to those total rows.
And then, when we go back to show the details, the totals are set off in a different color.
Now, in case you've forgotten how to customize the toolbar, you want to right-click the toolbar and choose Customize.
You’re then shown the Customize dialogue with the center tab, the Commands tab.
You can go through the various categories, look in the Edit category and scroll down on the right-hand side, and you will see the icons for Select Visible Cells or Select Current Region.
Both great icons to have on your toolbar, once you find, we just drag them and drop them at the right spot on the toolbar, click Close, and they'll always be available to you.
These settings are fairly permanent, you'll have them until you get a new computer.
To move your toolbar settings from one computer to another, you need to find the .tlb file on your computer and copy that to the new computer.
Hey, another great trick for customizing toolbars.
Once the dialog is displayed, you're allowed to move buttons from place to place.
But an even better trick, if you want to move a button to a new place, hold down the Alt key while you click, and drag, and you're able to move that button to a different spot on any toolbar without displaying the Customize dialog box.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
In past netcasts I've talked about how to use the Automatic Subtotals feature to quickly create subtotals by customer.
And then once we have the subtotals, we have the three group and outline buttons that let us see all of the detail records, or just the customer records.
Once you're able to see just the customer totals, a problem that pops up is that we often want to format just the total rows.
And as I've shown in previous podcast, this often involves using Edit, Go To, Special, and then selecting Visible Cells Only.
Well I mentioned, I think, previously, that we can use Alt+; to replicate that entire process of Edit, Go To, Special, Visible Cells Only.
But someone pointed out to me that an even easier way than using Alt+; is to customize the toolbar, and there is an icon available called Select Visible Cells.
Press Select Visible Cells, and then you can apply a special formatting, maybe Bold or a special color to those total rows.
And then, when we go back to show the details, the totals are set off in a different color.
Now, in case you've forgotten how to customize the toolbar, you want to right-click the toolbar and choose Customize.
You’re then shown the Customize dialogue with the center tab, the Commands tab.
You can go through the various categories, look in the Edit category and scroll down on the right-hand side, and you will see the icons for Select Visible Cells or Select Current Region.
Both great icons to have on your toolbar, once you find, we just drag them and drop them at the right spot on the toolbar, click Close, and they'll always be available to you.
These settings are fairly permanent, you'll have them until you get a new computer.
To move your toolbar settings from one computer to another, you need to find the .tlb file on your computer and copy that to the new computer.
Hey, another great trick for customizing toolbars.
Once the dialog is displayed, you're allowed to move buttons from place to place.
But an even better trick, if you want to move a button to a new place, hold down the Alt key while you click, and drag, and you're able to move that button to a different spot on any toolbar without displaying the Customize dialog box.
Hey, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
