Awesome quick formatting of chart elements in Excel 2010

I just discovered a minor enhancement to chart controls in Excel 2010 that makes a really big difference in terms of how long it takes to quickly add the formatting I need.

When you right click something in your chart you see the usual right click menu as with Excel 2007 but now you also get the floating mini-bar above it as shown below (click for larger version)

Excel 2010 chart formatting mini-bar

On the right hand end of this you can see the selected element (Series 3 in this case) which you can then directly format, but even more usefully, you can use the drop down here to select another element, format that, then another and so on, and the mini-bar stays on top throughout.

Excel2010 chart formatting2

Now of course you can achieve the same thing by selecting elements on the Chart Tools|Layout or Chart Tools|Format ribbons, but for some items like labels and axes you want to change font properties and that means going from one Ribbon to another. If you want to rapidly change the colours or borders of various things, make the gridlines and plot area less obvious, alter the fonts of data labels or axes and other quick changes, you can do all of this without ever leaving this mini-bar area

In the screenshot below you can see I have made all sorts of changes, including making the borders of the hidden series visible, so you can see that this waterfall chart is basically a stacked bar chart with no gaps between series, and did this without going up to the Ribbon once. (I am not suggesting the formatting of the final version is better, in fact the opposite is probably true, I simply wanted to make several changes to demonstrate the new feature).

Excel2010 chart formatting4

While this is not quite as instantly available to use as the tear-off colour palettes used to be in Excel 2003 and earlier, it does go a long way to replacing that functionality, and has the slight benefit of remembering the last choice made for most items so you can easily apply the same colour to axes and gridlines, for example, or the same fill colour to two related series (font options show the current value for the selected element, rather than the previous choice, and this makes good sense to me intuitively).

One minor niggle with this mini-bar is that you can’t move it, so it can obscure the things you are changing, so you should right click in the chart area near the top edge to avoid it getting it the way.

Helpfully, formats which don’t make sense get greyed out, so you can’t use fill colour for lines, which reminds you that the formatting option for “outline” applies to any element which consists only of lines, such as the axis, gridlines and so on.

Have you used this new feature already? Have you discovered other small but valuable features of Office 2010 (or earlier versions)? What are your favourite time-saving tips?

About ukcrmguru
I'm an MVP for Dynamics CRM, consultant, Microsoft Certified Trainer and self-confessed geek. I also lead the UK CRM User group when I'm not too busy with all that.

2 Responses to Awesome quick formatting of chart elements in Excel 2010

  1. Alex Kerin says:

    The return to double-click to edit is certainly welcome – this method is also great, especially when elements are hidden under something else or not visible.

  2. Adam Vero says:

    It is certainly a step back towards the convenience of 2003 in this area.
    I’m always amazed by users who don’t realise they can choose elements from a drop down list, whether on the old chart toolbar or the new ribbon, and painstakingly select things with the mouse (or fail to do so, especially if they are using ‘clever’ techniques which rely on invisible series). I’ve seen fairly advanced spreadsheet veterans waste loads of time on this.

    Same as scrolling through loads of worksheets instead of right clicking on the lower left arrows to jump to where they want to be – not obvious until someone shows you but then a huge timesaver and really easy to remember and use. Easily overlooked even after years of daily use.

%d bloggers like this: