Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Charting tips and tricks from Excel MVP Andy Pope

by
27th Apr 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

Following his successful series of articles on presenting financial figures with Excel, Simon Hurst picked up a few new ideas at the recent Excel User Conference.

Shortly after completing the three-part ExcelZone series on presenting financial data using Excel, I thought I'd treat myself by immersing myself for two days in the 2009 Excel User Conference. Following on the heels of my recent project, I couldn't resist sitting in on Microsoft MVP Andy Pope's session about “Tips and tricks in charting”. The talk covered dozens of different charting techniques from straightforward, but highly useful guidance on setting up the data to be charted, to some brilliant examples of lateral thinking and creativity to create effects that most people would think impossible to achieve in Excel.

Warm up with simple tips

Let's just start with a few of the simpler tips. When manipulating charts you'll need to select many of the different chart elements. Of course you can do this using the mouse, but once you've selected any element, the up and down keyboard arrows will cycle through the major chart elements while left and right will cycle through the minor elements. Ctrl+1 will display the formatting dialog for the currently selected chart element. Empty cells can cause a problem when plotting chart points. With a chart selected you can use Tools-Options-Chart Options (Excel 2007: Chart Tools Design tab, Data group, Select Data, Hidden and Empty Cells button) to tell your chart how to deal with empty cells - either by plotting them as zero, leaving the points unplotted or interpolating between the values that are there.

Mixing chart types

Next, Andy started to demonstrate some of the more creative applications of Excel charting techniques. The first of these involved creating charts that mix different chart types. Although the Custom Types tab of the chart wizard includes mixed types such as Column-Area and Line/Column, changing the types of different series manually provides more control. To do this, just set the chart up as normal using the main chart type you want to use, then select the series to change and choose a different chart type. Because a single series is selected, the change in chart type will only apply to the selected series, leaving the other series as they were.

Andy's second idea was to include one or more "dummy series" in your chart data, but rather than plotting the data to use it instead to create a particular chart format.

My favourite use of these techniques was the creation of a 'banded' chart where the line representing the data could move into red, amber and green bands. Starting with the series to be charted, three additional series are added in the columns to the right of the values to represent bad (red), average (amber) and good (green). Each column contains the same value for each row: that value being the range of the required band. Remember that, as a stacked column, these ranges will be cumulative: so if you set 'bad' to 40,000 and 'average' to 30,000, then the maximum value for average will be 40,000 plus 30,000. You can then create a stacked bar chart based on the entire data area. Your real data will be shown as one element of each of the stacked columns, with the bad, average and good values being the other three elements of each stacked column.

To achieve this effect, select the "real" data series and set the chart type to Line. Now go to one of the column series and double-click to open the Format Data Series dialog (Excel 2007: right-click on one of the column series and choose 'Format Data Series'). Select the Option tab (Excel 2007: Series Options) and change the Gap Width to zero. You can then select each of the 'band' series in turn and set the colours to red, amber and green and turn off the borders. This will create the effect of your data line against a banded background appearance, You might also want to change the maximum value of the vertical axis scale to match the total of your bad plus average plus good values:

There are lots more ideas for working with Excel charts on Andy's website, www.andypope.info/charts.htm

Further reading

Presenting financial figures with Excel: Part 1 - an introduction
Presenting financial figures with Excel: Part 2 - Charting choices
Presenting financial figures with Excel: Part 3 - Delivery options
ExcelZone Compendium: Graphs and charts digest

Subscribe to the ExcelZone newswire
To keep up with spreadsheet issues and regular tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB.co.uk home page after it adds your name to the subscription list.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.