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.
Same Calculation for the Goal Reached vs the Goal Remaining for the Company Goal Dashboard Chart:
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:
Your resulting chart will look like this:
Or Insert a 3-D Horizontal Cylinder graph to create an IPAD Battery Chart:
Your resulting Excel Chart will look like this:
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:
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:
Here are the visual display for the rotated Battery
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:
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
I like the battery as a progress chart. When I showed this to someone, he immediately asked, “Can you make the end explode if I exceed the goal?
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
Actually I usually see it in thermometer graphs. The top pops off and the fluid bursts upwards. (BTW: I suggested he set just his goal higher)
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.
You can get a semi explosion look with a pattern fill:
Was this helpful?
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.
You can do this by setting up more sections of the battery and use NA() function. Check out this post to see what I mean: https://www.exceldashboardtemplates.com/how-to-make-a-conditional-column-chart-in-excel-redo/
I like it I am looking for something like this but in my case the colors need to be from down to up instead of from one side to other how I can do it thanks
So are you trying to fill up the battery in this respect?
Battery is laid on its side, so fill comes from one side to another. See diagram below were 2/5 of the battery are filled from the bottom (right side of batter on its side)
Is this what you are looking for?
Nice chart! How would you add the nobbly bit at the positive end of the battery?
Hi Andrew, yeah that is a bit tougher. I will have to give it some more thought. You may want to just draw it in 🙂 for now