Welcome to Excel Avon
3-D battery chart with the conditional formatting in Excel
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post we will show you how to create a 3-D battery chart with Conditional Formatting in Excel Although it is quite simple, 3-D Battery charts are a great way to visualize information. Like we have taught to make 3-D battery chart earlier also but this post will make 3-D battery chart with conditional formatting like, here condition will be put that if battery is below a target point then battery color will be red battery The battery turns green if it is above the target point. This chart will be very professional to look at, using this chart can be a plus point for your professional career.
You can see how our battery chart will be made like this.
3-D battery chart with the conditional formatting
Take one cell where we will write the value to be displayed in the battery and another cell where we will set the value of the target point. Here the value we will fill will appear in the current value part of the battery.
Now we will create a table here in which we will fill our values like top value, lower value, red part’s value, red transparent part’s value, green part’s value, green transparent part’s value. We will apply condition for red and green part that if the value of show cell is greater than or equal to the value of target cell, then the color of battery will be green, otherwise it will give error. Same condition will be for red part if value then show cell. If the value is less than the value of the target cell, then the color of the battery will be red, otherwise it will give an error.
For green transparent part if the value of l2 is error then it will also be error otherwise 100-l2 part will be red transparent. For Red part we will drag the formula.
We will select the data sheet where we have written the data value. we have created go On the ‘Insert’ tab, in the Charts group, choose the Insert Column or Bar Chart button, From the Insert Column or Bar Chart dropdown list, under 3-D Bar, select stacked type chart Column.
Now we will change the type of chart, first click on the chart then we will go to chart design tab ‘Change Chart Type’.
‘Change Chart Type’ will show the type of chart that we will change.
We will remove some things from the chart like title and values as well as delete the trailing walls.
Now we will change the chart from box to cylinder type. For this, first you have to click on the chart, by clicking you will go to the data series option, by changing the battery chart, you will change it in the cylinder.
Now we will customize the 3-d battery chart, go to the Format Chart Area option and edit the X rotation and Y rotation.
The color of all the parts will be customized, here we will color the top dark brown, light brown for the bottom cap, target value 20, keep the value to show below the target value and color red, red the remaining part with 75% transparency.
Formatting error occurs if the current value of the battery is changed.
Now we will click on the chart and when it goes above the target point, it will turn green. keep the value to show up the target value and color green, green the remaining part with 75% transparency. Also add label which we have already done.
Now we will change the value of the cell, then the color of the 3-d battery chart will also change.
To change the target value, it will change the color of the chart only when the value goes below the target value.
Therefore, I hope that you have understood How to make 3-D battery with conditional formatting in Excel, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
LEARN MORE DASHBORAD AND CHART TOPIC HERE
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to make 3-D battery with conditional formatting in Excel
