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

Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.