This series of blogs will show you how to use the many Chart options in Excel and apply the vast array of graphical choices and features that are available. We will begin with a simple Sparkline, that provides a unique visual audit of data, through to multi-layered double axis Charts.
Excel calls this tool set ‘Charts’; so what is the difference been a Chart and Graph? – The best definition I have found is:
As this blog is illustrating business applications we are therefore producing Charts.
In 1982 A.S.C. Ehrenberg wrote a book titled ‘A Primer in Data Reduction’ to help us make sense of the increasing amount of data that computers were capturing and storing. If it was apparent back then that distilling data down to useful information was difficult, I wonder what he would make of the data warehouses we have today?
In the book he comments on Charts as “a means of making results clear and memorable though not good for communicating quantitative details or complex story-lines. A chart should be easy on the eye and the message it portrays should be capable of being summarised in a sentence”. The series of blogs will be written to heed this quote. Therefore, we will avoid multiple lines and colours which can confuse rather than clarify.
Charts can convey messages in management reports much more effectively than tables of data, particularly to people who have Numerophobia or a visual learning style. The first FD I worked for told me to make sure my reports ‘spread light not generated heat’. Perhaps Charts provide an effective way of fulfilling that aim.
Over the next few months the series of blogs will start at the beginning and build up to the more advanced areas. Some illustrations of the charts we will produce are as follows:
All the Charts are accessible off the middle of the Insert Ribbon as shown below:
The series will develop as follows:
How to use Sparklines showing the various options and features
Creating line charts, managing data series, axis position (on/between marks), axis labels and titles (linked to a cell).
Enhancing line charts with axis bounds, axis units. Formatting axis text, dates and data
Column/bar charts, clustered, stacked and 100%.
Enhancing block/bar charts – creating tornado charts with bar charts
Other chart types and their uses – Area, scatter, bubble, surface, stock, doughnut and pie
Best practice in layout titles, labels, legends, axis crossing, gridlines and tick marks, dynamic and static with =OFFSET
Colours, shadows and graphics (adding clarity not unnecessary use of options). Adding backgrounds. 3d charts
Data series, dual axis, multi-chart overlays and waterfall charts
Adding trend lines through data series and using the functions =FORECAST, =TREND, =SLOPE and =INTERCEPT
Controlling zeros and missing data, hanging lines using =NA, using conditions to highlight attributes (peaks, troughs), saving a chart as a picture to use in other applications
Screen shots will be from Excel within Microsoft 365 = version 2019
Excel version 2016 - Can do all the charts that will be explained in this series.
Excel versions 2010 and 2013 – The new Waterfall Chart in 2016 cannot be done in these versions but a timeless workaround will be explained in blog 10 to enable these to be created
Excel 2007 and previous – Compatibility with these versions will not be considered. The Excel 2010 version saw the introduction of Sparklines which are not backwards compatible.
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. He can be contacted on email@example.com
Looks very interesting. Waiting for its roll out.