will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Your Customized Excel Toolbars
Yesterday I posted a tip for adding the Refresh All button to the PivotTable toolbar, so you can quickly update all the pivot tables and queries in a workbook. In the comments, Jan Karel Pieterse mentioned that he can press Ctrl+Shift, or hold the Alt key, then drag toolbar buttons to a different toolbar. I can’t get that Ctrl+Shift shortcut to work, but often use the Alt key to adjust toolbars on the fly.
In another comment, AlexJ described how he moves well-hidden menu commands, such as Show Formula Auditing Toolbar, to a toolbar, so they’re easier to use. Great idea!
Besides the Refresh All button, there are a few others that I add to my Excel toolbars. For instance, when I’m creating data entry forms it helps to know if a cell is locked or unlocked. I copy the Lock Cell button from the Protection toolbar to my Standard toolbar (Ctrl+Alt, then drag the button).
When I click on a cell, the Lock Cell button shows the cell’s current setting, and I can click that button to lock or unlock the cell. Very handy!
I also add the Record Macro and Visual Basic Editor buttons, copied from the Visual Basic toolbar, and Toggle Grid from the Forms toolbar.
What Buttons Do You Add or Remove?
- Are there any standard command buttons that you always add to your toolbars when you install Excel?
- Any standard command buttons that you always remove, because you never use them?
If you’d like to share a screen shot of your toolbars, upload it somewhere like TinyPic.com, and paste a link in your comment. Or, if you’re feeling adventurous, you can open this Google Spreadsheet, choose Insert>Picture, and paste the picture link in there. I have no idea how well this will work, but it might be an interesting experiment!
Filed under: Excel, Productivity, Toolbars
Comment from Jan Karel Pieterse
Time: October 28, 2008, 5:38 am
Silly me, I meant to write that you can control+alt-drag or alt+drag buttons from toolbars. Not control+shift. Sorry!!
Comment from Bryony
Time: October 28, 2008, 7:35 am
I had a go with the Google spreadsheet, but nought happened. So here is my link…
http://www.facebook.com/photo.php?pid=1521881&l=c930e&id=576057493
The colourful ones at the bottom in the middle are a few little macros, the toolbar to the left is my custom one. I have taken a load off the standard bars as I absolutely never use so many of those features. I have my screen resolution set quite high, so I have quite a bit of space left!
Pingback from My Customized Excel Toolbars | PTS Blog
Time: October 28, 2008, 8:13 am
[...] Your Customized Excel Toolbars, Debra Dalgleish asks what customizations we’ve made to our Excel toolbars. I have a large [...]
Comment from Jon Peltier
Time: October 28, 2008, 8:15 am
I’ve just responded with a post about my custom toolbars in My Customized Excel Toolbars.
Comment from Debra Dalgleish
Time: October 28, 2008, 8:58 am
Jan Karel, no problem! I just thought you had some fancy new keyboard.
Bryony, it’s my first attempt at a shared Google spreadsheet, so maybe I need to adjust the settings. I’ve added your screenshot, and thanks for sharing it.
Now I have to go read Jon’s response…
Comment from Stephen
Time: October 29, 2008, 6:52 am
The one additon I use a lot and often add to others’ Excel toolbar is the Paste Values button.
I know it’s there on the drop-down of the standard Paste button, but I’m lazy and want a single click, and almost without exception, other users that I have helped out in the past do not look at the additional commands that sit behind those little down-arrows on toolbar buttons. (sad, but true)
Comment from Debra Dalgleish
Time: October 29, 2008, 9:06 am
Stephen, we’re not lazy, we’re efficient! I’ve got Paste Values, Paste Formulas and Paste Transpose right beside that Paste button. Two clicks? Who’s got time for that?
Comment from AlexJ
Time: October 29, 2008, 9:41 am
Part 1 of 2 is in the link. It takes too long to document this stuff. Wouldn’t a controls document utility be cool? Not a technical one like in Professional Excel development, but one for user manuals.
http://tinypic.com/view.php?pic=dr2nw9&s=4
Comment from Debra Dalgleish
Time: October 29, 2008, 12:17 pm
Alex, that’s a work of art! And I just added a Name Manager button to my toolbars — hadn’t thought of that till I saw yours.
Comment from AlexJ
Time: October 29, 2008, 1:12 pm
Thkx, Debra. My favourite of the group is [Name In Range] for its function (which Name Manager, I recently discovered, addresses by the highlight function).
I “stole” the [Name In Range] function from AppsPro Excel Utilities by starting the Add-In, and then copying the command button to my own tool bar from the Excel Utilities menu. After that, I unchecked the Excel Utilities Add In so that it does not load on startup, but when I select the [Name In Range] button, it LOADS the Add In and engages the function.
Not sure if this is a reliable deployment technique for all functions of this type, but it works for me. (And was a surprise, too).
Comment from Stephen
Time: October 29, 2008, 8:22 pm
Debra, you are right of course - it IS efficiency that drives our fine-tuning of the Excel toolbar.
In the same way, and because I exceed Excel’s list of 9 most recent documents, I keep Ken Puls’ XLG Favourites installed to facilitate access to my most commonly used files and folders.
http://www.excelguru.ca/node/64
Comment from Debra Dalgleish
Time: October 29, 2008, 9:11 pm
Thanks Stephen, I’ve just installed the XLG Favourites add-in, and looks like it will be really helpful.
Pingback from Contextures Blog » Excel Favourites Add-In
Time: October 30, 2008, 12:07 am
[...] you can download and install Ken Puls’ XLG Favourites Add-in. Stephen mentioned this add-in yesterday in the comments, and I’ve installed it to try it [...]
Comment from sam
Time: November 2, 2008, 5:45 am
For Excel 2007
http://tinypic.com/view.php?pic=2r556wz&s=4
Comment from sam
Time: November 2, 2008, 5:55 am
For Excel 2003
http://tinypic.com/view.php?pic=28ludjd&s=4
http://tinypic.com/view.php?pic=2rmbiwk&s=4
http://tinypic.com/view.php?pic=34sqjbm&s=4
http://tinypic.com/view.php?pic=aelkkk&s=4
Comment from Debra Dalgleish
Time: November 2, 2008, 10:46 am
Thanks Sam, looks like you’ve got everything you need in Excel 2007 in one handy Ribbon add-in! And I hope you have a backup of your customized toolbar file for Excel 2003 — it must have taken a long time to set that up.
Comment from Peggy Duncan
Time: December 11, 2008, 10:38 pm
Hi Debra, I added the toolbar button from the File menu “Mail recipient (as attachment)”. Turns emailing a workbook to one click. I did the same thing in Word and PowerPoint.
Comment from Debra Dalgleish
Time: December 12, 2008, 6:59 pm
Peggy, that’s a great idea. It will save a step when I’m finished working on a client’s file and want to email it.