Fuel Gauge Redo

Earlier this week I had a reader comment on my Fuel Gauge dashboard graphic. Not familiar with what a fuel gauge chart is, click here see my earlier post.

Basically a Fuel Gauge graphic represents a percentage from 0 to 100. The concept is based on a pie chart. Here is an example of what one looks like.

My reader liked the concept but wanted to add a twist to it. He wanted the gauge to represent numbers from 70 to 100 and couldn't figure out how to convert it.

Well as a former coder I often say it’s only 1s and 0s. Anything is possible. So how do we convert the above fuel gauge to the one that works from 70 – 100?

Well first we need to understand the concept of the gauge.

Where my gauge would show 0, his will show 70, my 25 would be his 77.5, my 50 would be his 85, etc…

To convert the gauge I need to setup a matrix and it starts with a basic question. What is the difference between milestones on his gauge?

your #	77.50	My #	25
Your Starting #	70.00
	7.50	7.5/25 =	0.3000

I randomly choose one of his milestones (70, 77.5, 85, 92.5 or 100)

I then choose the next lower milestone and subtract the two.

In this case I chose 77.50 as the starting milestone and 70 and the previous milestone. The difference between these two numbers is 7.5. If I chose 85 and 77.5 I would again get 7.5 when I subtract them. So 7.5 is my first key for converting my gauge.

Next I take the first milestone of 77.50 and identify what that would be on my gauge (25). I then divide the 7.5 by 25 and get a conversion factor of 0.30 (7.5/25=0.30).

Now I can create my conversion matrix based on the 0.30 number. I create a column and run numbers from 0 to 100. I then start with the readers low score (70) adding 0.30 to each subsequent number. In the below example I show the first 11 numbers of my scale from 0 to 100. The readers scale increases 0.30 in each row.

Column P	Column Q
Your Scale	My Scale
70.00	0
70.30	1
70.60	2
70.90	3
71.20	4
71.50	5
71.80	6
72.10	7
72.40	8
72.70	9
73.00	10

Another way to get this number is to subtract the users high and low value and multiply that by 0.01.  (100-70)*0.01 = 0.30

Now that I have my matrix setup I just need to do a vlookup to convert the percentage entered by a user.

=VLOOKUP([Number Entered By User],P:Q,2,TRUE). This formula is shown in cell D3 of the converted Versions tab in the spreadsheet that you can download at the end of this post.

I need to use TRUE in my vlookup instead of false since with true, if an exact match is not found, the next largest value that is less than lookup_value is returned.

The vlookup result converts the user’s number into my gauge number from 0 to 100 and the needle points correctly.

Confused? Download the converted example and play with it.

Speedometer Chart / Gas Gauge Chart

A speedometer graph is comprised of multiple components to give the illusion of one chart. The background is an image or donut chart. The needle is created with a pie chart. The numbers and text are done with text boxes.

To start you want to lay out your preliminary data. The following 3 columns contain all the data I will need to create the chart. You will notice that I have formulas in 3 cells. I have displayed them for reference. You will also notice that in row 14 I sum the above data and each column (A, B and C) total is 360 (the same amount of degrees in a circle). The needle will represent any position on the chart based on the value entered in cell C2.

To create the chart, highlight cells A2 through A12 (don’t include the header row). Create a Donut Chart (shown below using Excel 2007)…

This creates the necessary components to create the first half of the Donut Chart.

You want to rotate the chart 90 degrees so the solid blue portion is positioned at the bottom. To do that, right mouse click on the donut ring and select Format Data. Under Series Options, select Angle of first slice and set it to 90. When you close the window you will see the chart has rotated.


Now we have the foundation for the chart. You won’t need the legend on the right so you can remove it by left mouse clicking on it then pressing the delete key.

Next I want to make the bottom ring of the circle transparent. I left double click on the bottom section to select the bottom portion of the circle and then right mouse click on it. I select Format Data Point to bring up the Format Data Point Dialog Box. I click the Fill option and select No Fill. If the boarder is visible, I also change the boarder color to no fill.

In the below example I am in the process of making the bottom circle transparent.

When Done I am left with the following.

