Tailor Excel to Suit You
Yesterday I wrote about little know ways to help Excel work faster. Today we’ll continue the efficiency theme a little, and discuss how you can easily customize Excel to suit your personal work style and your specific needs so that you can get your work done faster and more easily.
(It’s worth noting that most of this discussion will apply to users of Excel versions up to 2003, though some concepts may be of use to Excel 2007 users too.)
I have a question for you. What’s easier: clicking on an icon on a toolbar (a collection of icon buttons), or navigating through layers of menus to access the feature you need?
Of course it’s easier to click on the icon. So why is it that so many of use continue to dig through the menus day after day? Obviously, Sandy, that’s how you access those features.
Not necessarily.
Did you know that you can add a lot of features to the toolbars as icons, so that you don’t have to go hunting through menus? It’s easy to do, and even the process to do so is pretty quick.
Instead of clicking the left mouse button as when you click the menus or icons, click the right mouse button on the icons instead. You’ll get a list of all of the available toolbar names. Odds are that only the Standard and Formatting toolbars are selected and displayed. You may see other toolbar display and hide as you’re working with other objects.
Way down at the bottom of the list you’ll see the Customize… option. Select it now – this is where the fun begins.
The Customize dialog box has three tabs: Toolbars, Commands, and Options.
The Toolbars tab lists all of the available toolbars. A quick scan of the names in the list probably reveals some names you’ve not seen. Those don’t show up in the normal list of toolbars! There are some nice toys here to help you set your work apart from the run-of-the-mill. Experiment with the Shadows Settings and 3-D Settings.
You can even create a new toolbar and name it (just about) whatever you want. Of course your new toolbar will be empty – it won’t have any buttons on it.
It’s the Commands tab where things get interesting. When this tab is active, the whole manner in which Excel is displayed to you and functions (in terms of interaction) is up to you. Lucky you: the power to make Excel far better for your particular needs is just a mouse click away.
How does this happen. It’s all drag and drop. Click on an item, be it an icon or a menu item, and drag it to its new home.
With Great Power Comes Great Responsibility
Be careful. It is a little too easy to make a mess of things. But the benefits are worth it.
As you drag items around, you may see the icon change a bit. For instance, if you drag the A to Z sort icon over the spreadsheet area, you’ll see an X appear. This is Excel’s way of telling you that if you release the mouse button now, the icon will be deleted from the toolbar and will not be moved to a new location.
If you hold down the Ctrl key as you drag, you’ll see a + sign appear next to the icon. When you see it, if you release the button you’ll make a copy of the original item in a new location. You’ll also see a vertical bar appear on the toolbars. This is how Excel is telling you where you’re about to place the item that you’re dragging around.
Of course, you can rearrange and copy existing items, but there’s more fun to be had.
There are Hidden Gems to Mine
You’ll notice that there are two scrolling lists on the Commands tab: Categories and Commands. All of various customizable Excel commands are listed logically within the categories.
As you select each of the category names, the list of available commands changes. The key thing to know here is that there are some really useful commands available in these lists that aren’t available in the default menus or toolbars! For now, I’ll leave to you to discover them.
The Options tab lets you control how some of Excel objects are displayed.
So how do we take advantage of all of this so that we can work with Excel more efficiently?
Think about the type of work you do with Excel and what sort of features you want at your fingertips. Then create a few toolbars that will each handle some related tasks. For instance, you might often use the Sort, AutoFilter and Text to Columns items found on the data menu, but rarely do you use anything else there. Create you own Data Tools toolbar, and put copies of those items on it. Do the same with your favorite formatting tools and publishing items, like page setup and page break preview mode.
Now right click on the toolbars and select to display only the toolbars you want to use.
Additionally, you may find that you’re some icons repeatedly in one session, constantly navigating from the cells that you’re working on up to the toolbars and back. Why not move the toolbars to where you need them?
Hover over the far left edge of the toolbar until your cursor changes into a four arrow icon. Then click and drag the toolbar around, even down into the sheet area. You can always move it back later by dragging the title bar of the toolbar.
We haven’t even talked about how you can make icons that run macros that you create when you click the icon. But we’ve covered a lot of ground, and have left you with some homework: explore the hidden tools available to you when you customize Excel.
Taking control and making Excel’s default interface your own is a great way for you to excel with Excel.
