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.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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.


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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


SPECIAL - SAVE 10% until July 20th. Use code EDT.


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









10 COMMENTS

    • Thanks. Glad you like it. Depends on what explode means. Can you provide a little more detail on what that would look like? Then I will see if I can come up with a solution. Thanks again for the kind comments. Steve=True

        • I see what you mean. Well, we have a problem because we are using a 3 D chart for this battery.

          I tried to replicate what you wanted and take a look at this picture. I copied and pasted the explosion clip art and you will see that because it is a 3-D chart, it tries to wrap the 2-D image around the chart section.
          Battery Explosion

          You can get a semi explosion look with a pattern fill:
          Fill Pattern in Excel Chart

          Was this helpful?

          Steve=True

  1. I am new to Excel and would love to use this on a dashboard I am putting together. But, I need the color of the battery to change based on actual vs planned of our goal. Say I have 100 of something to do and today we have done 80, the battery would be 80% filled. But, if we had planned to be 90% by now, I would like the battery to be another color. Sort of the red, yellow & green vs planned.

    Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here