In Cell Graphic Equaliser Chart

A while back now, I posted two blogs: How to make a graphic equaliser chart in Excel & How to create a dot table using the REPT function.  It occurred to me that in fact the two techniques could be combined to create a graphic equaliser table…

graphic equaliser table

You can download the sample worksheet here.

Step 1: Create your data table

Obviously for this type of chart you can only show one set of data.  You can see my data table below:

data table

You’ll notice that the fill is black and the text white, which is not my usual look for excel tables.  This is deliberate.  Graphic equalisers look better against a dark background that a white one.  If you decide to leave the worksheet white, then turn off the gridlines by deselecting the checkbox View > Gridlines

gridlines

Step 2: Add the calculations

My data table is in rows 1 – 14.  So I’ve left a blank row and then rows 16 – 20 will be my table.  The data table is in columns A and B, so I’ve left a blank column and then my table will be in columns D to P.  I’ve altered the width of these columns to 21 pixels wide.  To do this highlight the columns and hover the mouse over the trailing edge.  Then hold down the mouse and drag the trailing edge until the pop up window shows 21 pixels.

change column width

Set up X axis labels

In cell D20, type in the calculation:
=A2

In cell E20, type in the calculation:
= A3

Repeat until all of the X axis is showing.

Set up green bars

In cell D18 (I left a row to separate the labels from the bars), put the following calculation:
=IF($B2>6,REPT("¢",6),REPT("¢",$B2))

This calculation looks at the data in the first row of the table. If the value is 6 or less, it will duplicate the ¢ symbol the same number of times as the value. If the number is above 6 it will reproduce the ¢ symbol six times. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table.

first calculation

Then highlight the whole and change the font to “wingdings 2” and the font colour to green.  Adjust the row height so that you can see all six squares comfortably.  Your table should now look like this:

green bars

Set up yellow bars

In cell D17 (the cell above the first set of green squares), put the following calculation:
=IF(B$2<7,"",IF(B$2-6>4,REPT("¢",4),REPT("¢",B$2-6)))

Again, this calculation looks at the data in the first row of the table. This time it subtracts six from that value and looks to see if the new value is 4 or less.  If so, it will duplicate the ¢ symbol the same number of times as the value. If the number is above 4 it will reproduce the ¢ symbol four times. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table. Then highlight the whole and change the font to “wingdings 2” and the font colour to yellow.  Adjust the row height so that you can see all four squares comfortably.

Set up red bars

In cell D16 (the cell above the first set of yellow squares), put the following calculation:
=IF(B$2<11,"",IF(B$2-11>1,REPT("¢",2),REPT("¢",B$2-10)))

Again, this calculation looks at the data in the first row of the table. This time it subtracts ten (six+four) from that value and then duplicates the ¢ symbol the same number of times as the remaining value. Copy this calculation across the table, adjusting the cell reference to be the correct cell in the data table. Then highlight the whole and change the font to “wingdings 2” and the font colour to red.  Adjust the row height so that you can see all two squares comfortably.

Add a legend

In the column next to the table put a legend showing the scale:

graphic equaliser tableYou can then adjust the row height etc as required to finish the table.

What do you think?  Can you use this type of in-cell chart?

+Alesandra Blakeston

 

Advertisements

One thought on “In Cell Graphic Equaliser Chart

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s