Now I want to work on the needle of this chart. I first highlight cells C2 through C5 and create a Pie Chart.

Just as I did with the donut chart, I want to rotate the Pie Chart 90 degrees and remove the legend to the left.

I also want to make the Red, Blue and Purple sections of the Pie chart transparent without any boarders (just like the bottom of the donut). This will leave me with just the needle showing.

I make the whole pie chart background transparent. And the needle is shown on top of my donut chart.

You can also resize the needle by changing the pie chart size and then moving the Pie chart so it still is aligned correctly over my donut chart. In this example I have the needle slightly extending beyond the arch of the donut chart.

I also have changed the needle color to be black….

Well now you can test you chart by changing the value in cell C3. Set it to 0 and also 100. Check the position of the needle and readjust the pie chart as needed.

Now I want to add some numbers as indicators on the gauge. I simply insert a text box for each label I want to use. (In Excel 2007 from the Ribbon, Insert Tab >> Text Box). Make sure the background and outline of the text box is transparent.

I position by changing the value in cell C3 for each text box I have. This helps me position the text correctly.

Almost done! Now I want to add a heading to the gauge. This is where cell C2 come in. I insert another text box but instead of typing in text, I place an = in the formula bar and then select cell C2.

Now I was creating this chart to show a percent of holdings for an account so I used the word Share. You could just as easily put the word Percent, Total or any other text you want.

The last thing I want to do to complete this is to group all the components of this chart together so that I can resize the graph as needed.

From the Ribbon (Excel 2007) I select my items and then on the Format Tab I choose Group.
Depending on what you group first you may need to arrange the items on the chart again by bringing items forward or moving items backwards.

When done you have a fully functional speedometer graph or Gas Gauge chart. Used judicially, this little graphic can turn your ordinary dashboard into a “WOW did you see that dashboard”.

Now what if we wanted to dress the dial up some more.

Perhaps I wanted to do a gradient fill from left to right. Well to do that I would change my data in the dial column to be only two equal rows.

I then continue with the above procedure but when I select fill for the top section of the donut, I choose a Gradient fill and select the colors I want and direction of the angle.



Using the same technique, I can use 3 data points for my dial

Perhaps you are looking for a more realistic graphic. Just past in a picture and overlay the pie chart. Tweak the data to ensure the needle does not pass the E or F and viola!

So how would you use this technique in your dashboards?  

Best Dashboard

In a previous post I demonstrated a simple but effective dashboard but did not go into how to create it. 

Sales Dashboard

I will do so in the following post.

As with any dashboard, it all starts with the data you have and what you are trying to convey. A good dashboard will be easy to understand, (clear and concise). It will convey the data in as few steps as needed without losing key points. It will also not obfuscate key points by surrounding them with superfluous information.

For my demonstration, I will create almost the entire dashboard from one key data series (Sales per Month).

Month	Sales
Jan	$  20,947
Feb	$  45,494
Mar	$  35,173
Apr	$  20,249
May	$  47,792
Jun	$  42,511
Jul	$  28,343
Aug	$  28,343
Sep	$  28,000
Oct	$  27,161
Nov	$  17,864
Dec	$  29,987

Now to start my dashboard I first need to set a color theme. I personally don’t like dashboards that use dozens of colors. In my opinion they tend to end up looking like something from a circus. I prefer a sleek look and try to minimize my color pallet. For this example I will use variations on shades of blue.

So now that I have my data, know what my key points are, and color tablet; I can now begin.

I am going to stop for a moment and vent a bit… Quite often managers don’t know how long it actually takes to create a useful dashboard. A good dashboard is so simple to understand that the development side is often overlooked. A general rule of thumb is that the design and planning of the dashboard will take about 70 percent of the time it takes to create it. The other 30 percent is the actual creation. A good dashboard is almost always used over and over so it needs to be easily updated (so be sure to plan ahead).

So we plan what we are going to design and then design what we plan.

Now having said that, don’t allow yourself to be pigeonholed by your initial plan. Some times as you actually create the dashboard, inspiration strikes.  But I digress….

