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.
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:
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.
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.
1) First we need to Setup our data:
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.
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.
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.
2) Now lets create the executive goal or Company Goal Chart or IPHONE Battery chart. Highlight the range of cells as shown below:
Then for a Company Goal chart – Insert a 3D Stacked Column Chart to create your company goal chart for your dashboard project:
3) Now we need to Switch Row/Column on each chart from the Design Ribbon:
And the Company Goals Dashboard Chart will look like this:
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:
I set the 3-D Rotation to X = 10 degrees and the Y= 0 Degrees for the IPOD Battery.
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.
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!!
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%
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:
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.
Then choose the Fill Submenu and Choose Solid Fill and Then Choose Black.
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:
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