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...

In the old days, we used to tape down receipts from our travels and submit them to accounts payable. But that was before remote employees who may live in a different city from the home office. And of course, there's all...
In 2011, electrical services and technology provider Parsons Electric in Minneapolis, Minn., decided to take its accounting to the cloud. Monica Ross, the company's director of strategic projects, talked with AWEB about...
Event Date: July 24, 2014, 2 pm ET In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the...

Upcoming CPE Webinars

Jul 16
Hand off work to others with finesse and success. Kristen Rampe, CPA will share how to ensure delegated work is properly handled from start to finish in this content-rich one hour webinar.
Jul 17
This webcast will cover the preparation of the statement of cash flows and focus on accounting and disclosure policies for other important issues described below.
Jul 23
We can’t deny a great divide exists between the expectations and workplace needs of Baby Boomers and Millennials. To create thriving organizational performance, we need to shift the way in which we groom future leaders.
Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.