To start I first highlight the entire worksheet and change the background to white. This removes the gridlines. I then enter in my data In cells C3..D15 and then format the data to be more visual appealing. I then tweak the data by merging cells B2, C2, D2 and E2. I enter the Sales in this merged area. I also add the Quarter for each Month in column B. In my dashboard I am using the Calendar quarters so Jan Feb and March are Q1, Apr May and June are Q2, etc…

I use the Boarders tool to outline my cells and fill ever other cell so they are shaded light blue. I bold and shade the Header cells grey and am pleased with the following result. If your data has cents, you may want to remove them to help streamline the look.

.

So far the data looks nice but it does not easily tell a story. I want to quickly know how the data is trending. I don’t want to have to visually compare each row so I want to add trending arrow indicators in column E.

What’s a trend indicator? Well it’s just like it sounds. Some text or graphic to indicated a direction of increase or decrease for a particular metric.

To add these trend indicators, I use a technique I explained in a prior post. Click on the link for instructions on how to accomplish this.

Trend Indicators

Don’t want to click on the link??? The formula you need in cell E5 is:

=D3 & " " & IF(D2<D3,"▲", IF(D2=D3,"*","▼"))

Copy this formula down through E15. Use conditional formatting to change the color. What’s that, you don’t know how to do that? Click on the above link (go on, it won’t hurt)…

Next I want to work the second portion of my dashboard, Sales by Quarter. If you have followed my instructions for the above, then this is just a repeat only with a consolidated result.

Q1 Q2 Q3 and Q4 in cells B 18 – B 21. In D18 I sum the first three months of my above data (cells D4, D5 & D6). For cell D19 (Q2) I sum (D7, D8 and D9), etc…

I then merge Cells B17 and C17 adding the title “Quarter”.  I also merge cells D17 and D18 and add the title “Sales”. I format the same as the above (blue background fill, outlining the cells and bolding / shading my headers grey).

I again use my trend line technique to compare the Quarter results.

Now I have displayed sales by Month but also summarized by Quarter including trend indicators for quick reference on how sales are trending by month and quarter.

Next up is the Summary section of the Dashboard. This is where I place my key points. I group them in one area for easy data access. I also center these key points toward the top. Now perhaps I could apply some heat map logic used for websites to optimize this focal point but I feel they best are represented in this dash board where I placed them(above and to the left of the graphics)

To create the summary I just repeat the formatting of the first two parts of the dashboard and use simple formulas to create the lookups. You will also notice that to create the Summary, I have a row for Sale Target for Year. This is hard coded but could be a lookup to a budget worksheet.

Again all of the formulas use basic Excel functions….

=AVERAGE(Sheet1!D4:D15)

=MAX(Sheet1!D4:D15)

=MIN(Sheet1!D4:D15)

=MAX(Sheet1!D18:D21)

=MIN(Sheet1!D18:D21)

=SUM(D4:D15)

For the lookups I could have used a combination of Indirect and Match as described in the following post…

Vlookup To The Left

However I opted to use helper cells. In the below image I have changed the font color from White to dark red for the helper cells to help you better understand the formulas. Now all of the helper cells are done by a function. For example cell F4 has the formula =C4. This way if I sort my data, the helper cells also sort.

Next on the dashboard, I create the Sales By Quarter bar chart and size it to fit.

Same with the Sales by Month bar chart (with trend line). No great mystery on creating a bar chart. To add the trend line (Excel 2007) from the ribbon select the Layout tab and under Analysis, select Trendline.

I format the Trendline as Polynomial with Order 4 to give it a smooth look.

Now all that’s left to create is the sweet little Sales by Month gauge / summary board in the upper right corner.

WARNING HERE COMES THE CONTROVERSY!!!!

People either love or hate the “Fuel Chart” or “Speedometer Chart”. I will concede that it can be misused and is usually not my first choice in graphs. However, used correctly it can be a powerful tool. 

Besides a billion automobiles can’t be all wrong, if they were, we would all have bar charts on our car displays….

Now I won’t go into detail on how to get the Current, Trend, Goal info. That’s all covered above and done with simple lookups. But the speedometer graph is done by overlaying a donut chart with a pie chart.

To create this graphic, see my next post on creating a speedometer graph.