Excel charts tutorial: How to manipulate the pop charts

The tech team at our sister site, Accounting.co.uk, put their heads together and came up with this response to the recent net phenomenon where abstract graphs and diagrams are used to illustrate pop lyrics such as "Never Gonna Give You Up." This tutorial applies some of the more obscure charting options to your data.

Some challenges are hard to ignore. When we read of the Internet song chart meme phenomenon, it touched on two of our great passions - music and Excel. The idea is to use a computer application to create a visual representation of a song title or its lyrics. The result is this tutorial to show how Excel could be used to generate the same sorts of charts that people had created.

Our UK Excel guru immediately starting pulling CDs and vinyl LPs off the shelves (he bought a new turntable as the rest of the world forsook CDs for MP3s - sometimes quality must take precedence over convenience) in the search for suitable song titles.

Just by way of a warm up, and completely ignoring the charting task at hand, here are a couple of Excel examples that don't use charts:

Conditional formatting

Functions

=INDEX({"Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown","Nervous breakdown"},19)

Charts

Let's start with a very simple one. Here is our block of data:

The heading has been wrapped using Format Cells-Alignment-Wrap text to keep it in a single cell (you could also do this by using the ALT+Return key combination at the end of 'whose' to insert a new line as you type).

We can now select the data we want to turn into a chart or, because we haven't included any blank rows or columns within our block of data, just click on a single cell within the block. Then choose Insert-Chart or click on the Chart Wizard button. We want to set up a very simple pie chart so we can just select the Pie option and press finish (Excel 2007: Insert ribbon, Charts section, Pie). This gives us a very basic pie chart. Whilst it already adequately portrays our song title, we can add a pictorial clue. To do this, we want to fill the area representing 'Mine' with a picture. The first task is to select just the 'Mine' data point, rather than the entire 'People whose fault it is' series. To do this, click in the pie chart once (this selects the whole series) then once again (this selects the data point that you click in). Now right-click in the same area and choose the Format Data Point option (if the option is Format Data Series, you need to left click again, then right click).

Choose the 'Fill effects' (Excel 2007: Fill) option. Now click on the Picture tab (Excel 2007: select Picture or texture fill). You can now click on Select picture and browse to find the picture to use. You can use clip art by navigating to a folder that includes clip art, but the lack of a thumbnail view makes finding the right item tedious – using the Preview display option can help:

Excel 2007 has separate buttons for File, Clipboard and Clip Art making it much easier to find a particular piece of clip art or copy a picture from another source.

So here's our final pie chart with pictorial clue:

You can do more when using pictures as fill effects. In this example we have used a stacked column chart and, as well as using pictures for the column fill, we have used the ability to 'stack and scale' the pictures, rather than just fill the data point with a single picture. We have chosen to scale to one picture to one unit. Obviously, if you were dealing with larger numbers, you would scale each picture to represent a larger number of units:

This option is in the format section of the Fill, Picture tab:

Excel 2007 – on the Fill screen, under the picture type option buttons:

Good luck with identifying the songs and with your Excel chart formatting, but it's worth bearing in mind that the best use of a chart is often to present data clearly and simply, rather than demonstrating the level of your own Excel expertise.

Further reading
ExcelZone Compendium - Graphs and charts

You may like these other stories...

Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

Upcoming CPE Webinars

Apr 22
Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.