Create a Battery Chart in Excel

4 Flares Filament.io 4 Flares ×

Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.

In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.

There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.

The image below shows a battery chart being used to show the amount outstanding on a training budget.

Battery chart in Excel

In this tutorial we will look at how to create a battery chart just like the one above.

Read on or skip to the video.

Setting Up the Spreadsheet

The image below shows the data being used for this training budget battery chart. Here are some things about this setup.

  • Cell A14 contains a SUM function totalling the amount spent from A6:A12.
  • Cells B2 and E2 both contain 5%. This value will be used to create the top and bottom caps of the battery.
  • Cell D2 contains the formula A14/C6. This is the total spent/budget. This cell is formatted as a percentage.
  • Cell C2 contains the formula 1-D2. This finds the remaining percentage.

Data being used for the battery chart

Creating the Battery Chart

  1. Select the range of cells you want to use for the chart. In this example that is A1:E2.
  2. Click the Insert tab on the Ribbon. Then click Column and select the Stacked Cylinder.

Stacked cylinder chart

  1. A separate cylinder is used for each column of our data. Click the Switch Row/Column button on the Design tab to correct this.

Switch Row/Column for one cylinder

  1. Remove the 3D rotation by double clicking on the chart area (white space around the outside of the chart), select 3-D Rotation and set the X and Y values to 0.

Remove the 3D rotation from the chart

  1. Next we will remove all the unwanted chart elements. These include the legend, both axis, gridlines and the floor.

Some of these are awkward to select so use the Chart Elements list of the far left of the Layout tab on the Ribbon. Then press Delete on the keyboard.

Battery chart with removed elements

  1. Formatting the battery chart is next. Double click on each of the four parts of the battery in turn and select Fill, Solid Fill and then a colour of your choice from the options provided.

Changing the fill colour of a data series

I have opted for dark caps, with a white spent area and a green remaining area similar to what you would find on a smartphone battery.

Finished smartphone battery chart

  1. The final and optional step is to add a chart title. Click the Layout tab on the Ribbon, Chart Title and then Above Chart. Enter a title and press Enter.

Create a battery chart in your own Excel spreadsheets to track progress. They are easy to understand and look awesome.

Watch the Video

3 thoughts on “Create a Battery Chart in Excel

  1. Hi Alan
    Love your tutorials, many thanks for your tireless effort in educating the general public for free, appreciate it very much.
    I have a quick question on this battery chart, the chart works well as long as the actual cost is below the budgeted cost, however when the actual cost exceeds budget is there a way it can be coloured in Red and a remark printed to say “over budget”, if so how can I do this?
    Please advise
    Thanks & kind regards
    Mohideen

Leave a Reply to Mohideen Thassim Cancel reply

Your email address will not be published. Required fields are marked *