In the first part of the series, we concentrated on the new Excel 2007/2010 ribbon interface and on arranging the underlying data for our charts.
In the first part of the series, we concentrated on the new Excel 2007/2010 ribbon interface and on arranging the underlying data for our charts. In this second part we will consider some of the less obvious chart features. Once again, the majority of the areas covered will be relevant to users of earlier versions of Excel as well as the latest, ribbon-enabled, versions.
Detailed options are available for editing every element of an Excel chart. In Excel 2003, you could double-click on the element to be edited to open the appropriate format dialog screen. This double-click capability went missing in Excel 2007, where it was replaced by the need to right-click on the object and select the ‘Format...’ option from the menu. Fortunately, the double-click made a dramatic reappearance in Excel 2010. As an example, if the text for the horizontal axis doesn’t fit well, double-clicking on the axis text area opens the ‘Format Axis’ dialog, with options to change the alignment:
If you want to change the font size or other attributes, then font formatting for text in charts can be applied directly from the Home ribbon or from the right-click mini-toolbar (Excel 2007/10) – or the formatting toolbar in Excel 2003. In Excel 2003, these options cannot be accessed when the format dialog is open, but the Format Axis dialog includes a ‘Font’ tab with all the font formatting options. In Excel 2007/10 there is no Font option in the dialog, but the ribbon and mini-toolbar options are still accessible with the dialog open.
Some chart elements can be more difficult than others to select using a double-click or a right-click. As an alternative, you can go to the Chart Layout contextual tab, Current Selection group, and choose the element to edit from the dropdown:
Having selected the required element, the ‘Format Selection’ button will open the dialog. In Excel 2003, the equivalent dropdown is included on the chart toolbar with the ‘Format…’ button to its right.
To demonstrate just how comprehensive the options for different elements of our charts are, we will have a look at a few different types of chart and some associated options. For our first example we will use a doughnut chart. We can select one of the doughnut series and change the rotation and size of the doughnut hole. We can choose the outer series or, as in this case, a single data point on the outer series and ‘explode’ it:
Back to a column chart and this time we have selected each series in turn and changed the ‘Fill’ to show a picture representing the contents of the series:
Each of our Fill pictures has been chosen using the Clip Art option to search for an appropriate picture. We have the used the ‘Stack and Scale with’ option to decide how many units each picture will represent – in our case 5,000.
The ‘Fill’ option has changed substantially between Excel 2003 and 2007/10. In Excel 2003, the different types of fill could be accessed via the ‘Fill Effects’ button in the Patterns tab of the Format Data Series dialog:
As for 2007/10, the fill could be set to a Gradient, Texture, Pattern or Picture, but pictures could only be included by selecting a picture file. In Excel 2007/10, we can select a picture from a file, use clip art or copy any picture to the clipboard (taking copyright issues into account of course) for use in our chart. The Excel 2007/10 dialog also allows us to leave the dialog open and select each of our chart series in turn, whereas in Excel 2003 we have to close the dialog before selecting the next series. On the other hand, Excel 2003 offers a default value for 'Stack and Scale' and will remember a custom value for future picture fills in the same chart, rather than requiring the value to be entered manually each time.
As we have seen above, it is possible to change the chart type of an existing chart. In Excel 2007/10 the Design contextual tab, Type group includes a ‘Change Chart Type’ button that allows you to select from any of the Excel chart types and sub-types. In Excel 2003, you can select Chart Types from the Chart menu or use the Chart Type dropdown on the Chart toolbar:
In this example, we have selected the whole chart before changing the chart type. It is also possible to change the chart type of individual series by selecting the series before changing the chart type.
Here we have selected the 'Cucumber' series before changing the Chart Type to a line chart. Because the single series had been selected before choosing the new chart type, the line type is only applied to that one series:
You do not have complete freedom of choice when mixing chart types in a single chart. You can't mix 2D and 3D chart types, but you could mix a bar chart and a pie chart.
The detailed chart options can certainly be used to increase the impact of an Excel chart, but it’s worth bearing in mind the reason why you are using a chart. Generally, the idea is to communicate the meaning behind the figures more effectively than by just using the figures themselves. Although using some of these techniques can certainly make a chart stand out, this shouldn’t be at the expense of clarity. The chart needs to focus attention on what the figures mean, not on your own Excel expertise. Often, simplifying the chart to its essentials, rather than complicating it with unnecessary adornments, allows more data to be displayed in the space available, increasing the likelihood of making important correlations and comparisons.
Having looked at some of the tools and techniques available for manipulating charts, in the next part of the series we will investigate some other chart types and some additional techniques, including the creation of chart templates and some of the other new features included in Excel 2007 and 2010.
Many useful tips for Access, Word, Excel and other Office applications are covered in Simon's internationally renowned publication 100 ways to save time with Office. Specially written for accountants, it is packed with time-saving tips that will be genuinely useful i your day-to-day work. Simon has also included many useful Excel tips for accountants in his latest publication: Advanced Excel techniques for accountants and his website also includes a section for IT hints and tips for accountants. The Excel Community site includes many articles on Excel and other office software. It includes a forum for your questions and answers.