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

Excel 2010 KPI tutorial 2: Simplify your charts

10th Oct 2011
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst continues his tutorial series on KPIs by explaining how to present information clearly and with maximum impact.

Part 1: Conditional formatting demonstrated the use of some of the new formatting features available in Excel 2007 and 2010 . In this second part we will investigate some of the more traditional charting options in Excel.

Creating a standard chart in Excel is generally straightforward regardless of the version. As long as your data is well structured, without blank rows and columns and preferably with row and column labels in single cells, then it should just be a case of selecting the data and running through the chart wizard in Excel 2003 or using the Chart section of the Insert ribbon tab in Excel 2007 and 2010 to choose the required chart type. In all recent versions the F11 shortcut will create a standard column chart based on the selected data range as a separate sheet. In Excel 2007 and 2010 the Alt+F1 shortcut creates the chart as an embedded object, rather than a separate chart sheet.

Once you have created your chart then you can choose how to format almost every aspect of it. Here we have a rather extreme example where we have formatted each of the data points to replace the solid fill with a relevant picture which is then stacked and scaled so that each picture represents 20,000 units. We have also changed the fill of the Chart Area to a picture of a field and set the Plot Area to ‘No fill’ to make it transparent. In order to be able to still see the text and numbers we have changed the font colours to white, increased the font sizes and also made them bold:

In Excel 2003 and before, having selected each data point, you could use the Fill Effects button and then the Picture tab to choose a picture from a file and set up the Stack and Scale units. In Excel 2007 and 2010 the Fill, Picture or Texture option additionally provides options to use pictures from the Clip Art library or anything copied to the Clipboard:

KPIs - Excel fill options

It’s also possible to set the transparency of the fill. Here we have increased the transparency of our background picture to produce an alternative presentation of our chart:

Excel KPI chart - transparent background

Although the flexibility is impressive and there are situations where employing these formatting techniques would certainly make a chart stand out, there are also good arguments for keeping charts as simple as possible. For comparison, here is an alternative presentation of the same data:

Simple Excel 2010 KPI chart

In part 3 we will consider what advantages there may be in keeping charts simple and investigate some of the new features in Excel 2007, and particularly 2010, that are based on simplicity and minimising ‘clutter’.

More Excel KPI and charting tutorials

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.

Replies (0)

Please login or register to join the discussion.

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