This seems to have been a week of Excel challenges for me. First there was the Metro UI style Excel dashboard challenge from a close friend of mine. Then yesterday I was asked if I could make a chart that looked like a graphic equaliser to represent some sound readings in a very visual way. Challenge accepted!
We all know what a graphic equaliser looks like:
My first thought was this should be relatively simple. All I need is to draw a bar like shape (small thin rectangle) on to the page. Then create a normal bar chart. Then copy the bar, press Ctrl+1 and use the contents of the clipboard as the fill, stacked and scaled with 1 bar per unit of data. Much like what I did to create the arrow chart earlier in the year.
What I actually got was not so pretty:
Without an outline, each bar merged into the next, making the graph look exactly like a normal bar chart. So I put my thinking cap back on and realised that I needed two bars, a white one behind the coloured one to space out the lines. This time was better, but still quite boring:
Then I started formatting. I removed the Y axis and increased the size of the X axis font. That’s when I realised that it was completely impossible to read the values without the Y axis. So I decided to add coloured graduations, by creating a group of 5 coloured bars (four grey bars and 1 blue bar to mark the 5th graduation), again with a white background. Of course then I had to stack and scale the picture fill by 5 units for the five coloured bars:
Now we were starting to cook with gas. I went back to the internet at this stage and started looking for some great pictures of a graphic equaliser. I quickly realised that I needed a black background, and perhaps a rainbow gradient with my coloured bars, going from green to red. This meant having 70 coloured bars with a black background, and again changing the fill to stack and scale to 70 units – a lot of formatting, copying and pasting! Also, since most of the pictures I saw had a reflection of some sort, I needed to use the camera tool (see this post for more details) to get a realistic image, which will of course update automatically:
Just in case, I also created a bar chart version as well, with different values to see how it would look. What do you think? The full excel file can be found here. Enjoy!