ICAEW.com works better with JavaScript enabled.
Exclusive

Exploring Charts (Graphs) in Excel

Exploring Charts (Graphs) in Excel - Part 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 students, users, subscribers and members.
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.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250