In the next few posts I'll be exploring some of the more interesting Excel 2003 toolbar buttons you may have forgotten about, or not known existed.
(See Useful toolbar buttons you don't see every day in Outlook 2003 for the Outlook version.)
In order to use any of these buttons, you'll find them on the Commands tab of the Customize dialog box:
In the Commands listbox (the one on the right), find the command you want to use and click-and-drag it to the toolbar of your choice. Right-click the newly added toolbar button and customize the name and icon. You can find some more screenshots at TechRepublic.
Freeze Panes
The Freeze Panes button is located on the Window and Help menu. It even has a cool icon you can use. I use this feature a lot for reports, so you can still view the header when scrolling through a long worksheet of data. Just make sure you select the correct cell before clicking the button.
The Data Menu Overfloweth
The Data Menu has several interesting commands that you might want to keep in one-mouse-click territory.
- PivotTable and PivotChart Report…
- Import Data…
- New Web Query…
- New Database Query…
- Import text File…
- Refresh Data
- Refresh All
These would be most useful if you find yourself doing a lot of importing or web querying. Even the PivotTable button lets you import and summarize data from external sources such as an Access database.
Mike Alexander has a great example of what you can do with another hidden toolbar button from the Data menu.
Set / Clear Print Area
These are my personal favorites; they're the first buttons I add when installing a new copy of Excel. When you want to print a specific area of a worksheet, just select it and then click the Set Print Area button. Click the Print Preview button to see what you're about to print.
The Clear Print Area button doesn't have a face ID, so you'll need some code to change it.
ps- the review of JMT Excel Utilities is forthcoming, but it probably won't happen until September.
About JP
9 Comments:
Another useful button that people may not be aware of is the Camera Tool, which copies a dynamic picture of a worksheet range (including shapes and charts on that range), that you can paste elsewhere to give you a view of how that range is changing.
Also, you need no code to give Clear Print Area an icon. Add it to a toolbar, right click on it (you're still in Customize), choose Default Style for icon only or Image and Text. Then right click > Change Button Image to select from about 40 canned icons, or Edit Button Image to roll your own. Or to reuse an existing icon, right click on the other icon > Copy Button Image, then right click on this one > Paste Button Image.
Some more less known useful buttons
1) In Data : Filter by Selected Cells Value
2) In Edit : Select Visible Cells, Select Current Area
3) In File : Toggle Read Only
4) In View : Zoom In/Zoom Out
5) In Drawing : Select Multiple Objects
Oops forgot the "Generate GetPivot Data" in Data to toggle the GetPivotData Function
Sam -
You mean, to turn off GetPivotData.
Jon
Yes..Till I "discovered" it I searched every where under Table Options…
Jon, The camera can be incredibly slow though. I created a spreadsheet over the weekend which has a dropdown to select a category, and then it dynamically builds a list of items for that category and some Cube formulae based upon the date header and the item code.
I added a couple of simple column/line combo charts, and then snapped them to put on my dashboard. Without the snapshots, the category refresh was instantaneous, with them it was a noticeable number of seconds. With the number of items in the region of 0-20, the culprit was clearly the camera snapshot.
Bob -
The Camera should be used only when it's the only way to get what you want. If you can you should use links within another worksheet range to show a table, or a second copy of a chart.
Hi Jon,
I agree, and that is what I have reverted back to. In this case I wanted a simple jump from a illustrative chart to a more informative detail page, so I thought the camera would be good as I could assign a macro to the shape. I was wrong, it was bad! I do note that Charley Kyd uses them extensivley in his Dashboard Kit!
Charley uses the Camera tool extensively, and files error reports extensively. That's one feature that has plagued Excel 2007.