Fun with Bar Charts – Part 2 – Drawing Pictures

Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract.  As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe??

What came immediately to mind is the fundraiser tracker.  You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money:

0823bars2_0

 

I wanted to create a visualization like that, in Excel, next to my family’s finances.  Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable.  How do I do it?

 

 

Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts.  Wanna draw a circle with an absolutely rectangular bar chart?  Let’s do it visually:

Step 1 – Create a stacked bar chart.  The number of stacks that you need depends… and trust me for now that you need 4 for a circle:

0823bars2_1

Step 2 – Make sure you have enough bars.  100 is always a good starting point in my opinion.

0823bars2_2

Step 3 – Set the gap between bars to 0%.  Now this chart looks like some European country’s flag.  Still quite rectangular:

0823bars2_3

Step 4 – Adjust the height of these 100 bars so they look like this.  Note how some stacks are much taller than others:

0823bars2_4

Step 5 – Resize your chart into a square.  Clean up the colors and update the axes ranges, and…

0823bars2_5

Ta-da!  Here’s the circle!  Don’t like how pixelated it is?  Add more bars!

0823bars2_6

 

The key here is to think of a picture as a rectangle with w-by-h pixels.  Then you can replicate it with w stacked bars each having a maximum height of h.  How many stacks you need depends on how complicated your graph is.  Is it possible in theory to draw a Mickey Mouse?  Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors.  You’d need to either set up formulas or a lookup table for the height of these stacks on each bar.  For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0.

 

Back to my original goal of drawing a target tracking graphic.  I decided to go with a house complete with a chimney, even though our home looks nothing like it.  It involves a handful of vertical, horizontal, and sloped lines:

0823bars2_7

 

The shape of my house, including where the chimney is, is entirely parameter-ized.  So by updating a few cells I can get to alternate versions like these:

0823bars2_8

 

Lastly, I needed to fill it in with the progress tracker, as follows.  Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value:

0823bars2_9

0823bars2_10

 

Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well:

0823bars2_11

 

This way, I’ve got a bar-based graphics that updates automatically.  The only thing left to do is actually paying off that mortgage!

Reply