ICAEW.com works better with JavaScript enabled.
Exclusive

Exploring Charts (Graphs) in Excel

Exploring Charts (Graphs) in Excel - 9: Colours, shadows and graphics (adding clarity not unnecessary use of options). Adding backgrounds.

Author: John Tennent

Published: 12 Oct 2021

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.
With the plethora of chart options available it is all too easy to get ‘carried away’ and as mentioned in blog 5 there is a balance between medium and message. We also mentioned that in a pole of FDs that were asked to vote on their least favourite Excel feature, it was 3-D charts that topped the list. In the words of A.S.C. Ehrenberg “a Chart is a means of making results clear and memorable” and thus clarity is the primary objective

Colours

The colours used are entirely personal choice however, using a theme tends to look more professional than a set of random bright colours. Using shades of one colour can become difficult to interpret especially when there are more than a couple of series to display on the legend.

If the chart will be printed in black and white it will be important to ensure the different coloured series can still be interpreted and thus test it out to ensure clarity – the use of Pattern Fill for charts with areas can assist for black and white presentations. To access these: right click the data series, select Format Data Series, choose the tipping paint point and select the Fill options. Pattern Fill is the fifth option on the list.

Screenshot from an Excel spreadsheet
Screenshot from an Excel spreadsheet

At the bottom of the list is Customize Colors which allows you to build your own palette which can be great to set up a series that aligns to your corporate or client colours.

Be careful when you change the colour palette as all charts using that palette will change not just the one you are editing.

Shadows

Shadows can be an effective way to ‘lift’ a chart off the page. What is created is a rather curious illusion through a medium that for all other aspects of reporting is a 2D piece of paper. When shadows are overdone it can appear to add another data series for which there is no explanation.

The following two examples show a sales chart with and without shadow.

Screenshot from an Excel spreadsheet

To use shadows, right click any data series and select series options. The first set of options is Shadow. Below that are further options to cover Glow, Soft edges and 3-D format.

Screenshot from an Excel spreadsheet

Transparency is also a setting that can be set for displaying the main data series and thus for charts where there is overlap it can be a helpful way to see all the displayed data.

Graphics

Instead of using colours, shades and patterns for filling block charts you can use images.

Screenshot from an Excel spreadsheet

There are two modes - Stretch, whereby the image is stretched (distorted) to fill the area and Stacked whereby the aspect ratio is maintained (retaining the image integrity) and multiple images are stacked on top of each other to fill the bars.

Screenshot from an Excel spreadsheet

To use this feature, select the Fill area of Series Options and choose Picture or texture fill. The texture provides various images of materials. For a picture click on the Insert button below Picture source and load an image. The stretch and stack options are beneath the Insert button.

Adding backgrounds

Just as we can add colours, patterns and images to a data series you can also add them to the background. Right click the background area where no data is displayed and select Format Plot Area and the same options appear as for the data.

In the chart below a picture of a market stall is showed as the background. All colourful, but how clear is the final product in conveying the message you want to present?

Screenshot from an Excel spreadsheet

In the next blog we will cover Data series, dual axis, multi-chart overlays and waterfall charts.

 About the author

John Tennent
John Tennent Chartered Accountant and Managing Director, Corporate Edge Ltd

The series of blogs is written by John Tennent who is a Chartered Accountant and Managing Director of Corporate Edge Ltd. He is a member if the ICAEW Excel Community Advisory Committee. He is the Author of ‘The Economist Guide to Business Modelling’ and both builds models for clients as well as runs training courses to help people build their own models.