How-to Make a Company Goal Chart or IPAD Battery Chart in Excel

I was looking at my IPod Touch and I really liked the battery graphic that is shown.  Then I looked at my IPhone and saw the same battery and I thought to myself…”I wonder if I could recreate this graphic in an Excel chart or graph?…”  And thus this post was born.

image

More importantly, I wondered if others would like to learn this technique in Excel.  I also wondered if there were this IPad battery chart could be used in an Executive Company Dashboard.

If you like this technique and you use it in one of your dashboard projects, please send me a copy so I can post how you used.

I think it would be a great graphic that can be used when you have a goal to reach much like a thermometer chart or if you want to know a quick snapshot of your progress.  Especially if we create it in a vertical fashion (sort of like an IPhone Battery Chart standing up) like this:

image

Most BI, Chart and Dashboard Designers do not like 3-D Excel Charts at all, but I think this may be one of the best uses for these types of graphs.  Lets get into the details of how I built these charts so you can learn this excel tip and trick.

The Breakdown

1a) For IPAD Battery Chart – Create your data with a base value, a cap value and a Battery Life Remaining value and a Spent Value that totals to 100.

1b) For a Company Goal Dashboard Chart – Create your data with a base value, a cap value and a Goal Reached and Goal Remaining value that totals to 100.

2a) For IPAD Battery Chart – Insert a 3D Excel Stacked Horizontal Cylinder Chart.

2b) For an Business Goal Reached Chart – Insert a 3D Excel Stacked Cylinder Chart.

3) Tilt or change the 3-D Chart Rotation for your desired dashboard display.

4) Format the Background and battery or company graphs.

Step-by-Step Instructions

1) First we need to Setup our data:

image

First I like to make the main part of the IPOD battery or Company Goal Chart equal to 100 so you may need to translate your data into a percentage of your total available or total goal.  So I entered a value of 65 out of 100 as my Remaining Battery worksheet cell or Goal reached.  You might also think of this as the percentage of the goal for your company dashboard.

image

Since the total will need to equal 100 for our IPHONE Battery Chart or our executive goal dashboard graph, in the Spent Cell of the excel worksheet I put in a calculated field.  It is equal to 100 minus the the value we put in the Remaining field.

image

Same Calculation for the Goal Reached vs the Goal Remaining for the Company Goal Dashboard Chart:image

Finally, we have a value for the left or bottom caps of the IPAD Battery and then a value for the Right battery cap.  I chose 5% of the total of the entire battery.  The caps are the same for the goal reached graph.

image

2) Now lets create the executive goal or Company Goal Chart or IPHONE Battery chart.  Highlight the range of cells as shown below:

Goal Chart:image

IPOD Battery:image

Then for a Company Goal chart – Insert a 3D Stacked Column Chart to create your company goal chart for your dashboard project:

Your resulting chart will look like this:imageimage

Or Insert a 3-D Horizontal Cylinder graph to create an IPAD Battery Chart:SNAGHTML21764365

Your resulting Excel Chart will look like this:image

3) Now we need to Switch Row/Column on each chart from the Design Ribbon:

For the IPHONE Battery Chart it will look like this:SNAGHTML222f4fb8

And the Company Goals Dashboard Chart will look like this:

SNAGHTML2230a518

Now it is starting to look like what we want.

4) Not lets set the 3-D Rotation to Zero Vertically and Horizontally.  You can do this by right clicking on the chart and choosing 3-D Rotation:

Here are the visual display for the rotated BatterySNAGHTML2234e3fb

SNAGHTML22394b65

I set the 3-D Rotation to X = 10 degrees and the Y= 0 Degrees for the IPOD Battery.

For the Executive Dashboard Progress/Goal Chart, I like to change the 3-D Excel Chart Rotation to X = 0 and Y = 2.  It should look like this:SNAGHTML223d3fff

5) Now lets format the chart data series.  First right click on the right cap data series and then choose Format Data Series…   If you accidentally click on Format Data Point because you select the data point a few times, do not worry as this will be okay since we really only have 4 data points or series.image

And then Choose the Fill Menu and Choose the Solid Fill Radio Button and choose a Grey Color and also change the Border Color to a Solid Line and change it to the same Grey color you just chose.  Also, DO NOT click on the CLOSE Button!!image

SNAGHTML22501a86

Repeat both of these steps (Fill Color and Border Color) for the Spent data series and the Left Cap and choose a grey color that is lighter than the grey color you chose for the end caps.

For the final series, repeat the same steps for the “Remaining” series and change it to a Green Color for the Fill and the same Grey for the Border Color.

Finally, click back to the Spent Data Series and then click on the Fill Menu and lets change the Transparency to something like 65%

SNAGHTML2255c1dc

Please follow the same steps as you just completed for the IPAD Battery chart for the Excel Company Progress/Goal Chart.

6) Now we should delete all of the Chart Junk, like the Legend, Gridlines, Axis Titles, Chart Title, and Axis values.

All of these can be selected with a mouse click and then press your delete key.

Your charts will now look like this:

image

image

7) Okay we are almost finished.  This step is not necessary if you do not like it, but if you want to make an IPHONE Battery Chart, you would need to add a Black Background.

First Right Click on the white area in chart and then select either Format Plot Area and then Repeat for the remaining white area in the by right clicking in that white space and choose Format Chart Area. SNAGHTML2336661b

Then choose the Fill Submenu and Choose Solid Fill and Then Choose Black.

image

And there you go. You have now made an IPAD Excel Battery Chart!

Also you now have a really cool Excel Company Progress Chart so that your Executives and quickly tell how far you are to achieving the Goal.

 

Free Sample Dashboard Template File with Instructions:

Excel-IPOD-Battery-Meter-or-Company-Goal-Reached-Chart-Sample-File.xlsx

 

Video Demonstration:

Here is a quick video demonstration of making 3D Excel Company Goal Chart and IPOD Battery Chart:

 

Please let me know what you think of this chart in the Comments below.

Thanks – Steve=True