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

How to manipulate the pop charts with Excel

by
22nd Apr 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

The recent net phenomenon where abstract graphs and diagrams are used to illustrate pop lyrics such as 'Never Gonna Give You Up' inspired this tutorial from Simon Hurst on applying some of the more obscure charting options to your data.

Some challenges are hard to ignore. When John Stokdyk phoned to tell me know about the Internet song chart meme phenomenon, he touched on two of my 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. John used this as the tenuous pretext for a tutorial to show how Excel could be used to generate the same sorts of charts that people had created.

The challenge was duly accepted and I immediately starting pulling CDs and vinyl LPs off my shelves (I 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 what I've been asked to do, 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


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

Subscribe to the ExcelZone newswire
To keep up with all spreadsheet-related developments, click the "My Subscriptions" link in the user panel at the top right of this screen and the ExcelZone newswire to receive FREE regular updates.

Replies (2)

Please login or register to join the discussion.

Simon Hurst
By Simon Hurst
24th Apr 2008 20:37

4th one
Hi Margaret, I hope it won't be too much of a disappointment when you work it out. If you send me an email to [email protected] I'll give you a clue.

Thanks (0)
avatar
By Margaret Upton
24th Apr 2008 13:17

Please put us out of our misery!
OK - got the first three, but the 4th one is driving my whole office round the twist!!!!

Thanks (0)