Excel 2007 Upgrade FAQ: Charts And Graphics
Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.
Q: Double-clicking on a chart element doesn't display the Format dialog box.
A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.
Q. I find that it's very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?
A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it's always visible.
Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?
A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.
Q: What's a dialog box launcher?
A: It's the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.
Q: I'm working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title 'Format Shape,' and it has the focus. So the arrow keys move within the dialog box, not the worksheet.
A: Annoying, isn't it? When you're finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.
Q: In a chart, how do I control plotting empty cells and plotting hidden cells?
A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells.
Q. What happened to the chart fill patterns?
A. The fill patterns are no longer available in the user interface. However, they can be applied using a VBA macro.
Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don't work in Excel 2007.
A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.
Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.
A. Right-click your QAT and choose Customize. Choose 'All Commands' and then select 'Change Shape'. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.
Q. A cell comment is a shape. Why can't I use the ribbon commands to format it?
A. Comment formatting is done via the Format Comment dialog box. Right-click the comment's border and choose Format Comment. Oddly, the color options available are not from the document theme.
Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?
A: You don't. You can, however specify the Top and Left properties by using VBA.
Q: When I right-click a Shape, I see an option to "Set as Default Shape." This command seems to have no effect.
A: When you choose that command, Excel uses the *formatting* that you've applied to the shape as the default (fill, outline, effects). The command should probably read "Set as Default Shape Formatting."
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks